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