private void InsertTabularSection(Guid entityId, TabularSection section, SqlCommand childCmd, SqlCommand childCmdBatch) { if (childCmdBatch != null && section.Entities.Count > 1) //batch insert is supported { DataTable tbl = new DataTable(); for (int i = 1; i < childCmd.Parameters.Count; i++) { SqlParameter p = childCmd.Parameters[i]; String pName = p.ParameterName.Substring(1); if (!pName.ToLower().Equals("ref")) { tbl.Columns.Add(pName, SqlDbTypeToType(p.SqlDbType)); } } FillDataTable(tbl, section, false); childCmdBatch.Parameters["@Ref"].Value = entityId; childCmdBatch.Parameters["@Data"].Value = tbl; childCmdBatch.ExecuteNonQuery(); } else { foreach (var childRow in section.Entities) { childCmd = FillParameters(childCmd, childRow); childCmd.Parameters["@Ref"].Value = entityId; childCmd.ExecuteNonQuery(); } } }
public void AddTabularSection(String name, String key = null) { if (!String.IsNullOrEmpty(name)) { if (TabularSections == null) { TabularSections = new Dictionary <string, TabularSection>(); } CurrentTabularSection = new TabularSection(name, key); TabularSections.Add(name, CurrentTabularSection); } }
private void FillDataTable(DataTable tbl, TabularSection section, bool includeRow, bool checkDuplicates = true) { Dictionary <String, object> keyValues = null; String[] keyFields = section.KeyFields; if (keyFields != null) { if (keyFields.Length > 0 && checkDuplicates) { keyValues = new Dictionary <string, object>(); //will check duplicates } } foreach (var childRow in section.Entities) { DataRow r = FillDataRow(tbl, childRow, includeRow); if (keyValues != null) //check duplicates { String stringKey = ""; foreach (String kf in keyFields) { object keyValue = r[kf]; stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString().ToLower()) + "_"; } if (!keyValues.ContainsKey(stringKey)) //ignore duplicates { keyValues.Add(stringKey, null); } else { continue; } } tbl.Rows.Add(r); } }
/* * private void UpdateTabularSectionBatch(Guid entityId, TabularSection section, SqlCommand insCmd, SqlCommand selCmd, SqlCommand insCmdBatch, SqlCommand updCmdBatch, SqlCommand delCmdBatch) * { * String[] keyFields = section.KeyFields; * * //select existing * selCmd.Parameters["@Ref"].Value = entityId; * DataTable selTbl = new DataTable(); * SqlDataAdapter ad = new SqlDataAdapter(selCmd); * ad.Fill(selTbl); * * Dictionary<String, DataRow> existingValues = new Dictionary<string, DataRow>(); * foreach (DataRow r in selTbl.Rows) * { * String stringKey = ""; * foreach (String kf in keyFields) * { * object keyValue = r[kf]; * stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; * } * existingValues.Add(stringKey, r); * } * * //create tables * DataTable insTbl = new DataTable(); * DataTable updTbl = new DataTable(); * DataTable delTbl = new DataTable(); * for (int i = 1; i < insCmd.Parameters.Count; i++) * { * SqlParameter p = insCmd.Parameters[i]; * String pName = p.ParameterName.Substring(1); * Type t = SqlDbTypeToType(p.SqlDbType); * insTbl.Columns.Add(pName, t); * updTbl.Columns.Add(pName, t); * delTbl.Columns.Add(pName, t); * } * * //update or insert * foreach (var childRow in section.Entities) * { * DataRow r = FillDataRow(insTbl, childRow); * r["Ref"] = entityId; * * String stringKey = ""; * foreach (String kf in keyFields) * { * object keyValue = r[kf]; * stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; * } * * if (existingValues.ContainsKey(stringKey)) * { * //compare * DataRow eRow = existingValues[stringKey]; * if (eRow != null) * { * if (!eRow.ItemArray.SequenceEqual(r.ItemArray)) * { * AddRow(updTbl, r); * } * existingValues[stringKey] = null; //not to be deleted * } * } * else * AddRow(insTbl, r); * } * * //remove * foreach (KeyValuePair<String, DataRow> kvp in existingValues) * { * if (kvp.Value != null) * { * AddRow(delTbl, kvp.Value); * } * } * * //delete * if (delTbl.Rows.Count > 0) * { * delCmdBatch.Parameters["@Data"].Value = delTbl; * delCmdBatch.ExecuteNonQuery(); * } * * //update * if (updTbl.Rows.Count > 0) * { * updCmdBatch.Parameters["@Data"].Value = updTbl; * updCmdBatch.ExecuteNonQuery(); * } * * //insert * if (insTbl.Rows.Count > 0) * { * insCmdBatch.Parameters["@Data"].Value = insTbl; * insCmdBatch.ExecuteNonQuery(); * } * } */ private void UpdateTabularSectionBatchAll(Guid entityId, TabularSection section, SqlCommand insCmd, SqlCommand updCmdBatch) { String[] keyFields = section.KeyFields; //create table DataTable updTbl = new DataTable(); for (int i = 1; i < insCmd.Parameters.Count; i++) { SqlParameter p = insCmd.Parameters[i]; String pName = p.ParameterName.Substring(1); Type t = SqlDbTypeToType(p.SqlDbType); if (!pName.ToLower().Equals("ref")) { updTbl.Columns.Add(pName, t); } } FillDataTable(updTbl, section, false); updCmdBatch.Parameters["@Ref"].Value = entityId; updCmdBatch.Parameters["@Data"].Value = updTbl; updCmdBatch.ExecuteNonQuery(); }
public void AddTabularSection(String name, String key = null) { if (!String.IsNullOrEmpty(name)) { if (TabularSections == null) TabularSections = new Dictionary<string, TabularSection>(); CurrentTabularSection = new TabularSection(name, key); TabularSections.Add(name, CurrentTabularSection); } }
private void FillDataTable(DataTable tbl, TabularSection section, bool includeRow, bool checkDuplicates = true) { Dictionary<String, object> keyValues = null; String[] keyFields = section.KeyFields; if (keyFields != null) if (keyFields.Length > 0 && checkDuplicates) keyValues = new Dictionary<string, object>(); //will check duplicates foreach (var childRow in section.Entities) { DataRow r = FillDataRow(tbl, childRow, includeRow); if (keyValues != null) //check duplicates { String stringKey = ""; foreach (String kf in keyFields) { object keyValue = r[kf]; stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString().ToLower()) + "_"; } if (!keyValues.ContainsKey(stringKey)) //ignore duplicates keyValues.Add(stringKey, null); else continue; } tbl.Rows.Add(r); } }
/* private void UpdateTabularSectionBatch(Guid entityId, TabularSection section, SqlCommand insCmd, SqlCommand selCmd, SqlCommand insCmdBatch, SqlCommand updCmdBatch, SqlCommand delCmdBatch) { String[] keyFields = section.KeyFields; //select existing selCmd.Parameters["@Ref"].Value = entityId; DataTable selTbl = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(selCmd); ad.Fill(selTbl); Dictionary<String, DataRow> existingValues = new Dictionary<string, DataRow>(); foreach (DataRow r in selTbl.Rows) { String stringKey = ""; foreach (String kf in keyFields) { object keyValue = r[kf]; stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; } existingValues.Add(stringKey, r); } //create tables DataTable insTbl = new DataTable(); DataTable updTbl = new DataTable(); DataTable delTbl = new DataTable(); for (int i = 1; i < insCmd.Parameters.Count; i++) { SqlParameter p = insCmd.Parameters[i]; String pName = p.ParameterName.Substring(1); Type t = SqlDbTypeToType(p.SqlDbType); insTbl.Columns.Add(pName, t); updTbl.Columns.Add(pName, t); delTbl.Columns.Add(pName, t); } //update or insert foreach (var childRow in section.Entities) { DataRow r = FillDataRow(insTbl, childRow); r["Ref"] = entityId; String stringKey = ""; foreach (String kf in keyFields) { object keyValue = r[kf]; stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; } if (existingValues.ContainsKey(stringKey)) { //compare DataRow eRow = existingValues[stringKey]; if (eRow != null) { if (!eRow.ItemArray.SequenceEqual(r.ItemArray)) { AddRow(updTbl, r); } existingValues[stringKey] = null; //not to be deleted } } else AddRow(insTbl, r); } //remove foreach (KeyValuePair<String, DataRow> kvp in existingValues) { if (kvp.Value != null) { AddRow(delTbl, kvp.Value); } } //delete if (delTbl.Rows.Count > 0) { delCmdBatch.Parameters["@Data"].Value = delTbl; delCmdBatch.ExecuteNonQuery(); } //update if (updTbl.Rows.Count > 0) { updCmdBatch.Parameters["@Data"].Value = updTbl; updCmdBatch.ExecuteNonQuery(); } //insert if (insTbl.Rows.Count > 0) { insCmdBatch.Parameters["@Data"].Value = insTbl; insCmdBatch.ExecuteNonQuery(); } } */ private void UpdateTabularSectionBatchAll(Guid entityId, TabularSection section, SqlCommand insCmd, SqlCommand updCmdBatch) { String[] keyFields = section.KeyFields; //create table DataTable updTbl = new DataTable(); for (int i = 1; i < insCmd.Parameters.Count; i++) { SqlParameter p = insCmd.Parameters[i]; String pName = p.ParameterName.Substring(1); Type t = SqlDbTypeToType(p.SqlDbType); if (!pName.ToLower().Equals("ref")) updTbl.Columns.Add(pName, t); } FillDataTable(updTbl, section, false); updCmdBatch.Parameters["@Ref"].Value = entityId; updCmdBatch.Parameters["@Data"].Value = updTbl; updCmdBatch.ExecuteNonQuery(); }
private void InsertTabularSection(Guid entityId, TabularSection section, SqlCommand childCmd, SqlCommand childCmdBatch) { if (childCmdBatch != null && section.Entities.Count > 1) //batch insert is supported { DataTable tbl = new DataTable(); for (int i = 1; i < childCmd.Parameters.Count; i++) { SqlParameter p = childCmd.Parameters[i]; String pName = p.ParameterName.Substring(1); if (!pName.ToLower().Equals("ref")) tbl.Columns.Add(pName, SqlDbTypeToType(p.SqlDbType)); } FillDataTable(tbl, section, false); childCmdBatch.Parameters["@Ref"].Value = entityId; childCmdBatch.Parameters["@Data"].Value = tbl; childCmdBatch.ExecuteNonQuery(); } else { foreach (var childRow in section.Entities) { childCmd = FillParameters(childCmd, childRow); childCmd.Parameters["@Ref"].Value = entityId; childCmd.ExecuteNonQuery(); } } }
private bool UploadRow(Entity row, SqlTransaction tran, Dictionary <String, SqlCommand[]> sqlCommands, Dictionary <Entity, Exception> fkErrors, bool checkExisting = false) { String rawEntityName = row.Attributes["_Type"]; int cmdType = int.Parse(row.Attributes["_RS"]); String[] arr = rawEntityName.Split('.'); String entityName = String.Format("[{0}].[{1}]", arr[0], arr[1]); SqlCommand[] cmds = null; if (!sqlCommands.ContainsKey(entityName)) { cmds = new SqlCommand[5]; cmds[0] = new SqlCommand(String.Format("[{0}].[{1}_adm_insert]", arr[0], arr[1]), tran.Connection, tran); cmds[1] = new SqlCommand(String.Format("[{0}].[{1}_adm_update]", arr[0], arr[1]), tran.Connection, tran); cmds[2] = new SqlCommand(String.Format("[{0}].[{1}_adm_delete]", arr[0], arr[1]), tran.Connection, tran); cmds[3] = new SqlCommand(String.Format("[{0}].[{1}_adm_markdelete]", arr[0], arr[1]), tran.Connection, tran); cmds[4] = null; for (int i = 0; i < 4; i++) { SqlCommand c = cmds[i]; c.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(c); } sqlCommands.Add(entityName, cmds); if (checkExisting) { SqlCommand c = new SqlCommand(String.Format("[{0}].[{1}_adm_exists]", arr[0], arr[1]), tran.Connection, tran); c.CommandType = CommandType.StoredProcedure; try { SqlCommandBuilder.DeriveParameters(c); cmds[4] = c; } catch { } } } else { cmds = sqlCommands[entityName]; } SqlCommand cmd = cmds[cmdType]; FillParameters(cmd, row); if (checkExisting && cmds[4] != null && (cmdType == 0 || cmdType == 1)) { cmds[4].Parameters["@Id"].Value = Guid.Parse(row.Attributes["Id"]); object v = cmds[4].ExecuteScalar(); if (v != null && cmdType == 0) //exists, will do update instead { cmdType = 1; cmd = cmds[cmdType]; FillParameters(cmd, row); } if (v == null && cmdType == 1) //not exists, will do insert instead { cmdType = 0; cmd = cmds[cmdType]; FillParameters(cmd, row); } } try { cmd.ExecuteNonQuery(); if (cmdType == 0) { row.Attributes["_RS"] = "1"; } Guid entityId = (Guid)cmd.Parameters["@Id"].Value; if (cmdType != 2) //insert, update or markdelete { foreach (var tabularSection in row.GetTabularSections()) { TabularSection tSection = tabularSection.Value; SqlCommand childCmd = null; String ts = String.Format("{0}_{1}", rawEntityName, tabularSection.Key); SqlCommand[] cmdsc = null; if (!sqlCommands.ContainsKey(ts)) { cmdsc = new SqlCommand[7]; cmdsc[0] = new SqlCommand(String.Format("{0}_adm_clear", ts), tran.Connection, tran); cmdsc[1] = new SqlCommand(String.Format("{0}_adm_insert", ts), tran.Connection, tran); if (!String.IsNullOrEmpty(tSection.Key)) { cmdsc[2] = new SqlCommand(String.Format("{0}_adm_update", ts), tran.Connection, tran); cmdsc[3] = new SqlCommand(String.Format("{0}_adm_delete", ts), tran.Connection, tran); cmdsc[4] = new SqlCommand(String.Format("{0}_adm_selectkeys", ts), tran.Connection, tran); } for (int i = 0; i < cmdsc.Length; i++) { SqlCommand c = cmdsc[i]; if (c != null) { c.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(c); } } //batch operations try { SqlCommand bins = new SqlCommand(String.Format("{0}_adm_insert_batch", ts), tran.Connection, tran); bins.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(bins); String[] pn = bins.Parameters["@Data"].TypeName.Split('.'); bins.Parameters["@Data"].TypeName = String.Format("{0}.{1}", pn[pn.Length - 2], pn[pn.Length - 1]); //ignore db name cmdsc[5] = bins; SqlCommand bupd = new SqlCommand(String.Format("{0}_adm_update_batch_all", ts), tran.Connection, tran); bupd.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(bupd); String[] pn2 = bupd.Parameters["@Data"].TypeName.Split('.'); bupd.Parameters["@Data"].TypeName = String.Format("{0}.{1}", pn2[pn2.Length - 2], pn2[pn2.Length - 1]); //ignore db name cmdsc[6] = bupd; } catch { } sqlCommands.Add(ts, cmdsc); } cmdsc = sqlCommands[ts]; if (cmdType > 0) //update or markdelete { if (String.IsNullOrEmpty(tSection.Key) || tabularSection.Value.Entities.Count == 0) { //remove all childCmd = cmdsc[0]; childCmd.Parameters["@Ref"].Value = entityId; childCmd.ExecuteNonQuery(); //insert rows if (tabularSection.Value.Entities.Count > 0) { InsertTabularSection(entityId, tabularSection.Value, cmdsc[1], cmdsc[5]); } } else { if (cmdsc[6] != null) //batch update is supported { UpdateTabularSectionBatchAll(entityId, tabularSection.Value, cmdsc[1], cmdsc[6]); } else { //select existing childCmd = cmdsc[4]; childCmd.Parameters["@Ref"].Value = entityId; DataTable tbl = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(childCmd); ad.Fill(tbl); String[] keyFields = tSection.KeyFields; Dictionary <String, String> keyValues = new Dictionary <String, String>(); //update rows childCmd = cmdsc[2]; foreach (var childRow in tabularSection.Value.Entities) { childCmd = FillParameters(childCmd, childRow); String stringKey = ""; foreach (String kf in keyFields) { SqlParameter p = childCmd.Parameters["@" + kf]; object keyValue = p.Value; if (p.DbType == System.Data.DbType.DateTime || p.DbType == System.Data.DbType.DateTime2) { keyValue = System.DateTime.Parse(keyValue.ToString()); } if (keyValue is IFormattable) { stringKey = stringKey + (keyValue == null ? "null" : ((IFormattable)keyValue).ToString(null, System.Globalization.CultureInfo.InvariantCulture)) + "_"; } else { stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; } } if (!keyValues.ContainsKey(stringKey)) { keyValues.Add(stringKey, ""); } childCmd.Parameters["@Ref"].Value = entityId; childCmd.ExecuteNonQuery(); } //remove childCmd = cmdsc[3]; foreach (DataRow r in tbl.Rows) { String stringKey = ""; foreach (String kf in keyFields) { childCmd.Parameters["@" + kf].Value = r[kf]; object keyValue = r[kf]; if (keyValue is IFormattable) { stringKey = stringKey + (keyValue == null ? "null" : ((IFormattable)keyValue).ToString(null, System.Globalization.CultureInfo.InvariantCulture)) + "_"; } else { stringKey = stringKey + (keyValue == null ? "null" : keyValue.ToString()) + "_"; } } if (!keyValues.ContainsKey(stringKey)) { childCmd.Parameters["@Ref"].Value = entityId; childCmd.ExecuteNonQuery(); } } } } } else { //insert rows InsertTabularSection(entityId, tabularSection.Value, cmdsc[1], cmdsc[5]); } } } return(true); } catch (SqlException e) { if (e.Message.ToLower().Contains("foreign key") || e.Message.ToLower().Contains("reference constraint")) { if (!fkErrors.ContainsKey(row)) { fkErrors.Add(row, new Exception(String.Format("Key violation at object '{0}'", row.Attributes["Id"]), e)); } return(false); } else { throw; } } }