How to update a SQL database from a dictionary ?

  • Thread starter Thread starter Pindurs
  • Start date Start date
P

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 !
 
Pindurs said:
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...
Click to expand...
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).
 
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).
Click to expand...

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();
}
 
Back
Top