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
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using x.server.DatabaseDataset.DataSetStoredProceduresTableAdapters;
{
///
/// 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.
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.
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!
