Use and Generate PUT Stored Procedures

To me, the "best practice" for DataAccess to a database is to always utilize Stored Procedures, if your database server supports them. Microsoft SQL Server, of course, supports Stored Procs as does Microsoft Access. I use SQL Server exclusively, so this blog post will be limited to that database server. It's easy enough to take the concepts shown here and apply the same methodology to an Access database, or to any other database that allows Stored Procs.

However, if you Google for information about the best practices involving Stored Procedures, you will, of course, find a gazillion conflicting opinions ... some agreeing with my opinion and others not. But still, I think it's an important best practice.

OK, now I'll get down off my soap box and on to the topic of my post!

I have a base DataAccess class, than can be used directly, or sub-classed to a domain use (for example, a large inventory system may have an OrdersDataAccess, an InventoryDataAccess and a CustomerDataAccess ... all subclassed from the BaseDataAccess). I have a series of DataAccess posts here in my blog, and I will show bits and pieces of it here in this post to illustrate my point. If you'd like to read these additional posts, you can use this search: http://geek-goddess-bonnie.blogspot.com/search?q=DataAccess Some of the earlier posts are incomplete, code-wise, but get "fleshed out" in later posts.

I see people on the Forums struggling with simple CRUD activity, trying to utilize the SqlCommandBuilder (usually incorrectly) and having all kinds of problems. SqlCommandBuilder is not the answer, in fact, I never use it ... there is an easier way to Insert and/or Update from a DataTable and do it by using the same Stored Proc (one for each table).  It's what I refer to as PUT procs, and the PUT Stored Proc will either INSERT or UPDATE depending on whether or not the Primary Key (PK) is included in the Parameters.

One caveat here ... I use the DataSet/DataTable paradigm for my DTOs (Data Transfer Objects). The same concepts can apply to POCO, with just different methods in the DataAccess class needed. (POCO means "Plain Old CLR Object which is defined here in Wikipedia: https://en.wikipedia.org/wiki/Plain_old_CLR_object as "a POCO does not have any dependency on an external framework and generally does not have any attached behaviour." ... but go ahead and read WikiPedia if you are unfamiliar with the term).

Here's a simple example of a PUT Stored Proc. I will show some methods you can use to create a Utility for generating such Procs automatically at the end of this blog post:

CREATE PROCEDURE [dbo].[bsp_StatusPut]
    @statuskey             bigint = NULL OUTPUT,
    @statusdatetime        datetime = NULL,
    @status                varchar(max) = NULL,
    @agency                varchar(100) = NULL,
    @messagetype           varchar(128) = NULL,
    @id                    uniqueidentifier = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRANSACTION
    IF (@statuskey <= 0 OR @statuskey IS NULL)
    BEGIN
        -- Insert Values into the Status table
        INSERT Status
            (
            statusdatetime,
            status,
            agency,
            messagetype,
            id)
        SELECT 
            @statusdatetime,
            @status,
            @agency,
            @messagetype,
            @id
 
        SELECT @statuskey = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
        -- Update the Status table
        UPDATE Status
        SET 
            statusdatetime = ISNULL(@statusdatetime, statusdatetime),
            status = ISNULL(@status, status),
            agency = ISNULL(@agency, agency),
            messagetype = ISNULL(@messagetype, messagetype),
            id = ISNULL(@id, id)
        WHERE statuskey = @statuskey
    END
 
    COMMIT TRANSACTION
    RETURN 0
GO

Now, as you can see, this SP doesn't have many parameters, and it would be easy to add them one-by-one. However, there's an easier way when you have a lot of columns in your table.  In my DataAccess class, I  have a set  of methods for dealing with Parameters. Here are the relevant bits of code from a BaseDataAccess class to go along with my example:

// This is not the entire class, see blog posts for more of the class
public class BaseDataAccess : IDisposable
{
    private IDbConnection oConnection;
    private IDbCommand oCommand;
    private IDbDataAdapter oAdapter;
    private IDataParameterCollection oParms;
    
    public string ConnectionString = "";
 
    public BaseDataAccess(string connectionString)
    {
        this.ConnectionString = connectionString;
        this.oConnection = new SqlConnection(connectionString);
        this.oCommand = new SqlCommand();
        this.oAdapter = new SqlDataAdapter((SqlCommand)this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
 
    protected void SetAllParameters(DataRow Row)
    {
        this.ClearParameters();
        for (int i = 0; i < Row.Table.Columns.Count; i++)
        {
            this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
        }
    }
    public void AddParms(string ParmName, object ParmValue)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
        
        if (ParmValue != DBNull.Value)
        {
            if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
                this.oCommand.Parameters[ParmName].Value = ParmValue;
            else
                this.oCommand.Parameters.AddWithValue(ParmName, ParmValue);
        }
        else
            this.RemoveParm(ParmName);
    }
    protected void ClearParameters()
    {
        this.oCommand.Parameters.Clear();
    }
    public void RemoveParm(string ParmName)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            this.oCommand.Parameters.RemoveAt(ParmName);
    }
    public void SetParmDirection(string ParmName, ParameterDirection direction)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            this.oCommand.Parameters[ParmName].Direction = direction;
    }
    
    public void ExecuteCommand()
    {
        this.ExecuteCommand(this.oCommand.CommandText);
    }
    public void ExecuteCommand(string StoredProcName)
    {
        this.OpenConnection();
        this.oCommand.CommandText = StoredProcName;
        try
        {
            this.oCommand.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            // Process the SqlException with your normal logging methodology
        }
    }
 
    public void OpenConnection()
    {
        try
        {
            if (this.oConnection.State != ConnectionState.Open)
            {
                this.oConnection.Open();
            }
        }
        catch (Exception ex)
        {
            // Process the Exception with your normal logging methodology
        }
    }
 
    public void Dispose()
    {
        if (this.oConnection != null)
        {
            if (this.oConnection.State != ConnectionState.Closed)
                this.oConnection.Close();
            this.oConnection.Dispose();
        }
    }
}

Here is how you might use these in your own DataAccess classes, for example, if you've created a StatusDataAccess class, sub-classed from BaseDataAccess. My example uses a Typed DataSet, but it could just as easily be used with a plain old DataSet (this is based on my SP example above):

public void UpdateStatus(StatusDataSet ds)
{
    using (DataAccess da = new DataAccess(this.MyConnectionString))
    {
        da.SetCommand("bsp_StatusPut");
        foreach (StatusDataSet.StatusRow call in ds.Status)
        {
            da.SetAllParameters(call);
            da.SetParmDirection("statuskey", ParameterDirection.InputOutput);
            da.ExecuteCommand();
        }
    }
}

OK, as promised earlier, here is a set of methods you can use to automatically generate the SQL Script to create such PUT Stored Procedures for every table in your database. Write yourself a database utility application, if you don't already have one, and include these methods in it:

#region Generating PUT Stored Procs
 
StringBuilder ProcBuilder;
public string GeneratePutStoredProc(string DatabaseName, string TableName)
{
    DataTable dtColumns = new DataTable(); ;
    string ErrorMsg = "";
    this.ProcBuilder = new StringBuilder();
 
    // First, let's fill a DataTable with the information for each column in the database Table
    using (SqlConnection conn = new SqlConnection(this.ConnectionStringStatus))
    {
        try
        {
            conn.Open();
            dtColumns = conn.GetSchema("Columns", new[] { DatabaseName, null, TableName });
        }
        catch (Exception ex)
        {
            ErrorMsg = ex.Message + "\r\n";
            if (ex.InnerException != null && ex.InnerException.Message != ex.Message)
                ErrorMsg += ex.InnerException.Message + "\r\n";
        }
    }
    // Get error-handling out of the way
    if (dtColumns.Rows.Count == 0 && ErrorMsg != "")
        ErrorMsg += string.Format("No columns retrieved for {0}/{1}.\r\n", DatabaseName, TableName);
    if (ErrorMsg != "")
    {
        ErrorMsg += "Check your database and/or connection";
        MessageBox.Show(ErrorMsg);
        return "";
    }
 
    // One of our database conventions is that the name of the PK column is always TableName + "Key"
        string KeyName = TableName + "Key";
 
    // Then start building the SQL script for the creation of the Stored Proc,
    // calling a few methods to do the heavy lifting
    this.BuildHeader(TableName, "PUT");
    this.BuildParameterList(dtColumns, KeyName);
 
    this.ProcBuilder.Append("\r\nAS\r\n");
    this.ProcBuilder.Append("\tSET NOCOUNT ON\r\n");
    this.ProcBuilder.Append("\tSET XACT_ABORT ON\r\n\r\n");
    this.ProcBuilder.Append("\tBEGIN TRANSACTION\r\n");
 
    this.ProcBuilder.AppendFormat("\tIF (@{0} <= 0 OR @{0} IS NULL)\r\n", KeyName);
    this.ProcBuilder.Append("\tBEGIN\r\n");
    this.BuildInsert(dtColumns, KeyName, TableName);
    this.ProcBuilder.Append("\tEND\r\n\tELSE\r\n\tBEGIN\r\n");
    this.BuildUpdate(dtColumns, KeyName, TableName);
    this.ProcBuilder.Append("\tEND\r\n\r\n\tCOMMIT TRANSACTION\r\n\tRETURN 0\r\n");
 
    this.BuildFooter();
 
    return this.ProcBuilder.ToString();
}
 
protected void BuildHeader(string TableName, string ProcType)
{
    string prefix = "bsp";
    string fullProcName = String.Format("{0}_{1}{2}", prefix, TableName, ProcType);
 
    this.ProcBuilder.Append("\r\n");
    this.ProcBuilder.Append("---------------------------------------------------------------\r\n");
    this.ProcBuilder.AppendFormat("-- Stored Procedure {0}\r\n", fullProcName);
    this.ProcBuilder.AppendFormat("-- Basic {1} Procedure for {0} \r\n", TableName, ProcType);
    this.ProcBuilder.AppendFormat("-- Automatically generated {0}\r\n", DateTime.Now);
    this.ProcBuilder.Append("---------------------------------------------------------------\r\n");
    this.ProcBuilder.AppendFormat("IF EXISTS(SELECT * FROM sys.objects WHERE name LIKE '{0}' and type = 'P')\r\n", fullProcName);
    this.ProcBuilder.AppendFormat("\tDROP PROCEDURE {0}\r\n", fullProcName);
    this.ProcBuilder.Append("GO\r\n");
    this.ProcBuilder.Append("SET QUOTED_IDENTIFIER OFF \r\n");
    this.ProcBuilder.Append("GO\r\n");
    this.ProcBuilder.Append("SET ANSI_NULLS ON \r\n");
    this.ProcBuilder.Append("GO\r\n\r\n");
    this.ProcBuilder.AppendFormat("CREATE PROCEDURE {0}\r\n", fullProcName);
}
protected void BuildParameterList(DataTable dtColumns, string KeyName)
{
    StringBuilder sb = new StringBuilder();
    foreach (DataRow row in dtColumns.Rows)
    {
        if (sb.Length > 0)
            sb.Append(",\r\n");
        sb.AppendFormat("\t@{0}\t\t{1} = NULL", row["COLUMN_NAME"], row["DATA_TYPE"]);
        if (row["COLUMN_NAME"].ToString().ToUpper() == KeyName.ToUpper())
            sb.Append(" OUTPUT");
    }
    this.ProcBuilder.Append(sb.ToString());
}
protected void BuildInsert(DataTable dtColumns, string KeyName, string TableName)
{
    this.ProcBuilder.AppendFormat("\t\t-- Insert Values into the {0} table\r\n", TableName);
    this.ProcBuilder.AppendFormat("\t\tINSERT {0}\r\n", TableName);
 
    StringBuilder sbColumns = new StringBuilder();
    StringBuilder sbParms = new StringBuilder();
    foreach (DataRow row in dtColumns.Rows)
    {
        if (row["COLUMN_NAME"].ToString().ToUpper() == KeyName.ToUpper())
            continue;
        if (sbColumns.Length > 0)
            sbColumns.Append(",\r\n");
        if (sbParms.Length > 0)
            sbParms.Append(",\r\n");
        sbColumns.AppendFormat("\t\t\t{0}", row["COLUMN_NAME"]);
        sbParms.AppendFormat("\t\t\t@{0}", row["COLUMN_NAME"]);
    }
    this.ProcBuilder.Append("\t\t\t(\r\n");
    this.ProcBuilder.AppendFormat("{0})\r\n", sbColumns.ToString());
    this.ProcBuilder.Append("\t\tSELECT\r\n");
    this.ProcBuilder.AppendFormat("{0}\r\n\r\n", sbParms.ToString());
    this.ProcBuilder.AppendFormat("\t\tSELECT @{0} = SCOPE_IDENTITY()\r\n", KeyName);
}
protected void BuildUpdate(DataTable dtColumns, string KeyName, string TableName)
{
    this.ProcBuilder.AppendFormat("\t\t-- Update the {0} table\r\n", TableName);
    this.ProcBuilder.AppendFormat("\t\tUPDATE {0}\r\n", TableName);
    this.ProcBuilder.Append("\t\tSET\r\n");
 
    StringBuilder sb = new StringBuilder();
    foreach (DataRow row in dtColumns.Rows)
    {
        if (row["COLUMN_NAME"].ToString().ToUpper() == KeyName.ToUpper())
            continue;
        if (sb.Length > 0)
            sb.Append(",\r\n");
        sb.AppendFormat("\t\t\t{0} = ISNULL(@{0}, {0})", row["COLUMN_NAME"]);
    }
    this.ProcBuilder.Append(sb.ToString());
    this.ProcBuilder.AppendFormat("\r\n\t\tWHERE {0} = @{0}\r\n", KeyName);
}
protected void BuildFooter()
{
    this.ProcBuilder.Append("\r\n");
    this.ProcBuilder.Append("GO\r\n");
    this.ProcBuilder.Append("SET QUOTED_IDENTIFIER OFF \r\n");
    this.ProcBuilder.Append("GO\r\n");
    this.ProcBuilder.Append("SET ANSI_NULLS ON \r\n");
    this.ProcBuilder.Append("GO\r\n");
}
 
#endregion

That's it. Happy coding!!  =0)