public static int Insert(string tablename, IEnumerable <string> columnNames, DataRow row, IEnumerable <string> virtualFields)
{
string sql = SqlBuilder.BuildParameterizedInsertStatement(tablename, columnNames, true, virtualFields);
SqlCommand command = new SqlCommand(sql);
SqlCommandUtils.SetParams(command, columnNames, row);
// also need to set params for tablename and virtual fields
bool setTablename = true;
int i = 1;
foreach (string f in virtualFields)
{
if (setTablename)
{
command.Parameters.Add(new SqlParameter("@TableName", tablename));
setTablename = false;
}
// set @FNi, @FVi for i in [1, vf count]
command.Parameters.Add(new SqlParameter(string.Format("@FN{0}", i), f));
command.Parameters.Add(new SqlParameter(string.Format("@FV{0}", i), row[f].ToString()));
i++;
}
return(Insert(command));
}
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 Delete(string tablename, string username, string primaryKeyName, int primaryKey, bool deleteVirtualValues)
{
string sql;
if (deleteVirtualValues)
{
sql = SqlBuilder.BuildVirtualParameterizedDeleteStatement(tablename, primaryKeyName);
}
else
{
sql = SqlBuilder.BuildParameterizedDeleteStatement(tablename, primaryKeyName);
}
SqlCommand command = new SqlCommand(sql);
SqlCommandUtils.AddIntInputParam(command, primaryKeyName, primaryKey);
if (deleteVirtualValues)
{
command.Parameters.Add(new SqlParameter("@TableName", tablename));
command.Parameters.Add(new SqlParameter("@PrimaryKey", primaryKey));
}
return(Delete(command, BuildAuditDeleteCommand(tablename, username, primaryKeyName, primaryKey.ToString(), null, null)));
}
public static int GetChildren(string tablename, string primaryKeyName, string parentKeyName, int parentKey, IEnumerable <string> virtualFields, DataTable table)
{
SqlCommand command = new SqlCommand(SqlBuilder.BuildVirtualParameterizedSelectStatement(tablename, primaryKeyName, parentKeyName, virtualFields));
SqlCommandUtils.AddIntInputParam(command, parentKeyName, parentKey);
return(Get(command, table));
}
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 void InsertWithoutIdentity(string tablename, IEnumerable <string> columnNames, DataRow row)
{
string sql = SqlBuilder.BuildParameterizedInsertStatement(tablename, columnNames, false, null);
SqlCommand command = new SqlCommand(sql);
SqlCommandUtils.SetParams(command, columnNames, row);
ExecuteNonQuery(command);
}
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));
}
public static int Get(string tablename, string primaryKeyName, int primaryKey, DataTable table)
{
string sql = SqlBuilder.BuildParameterizedSelectByStatement(tablename, primaryKeyName);
SqlCommand command = new SqlCommand(sql);
SqlCommandUtils.AddIntInputParam(command, primaryKeyName, primaryKey);
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));
}
//public static int Update(string tablename, IEnumerable<string> columnNames, string keyName, DataRow row)
//{
// string sql = SqlBuilder.BuildParameterizedUpdateByStatement(tablename, columnNames, keyName);
// // TODO: filter virtual fields but not primary key
// SqlCommand command = new SqlCommand(sql);
// SqlCommandUtils.SetParams(command, row);
// return Update(command);
//}
public static int Update(string tablename, IEnumerable <string> columnNames, IEnumerable <string> keyNames, DataRow row, IEnumerable <string> virtualFields)
{
string sql = SqlBuilder.BuildParameterizedUpdateByStatement(tablename, columnNames, keyNames, virtualFields);
SqlCommand command = new SqlCommand(sql);
// TODO: filter virtual fields but not primary key
SqlCommandUtils.SetParams(command, columnNames, row);
SqlCommandUtils.SetParams(command, keyNames, row);
// TODO: add virtual fields params [@TableName, @FNi, @FVi, @PrimaryKey]
// we could change @PrimaryKey -> @[$PrimaryKeyName], reusing the primary key param from the main update statement
bool runOnce = true;
int i = 1;
foreach (string f in virtualFields)
{
if (runOnce)
{
string primaryKeyName = null;
foreach (string k in keyNames)
{
primaryKeyName = k;
break;
}
command.Parameters.Add(new SqlParameter("@TableName", tablename));
command.Parameters.Add(new SqlParameter("@PrimaryKey", row[primaryKeyName]));
runOnce = false;
}
// set @FNi, @FVi for i in [1, vf count]
command.Parameters.Add(new SqlParameter(string.Format("@FN{0}", i), f));
command.Parameters.Add(new SqlParameter(string.Format("@FV{0}", i), row[f].ToString()));
i++;
}
return(Update(command));
}
// 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);
}