C# – How to update a large table using ADO.NET

ado.netc

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.

Best Answer

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();
            }
        }
    }
}
Related Topic