private void InsertBulkDataIntoTable(DataTable dtInfo) { try { DataColumn[] pkey = dtInfo.PrimaryKey; string pKeyStr = string.Empty; string oCreatesqlQuery = string.Empty; if (pkey.Count() > 0) { pKeyStr = pkey[0].ColumnName; } else { pKeyStr = dtInfo.Columns[dtInfo.Columns.Count - 1].ColumnName; } Regex re = new Regex("[;\\/:*?\"<>|&']"); InsertTableInfo oTableInfo = new InsertTableInfo(); string oInsertQuery = "DELETE FROM " + dtInfo.TableName + " \n"; bool IsCreated = Convert.ToBoolean(obDBCtxt.ExecuteStoreCommand(oInsertQuery)); string[] columnNames = dtInfo.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); //DataTable temp=RemoveDuplicateRows(dtInfo, pKeyStr); //IsCreated = InsertBulkDataToDB(RemoveDuplicateRows(dtInfo, pKeyStr)); IsCreated = InsertBulkDataToDB(dtInfo); if (IsCreated) { oTableInfo.TableName = dtInfo.TableName; oTableInfo.IsInserted = true; oTableInfo.InsertedRow = dtInfo.Rows.Count; oInsertTableInfo.Add(oTableInfo); } else { InsertDataIntoTable(dtInfo.DefaultView.ToTable(true, columnNames)); oTableInfo.TableName = dtInfo.TableName; oTableInfo.IsInserted = false; oTableInfo.InsertedRow = dtInfo.Rows.Count; oInsertTableInfo.Add(oTableInfo); } } catch (Exception ex) { } }
private void InsertDataIntoTable(DataTable dtInfo) { try { Regex re = new Regex("[;\\/:*?\"<>|&']"); InsertTableInfo oTableInfo = new InsertTableInfo(); string oInsertQuery = "DELETE FROM " + dtInfo.TableName + " \n"; foreach (DataRow drInfo in dtInfo.Rows) { oInsertQuery += " INSERT INTO " + dtInfo.TableName + " ("; for (int i = 0; i < dtInfo.Columns.Count; i++) { oInsertQuery += dtInfo.Columns[i].ColumnName; if (i != (dtInfo.Columns.Count - 1)) { oInsertQuery += " , \n"; } } oInsertQuery += " ) VALUES ("; for (int i = 0; i < dtInfo.Columns.Count; i++) { if (dtInfo.Columns[i].DataType.ToString().Contains("System.Int32") || dtInfo.Columns[i].DataType.ToString().Contains("System.Double")) { oInsertQuery += string.IsNullOrEmpty(drInfo[i].ToString()) ? " NULL " : drInfo[i].ToString(); } else if (!string.IsNullOrEmpty(drInfo[i].ToString()) && drInfo[i].ToString().Length > 10 && dtInfo.Columns[i].DataType.ToString().Contains("System.String") && dtInfo.Columns[i].ColumnName.ToLower().Contains("date")) { oInsertQuery += "'" + re.Replace(drInfo[i].ToString().Substring(0, 10), "") + "'"; } else if (dtInfo.Columns[i].DataType.ToString().Contains("System.String")) { oInsertQuery += "'" + re.Replace(drInfo[i].ToString(), "") + "'"; } else if (dtInfo.Columns[i].DataType.ToString().Contains("System.DateTime")) { oInsertQuery += string.IsNullOrEmpty(drInfo[i].ToString()) ? " NULL " : "'" + re.Replace(drInfo[i].ToString().Substring(0, 10), "") + "'"; } else { oInsertQuery += string.IsNullOrEmpty(drInfo[i].ToString()) ? " NULL " : "'" + drInfo[i].ToString() + "'"; } if (i != (dtInfo.Columns.Count - 1)) { oInsertQuery += " , \n"; } } oInsertQuery += " )"; } bool IsCreated = Convert.ToBoolean(obDBCtxt.ExecuteStoreCommand(oInsertQuery)); if (IsCreated) { oTableInfo.TableName = dtInfo.TableName; oTableInfo.IsInserted = true; oTableInfo.InsertedRow = dtInfo.Rows.Count; oInsertTableInfo.Add(oTableInfo); } } catch (Exception ex) { } }