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