Help Required for Best way to use Insert and Update statement in a for loop of C# with best performance
Posted on 2013-06-01
My requirement is to create a job which connects a MS-SQL server of an application and retrieves a table domain id from table Employee and connect LDAP and compare the domain . Only if different then first insert in table Domain and then Update in table Employee.
I used below code for it.....But each time I am opening a connection only when the domain are different and performing Insert and Update operation. This is very slow and it takes lot of time to update more than 2000 records...
//Here I am using Code for SqlConnection, SqlDataAdapter adap and filling to Datatable dt
and closing the conn.
// Here the FOR loop of datatable starts....
for (int i = 0; i < dt.Rows.Count; i++)
retreived old domain from dt table.
Here I have written the code Connect to LDAP and run the LDAP query to retreive the current user from dt.
Here i am setting the new domain from LDAP value.
// closing the LDAP connection
below the is if statement within this FOR loop
if (newDomain != oldDomain)
//Add old domain and new domain in the new table with the date
SqlCommand cmdInsert = new SqlCommand("INSERT INTO DOMAIN (PROJECT_POOL_ID, OLD_DOMAIN, NEW_DOMAIN, UPDATED_TIME) VALUES (" + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim() + ",'" + oldDomain + "','" + newDomain + "',GETDATE())", conn);
//update the record in employee table with the new domain
SqlCommand cmdUpdate = new SqlCommand("UPDATE employee SET DOMAIN = '" + newDomain + "' WHERE LOGIN_ID = '" + dt.Rows(i)["LOGIN_ID"].ToString().Trim() + "' AND PROJECT_POOL_ID = " + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim(), conn);
int k = (int)cmdInsert.ExecuteNonQuery();
int j = (int)cmdUpdate.ExecuteNonQuery();
Note changed to (i) since problem with posting in square bracket
The above code runs perfectly but it takes lot of time to perform the update and insert operation. Could someone help me know the best way to perform Update and insert at much faster rate?