I am building an sqlwrapper to handle both MSsql and Sqlite, I have no issues using the generic dbconnection, dataset and dataadapter so far, but with dbcommand parameters.add is the only option. I’d like to use parameter.addwithvalue like sqlcommand and sqlitecommand have, but I am unsure how to implement it.
For example here is my sqlitefactory:
public class SqlLiteFactory : SqlFactory
{
public override DbConnection CreateConnection()
{
return new SQLiteConnection(ConfigurationManager.ConnectionStrings["ClientDBConnectionString"].ConnectionString);
//return new SQLiteConnection("Data Source=Resources\qaqc.sqlite;Version=3");
}
public override DataAdapter CreateAdapter(string command, DbConnection connection)
{
return new SQLiteDataAdapter(command, connection as SQLiteConnection);
}
public override DbCommand CreateCommand()
{
return new SQLiteCommand();
}
}
Here is my implementation:
DbConnection c = SqlHandler.Instance.CreateConnection();
DbCommand cmd = SqlHandler.Instance.CreateCommand();
cmd.Connection = c;
cmd.CommandText = "SELECT column FROM table WHERE syncstatus = 0 and UserName = @user GROUP BY column;";
cmd.Parameters.Add("@user");
cmd.Parameters["@user"].Value = SystemInformation.UserName;
DbDataReader dr = cmd.ExecuteReader();
in the more specific sqlcommand and sqlitecommand there is a method .addwithvalue() which is what I’d like to have in the parent dbcommand class.
Advertisement
Answer
Solved it with and extension method
static class MyExtensions
{
public static void AddParameterWithValue(this DbCommand command, string parameterName, object parameterValue)
{
var parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
command.Parameters.Add(parameter);
}
}