SqlCommandBuilder и сохранение изменений DataSet в базе данных

Данное руководство устарело. Актуальное руководство: по ADO.NET и работе с базами данных в .NET 6

Получив данные в DataSet, мы можем производить с ними различными операции: удалять, изменять, добавлять новые записи. Однако все делаемые нами изменения автоматически не будут сохраняться в БД. Для этого нам еще надо вызвать метод Update объекта SqlDataAdapter, который заполнял DataSet.

Для модификации данных в БД в соответствии с изменениями в DataSet SqlDataAdapter использует команды InsertCommand, UpdateCommand и DeleteCommand. Мы можем сами определить для этих команд sql-выражения, либо мы можем воспользоваться классом SqlCommandBuilder, который позволяет автоматически сгенерировать нужные выражения. Используем SqlCommandBuilder:

static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
static void Main(string[] args) 
{
    string sql = "SELECT * FROM Users";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
		DataSet ds = new DataSet();
        adapter.Fill(ds);

        DataTable dt = ds.Tables[0];
        // добавим новую строку
        DataRow newRow = dt.NewRow();
        newRow["Name"] = "Alice";
        newRow["Age"] = 24;
        dt.Rows.Add(newRow);

        // создаем объект SqlCommandBuilder
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
        adapter.Update(ds);
        // альтернативный способ - обновление только одной таблицы
        //adapter.Update(dt);
        // заново получаем данные из бд
        // очищаем полностью DataSet
        ds.Clear();
		// перезагружаем данные
        adapter.Fill(ds);

        foreach (DataColumn column in dt.Columns)
            Console.Write("\t{0}", column.ColumnName);
        Console.WriteLine();
        // перебор всех строк таблицы
        foreach (DataRow row in dt.Rows)
        {
            // получаем все ячейки строки
            var cells = row.ItemArray;
            foreach (object cell in cells)
                Console.Write("\t{0}", cell);
            Console.WriteLine();
        }
	}
	Console.Read();
}

Здесь после загрузки данных создается новая строка, которая затем добавляется в DataTable. При вызове у адаптера метода Update() происходит анализ изменений, которые произошли. И после этого выполняется соответствующая команда. В данном случае так как идет добавление новой строки, то будет выполняться команда InsertCommand. Однако в данном коде мы нигде явным образом не задаем эту команду, за нас все автоматически делает SqlCommandBuilder. Для применения этого класса достаточно вызвать его конструктор, в который передается нужный адаптер:

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

Причем больше нигде в коде вы этот объект не вызываем.

При необходимости мы можем получить sql-выражения используемых команд:

Console.WriteLine(commandBuilder.GetUpdateCommand().CommandText);
Console.WriteLine(commandBuilder.GetInsertCommand().CommandText);
Console.WriteLine(commandBuilder.GetDeleteCommand().CommandText);

В моем случае команда обновления будет выглядеть так:

UPDATE [Users] SET [Name]=@p1, [Age]=@p2 WHERE (([Id]=@p3) AND ([Name]=@p4) AND ([Age]=@p5))

Команда вставки:

INSERT INTO [Users] ([Name],[Age]) VALUES (@p1, @p2)

Команда удаления:

DELETE FROM [Users] WHERE (([Id]=@p1) AND ([Name]=@p2) AND ([Age]=@p3))