C# Datatable- slow processing on large amount of rows

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?

I think I need to not process the datatable as a whole. Is there a way I could feed in rows by their 100's?
Or is there a better way and to have to not use a datatable.

P.S I know i can insert it straight into SQL from the datatable but i need it as an output script.

Thanks

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();

; ;

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Basil Ntinga10y ago
Deepak Verma Thank you for the article
Deepak Verma11y ago
Hi Anthony,

List is a good alternative to DataTable. Please visit the following links:

1) http://lauteikkehn.blogspot.in/2012/03/datatable-vs-list.html

2) http://java.dzone.com/articles/reasons-move-datatables