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)
