The first thing we need to do is to create the actual dataset. There are a number of ways to accomplish this, however I have found that the most straightforward way is to right click your solution, select Add… then select Dataset from the list.

Datasets can contain many tables or queries or both, I tend to create a dataset per table unless I need to work with joins or multiple tables at once.

There are also a number of options involved in creating a dataset, we are going to explore several of them in this series. The most significant decision you will need to make is whether or not to base your dataset on Stored Procedures or Queries.

In this first example we will be using Queries. But first we need to create the dataset. Once you have selected Dataset you should give it a name, I typically use the table name it is going to be based on, in this example, Employees.xsd

The Dataset Designer will appear, a mostly blank screen that we will drag objects from Server Explorer onto. Go to the Server Explorer window, if it is not visible you can pull down the View menu and select Server Explorer. You should see something like Figure 1:

The Data Connections node is what we are going to work with, right-click it and select the Add Connection… option. Fill in the resulting dialog to connect to your instance of SQL Server and make sure the database selected is “pubs”.

Expand your connection and you will see a listing like Figure 2:

As you can see we now have access to a number of items within the database itself, most importantly for this example, the Tables node.

Expand the Tables node and drag the ‘employee’ table onto the dataset designer surface. By default you will get an ‘employee’ dataset and an ‘employeeTableAdapter’ with two methods, Fill and GetData().

Behind the scenes a great deal of code has been generated for you, or will be once you press Save. But we don’t want to accept the defaults, so we are going to make some adjustments.

The first thing you should know is that the process of dropping the table onto the designer surface persisted the connection you created in you applications app.config file. It will have created the app.config file if one did not already exist.

In a production application you would want to alter this connection string to match the production servers connection parameters as well as probably encrypt it. For this example I would suggest that you open your app.config file and rename the connection to “pubs”. My ConnectionStrings section contains the following:

    <connectionStrings>

        <add name="pubs" connectionString="Data Source=DELLXPS;Initial Catalog=pubs;Integrated Security=True"

            providerName="System.Data.SqlClient" />

    </connectionStrings>

I will show you later how to use this renamed connection string as opposed to the one the dataset will be expecting, and explain why I do that.

Next, we need to tweak some of the defaults created for us by the designer. Right-click the employee Dataset and select Configure from the context menu.

You will see a TableAdapter Configuration Wizard pop up. It will show you the query it is using to build and populate the dataset. Click the “Advanced Options…” button and make sure all 3 checkboxes are checked. They usually are by default. The first option tells the designer to create Insert, Update and Delete statements that match the Select statement used.

The second option tells the designer to Use optimistic concurrency which adds intelligence to the Update and Delete statements to allow them to detect if the record(s) they are about to Update or Delete have been modified since they were populated.

The final option, “Refresh the data table” fires a Select after every Insert and Update statement in order to retrieve calculated fields, default values and identity values that are generated by the database.

Once you have done that, return to the Wizard and select Next, you will get the Choose Methods to Generate screen. You will see 3 options, the first two are what we are concerned with for now.

All 3 should again be checked, modify the names of the methods to better reflect what they are doing, in this case returning all employees. See Figure 3 below for an example:

Press next once more and you will get Wizard Results screen that shows that the designer has generated all the code, queries and methods we asked it to.

Review the results, then press Finish to return to the designer. Your dataset should now look something like this in the designer:

Notice the highlighted section, this shows that the method names have changed like we wanted.

Thats it! You have now created your first strongly typed dataset.

Next up, we will discuss what exactly this does for you, and how you would use it in code and what advantages you gain from using this construct.

Stay tuned!

Bob Porter