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); }