How to update a SQL database from a dictionary ?

[Pindurs]

Hello everyone,

I have to update a database with a dictionary which contains about 1
million entries.

I wrote a simple stored procedure that updates the row if the entry
exists and creates a new one otherwise.

For sure, when I loop the dictionary, it takes way too long to update
the table:


foreach (KeyValuePair<string, int> kvp in TripleDic)
{
SqlCommand Sqlcom = sqlCon.CreateCommand();
Sqlcom.CommandText = ("EXEC UpdateTriple '" + kvp.Key
+ "', " + kvp.Value);
Sqlcom.ExecuteNonQuery();
}


I could send a list of rows, which would increase speed, but I was
wondering if there was a better and easier way to do this...

Thanks in advance !

 

[Jeroen Mostert]

I have to update a database with a dictionary which contains about 1
million entries.

I wrote a simple stored procedure that updates the row if the entry
exists and creates a new one otherwise.

For sure, when I loop the dictionary, it takes way too long to update
the table:


foreach (KeyValuePair<string, int> kvp in TripleDic)
{
SqlCommand Sqlcom = sqlCon.CreateCommand();
Sqlcom.CommandText = ("EXEC UpdateTriple '" + kvp.Key
+ "', " + kvp.Value);
Sqlcom.ExecuteNonQuery();
}


I could send a list of rows, which would increase speed, but I was
wondering if there was a better and easier way to do this...

Take a look at the SqlBulkCopy class. This can only do pure inserts, but you
can bulk copy the data to a temporary table on the server, then invoke a
stored procedure that uses an INSERT and UPDATE statement (or a single MERGE
statement if you're using SQL Server 2008) to update all the rows at once
(or in batches, if your transaction log grows too quickly).

 

[Pindurs]

Take a look at the SqlBulkCopy class. This can only do pure inserts, but you
can bulk copy the data to a temporary table on the server, then invoke a
stored procedure that uses an INSERT andUPDATEstatement (or a single MERGE
statement if you're usingSQLServer 2008) toupdateall the rows at once
(or in batches, if your transaction log grows too quickly).

Thanks for your answer, it worked very well.

I was populating a DataTable from the Dictionary and using this
DataTable as the DataSource for SqlBulkCopy.

Result: instead of some hours, it takes now some seconds !

As you mentioned, it's adding rows instead of updating them, but I
solved it by first checking the last Id of the table before
SqlBulkCopy and deleting the rows afterwards. A stored procedure would
probably be more secure, but I'm not comfortable enough with sql yet.


In case my clumsy code could be useful for anyone, here it is:

Dictionary<string, int> TripleWordsDic = new
Dictionary<string, int>(StringComparer.OrdinalIgnoreCase); //
The key is a string and the value is an int

° ° °

private void CopyDicToDB()
{
// 3 columns in the same format as the destination table
DataColumn DCId = new DataColumn();
DCId.DataType = System.Type.GetType("System.Int32");

DataColumn DCKey = new DataColumn();
DCKey.DataType = System.Type.GetType("System.String");

DataColumn DCValue = new DataColumn();
DCValue.DataType = System.Type.GetType("System.Int32");

//Create a DataTable
DataTable DT_Triples = new DataTable();

DT_Triples.Columns.Add(DCId);
DT_Triples.Columns.Add(DCKey);
DT_Triples.Columns.Add(DCValue);

DataRow DTRowTmp;

foreach (KeyValuePair<string, int> kvp in TripleWordsDic)
{
DTRowTmp = DT_Triples.NewRow();

DTRowTmp[0] = DBNull.Value; // the destination
column 0 is an Identity
DTRowTmp[1] = kvp.Key;
DTRowTmp[2] = kvp.Value;

DT_Triples.Rows.Add(DTRowTmp);
}

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = @"Integrated
Security=SSPI;Persist Security Info=False;Initial
Catalog=Words_French;Data Source=PAPS\SQLEXPRESS";
sqlConn.Open();

// let's first find out the last Id on the destination
table
long MaxId = 0;
string lastId = SimpleReadReq("SELECT MAX(Id) FROM
Triple"); //SimpleReadReq and SimpleExecReq are homemade

if (lastId !="")
{
MaxId = Convert.ToInt64(lastId);
}

//Copy the content of the Dictionary to the destination
Table
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
{
bulkCopy.DestinationTableName = "dbo.Triple";

try
{
bulkCopy.WriteToServer(DT_Triples,
DataRowState.Added);

//looks like it worked out, so lets delete former
records
if (MaxId >0)
{
SimpleExecReq("DELETE FROM Triple WHERE Id <=
" + MaxId.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
sqlConn.Close();
}