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