Hi i have a function of which iterates through a data table and constructs the output of a table to SQL format.
i.e Col 1, Col2, Col3, Row1, Row2, Row3 Outputs to INSERT INTO *tablename*(Col1, Col2, Col2)VALUES('Row1','Row2','Row3')
If the datatable is loaded with a small amount of rows the processing speed per row is fast. i.e if i load 200 rows it's instant.
But if I loaded 10,000 rows its 2-3 seconds per row, can you help optimize please?
Here is my function
public string sqlconstructorForOutputofCSVandReturnTEXT(string sCommand)
{
string concat = "";
using (DataTable dt = new DataTable())
{
StringBuilder sb = new StringBuilder();
using (StreamWriter sw = new StreamWriter(textBox2.Text))
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(sCommand, sqlConnection);
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
adapter.Fill(dt);
string[] columnnames = dt.Columns.Cast().Select(x => x.ColumnName).ToArray();
dt.Columns.Add("SQL");
dt.Columns["SQL"].SetOrdinal(0);
foreach (string colu in columnnames)
if (colu == "SQL")
{
}
else
concat = String.Format("{0}{1},", concat, colu);
string d = concat.Remove(concat.Length - 1, 1);
int rowcount = 0;
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
for (int i = 0; i < dt.Rows.Count; i++)
dt.Rows[i]["SQL"] = String.Format("INSERT INTO {0} ({1})VALUES(", listBox1.SelectedItem, d);
if (col.ColumnName == "SQL")
sb.AppendFormat(String.Format("{0}", row[col]));
else
sb.AppendFormat(String.Format("'{0}',", row[col]));
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
rowcount = rowcount + 1;
Text = rowcount.ToString();
}
sw.WriteLine(sb);
return sb.ToString();
; ;
}
}
}
}
Gowtham RajamanickamPosted Apr 5, 2015, 11:13 PM
Syed ShanuPosted Apr 5, 2015, 10:42 PM