Taking the advice of Scott Hanselman today. It's a code garage sale. Everything of worth (you be the judge!) must be opened up and freed for WAY below cost.
SQL Server ADO Abstractor/Wrapper
never write a line of ADO.NET setup code again!
The first offering is a .NET class that you can reference in any project that needs to call SQL Server stored procedures. It takes care of spinning up the ADO.NET objects and closing them (mostly) when appropriate.
Use it to:
- ExecNonQuery
- ExecScalar
- ExecDataReader
- ExecDataSet
All you've got to do is supply it with:
- a connection string (as managed by your own datalayer and/or app.config. Hardcode a string into it, go ahead, see if anyone cares),
- a stored procedure name
- any parameter names and values that you need.
Another great feature here is that this class implements the IDisposable interface. This means you can use it with the Using statement/construct, and it will go out of scope when execution steps out of the Using statement/construct. This saves you a line or two of code.
Here's some sample code showing how to use it:
C#
1: // run a non-query stored procedure
2: try{
3: using (SqlAdoAbstractor db = new SQLDataHelper(someConnectionString))
4: {
5: db.AddParam("@MyNumericIdentifier", 1);
6: db.AddParam("@MyString", "Hello World");
7:
8: db.ExecNonQuery("MySproc");
9: }
10: }
11: catch (Exception ex) { throw; }
12:
13: // get a datareader
14: try{
15: using (SqlAdoAbstractor db = new SQLDataHelper(someConnectionString))
16: {
17: db.AddParam("@SomeNumericIdentifier", 1);
18:
19: SqlDataReader dr = db.ExecReader("ListAllEmployeesByRegionID");
20:
21: //iterate through your datareader as necessary
22: while (dr.Read()){
23: //dr["EmpID"].ToString();
24: }
25: dr.Close(); //you must close the open reader
26: }
27: }
28: catch (Exception ex) { throw; }
29:
VB
1: Using help As SqlAdoAbstractor = New SqlAdoAbstractor(someConnectionString)
2: help.AddParam("@MyNumericIdentifier", 1)
3: help.ExecNonQuery("MySproc")
4:
5: End Using
6:
7:
8: Using help As SqlAdoAbstractor = New SqlAdoAbstractor(someConnectionString)
9: help.AddParam("@MyNumericIdentifier", 1)
10: Dim dr As SqlDataReader = help.ExecReader("MySproc")
11:
12: While dr.Read
13: 'dr("EmpID")
14: End While
15: dr.Close 'you must close the datareader yourself
16: End Using