Refactoring DAL Code to Support Stored Procedures
private static readonly string dataProvider = ConfigurationManager.AppSettings.Get("Provider");
private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
private static readonly string connectionString = ConfigurationManager.ConnectionStrings[dataProvider].ConnectionString;
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
connection.Open();
return command.ExecuteNonQuery();
}
}
}
I need help rewriting this to make it work with stored procedures. (Pass sproc name and parameters) Does anyone know how I should do this? Edit. In the area I'm having trouble with, you are trying to figure out ways to populate the parameters.
thanks
a source to share
You already need the parameters regardless of whether you are implementing stored procedures.
Right now your code can be called with a type request SELECT * FROM Table WHERE ID = @ID
, in which case you already need to pass Dictionary<string,object> params
. Enter your code into the parameter collection of an existing command and test it before worrying about stored procedures.
After that, you should simply create an overload that takes a bool that says it is a stored procedure, and then use that to set the CommandType property of the command.
Edit: here How would I do it?
Step 1: generalize the update
There is nothing special about the Update method that prevents it from being used for other unsolicited operations other than the name. So:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql);
}
public static int NonQuery(string sql)
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
connection.Open();
return command.ExecuteNonQuery();
}
}
}
Step 2: What about the parameters?
Your current code couldn't even handle UPDATE queries that used parameters, so let's start fixing it. First, make sure it still works if no options are specified:
public static int NonQuery(string sql)
{
Dictionary<string, object> parameters = null;
if (parameters == null)
{
parameters = new Dictionary<string, object>();
}
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
foreach (KeyValuePair<string, object> p in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = p.Key;
parameter.Value = p.Value;
command.Parameters.Add(parameter);
}
connection.Open();
return command.ExecuteNonQuery();
}
}
}
Once it works, promote the parameters as a parameter. This does not affect existing subscribers Update
:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql, null);
}
public static int NonQuery(string sql, Dictionary<string, object> parameters)
At this point, test the NonQuery with a parameterized query. After that, create an Update overload that takes parameters:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
return NonQuery(sql, null);
}
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, Dictionary<string, object> parameters)
{
return NonQuery(sql, parameters);
}
Step 3: Take saved procedures into account
There is little difference as to how stored procedures will be handled. What you got so far is implicitly like this:
using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
command.CommandType = CommandType.Text;
So, take CommandType.Text and promote it as a parameter in the overload:
public static int NonQuery(string sql, Dictionary<string, object> parameters)
{
return NonQuery(sql, CommandType.Text, parameters);
}
public static int NonQuery(string sql, CommandType commandType, Dictionary<string, object> parameters)
Finally, if you want, update Update
:
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, Dictionary<string, object> parameters)
{
return Update(sql, CommandType.Text, parameters);
}
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <param name="commandType">CommandType.Text or CommandType.StoredProcedure</param>
/// <param name="parameters">Name/value dictionary of parameters</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql, CommandType commandType, Dictionary<string, object> parameters)
{
return NonQuery(sql, parameters);
}
Of course, as a final exercise for the reader, you can replace all your Update calls with NonQuery calls and get rid of Update entirely.
Of course, this simple method does not handle output parameters or situations where you need to specify the DbType of this parameter. To do this, you need to take some kind of ParameterCollection.
a source to share
I am doing something similar to this using frontend and generics.
Example
using System.Data.SqlClient;
public interface IParameter<T> where T : IEntity<T>
{
void Populate(SqlParameterCollection parameters, T entity);
}
A class that implements the interface using System.Data; using System.Data.SqlClient;
public class UserParameter : IParameter<User>
{
public void Populate(SqlParameterCollection parameters, User entity)
{
parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = entity.Id;
parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = entity.Name;
parameters.Add("@EmailAddress", SqlDbType.NVarChar, 255).Value = entity.EmailAddress;
}
}
We then have a method that does the update
public void Update<T>(string prefix, IParameter<T> parameters, T entity)
where T : class, IEntity<T>
{
using (var connection = this.Connection())
{
using (var command = new SqlCommand(string.Format("dbo.{0}_Update", prefix), connection))
{
command.CommandType = CommandType.StoredProcedure;
parameters.Populate(command.Parameters, entity);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
Then I call it by doing something like
Update ("Users", new UserParameter (), value);
I also do the same for populating an object from reader values.
a source to share
You might find it difficult with this, depending on the environment in which you plan to host it.
If I understand correctly, you would like to keep the function signature (one string parameter), but handle it differently if the first word in the line is not "UPDATE". For example, this line should execute a stored procedure named UpdateTable1 with four arguments:
UpdateTable1 NULL, A5KMI, "New description", #5/10/2009#
or something like that.
The problem is that you need a separator between the arguments in the rest of the string. To do this, you will have to split the string into tokens and this can be really a trick.
So the alternative is to add an EXEC command in front of the entire line, set the whole thing to the CommandText property, and then call one of the Execute methods.
What I don't like about this is that it really exposes you to the SQL injection vulnerability. If at all possible, use overloaded methods like the ones others have answered and make the caller a split argument into some kind of collection.
a source to share