The last days I had to build a web application with a lot of stored procedures to a SQL Server database. Because I had to change parameters very often I decided to write a simple wrappr for these stored procedures.

You can download the current version at http://www.schwarz-interactive.de/sqlsp.zip ^[1]. There you will find a sqlsp.exe which you have to call with several arguments:

sqlsp.exe /d:Northwind /n:Microsoft.Northwind /c:StoredProcedures /o:Northwind.cs

The argument /d will specify the database on the local SQL Server. To change the server you have to add the argument /s:server. The /n command will change the namespace to "Microsoft.Northwind". The class name will be "StoredProcedures" and the output filename will be "Northwind.cs".

Here you can see a code fragment of the code that will be generated:

using System; using System.Data; using System.Data.SqlClient;

namespace Microsoft.Northwind { public class StoredProcedures : IDisposable { private SqlConnection conn = null; private Exception m_LastError = null;

public StoredProcedures(string connectionString) { conn = new SqlConnection(connectionString); }

region Public Properties

public Exception LastError { get{ return m_LastError; } }

endregion

public bool Ten_Most_Expensive_Products(ref DataSet ds) { SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn); cmd.CommandType = CommandType.StoredProcedure;

try { conn.Open();

try { SqlDataAdapter da = new SqlDataAdapter(cmd); if(ds == null) ds = new DataSet();

da.Fill(ds); } catch(SqlException ex) { m_LastError = ex; return false; } finally { conn.Close(); } } catch(Exception ex) { m_LastError = ex; return false; }

return true; }

[...]

If you are using Output variables in your stored procedure you don't get a DataSet as result. The Output variables are used by reference and are filled after calling the procedure:

public bool AddAccount(string Username, string Password, ref int UserID) { SqlCommand cmd = new SqlCommand("AddAccount", conn); cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 120).Value = Username; cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 80).Value = Password; cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID; cmd.Parameters["@UserID"].Direction = ParameterDirection.InputOutput;

[...]

At the moment not all database types are working. If you get any error or if you have questions or remarks please contact me!