|
Starting to reduce the duplication
(continued from .NET wrappers for data objects)
Note: This page is an early note about what has now become DbWrappers, a free C# data object code generator. Source code is available. Check that out; it's much more up-to-date than the info here. It works, too ;)
Here's what I'm doing at the moment. For each type of row, I have an object which stores all the relevant data. I have a method that will make an array of DataParameter objects that includes the current state of the object and another method that generates an SQL query string to specify the primary key. If I want to add the row to a database, I call Myelin.Data.CommandBuilder.BuildInsertCommand() with the aforementioned array, and when I want to update the row, I call BuildUpdateCommand() with the array.
It's not perfect, but it gets rid of some of the duplication, it doesn't force me to use a DataSet for everything (see the previous story) and means I can port to Java or something if I wake up one day and decide to be a Mac programmer ;-)
Here we go:
using System;
using System.Data.OleDb;
namespace Myelin.Data
{
/// <summary>
/// A typed value that goes in a specific column of a table.
/// Used to give CommandBuilder.* a list of values & columns
/// to form SQL queries.
/// </summary>
public class DataParameter
{
public OleDbParameter param;
public string colName;
public DataParameter(
string colName,
OleDbType colType,
object colValue
)
{
this.colName = colName;
this.param = new OleDbParameter( "@" + colName, colType );
this.param.Value = colValue;
}
}
/// <summary>
/// Some functions to build up OleDbCommand objects from
/// arrays of parameters.
/// </summary>
public class CommandBuilder
{
/// <summary>
/// A type of SQL command - INSERT or UPDATE
/// </summary>
private enum CommandType
{
InsertCommand,
UpdateCommand
}
/// <summary>
/// Make an SQL INSERT command
/// </summary>
/// <param name="tableName">The table to act on</param>
/// <param name="parameters">An array of typed parameters
/// (with associated column names)</param>
/// <returns>An OleDbCommand object with the
/// command text and parameters filled in</returns>
public static OleDbCommand BuildInsertCommand(
string tableName,
DataParameter[] parameters
)
{
return BuildCommand(
CommandType.InsertCommand,
tableName,
parameters,
"" );
}
/// <summary>
/// Make an SQL UPDATE command
/// </summary>
/// <param name="tableName">The table to act on</param>
/// <param name="parameters">An array of typed parameters
/// (with associated column names)</param>
/// <param name="keyQuery">An SQL query that identifies
/// the row(s) to be updated, if this is an update command</param>
/// <returns>An OleDbCommand object with the
/// command text and parameters filled in</returns>
public static OleDbCommand BuildUpdateCommand(
string tableName,
DataParameter[] parameters,
string keyQuery
)
{
return BuildCommand(
CommandType.UpdateCommand,
tableName,
parameters,
keyQuery );
}
/// <summary>
/// Actually build an SQL command.
/// </summary>
/// <param name="cmdType">Type of command - UPDATE
/// or INSERT</param>
/// <param name="tableName">SQL table to act on</param>
/// <param name="parameters">An array of typed parameters
/// (with associated column names)</param>
/// <param name="keyQuery">An SQL query that identifies
/// the row(s) to be updated, if this is an update command</param>
/// <returns>An OleDbCommand object with the
/// command text and parameters filled in</returns>
private static OleDbCommand BuildCommand(
CommandType cmdType,
string tableName,
DataParameter[] parameters,
string keyQuery
)
{
OleDbCommand cmd = new OleDbCommand();
string query = "";
string query2 = "";
string query3 = "";
switch ( cmdType )
{
case CommandType.InsertCommand:
query = "INSERT INTO " + tableName + "(";
query2 = ") VALUES (";
query3 = ")";
break;
case CommandType.UpdateCommand:
query = "UPDATE " + tableName + " SET ";
query2 = "";
query3 = " WHERE " + keyQuery;
break;
}
bool first = true;
foreach ( DataParameter parameter in parameters )
{
cmd.Parameters.Add( parameter.param );
switch ( cmdType )
{
case CommandType.InsertCommand:
if ( !first )
{
query += ", ";
query2 += ", ";
}
query += parameter.colName;
query2 += "@" + parameter.colName;
break;
case CommandType.UpdateCommand:
if ( !first )
{
query += ", ";
}
query += parameter.colName + "=@" + parameter.colName;
break;
}
first = false;
}
cmd.CommandText = query + query2 + query3;
return cmd;
}
}
}
|
| csharpindex.com/colorCode |
This hasn't been tested much yet, so if it does something strange, that's quite normal - drop me a line if you find a bug :)
P.S. I have the feeling I just reimplemented OleDbCommandBuilder - time to go look at MSDN again.
|
