如何使用ADO.NET来更新一个大表 [英] How to update a large table using ADO.NET

问题描述

好了，所以这里有一个问题我来解决。我需要写在C＃中的方法，将修改表SQL Server 2008中的表可能包含数百万条记录。所述修饰包括改变表通过添加新的列，然后计算和设置新的字段的值表中的每一行。

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.

添加列是没有问题的。它的有效设置值是问题。我不想读取整个表变成一个数据表，然后更新和提交，原因很明显。我在想，我想用游标遍历表中的行和一个更新它们。我没有做了一大堆ADO.NET的发展，但它是我的理解，只有只读服务器端（流水）游标支持。

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.

那么，什么是去这样做这样的事情（preferably在C＃中一些样本code）正确的方法是什么？存储过程或其他这样的修改和DB是不允许的。

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.

推荐答案

jpgoody，

下面是一个例子，啃使用的NerdDinner 数据库和一些的SQLConnection，SqlCommand和SqlDataReader的对象。它增加了一天的每个事件的日期在晚餐表。

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

这篇关于如何使用ADO.NET来更新一个大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！