Simplify Your Code with Custom Fill Methods on DataSets

Entity Framework gets all the attention, but there's still something to like about ADO.NET DataSets. For instance, one reason that typed DataSets remain popular is that you can dramatically simplify your code by extending the DataSet with your own custom TableAdapter methods. All you have to be able to do is create an SQL statement in the Visual Studio query designer: Visual Studio will generate the code for you.

Ordinarily, to fill a table in a DataSet you need a half-dozen lines to open a connection, create an adapter (with its commands and parameters), set values in the adapter's parameters, call the adapter's Fill method, and then retrieve the resulting table (without, of course, forgetting to close the connection).

Update, insert, and delete statements aren't any simpler. What's especially irritating is that the code is virtually identical from one retrieval/update to another: really, only the SQL statement changes. With a custom TableAdapter method, though, you just call a method, passing the values required by your SQL statement.

When you create a typed DataSet, you get a TableAdapter with two methods on it for each table you drag into the DataSet: You get a Fill method that loads data into a DataTable but doesn't return anything, and a GetData method that fills the DataTable and then returns the DataTable to the calling code. As an example, here's the GetData method in action, retrieving all the rows in Northwind database's Orders table into a DataTable in a DataSet and then returning the DataTable:

Dim nwTB As New Northwind.OrdersDataTable
Dim nwtba As New  NorthwindTableAdapters.OrdersTableAdapter
nwTB = nwtba.GetData

To create your own GetData, Fill, or update method, first right-mouse click on your table in the TableAdapters area at the bottom of the table and pick Add Query. That will bring up the query wizard where you can choose between using an SQL statement to retrieve your data, select from existing stored procedures, or create your own stored procedure.

Assuming you select Use SQL Statements, you'll be taken to a page that allows you to select the kind of SQL statement you want (everything from Select to Insert statements). After that choice, you'll be shown a default SQL statement you can modify. If you're not confident about your SQL writing skills, click the Query Builder button at the bottom of the dialog to be taken to Visual Studio's form-based query builder.

Once you've finished writing your SQL statement, the wizard will take care of writing all the ADO.NET code for you—all you have to do is name the method that will be added to your TableAdapter. For SQL statements, the wizard even generates a Fill and a GetData method. So to call a SQL statement that fills a DataTable with orders for a particular customer and hands the DataTable back to you, you just need this code:

Dim nwTB As New Northwind.OrdersDataTable
Dim nwtba As New  NorthwindTableAdapters.OrdersTableAdapter
nwTB = nwtba.GetDataByCustomerID("ALFKI")

If you're paid by the hour, feel free to write the ADO.NET code. But this is the best kind of code-generation solution, because it allows you to concentrate on the one part of the code that matters -- the SQL statement -- while the boilerplate code is taken care of for you.

Posted by Peter Vogel on 10/09/2012 at 1:16 PM0 comments


Open PDF files Where (and How) You Want from a URL

It's not unusual to include a link in your page with a URL that points to a PDF file to let your users view the file on their computer. However, you can also control, through the URL, where and how the PDF file is displayed: just add a hash/pound sign (#) and some parameters to the PDF's URL.

For instance, the page parameter lets you open the PDF file at a specific page. This example opens the file at page 3:

http://mysite.com/userguide.pdf#page=3

You can also set up a search term for the user and position the view on the first appearance of the term (with the term highlighted) using the search parameter. This example searches for the word "test":

http://mysite.com/userguide.pdf#search=test

You're not limited to just positioning the user in the document. You can also specify how the document is to be displayed by using the view parameter. You can also combine parameters using the ampersand (&). This example repeats the search from the previous example but also fits the whole page into the view:

http://mysite.com/userguide.pdf#search=test&view=fit

Additional parameters let you turn on (or off) toolbars, panes, and draw highlight boxes around arbitrary text. Adobe has a document (in PDF, of course) describing all of the options.

Thanks to Larry Raby for letting me in on this!

Posted by Peter Vogel on 10/03/2012 at 1:16 PM0 comments


Easier (and Free) Keyboard Shortcuts with VsVim

A reader responded to an earlier tip on Visual Studio keyboard shortcuts by recommending VsVim, by Jared Parker. If the idea of never taking your fingers off the home row of your keyboard again, never having to use a menu, and never, ever reaching for the mouse sounds like heaven to you, then Jared's right: you should be looking at VsVim.

If using the letter d to delete, using uppercase D to delete to end of line, and using dd to delete a whole line is something you'll remember, then you're probably a veteran Unix programmer. In that case, you'll find the Visual Studio focus on WIMP (Windows, Icons, Menus, Pointer), well, wimpy. VsVim will put you back in control.

If you're as old as me, you remember vi as the world's most powerful keyboard editor -- the populariser (if not the inventor) of the Ctrl-Alt-Shift-meta-Coke-Bottle keyboard shortcut combination. You could do anything with vi if you knew the right keyboard combination. I've never been a keyboard-oriented person, but even I grew to have a nagging affection for the power of vi's keystroke combinations -- and my co-workers who were keyboard oriented loved what they could do with vi without taking their hands off the home row keyboard.

Vi gave birth to vim (vi iMproved), which added mouse support and even more editing commands. Now there's VsVim, which delivers vim functionality as a Visual Studio add-in. You can get VsVim from the Visual Studio Gallery site, or through Extension Manager from within Visual Studio.

It's an excellent package: reliable, quick, and it doesn't seem to fight with other add-ins (I used it with ReSharper installed, for instance). It's not overbearing in re-assigning your keys, so while you'll pick up lots of new keyboard commands, you shouldn't lose any of the ones you're used to. From the time I spent playing with it, it looks like a great tool for the keyboard oriented (and a free one).

However, VsVim is also a single-developer project and you might be more comfortable with a product that has a whole company behind it. If so, you should check out ViEmu. ViEmu adds more vi-related features and also works in Outlook and SQL Server (but costs actual money).

Posted by Peter Vogel on 09/25/2012 at 1:16 PM0 comments


Visual Studio Tip: Cleaning Up the Template Lists

Do you get tired of scrolling through the New Project Item lists to get to the item templates that you need, while skipping the ones that you'll never use? The same is probably true, though to a lesser extent, with the New Project dialog.

Why not slim those lists down to the half-dozen items you actually use that will, as a result, be right there when you need them? This probably goes against the grain of most developers' attitude towards new technology, but let's face it: you (and your organization) have probably figured out which templates you are and aren't going to use in Visual Studio.

The templates in the New Project and New Item dialogs are zip files kept in the C:\Program Files\Microsoft Visual Studio versionNumber\Common7\IDE\ItemOrProjectTemplates\language folder (this does vary from one installation to another. Just keep looking -- they're in there somewhere). Before you delete the templates you don't want, copy the folder to someplace safe so that you can get back the templates you've deleted if you ever need them (and drop a text file in the folder to remind yourself where you put the original templates). Then clean house.

When you're done, make sure Visual Studio isn't running and, from the Visual Studio Command prompt, have Visual Studio rebuild its lists with this command:

devnv.com /installvstemplates

NEVER INTERRUPT THIS COMMMAND. Wait patiently for it to finish. When you start Visual Studio next you'll have all (and only) the templates you use.

Posted by Peter Vogel on 08/23/2012 at 1:16 PM2 comments


Massaging Bound Data in WPF and Silverlight

I was recently asked by a WPF programmer how to modify a value passed between bound properties in a XAML file. You can't, for instance, integrate math into a binding expression, as this example does, to divide the value returned by 2:

<Image Height="100" Width="100" Name="MyImage" Source="Me.jpg" 
     Opacity="{Binding ElementName=MySlider, Path=Value/2}" /&rt;
This doesn't work, either:
<Image Height="225" Width="300" Name="CurrentImage" Source="Me.jpg" 
     Opacity="{Binding ElementName=MySlider, Path=Value}/2" /&rt;
While XAML gives you a lot of options when binding a property on one control to something else, modifying the data isn't allowed; at least, not directly. The answer is to use a custom converter. Programmers usually create converters to handle converting the data type of one property to the data type to another property (though XAML will handle a lot of conversions -- numerics to strings, for instance --on its own). But you can also use converters to massage the data in any way you want. This example, for instance, binds the Opacity setting for an Image control to the value in a slider control:
<Slider Margin="10" Name="MySlider" Minimum="0.0" 
     Maximum="2" Value="0.5"/&rt; 
<Image Height="100" Width="100" Name="MyImage" Source="Me.jpg" 
     Opacity="{Binding ElementName=MySlider, Path=Value}" /&rt;

Let's say that you want the transparency to be one-half of the value in the Slider: a converter will solve the problem. This converter, for instance, divides the number passed to it by 2 in its Convert method:

[ValueConversion(typeof(int), typeof(int))]
public class Divider : IValueConverter
{
    public object Convert(object value, Type targetType, 
        object parameter, System.Globalization.CultureInfo culture)
    {
        return object/2;
    }
    public object ConvertBack(object value, Type targetType, 
        object parameter, System.Globalization.CultureInfo culture)
    {
        return object * 2;        
    }
}

You need to do three things to use your converter. First, define a namespace for your converter (I've assumed that the converter is in the same project as the XAML file and the namespace for the project is MyApp):

<Window x:Class="MyClass"
  …
  xmlns:current="clr-namespace:MyApp"&rt;

Then you have to add your converter to your resources. I'm adding this converter to the Window's resources with the key MyDivider:

<Window.Resources&rt;
  <current:Divider x:Key="MyDivider"/&rt;
</Window.Resources&rt;

Finally, invoke your converter by passing the resource's key to the StaticResource object's constructor to retrieve it from the resources collection, and then passing that result to the Binding object's Converter property, like this:

<Image Height="100" Width="100" Name="MyImage" Source="Me.jpg" 
       Opacity="{Binding ElementName=MySlider, Path=Value 
               Converter={StaticResource MyDivider}}

Posted by Peter Vogel on 08/09/2012 at 1:16 PM3 comments


Visual Studio Tip: Create Your Own Keyboard Shortcuts

Probably the most popular tip has been in this column has been the one on four Visual Studio keyboard shortcuts. But if you want, you can create your own shortcuts for any Visual Studio commands you use a lot. Of course, most commands are already assigned to keyboard shortcuts, but this feature also lets you define a command to some key combination you'll actually remember.

To create your own shortcut, go to Tools | Options | Environment | Keyboard. From the listbox in the middle of the resulting dialog, select the Visual Studio command to which you want to tie your keyboard shortcut (there are lots of commands, so the search function that's provided can be helpful here). Then click in the Press shortcut keys textbox and press the key combination you want to use for the command. Click the OK button and you've created your own shortcut.

Posted by Peter Vogel on 07/29/2012 at 1:16 PM2 comments


Simplify WPF or Silverlight and Prism/Unity Code with Generic Methods

In an earlier column I discussed using the Register and Resolve methods with Microsoft's Unity dependency container. However, if you've got all of Visual Studio's hotfixes applied, you'll have access to some overloaded and generic versions of those methods. For instance, for the Resolve method, I used this version:

res  = cont.Resolve(typeof(ICustListVM),"DefaultCustListVM");

The generic version moves the first parameter (the type of class you're looking for) into the generic:

res  = cont.Resolve<ICustListVM>("DefaultCustListVM");

In addition to saving you some typing, it can also save you some type casting. The non-generic version of the Resolve method always returns type object, but the generic version returns the type specified in the generic.

There are also several overloads for the Register method, so you don't have to pass as many parameters as I used in the article. In the article, I used this wordy version:

ContainerControlledLifetimeManager cclm = 
   new ContainerControlledLifetimeManager();
cont.RegisterType(typeof(ICustListVM),
     typeof(CustListVM), "DefaultCustListVM", cclm);

With the hotfixes installed, you can just use this:

cont.RegisterType(typeof(ICustListVM),"DefaultCustListVM");

And, if you use the generic version, you can get away with as little as this:

cont.RegisterType<ICustListVM>("DefaultCustListVM");

Posted by Peter Vogel on 07/17/2012 at 1:16 PM0 comments


Command History in the Command Windows (and Fast File Names)

Up until a few weeks ago, the only way I knew of to get back to an old command in the Command Prompt was to press the Up arrow and walk back through the commands. It turns out that if you press F7 in the Command Prompt, a window pops up showing all the commands you've executed, and you can cycle through them with the Up or Down arrows (you can't edit the commands, though).

Speaking of editing commands: The most common error I make when entering commands is misspelling file or folder names. It turns out that I don't have to type in a full file name in the Command Prompt. If I type my command, followed by the first letters of the file or folder name I want and just keep hitting the Tab key, I'll cycle through all the file and folder names in the directory. I can even use wildcard characters (e.g. c*.DLL to find all the DLLs with names beginning with the letter C) with this trick.

And did you know that if you click on the icon in the left-hand corner of the Command Prompt title bar and select the Edit choice, you can cut, copy and paste in the Command Prompt?

Posted by Peter Vogel on 07/08/2012 at 1:16 PM5 comments


Comparing Anonymous Objects in C# and VB

Trust me: There's actually a tip at the end of this column, but it's going to take me awhile to get there. Be patient -- I do have a point and I am getting to it.

When it comes to comparing objects, what .NET considers the same isn't what normal human beings consider the same. Consider this code that creates two Customer objects, both representing Customer A123:

Dim cust1 As New Customer("A123")
Dim cust2 as Customer
cust2 = cust1

In VB, if I try to compare the two objects using this syntax, I get a compile time error:

If cust1 = cust2 Then

I can, however, use this syntax and the result, unsurprisingly, is True:

If cust1.Equals(cust2) Then

The test is true, however, only because the variable cust1 is pointing at the same object as the variable cust2. The .NET documentation refers to this as "reference equality." If, however, I create two separate objects, as this code does, and compare cust1 and cust2, the test returns false:

Dim cust1 As New Customer("A123")
Dim cust2 As New Customer("A123")
If cust1.Equals(cust2) Then

The test fails because the cust1 variable is pointing at a different object than cust2, even though the two objects, presumably, represent exactly the same customer entity and have identical values in all of their properties.

We, as human beings, might consider these objects to be "the same" because the objects represent the same data -- that's called "value equality" -- but we're not .NET. C# gives the same results as VB, but is willing to accept the x == y syntax.

Anonymous and Different
.NET uses object equality for most objects, but there are exceptions. For scalar variables and strings, for instance, .NET uses value equality. As a result, this test is true:

Dim num1 As Integer = 2
Dim num2 As Integer = 2
If num1 = num2 Then

Things are also different if you use anonymous objects -- but only if you're using C#. In C#, you might create two anonymous objects like this:

var x = new {  id = "A123", 
    CompanyName = "PH&V Information Services" };
var y = new {  id = "A123", 
    CompanyName = "PH&V Information Services" };

In C#, this test is false:

if (x == y)

But this test is true:

if (x.Equals(y))

For anonymous objects and with the Equals method, C# stops using reference equality and starts using value equality: C# compares the value of properties with identical signatures (names and types). But wait! It gets worse. In VB, the anonymous objects look like this:

Dim x = New With {.id = "A123", 
    .CompanyName = "PH&V Information Services"}
Dim y = New With {.id = "A123", 
    .CompanyName = "PH&V Information Services"}

The x = y syntax is still an error but this test, which looks just like the C# test, gives the same answer as with "un-anonymous objects" and remains false:

If x.Equals(y) Then

So, unlike C#, VB continues to use reference equality with anonymous objects.

Listen: It's not my fault.

The Tip (Finally)
This is, of course, an accident waiting to happen. The good news is that you can fix this difference in behavior (and, by "fix", I mean: get the almost identical behavior almost everywhere). As a bonus, it simplifies your VB code when you do want to compare two anonymous objects: Just use the Key keyword to identify to VB properties to be used when comparing anonymous objects. Using Key causes VB to switch to using value equality when comparing anonymous objects, just like C#.

This example flags just the id property in my anonymous object as providing the value to be used when comparing this anonymous object with another anonymous object:

Dim x = New With {Key .id = "A123", CompanyName …
Dim y = New With {Key .id = "A123", CompanyName …

The x = y syntax is still a syntax error, but x.Equals(y) now returns the same result as the equivalent C# test: True. And, actually, VB's approach is more flexible than C#'s, as you can choose what counts as equality: all of the properties on the object or some arbitrary collection. One caveat: If you're comparing anonymous objects in either C# or VB, they must have identical sets of properties or the comparison automatically fails.

Of course, in C# x == y still returns a different result than x.Equals(y) for anonymous objects.

Hey: There's only so much I can do.

Posted by Peter Vogel on 06/25/2012 at 1:16 PM8 comments


Creating Inheritance Relationships in Entity Framework

Far too many developers are under the impression that the business entities that Entity Framework (EF) creates in database-first mode are the business entities you're stuck with: one entity = one table. That's not true; and when you need a more sophisticated design, you can create that.

For instance, imagine a Customers table that contains several different kinds of customers (e.g. "premium" and "ordinary" customers") -- usually flagged by some kind of ‘CustomerType' column. You could create a single class with methods filled with "If…then" statements that check the CustomerType property and run the right code.

Or you could create two classes: a PremiumCustomer entity class with all and only the code/properties that relate to premium customers and a TypicalCustomer entity class with all and only the code/properties that relate to a TypicalCustomer. The code and properties that are common to both sets of customers would stay in a base Customer entity that the other two classes inherit from.

The beauty of EF is that it would manage all the updates made through the three entities back into the single table. If you tell EF about the CustomerType column, it will ensure that the right object is created when retrieving rows in the table, and update the column correctly when you create a new entity.

To implement this design, first let the EF wizard generate your its default entities from your tables. Then right-mouse click on the EF designer and select Add | Entity.  In the resulting dialog, give your new entity a name (e.g., "PremiumCustomer") and specify that it inherits from an existing class (e.g., "Customer") using the Base type dropdown list. You can then cut and paste properties from one class to another (e.g. does the "DiscountRate" only apply to PremiumCustomers? Then select that property in the Customer class, cut it, and paste it into the PremiumCustomer's properties). 

You won't be instantiating the original, base class entity in code anymore, so select it, and in its Properties Window, set its Abstract property to True (i.e. from now on I'll use PremiumCustomer or TypicalCustomer, but not Customer, so I make the Customer entity abstract).

Now you need to tell EF about your type column so it will know when to create the right object. Right-mouse click on one of your new classes (e.g., PremiumCustomer) and select Table Mapping to display the mappings between table columns (on the left) and entity properties (on the right); right now the mapping table will be empty. Click on <Add Table or View> and select your table (e.g., Customers) to tie the table your new entity. Click on the new <Add a Condition> choice and select your type column. In the Value/Property column, enter the data that identifies the row (if it's a string value, make sure you put quotes around the value). Repeat for your other new entities. Finally, delete your type column from your base table -- EF will take of making sure it's set correctly.

And you're done! You now have several classes managing a single table, but the right class will be generated for you at the right time by EF (and, when it adds new rows to your table, it will make sure that your type column is updated correctly). You also have less logic to design because you just have to put the code appropriate for each entity in each of your entities.

Posted by Peter Vogel on 06/21/2012 at 1:16 PM0 comments


The Incredibly Useful Sysinternals Suite

If you like knowing what's going on with your computer -- even if it doesn't have much to do with .NET development -- you'll like the Sysinternals kit from Microsoft. The Sysinternals kit bundles up almost 70 utilities that extract information from Windows. And, while not aimed specifically at .NET developers, it's surprising (to me) how often I've found some of these tools useful when building business applications.

For instance, if you place BigInfo in your StartUp folder, it adds a bitmap to your desktop with your computer's TCP/IP address, OS version, service pack, and last time the computer was rebooted (among other information). I include BigInfo as a matter of course on all my development VMs and test machines.

One of my clients found that, after installing a Windows Service I built for them, that the service gradually took over the whole computer. We used RamMap to track how memory was being allocated by Windows and discovered that, while my service was taking over the whole computer, it was because the computer had nothing else to do. Given another application to run, Windows gave that other application the memory it needed.

If you've ever wondered exactly what DLLs a running process is using -- or what processes are using a particular DLL -- ListDlls will tell you. I was trying to replace a DLL on a computer that some process owned and it was ListDLL that identified the process. I then used another utility in the suite, PsKill, to end that process (PsKill also has the power to kill processes on other computers -- a power I've never used but have really, really wanted to, on occasion).

I will admit that I've never used MoveFile or AutoRuns for development purposes. But MoveFile will tell you what files are going to be updated after your next reboot (for instance, after an installation program tells you have to reboot), so you can make a semi-informed decision about putting off the reboot.

AutoRuns gives you a complete list of what programs are set up to run when your computer starts up, including items from the Startup Folder and programs being executed through the various Run* registry keys. I'm only interested in third party tools when I use AutoRuns, so I use the –m option to hide the Microsoft applications.

I used Strings (which lists all the Unicode and ASCII strings embedded in EXEs or DLLs in a folder, or its subfolders) against a third-party DLL once. Strings let me track down all the config files the application was using -- something not obvious from the documentation.

There's lots more goodies in the suite, but if you want to run one of them without going to the trouble of downloading it, you can use Systinternals Live. In Windows Explorer (or a command prompt), just enter \\live.sysinternals.com\tools\ followed by the name of the tool you want.

Posted by Peter Vogel on 06/14/2012 at 1:16 PM0 comments


4 Must-Know Visual Studio Keyboard Shortcuts

Here are four keyboard short cuts for things you probably do often in Visual Studio.

  1. You want to add a blank line after the line you're on. You don't have to go to the end of the line and hit the <Enter> key. Just leave your cursor where it is and use Ctrl+Shift+Enter (Ctrl+Enter adds a line above).

  2. You want to uncomment a block of commented code. You don't have to select the whole commented block. Just put your cursor somewhere in the commented block and type the uncomment chord: Ctrl+K, U.

  3. You've got an error on line and see the little red bar that marks the SmartTag that provides the options for fixing the problem. You don't have to fiddle with the mouse to display the dropdown list of options. You can just type Ctrl+. (You don't even have to move your cursor back to the line in error).

  4. You type an object name, type a period, type the first letter of the member you want and get an IntelliSense list with lots and lots of entries. You don't have to type the whole name of the member you want. IntelliSense in Visual Studio 2010 recognizes camel-casing, so if you hold down the Shift key and type the capitalized letters in the member name you want, IntelliSense will give narrow the list down to that one.

Posted by Peter Vogel on 05/31/2012 at 1:16 PM18 comments


Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.