SQL Server Stored Procedures Class

Wednesday, December 8, 2004 8:44:00 AM

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. 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!

2 Comments

Comments have been disabled for this content.