public static void InsertBulkChange(string uid, DateTime dateupdate, string tablename, int tableId,
IEnumerable <ChangeItem> changeItems)
{
string query = "INSERT INTO tbl_ChangeLog(UID, DateUpdate, TableName, ID, Fieldname, Oldvalue, Newvalue)";
BulkInsertData data = new BulkInsertData();
bool first = true;
int param_index = 0;
foreach (ChangeItem item in changeItems)
{
if (first)
{
first = false;
}
else
{
query += " UNION ";
}
data.prms.Add(uid);
data.prms.Add(dateupdate);
data.prms.Add(tablename);
data.prms.Add(tableId);
data.prms.Add(item.FieldName);
data.prms.Add(item.OldValue);
data.prms.Add(item.NewValue);
query += " SELECT ";
for (int j = 0; j < 7; j++)
{
query += (j == 0 ? "" : ",") + "@" + param_index++.ToString();
}
// +uid + "', '" + dateupdate.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + tablename + "', " + tableId.ToString() + ", '" + item.FieldName + "', '" + item.OldValue + "', '" + item.NewValue + "' ";
}
data.query = query;
Common.Database.ExecuteDatabaseQuery(query, data.prms.ToArray(), true);
}
public static BulkInsertData GenerateBulkInsertQuery(IEnumerable <Call> callsToInsert)
{
string query = "INSERT INTO tbl_Calls (idServiceUser, timeofCall, duration, required_workers, flagcode, notes, traveltime, idBatchIndex)";
BulkInsertData data = new BulkInsertData();
bool first = true;
int param_index = 0;
foreach (Call c in callsToInsert)
{
if (first)
{
first = false;
}
else
{
query += " UNION ";
}
query += " SELECT ";
data.prms.Add(c.ServiceUser.id);
data.prms.Add(c.time.ToString("yyyy-MM-dd HH:mm:00"));
data.prms.Add(c.duration_mins);
data.prms.Add(c.required_workers);
data.prms.Add((int)c.flag);
data.prms.Add(c.notes);
data.prms.Add(c.traveltime_mins);
data.prms.Add(c.idBatchIndex);
for (int j = 0; j < 8; j++)
{
query += (j == 0?"":",") + "@" + param_index++.ToString();
}
}
data.query = query;
return(data);
}
