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