How to update a large table using ADO.NET

Question

So here is the problem I have to solve. I need to write a method in C # that will change a table in SQL Server 2008. A table can contain millions of records. Changes include changing the table by adding a new column, and then calculating and setting the value of the new field for each row in the table.

Adding a column is not a problem. This effectively determines the values. I do not want to read the entire table in a DataTable, and then update and transfer for obvious reasons. I think I would like to use the cursor to iterate over rows in a table and update them one at a time. I have not done much ADO.NET development, but I understand that only server-side cursors (firehose) are read-only.

So, what is the correct way to do something like this (preferably with some C # code example)? Stored procedures or other such changes to the database are not allowed.

Answer 1

jpgoody,

Here's an example to chew on the NerdDinner database and some SQLConnection, SQLCommand, and SQLDataReader objects. He adds one day to each of the event dates in the Dinners table.

using System; using System.Data.SqlClient; namespace NerdDinner { public class Class1 { public void Execute() { SqlConnection readerConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); readerConnection.Open(); SqlCommand cmd = new SqlCommand("SELECT DinnerID, EventDate FROM Dinners", readerConnection); SqlDataReader reader = cmd.ExecuteReader(); SqlConnection writerConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); writerConnection.Open(); SqlCommand writerCommand = new SqlCommand("", writerConnection); while (reader.Read()) { int DinnerID = reader.GetInt32(0); DateTime EventDate = reader.GetDateTime(1); writerCommand.CommandText = "UPDATE Dinners SET EventDate = '" + EventDate.AddDays(1).ToString() + "' WHERE DinnerID = " + DinnerID.ToString(); writerCommand.ExecuteNonQuery(); } } } } 

Answer 2

Your problem looks like what you need to solve using T-SQL, not C #, unless there is some kind of business rule that you select dynamically and calculate the column values. T-SQL should be a way. Just write a stored procedure or just open a management studio and write a code to make changes.

If this does not help, please clarify what exactly you want to do in the table, then we can help you figure out whether it can be done through T-SQL or not.

[EDIT] you can do something like this

  string sql = " USE " + paramDbName; sql+= " ALTER TABLE XYZ ADD COLUMN " + param1 + " datatype etc, then put semicolon to separate the commands as well" sql+= " UPDATE XYZ SET Columnx = " + some logic here cmd.CommandText = sql; cmd.ExecuteNonQuery(); 

Run this on the right instance of Sql Server 2008.
If you have too many lines of text, use StringBuilder.

Answer 3

Here is a suggestion: You can read data using the DataReader, create an update command for the current line and add it to the list of commands. Then run update commands in the transaction. something like that:

 var commands=new List<SqlCommand>(); while(dr.Read()) { var cmd=new SqlCommand(); cmd.CommandText="Add your command text here"; commands.Add(cmd); } using(var cnn=new SqlConnection("Connection String")) { IDbTransaction transaction; try { cnn.Open(); transaction=cnn.BeginTransaction(); foreach(var cmd in commands) { cmd.Transaction=transaction; cmd.ExecuteNonQuery(); cmd.Dispose(); } transaction.Commit(); } catch(SqlException) { if(transaction!=null) transaction.Rollback(); throw; } }