protected void SaveDataTableToSQL(DataTable dt, int filepk)
{
dt.Columns.Add("filepk", typeof(int));
dt.Columns.Add("pk", typeof(int));
int counter = 1;
foreach (DataRow row in dt.Rows)
{
row["filepk"] = filepk;
row["pk"] = counter;
counter++;
}
DataColumn[] pkcol = new DataColumn[1];
pkcol[0] = dt.Columns["pk"];
pkcol[0].AutoIncrement = true;
dt.PrimaryKey = pkcol;
string DestinationTableName = "file" + filepk.ToString("D3");
SQL_utils sql = new SQL_utils("backend");
string createSQL = sql.GetCreateTableSql(dt, "gev", DestinationTableName, false);
string createTable = sql.StringScalar_from_SQLstring(createSQL);
//If there is a max column, truncate data to left(2000)
if (createSQL.Contains("/*TOOLONG*/"))
{
lblSubmitInfo.Text += "Warning: Some columns were truncated to 200 characters.<br/><br/>";
lblSubmitInfo.ForeColor = System.Drawing.Color.DarkRed;
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
if (row[col.ColumnName].ToString().Length > 200)
{
row[col.ColumnName] = row[col.ColumnName].ToString().PadRight(200).Substring(0, 200).Trim();
}
}
}
}
// Create the SqlBulkCopy object.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sql.SqlConnection))
{
bulkCopy.DestinationTableName = "gev." + DestinationTableName;
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dt);
string nrows_inserted = sql.StringScalar_from_ProcName("gev.spEvents_EXTRACT",
sql.CreateParam("filepk", filepk.ToString(), "int"));
string prefix = (filepk < 10) ? "00" : (filepk < 100) ? ")" : "";
int n = sql.IntScalar_from_SQLstring("select count(*) from gev.file" + prefix + filepk.ToString());
lblSubmitInfo.Text += n.ToString() + " rows inserted.";
lblSubmitInfo.ForeColor = System.Drawing.Color.Blue;
}
catch (Exception ex)
{
lblSubmitInfo.Text += (ex.Message);
//Delete this file as it was not successfully oploaded.
string del = sql.StringScalar_from_ProcName("gev.spFiles_DELETE",
sql.CreateParam("filepk", filepk.ToString(), "int"));
}
}
ListGeneEventFiles();
}