How to update a large table using ADO.NET

Question

Ok, so here's the problem I have to solve. I need to write a method in C# that will modify a table in SQL Server 2008. The table could potentially contain millions of records. The modifications include altering the table by adding a new column and then calculating and setting the value of the new field for every row in the table.

Adding the column is not a problem. It's setting the values efficiently that is the issue. I don't want to read in the whole table into a DataTable and then update and commit for obvious reasons. I'm thinking that I would like to use a cursor to iterate over the rows in the table and update them one by one. I haven't done a whole lot of ADO.NET development, but it is my understanding that only read-only server side (firehose) cursors are supported.

So what is the correct way to go about doing something like this (preferably with some sample code in C#)? Stored procedures or other such modifications to the DB are not allowed.

Answer 1

Here's a suggestion: You can read data using a DataReader , create a update command for current row and add it to a list of commands.Then run update commands in a transaction. something like this:

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;
}
}

Answer 2

Your problem looks like something that you should be solving using T-SQL and not C#, unless there is some business rule that you are picking up dynamically and calculating the column values T-SQL should be the way to go. Just write a stored procedure or just open up Management studio and write the code to make your changes.
If this does not help then please elaborate on what exactly you want to do to the table, then we can help you figure out if this can be done via 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();

get this executed on the required instance of Sql Server 2008.
If you have too many lines of text then use StringBuilder.

Answer 3

jpgoody,

Here is an example to chew on using the NerdDinner database and some SQLConnection, SQLCommand, and SQLDataReader objects. It 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();
            }
        }
    }
}