public static int Delete(string tablename, string username, List <KeyValuePair <string, int> > primaryKey)
{
// TODO: we could use an enumerator here. seems like a waste to make a copy of a collection
IEnumerable <string> keys = ExtractKeys <string, int>(primaryKey);
string sql = SqlBuilder.BuildParameterizedDeleteStatement(tablename, keys);
SqlCommand command = new SqlCommand(sql);
foreach (KeyValuePair <string, int> kvp in primaryKey)
{
SqlCommandUtils.AddInputParam(command, kvp.Key, kvp.Value);
}
SqlCommand dcmd;
if (primaryKey.Count > 1)
{
dcmd = BuildAuditDeleteCommand(tablename, username, primaryKey[0].Key, primaryKey[0].Value.ToString(),
primaryKey[1].Key, primaryKey[1].Value.ToString());
}
else
{
dcmd = BuildAuditDeleteCommand(tablename, username, primaryKey[0].Key, primaryKey[0].Value.ToString(), null, null);
}
return(Delete(command, dcmd));
}
public static int GetByField(string tablename, string fieldname, object fieldvalue, string primaryKeyName, IEnumerable <string> virtualFields, DataTable table)
{
SqlCommand command = new SqlCommand(SqlBuilder.BuildVirtualParameterizedSelectStatement(tablename, primaryKeyName, fieldname, virtualFields));
SqlCommandUtils.AddInputParam(command, fieldname, fieldvalue);
return(Get(command, table));
}
public static int GetByField(string tablename, string fieldname, object fieldvalue, DataTable table)
{
string sql = SqlBuilder.BuildParameterizedSelectByStatement(tablename, fieldname);
SqlCommand command = new SqlCommand(sql);
SqlCommandUtils.AddInputParam(command, fieldname, fieldvalue);
return(Get(command, table));
}
// uses new QueryBuilder
public static int Get(QueryBuilder query, DataTable table)
{
SqlCommand command = new SqlCommand(query.ToSql());
foreach (string k in query.Parameters.Keys)
{
SqlCommandUtils.AddInputParam(command, k, query.Parameters[k]);
}
return(Get(command, table));
}
private static T Crud <T>(string tablename, List <KeyValuePair <string, int> > primaryKey,
DataTable table, CrudDelegate f, ExecutionDelegate <T> execsql)
{
SqlCommand cmd = new SqlCommand(f(table, ExtractKeys <string, int>(primaryKey)));
foreach (KeyValuePair <string, int> kvp in primaryKey)
{
SqlCommandUtils.AddInputParam(cmd, kvp.Key, kvp.Value);
}
return(execsql(cmd, table));
}
public static int Get(string tablename, List <KeyValuePair <string, int> > primaryKey, DataTable table)
{
IEnumerable <string> keys = ExtractKeys <string, int>(primaryKey);
// our various CRUD statements differ only via this method call (all share the same signature?)
string sql = SqlBuilder.BuildParameterizedSelectByStatement(tablename, keys);
SqlCommand command = new SqlCommand(sql);
foreach (KeyValuePair <string, int> kvp in primaryKey)
{
SqlCommandUtils.AddInputParam(command, kvp.Key, kvp.Value);
}
return(Get(command, table));
}
// reuse the connection of other delete commands
private static SqlCommand BuildAuditDeleteCommand(string tablename, string username, string pkname1, string pkvalue1,
string pkname2, string pkvalue2)
{
SqlCommand cmd = new SqlCommand("spUpdateAuditLogDeletedBy");
cmd.CommandType = CommandType.StoredProcedure;
// TableName, UserName, PKColumnName1, PKColumnValue1, PKColumnName2?, PKColumnValue2?
SqlCommandUtils.AddInputParam(cmd, "TableName", tablename);
SqlCommandUtils.AddInputParam(cmd, "UserName", username);
SqlCommandUtils.AddInputParam(cmd, "PKColumnName1", pkname1);
SqlCommandUtils.AddInputParam(cmd, "PKColumnValue1", pkvalue1);
if (pkname2 != null && pkname2 != "" && pkvalue2 != null && pkvalue2 != "")
{
SqlCommandUtils.AddInputParam(cmd, "PKColumnName2", pkname2);
SqlCommandUtils.AddInputParam(cmd, "PKColumnValue2", pkvalue2);
}
return(cmd);
}