Обновление источников данных с помощью DataAdapter

Область применения: платформа .NET Framework .NET Standard

Скачать ADO.NET

Метод Update объекта DataAdapter вызывается для решения задачи по передаче изменений из DataSet обратно в источник данных. Метод Update, как и метод Fill, принимает в качестве аргументов экземпляр DataSet, а также (необязательно) объект DataTable или имя DataTable. Экземпляр DataSet представляет собой объект DataSet, который содержит выполненные изменения, а DataTable указывает на таблицу, из которой должны быть получены эти изменения. Если ни один объект DataTable не задан, используется первый объект DataTable в DataSet.

При вызове метода Update в DataAdapter анализируются внесенные изменения и выполняется соответствующая команда (INSERT, UPDATE или DELETE). Если в DataAdapter обнаруживается изменение в DataRow, то в этом объекте используется команда InsertCommand, UpdateCommand или DeleteCommand для обработки этого изменения.

Эти свойства позволяют максимально увеличить производительность приложений ADO.NET за счет указания синтаксиса команд во время разработки и, где это возможно, за счет использования хранимых процедур. Необходимо явно задавать команды перед вызовом Update. Если вызывается Update, и не существует подходящая команда для конкретного обновления (например, отсутствует DeleteCommand для удаленных строк), то активизируется исключение.

Параметры команды можно использовать для указания входных и выходных значений инструкции SQL или хранимой процедуры для каждой измененной строки в строке DataSet. Дополнительные сведения см. в разделе Параметры DataAdapter.

Если значение DataTable сопоставляется или создается на основе одной таблицы базы данных, то можно воспользоваться тем, что объект DbCommandBuilder автоматически создает объекты DeleteCommand, InsertCommand и UpdateCommand для DataAdapter. Дополнительные сведения см. в статье Создание команд с помощью классов CommandBuilder.

Использование UpdatedRowSource для сопоставления значений в наборе данных

Можно управлять тем, как значения, возвращаемые из источника данных, сопоставляются с DataTable после вызова метода Update объекта DataAdapter, используя свойство UpdatedRowSource объекта SqlCommand. Задавая значение свойства UpdatedRowSource равным одному из значений перечисления UpdateRowSource, можно управлять тем, должны ли пропускаться выходные параметры, возвращаемые командами DataAdapter, или применяться к изменившейся строке в DataSet. Можно также указать, применяется ли первая возвращенная строка (если она существует) к изменившейся строке в DataTable.

В следующей таблице приведено описание различных значений перечисления UpdateRowSource и показано, как они влияют на поведение команды, используемой в сочетании с DataAdapter.

Метод Update позволяет решить задачу по передаче внесенных изменений обратно в источник данных; но может оказаться так, что другие клиенты уже внесли изменения в данные источника данных с того момента, как последний раз было осуществлено заполнение DataSet. Чтобы обновить применяемый объект DataSet с использованием текущих данных, воспользуйтесь DataAdapter и методом Fill. Произойдет добавление новых строк к таблице, а обновленная информация будет включена в существующие строки.

Метод Fill определяет, должна ли быть добавлена новая строка или обновлена существующая строка, путем проверки значений первичного ключа в строках объекта DataSet и в строках, возвращенных SelectCommand. Если в методе Fill обнаруживается значение первичного ключа какой-то строки в DataSet, которое совпадает со значением первичного ключа строки в результатах, возвращенных SelectCommand, то метод обновляет существующую строку на основании данных из строки, возвращенной SelectCommand, и задает значение RowState существующей строки, равное Unchanged. Если строка, возвращенная SelectCommand, имеет значение первичного ключа, не совпадающее ни с одним из значений первичного ключа в строках в DataSet, то метод Fill добавляет новую строку со значением RowState, равным Unchanged.

Чтобы обеспечить обработку исключений, которые могут возникнуть при вызове метода Update, можно воспользоваться событием RowUpdated для осуществления ответных действий на ошибки обновления строк по мере их возникновения (см. раздел об обработке событий DataAdapter) или задать значение ContinueUpdateOnError равным true перед вызовом Update и осуществлять ответные действия на основании сведений об ошибке, хранящихся в свойстве RowError конкретной строки, после завершения обновления.

В следующих примерах показано, как выполнить обновления применительно к модифицированным строкам путем явного задания значения UpdateCommand объекта DataAdapter и вызова его метода Update.

private static void AdapterUpdate(string connectionString)
{
    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlDataAdapter dataAdpater = new SqlDataAdapter(
          "SELECT CategoryID, CategoryName FROM Categories",
          connection);

        dataAdpater.UpdateCommand = new SqlCommand(
           "UPDATE Categories SET CategoryName = @CategoryName " +
           "WHERE CategoryID = @CategoryID", connection);

        dataAdpater.UpdateCommand.Parameters.Add(
           "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");

        SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add(
          "@CategoryID", SqlDbType.Int);
        parameter.SourceColumn = "CategoryID";
        parameter.SourceVersion = DataRowVersion.Original;

        DataTable categoryTable = new DataTable();
        dataAdpater.Fill(categoryTable);

        DataRow categoryRow = categoryTable.Rows[0];
        categoryRow["CategoryName"] = "New Beverages";

        dataAdpater.Update(categoryTable);

        Console.WriteLine("Rows after update.");
        foreach (DataRow row in categoryTable.Rows)
        {
            {
                Console.WriteLine("{0}: {1}", row[0], row[1]);
            }
        }
    }
}

Столбцы autoIncrement

Если в таблицах из применяемого источника данных имеются столбцы с автоматическим увеличением значений, то можно обеспечить заполнение столбцов в применяемом DataSet путем возврата автоматически увеличивающегося значения как выходного параметра хранимой процедуры и сопоставления его со столбцом таблицы; возврата автоматически увеличивающегося значения в первой строке результирующего набора, возвращенного хранимой процедурой или инструкцией SQL; а также использование события RowUpdated объекта DataAdapter для выполнения дополнительной инструкции SELECT. Дополнительные сведения и пример см. в статье Извлечение значений идентификаторов или автонумерации.

Порядок в операциях вставки, обновления и удаления

Во многих обстоятельствах имеет значение последовательность передачи изменений, внесенных с помощью DataSet, в источник данных. Например, если происходит обновление значения первичного ключа для существующей строки и добавляется новая строка с новым значением первичного ключа в качестве внешнего ключа, то важно вначале осуществить обновление, а затем вставку.

Можно использовать метод Select объекта DataTable для возврата массива DataRow, который ссылается только на строки с конкретным значением RowState. После этого можно передать возвращенный массив DataRow в метод Update объекта DataAdapter для обработки измененных строк. Задавая подмножество строк, подлежащих обновлению, можно управлять тем, в какой последовательности обрабатываются вставки, обновления и удаления.

Пример

Например, в следующем коде обеспечивается то, что удаленные строки таблицы обрабатываются в первую очередь, затем происходит обработка обновленных строк, после чего обрабатываются вставленные строки.

// Assumes that dataSet and adapter are valid objects.
DataTable table = dataSet.Tables["Customers"];

// First process deletes.
adapter.Update(table.Select(null, null, DataViewRowState.Deleted));

// Next process updates.
adapter.Update(table.Select(null, null,
    DataViewRowState.ModifiedCurrent));

// Finally, process inserts.
adapter.Update(table.Select(null, null, DataViewRowState.Added));

Использование объекта DataAdapter для получения и обновления данных

DataAdapter можно использовать для извлечения и обновления данных.

• В примере используется метод DataAdapter.AcceptChangesDuringFill для клонирования данных в базе данных. Если свойство имеет значение false, метод AcceptChanges не вызывается при заполнении таблицы, а вновь добавленные строки рассматриваются как вставленные строки. Таким образом, в примере эти строки используются для вставки новых строк в базу данных.

• В примерах используется метод DataAdapter.TableMappings для определения сопоставления между исходной таблицей и DataTable.

• В примере используется метод DataAdapter.FillLoadOption, чтобы определить, как адаптер заполняет объект DataTable из DbDataReader. При создании объекта DataTable можно записать данные из базы данных только в текущую или исходную версию, задав в качестве значения свойства LoadOption.Upsert или LoadOption.PreserveChanges.

• Пример также обновит таблицу с помощью метода DbDataAdapter.UpdateBatchSize для выполнения пакетных операций.

Перед компиляцией и выполнением примера необходимо создать образец базы данных:

USE [master]
GO

CREATE DATABASE [MySchool]

GO

USE [MySchool]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)

SET IDENTITY_INSERT [dbo].[Department] ON

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF

ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO

using System;
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using System.Linq;
using CSDataAdapterOperations.Properties;

class Program
{
    static void Main(string[] args)
    {
        Settings settings = new Settings();

        // Copy the data from the database.  Get the table Department and Course from the database.
        String selectString = @"SELECT [DepartmentID],[Name],[Budget],[StartDate],[Administrator]
                                     FROM [MySchool].[dbo].[Department];

                                   SELECT [CourseID],@Year as [Year],Max([Title]) as [Title],
                                   Max([Credits]) as [Credits],Max([DepartmentID]) as [DepartmentID]
                                   FROM [MySchool].[dbo].[Course]
                                   Group by [CourseID]";

        DataSet mySchool = new DataSet();

        SqlCommand selectCommand = new SqlCommand(selectString);
        SqlParameter parameter = selectCommand.Parameters.Add("@Year", SqlDbType.SmallInt, 2);
        parameter.Value = new Random(DateTime.Now.Millisecond).Next(9999);

        // Use DataTableMapping to map the source tables and the destination tables.
        DataTableMapping[] tableMappings = { new DataTableMapping("Table", "Department"), new DataTableMapping("Table1", "Course") };
        CopyData(mySchool, settings.MySchoolConnectionString, selectCommand, tableMappings);

        Console.WriteLine("The following tables are from the database.");
        foreach (DataTable table in mySchool.Tables)
        {
            Console.WriteLine(table.TableName);
            ShowDataTable(table);
        }

        // Roll back the changes
        DataTable department = mySchool.Tables["Department"];
        DataTable course = mySchool.Tables["Course"];

        department.Rows[0]["Name"] = "New" + department.Rows[0][1];
        course.Rows[0]["Title"] = "New" + course.Rows[0]["Title"];
        course.Rows[0]["Credits"] = 10;

        Console.WriteLine("After we changed the tables:");
        foreach (DataTable table in mySchool.Tables)
        {
            Console.WriteLine(table.TableName);
            ShowDataTable(table);
        }

        department.RejectChanges();
        Console.WriteLine("After use the RejectChanges method in Department table to roll back the changes:");
        ShowDataTable(department);

        DataColumn[] primaryColumns = { course.Columns["CourseID"] };
        DataColumn[] resetColumns = { course.Columns["Title"] };
        ResetCourse(course, settings.MySchoolConnectionString, primaryColumns, resetColumns);
        Console.WriteLine("After use the ResetCourse method in Course table to roll back the changes:");
        ShowDataTable(course);

        // Batch update the table.
        String insertString = @"Insert into [MySchool].[dbo].[Course]([CourseID],[Year],[Title],
                                   [Credits],[DepartmentID])
             values (@CourseID,@Year,@Title,@Credits,@DepartmentID)";
        SqlCommand insertCommand = new SqlCommand(insertString);
        insertCommand.Parameters.Add("@CourseID", SqlDbType.NVarChar, 10, "CourseID");
        insertCommand.Parameters.Add("@Year", SqlDbType.SmallInt, 2, "Year");
        insertCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 100, "Title");
        insertCommand.Parameters.Add("@Credits", SqlDbType.Int, 4, "Credits");
        insertCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4, "DepartmentID");

        const Int32 batchSize = 10;
        BatchInsertUpdate(course, settings.MySchoolConnectionString, insertCommand, batchSize);
    }

    private static void CopyData(DataSet dataSet, String connectionString, SqlCommand selectCommand, DataTableMapping[] tableMappings)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            selectCommand.Connection = connection;

            connection.Open();

            using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
            {
                adapter.TableMappings.AddRange(tableMappings);
                // If set the AcceptChangesDuringFill as the false, AcceptChanges will not be called on a
                // DataRow after it is added to the DataTable during any of the Fill operations.
                adapter.AcceptChangesDuringFill = false;

                adapter.Fill(dataSet);
            }
        }
    }

    // Roll back only one column or several columns data of the Course table by call ResetDataTable method.
    private static void ResetCourse(DataTable table, String connectionString,
        DataColumn[] primaryColumns, DataColumn[] resetColumns)
    {
        table.PrimaryKey = primaryColumns;

        // Build the query string
        String primaryCols = String.Join(",", primaryColumns.Select(col => col.ColumnName));
        String resetCols = String.Join(",", resetColumns.Select(col => $"Max({col.ColumnName}) as {col.ColumnName}"));

        String selectString = $"Select {primaryCols},{resetCols} from Course Group by {primaryCols}";

        SqlCommand selectCommand = new SqlCommand(selectString);

        ResetDataTable(table, connectionString, selectCommand);
    }

    // RejectChanges will roll back all changes made to the table since it was loaded, or the last time AcceptChanges
    // was called. When you copy from the database, you can lose all the data after calling RejectChanges
    // The ResetDataTable method rolls back one or more columns of data.
    private static void ResetDataTable(DataTable table, String connectionString,
        SqlCommand selectCommand)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            selectCommand.Connection = connection;

            connection.Open();

            using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
            {
                // The incoming values for this row will be written to the current version of each
                // column. The original version of each column's data will not be changed.
                adapter.FillLoadOption = LoadOption.Upsert;

                adapter.Fill(table);
            }
        }
    }

    private static void BatchInsertUpdate(DataTable table, String connectionString,
        SqlCommand insertCommand, Int32 batchSize)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            insertCommand.Connection = connection;
            // When setting UpdateBatchSize to a value other than 1, all the commands
            // associated with the SqlDataAdapter have to have their UpdatedRowSource
            // property set to None or OutputParameters. An exception is thrown otherwise.
            insertCommand.UpdatedRowSource = UpdateRowSource.None;

            connection.Open();

            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                adapter.InsertCommand = insertCommand;
                // Gets or sets the number of rows that are processed in each round-trip to the server.
                // Setting it to 1 disables batch updates, as rows are sent one at a time.
                adapter.UpdateBatchSize = batchSize;

                adapter.Update(table);

                Console.WriteLine("Successfully to update the table.");
            }
        }
    }

    private static void ShowDataTable(DataTable table)
    {
        foreach (DataColumn col in table.Columns)
        {
            Console.Write("{0,-14}", col.ColumnName);
        }
        Console.WriteLine("{0,-14}", "RowState");

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn col in table.Columns)
            {
                if (col.DataType.Equals(typeof(DateTime)))
                    Console.Write("{0,-14:d}", row[col]);
                else if (col.DataType.Equals(typeof(Decimal)))
                    Console.Write("{0,-14:C}", row[col]);
                else
                    Console.Write("{0,-14}", row[col]);
            }
            Console.WriteLine("{0,-14}", row.RowState);
        }
    }
}

namespace CSDataAdapterOperations.Properties
{
    internal sealed partial class Settings : System.Configuration.ApplicationSettingsBase
    {
        private static readonly Settings defaultInstance =
            ((Settings)(System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));

        public static Settings Default => defaultInstance;

        [System.Configuration.ApplicationScopedSetting()]
        [System.Configuration.DefaultSettingValue("Data Source=(local);Initial Catalog=MySchool;Integrated Security=True")]
        public string MySchoolConnectionString => ((string)(this["MySchoolConnectionString"]));
    }
}

См. также

• Объекты DataAdapter и DataReader
• Извлечение значений идентификаторов или автонумерации
• Microsoft ADO.NET для SQL Server