MSSQL - передача таблицы или списка значений в процедуру ( C# .NET )

MS SQL - по необходимости

Часто бывает необходимость передать за один раз некоторый набор данных в процедуру, в этой публикации будет приведен пример как это сделать на языке C# для базы данных MSSQL.

Использовать будем обычный способ вызова процедуры без каких либо фреймворков.

Для начала создадим тестовую таблицу в базе данных:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Bottles](
	[Uid] [uniqueidentifier] NOT NULL,
	[Name] [varchar](50) NULL,
	[Color] [varchar](50) NULL
) ON [PRIMARY]
GO

В данной таблице будем размещать бутылки разных названий и цветов (просто для теста).

Теперь создадим пользовательский табличный тип данных, который будем передавать в процедуру:

CREATE TYPE [dbo].[BottleColorType] AS TABLE(
	[Name] [varchar](50) NULL,
	[Color] [varchar](50) NULL
)
GO

Именно данные такого типа мы и будем передавать в процедуру.

Теперь напищем очень простую тестовую процедуру:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetBottleColors] 
	
	@DataList as dbo.BottleColorType readonly
AS
BEGIN
	SET NOCOUNT ON;

	declare @res as bit;
	set @res = 1;

	insert into Bottles (Uid, Name, Color)
    select 
	newid() as Uid,
	d.[Name] as Name,
	d.[Color] as Color
	 
	from @DataList d;

	select @res as Result;
END

Эта процедура будет вносить полученные табличные данные в таблицу Bottles

База данных теперь нами подготовлена, осталось только написать код вызова процедуры на C#:

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestProcTable
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Передаем табличку в процедуру");

            // Создаем таблицу для передачи в БД (идентичную типу BottleColorType)
            DataTable table = new DataTable();
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Color", typeof(string));

            // Заполняем в таблицу пару записей
            DataRow row = table.NewRow();
            row["Name"] = "Ессентуки";
            row["Color"] = "Зелёный";
            table.Rows.Add(row);

            row = table.NewRow();
            row["Name"] = "ХанКуль";
            row["Color"] = "Белый";
            table.Rows.Add(row);

            // Выполняем запрос к БД
            bool res = SendTableToBaseProc(table);

            Console.WriteLine(
                res ? "Запрос выполнен успешно" : "Запрос не выполнен"
                );

            Console.ReadKey();
        }

        /// <summary>
        /// Процедура выполняющая запрос к БД с передачей таблицы в качестве параметра
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool SendTableToBaseProc(DataTable table)
        {
            bool result = false;
            try
            {
                using (SqlConnection con = new SqlConnection("Server=ервер;Database=test;User Id=логин;Password=пароль;"))
                {
                    // Выполняем процедуру
                    using (SqlCommand cmd = new SqlCommand("[dbo].[GetBottleColors]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        // Добавляем таблицу как параметр
                        SqlParameter p_list = new SqlParameter
                        {
                            ParameterName = "@DataList",
                            SqlDbType = SqlDbType.Structured,
                            Direction = ParameterDirection.Input,
                            Value = table
                        };
                        cmd.Parameters.Add(p_list);

                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                result = reader.GetBoolean(reader.GetOrdinal("Result"));
                            }

                        }
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
    }
}

Вот и всё.

Выполнив данный код, в базу данных в таблицу Bottles, будут внесены 2 записи из переданной таблицы.

MSSQL Procedure Table TSQL SQL