Monday, August 27, 2007

First Experiences With VS2K8 Part 5 (Data Shaping)

This is the fifthin a series of posts I'm going to be making about my experiences with Visual Studio 2008 and .NET3.5. You might also be interested in the other posts which are linked at the right. This is another quick hit post to talk about something I've been doing a lot of with LINQ that I think is pretty damn powerful. MS calls is data shaping and the basic idea is that you can use LINQ queries to 'reshape' your data in order to make it easier to work with in a given context. That's probably not too helpful a description without an example so here goes. Let's say in my order application I need to create a quick reporting page that lists all of our customers' order totals. The page needs three columns: cust id, cust name and total for all orders. For the purpose of this illustration let's assume that I've already got a method that returns a Dictionary where the keys are my customers and the values are the sum total of each customer's orders. Without LINQ I could simply bind a GridView to this Dictionary and then specify a ColumnTemplate for cust id, cust name and orders total. With LINQ however I don't even need to specify those ColumnTemplates. Instead I can use LINQ to shape the data before I bind like so: var query = from cust in custOrdersDict.Keys select new { ID = cust.Id, Name = string.Concat(cust.FirstName, " ", cust.LastName), OrdersTotal = custOrdersDict[key].ToString("C") }; I then bind my plain vanilla GridView to 'query' and I get exactly what I want. My column headers are 'ID', 'Name' and 'OrdersTotal' and the values in the columns are formatted just how I specified. Name is a concatenation of first and last name and the OrdersTotal value is formatted as currency. This seems quite useful to me because now my GridView is not so intimately tied to the data it is being bound to. For instance if I needed to add City/State info to the report I would not have to touch my GridView at all. I would simply modify my LINQ query as follows: var query = from cust in custOrdersDict.Keys select new { ID = cust.Id, Name = string.Concat(cust.FirstName, " ", cust.LastName), Location = string.Concat(cust.City, ", ", cust.State), OrdersTotal = custOrdersDict[key].ToString("C") }; And now when I bind I get a new Location column. And I didn't have to mess with my GridView at all. Pretty neat!

Monday, August 20, 2007

First Experiences With VS2K8 Part 4

This is the fourth in a series of posts I'm going to be making about my experiences with Visual Studio 2008 and .NET3.5. You might also be interested in the other posts which are linked at the right. This is just a quick hit entry because I'm deep in the middle of my project now but I just figured something out that I want to share. One of the screens in my order taking application needs to allow the user to choose one or more orders from a list and then display those orders with all of their header information. The SQL equivalent would be something like: select * from OrderHeaders where OrderID in (list of user-selected ints) The trouble I ran into is that LINQ doesn't have an 'in' keyword. After a good deal of searching I finally figured out how to do it. LINQ doesn't have an 'in' keyword but it does have a 'Contains' method. Here is how you would use it: int[] orderIDs = getSelectedOrderIDsFromForm(); OrdersDataContext db = new OrdersDataContext(); var query = from oh in db.OrderHeaders where orderIDs.Contains(oh.OrderID) select oh; It's a little ass-backwards from the SQL way in that rather than in SQL we say we want OrderHeader records that contain the selected IDs. Here it sounds more like we're asking the list of IDs for matches instead. But using the Sql Profiler I see that the generated SQL is a normal query with an IN clause just like I would have written if I was writing the SQL myself. A little obtuse but pretty cool nonetheless.

Wednesday, August 15, 2007

First Experiences With VS2K8 Part 3

This is the third in a series of posts I'm going to be making about my experiences with Visual Studio 2008 and .NET3.5. You might also be interested in the first and second posts. At the end of the last post I had successfully created my basic data model and used it on a web page to display a list all of my customers. The problem was that the list really displayed ALL of my customers and the page was way too long. Nobody would want to sit there and sift through all of the rows much less wait for them to download so I figured the next step would be to add sorting and paging. Luckily for me this couldn't be easier. I simply set the AllowPaging and AllowSorting properties of the GridView to true and everything else was taken care of. I also used the AutoFormat functionality on the GridView to select a simple display style. I chose Classic. I now had a GridView that was displaying all of my Customer data in a fairly accessible way and it didn't look half bad. Since our phone operators would be taking calls from customers placing orders the first step in the system should probably be to find a customer and now I had a way to do that. But it still wasn't ideal. A user would have to page through customers to find the customer they wanted. Of course a search box would solve the problem. And I decided a little associated AJAX magic would make for a really nice UI. The search box would work sort of like an autocomplete box but instead of a drop down appearing with likely candidates when the user started typing I decided to populate the whole GridView with the likely candidates. I also decided not to start the autocomplete operation until after at least 3 characters had been typed. First of all let me admit that I couldn't figure out how to programmatically configure the LinqDataSource's where clause the way I needed to so I decided to ditch it. I deleted that control and the GridView's reference to it. Then I was ready to proceed with getting the autocomplete working. To accomplish this I first added an HTML TextBox Input element to the page. I didn't use the ASP:TextBox control becuase it's easier to set the 'onKeyUp' event on the HTML version. I did however set the input element's 'runat=server' property so that it would be easy to access the value of the box in my code behind. For the 'onKeyUp' property I set it to call a javascript function called 'onKeyUp()'. I'll get to that function in a bit. Next I wrapped my existing GridView with an UpdatePanel and its associated ContentTemplate. I set the onload property of the UpdatePanel to call the server side method "updatePanel1_Load". This is to facilitate the auto-complete functionality. Here's an explanation of how that works: The search textbox has a function called 'onKeyUp()' assigned to its 'onkeyup' event. This function is defined as: function onKeyUp() { var tb = $get('<%= searchTB.ClientID %>', null); if (tb.value.length > 2) { __doPostBack('<%= updatePanel1.ClientID %>', ''); } } This javascript function executes on the client. It gets a reference to the search textbox. It checks the length of the value there and if it's longer than two characters it triggers an AJAX postback that appears to come from the UpdatePanel. Notice that the ids of the search textbox and the UpdatePanel are being generated at runtime by the ASP engine. This is because ASP.NET prepends the IDs of containing controls onto the ID of their children controls. (So if the textbox's ID is 'searchTB' but it's in an UpdatePanel called updatePanel1 its rendered ID at runtime will be updatePanel1_searchTB, not just searchTB. Every control though has a ClientID property which returns the rendered value of the ID rather than that assigned to it programmatically.) The postback operation triggered by the above call will then cause the UpdatePanel's OnLoad event to fire and since we assigned the server side method 'updatePanel1_Load' to the onload property this method will now fire. It's contents are: protected void updatePanel1_Load(object sender, EventArgs e) { string searchTerm = searchTB.Value; if (searchTerm.Length < 2) { return; } OrdersDataContext db = new OrdersDataContext(); var query = from cust in db.Customers where cust.lastName.StartsWith(searchTerm) select cust; gv1.DataKeyNames = new string[] { "CustID" }; gv1.DataSource = query; gv1.DataBind(); } Put this all together and when the page loads it first presents the user with just a textbox. Once the user types three characters the client side script fires an AJAX postback and when that returns a GridView appears with all of the customers whose last names start with the letters in the text box. Each additional letter the user types further refines the results. In my next post we'll make this page a little more user friendly and then we'll look into giving the user a way to actually select the proper customer when they see the record on the screen.

First Experiences With VS2K8 Part 2

This is the second in a series of posts I'm going to be making about my experiences with Visual Studio 2008 and .NET3.5. The first post can be found here. In this post I will discuss how I set up my LINQ to SQL data model and how I started using it to build my order taking application. This is not intended to be a tutorial on the specifics of LINQ to SQL. For that I recommend ScottGu's excellent series of posts on the subject which can be found here. For now I've got two projects making up this solution. One is a Web Project (called OrderEntryApp) and is the site with that users will be accessing. The other is a class libarary and is called OrderDBLINQ. It contains the LINQ to SQL mapping and generated classes. For the OrderDBLINQ project I simply chose to add a new item and selected the Linq To Sql classes option. This created a designer view for me. I then opened the Server Explorer and dragged the tables I was interested in accessing from there onto the new designer view. This is a legacy database that was created by one of our vendors and for various reasons it would be quite difficult to change it. This is kind of lame since the db in question is not very well designed. Naming conventions are nonexistent and there isn't a single foreign key defined anywhere. But I figured this would make it a good test for LINQ to SQL. Since there aren't any foreign keys in my crappy db schema the LINQ to SQL designer was unable to infer the relationships of my new objects. This is only a minor stumbling block however since the designer offers tools for defining relationships between objects. The interface is quite similar to the one used by SqlServer to create foreign keys. Each order in my application is placed by a single customer so I created a relationship between the OrderHeaders table and the Customers table. An order header will have multiple detail items associated with it so I created a relationship between OrderHeaders and OrderDetails. Each OrderDetails entry references a single Product so I created a relationship between OrderDetails and Products. Finally there is a many-to-many relationship between Products and Categories. This many-to-many is defined in the ProductCategories table so I created relationships between Products and ProductCategories and also between Categories and ProductCategories. This all took me about 20 minutes and a good portion of that time was spent just figuring things out for the first time. All in all it was quite easy to do. But would the code generated from this diagram actually be able to service all of my applications needs? Well first I figured I'd start with a simple test page. In my OrderEntryApp web project I added a new AJAX Web Form item called Test.aspx. My current goal here was simply to bind a read-only GridView to my Customers table. The first step was to add a LinqDataSource object to my page and configure it. I was able to choose the DataContext object that was created by the code generator in my OrderDBLINQ project. Next I selected the Customers table from the drop down provided and left everything else at the defaults. Next I added a GridView to my page. The only property other than 'ID' and 'runat' that I had to set was DataSourceID. I set this to the ID of the LinqDataSource I'd just created and ran the page. Sure enough there on the page was a simple GridView with all of my customers' information listed. Pretty neat! Now this was a fairly simple test I suppose but it was also quite simple to implement. Certainly easier than doing this the old way. Of course now I had a gigantic web page listing every single customer in my database. My next step would be to add some tools to refine the list and add support for paging and sorting. And that will be the subject of my next post.

Tuesday, August 14, 2007

My First Post (First Experiences with Visual Studio 2008)

This is not only my first post but also the first post in a series of articles I intend to write about my experiences transitioning from Visual Studio 2005 to Visual Studio 2008 (and .NET 3.5). I'm doing this in the hopes that my stumblings will save others from having to do so in the future. My first and only experience with VS2K8 is with the recently released Visual Studio 2008 Beta 2 Release. I installed both it and the MSDN documentation from the same page. I installed the stuff a day or so after it came out but didn't really start playing around with it until a week ago when I got handed a new project that I figured would make a good test for 2K8. The two things I was looking forward to most were the improvements to the HTML editing side of things and LINQ (specifically LINQ to SQL.) I'd read so much about LINQ but hadn't yet bothered to download any of the earlier releases. And since I usually handle backend development in my job I was very interested to learn if and how LINQ to SQL might make my life easier. While I can't discuss the specifics of the project I was handed it is pretty similar to an order-taking application such as might be used by a phone representative to enter orders into a fulfillment system. It needed to be web based, support multiple users and have a reasonably slick UI. And no one cared what I wrote it in. So I decided why not dive into 2K8 and see what it had to offer? The application I'll be describing for my examples will be a pretty straightforward order entry system. It will be web-based and will utilize AJAX when called for (I hope.) There will be at a minimum a Customers table, an OrderHeaders table, an OrderDetails table, a Products table, a Categories table and finally a ProductCategories table that defines a many-to-many relationship between Categories and Products (a Category can have many Products and a Product can have many Categories.) In my next post I'll describe how I created a LINQ to SQL representation of my data model and how I started using that representation to access my database.