The .NET Dataset for Stored Procedures in Visual Studio – the easy way

In case you need to call or retrieve data from a lot of stored procedures in SQL Server. You basically have two options:

A) “Hardcore”

You create your own class and define a SqlCommand object for each stored procedure manually. While doing this, tell your wife you can't see her for the next weeks

B) “Lazy man”

You use a dataset and let Visual Studio do the hard work. You use those generated procedure, tell your boss how many hours this took and leave early to enjoy your weekend.

The last option seems to be better IMHO, but the DataSet has some funny glitches that can make using it a pain. I’ll try to sort them out here.

Step 1: Create a dataset

Easy, just right-click your project, select “Add item” and choose “Dataset”. In this case, the dataset is called “DataSetStoredProcedures”.

Step 2: Move the generated dataset

To not clutter the project, create a new folder (right-click project, “Add” -> “New Folder”) called “DatabaseDataset”. Drag and drop “DataSetStoredProcedures.xsd” there.

Step 3: Add stored procedures

Select “View” -> “Server Explorer” to display the Server Explorer window. Right-click “Data Connections” and select “Add Connection”. Define the properties so Visual Studio can connect to the database. Open the new entry in the tree view and move to “Stored Procedures”.

In case you haven’t opened DataSetStoredProcedures.xsd, simply double-click it. Simply drag and drop the stored procedures you want to use to this window.

You may be asked how the connection should be called that will be used to execute this stored procedure, simply choose a name that makes sense to you. Once this all has being done, the project should look something like this:

Step 4: Check the stored procedures

At any time, you can simply click on a stored procedure, open the properties window and click on the “…” button for the Parameters:

You should do this simply because sometimes the generation is not done right. For example, in this project there is a user defined function (yes, you can also add UDFs) called “fncLTC2UTC” that return an SQL Server datetime value. However, the dataset converted this to “Object”. By using the parameters collection, you can simply change the value of @RETURN from object to DateTime.

Step 5: The glitches, Part I

By using the object browser, you can have a look at the namespace layout:

The project where we have added all this is called “x.server”. Because we have created a subfolder to put the dataset in, the namespace of the dataset has become “x.server.DatabaseDataset”. Beside this, the stored procedures where create in a sub object “QueriesTableAdapter” that has the namespace “x.server.DatabaseDataSet.DataSetStoredProceduresTableAdapters”:

Now this is a hell of a namespace we need to add it before we can call a stored procedure:

using x.server.DatabaseDataset.DataSetStoredProceduresTableAdapters;

class JustTesting
{
public static void Test1()
{
QueriesTableAdapter qta = new QueriesTableAdapter();
qta.procClientAuthentication_GetLoginData(…

Step 6: The glitches, Part II

Beside this namespace glitch, there is another one: By default, the dataset insists on using the connection string that each stored procedure has attached to (Properties viewer) and you can not to change this. In case you do not want to have the database connection inside app.config this would normally stop the use of the dataset.

Step 7: The solution

However, both glitches can easily be fixed. Within your normal namespace (e.g. “x.server”) simply define a class like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using x.server.DatabaseDataset.DataSetStoredProceduresTableAdapters;

namespace x.server
{
///

/// Shortcut class to all database stored procedures (procXXX).
///
public class DatabaseProcs:QueriesTableAdapter
{
public DatabaseProcs()
: base()
{
string sConnString = “Your Connection string here”;
foreach (IDbCommand cmd in CommandCollection)
{
cmd.Connection.ConnectionString = sConnString;
}
}
}
}

This class solves the two glitches: You need to reference the namespace only in this class (DatabaseProcs), all other objects that will use this class do not need to do it.

Second, DatabaseProcs is derived from QueriesTableAdapter so it can use the protected property “ConnectionString” and change it to anything you want.

With this class in between, calling a stored procedure is quite simple:

using System;
using System.Collections.Generic;
using System.Text;

namespace x.server
{
class JustTesting
{
public static void Test1()
{
DatabaseProcs procs = new DatabaseProcs();

procs.procClientAuthentication_GetLoginData(…

No more stupid namespaces and you your custom connection string is also set.

Note

The generated class will also leave the connection in the same state as it was before. This means, when you use the class DatabaseProcs (aka “QueriesTableAdapter”) like this, a connection will be opened when you execute it and closed right after that.

This might lead to a performance issue if you do not use connection pooling. To enable connection pooling simply use a connection string like this:

Data Source=(local)\SQLEXPRESS;Initial Catalog=X;Integrated Security=True;Pooling=True;Min Pool Size=0;Max Pool Size=5;Application Name=MyApp

If pooling is activated, a Connection.Close() does not actually close the connection but instead it will put the connection in the pool and reuse it when the next request to Connection.Open() comes in.

Enjoy!