Обновление БД из DataSet вручную

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

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

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
adapter.Update(ds);
ds.Clear();
adapter.Fill(ds);

После обновления происходит очистка DataSet и перезагрузка данных, что снижает производительность приложения. Хотя в DataTable у нас уже добавлена строка с новыми данными, и в ней не хватает только id - значения, которое генерируется самой базой данных при добавлении. Без id нам трудно будет управлять данными, мы не сможем их автоматически через тот же SqlCommandBuilder обновлять или удалять. Поэтому в идеале хотелось бы избежать и перезагрузки данных и в то же время получить id новой записи при выполнении метода adapter.Update. И более гибкий способ состоит в том, что мы сами вручную определяем все те выражения, которые будут выполняться.

Итак, добавим в базу данных следующую хранимую процедуру:

CREATE PROCEDURE [dbo].[sp_CreateUser]
    @name nvarchar(50),
    @age int,
	@Id int out
AS
    INSERT INTO Users (Name, Age)
    VALUES (@name, @age)
 
    SET @Id=SCOPE_IDENTITY()
GO

В качестве входных параметров она принимает имя и возраст пользователя и возвращает его id.

Теперь изменим код из прошлой темы:

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);
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

		// устанавливаем команду на вставку
        adapter.InsertCommand = new SqlCommand("sp_CreateUser", connection);
		// это будет зранимая процедура
        adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
		// добавляем параметр для name
        adapter.InsertCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50, "Name"));
		// добавляем параметр для age
        adapter.InsertCommand.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 0, "Age"));
		// добавляем выходной параметр для id
        SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
        parameter.Direction = ParameterDirection.Output;
		
		DataSet ds = new DataSet();
        adapter.Fill(ds);

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

        adapter.Update(ds);
        ds.AcceptChanges();
        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();
}

Здесь также используется SqlCommandBuilder, но теперь из-за переустановки свойства adapter.InsertCommand выполнение добавления нового объекта будет переопределено.

При определении выходного параметра мы указываем, что он будет называться "@Id" и будет возвращать значение для столбца Id в DataTable:

adapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");

В итоге при выполнении метода adapter.Update() id автоматически попадет в DataTable. При этом операции по обновлению и удалению мы можем использовать те же, что предоставляет SqlCommandBuilder.

И после обновления базы данных с помощью метода AcceptChanges() объекта DataSet производится принятие всех изменений в DataSet ко всем измененным строкам.