Как загрузить DataSet из хранимой процедуры SQL Server

Почему данные берут из процедуры

В приложениях на Web Forms и WinForms выборки часто прячут в хранимые процедуры. Причины практичные: план выполнения кешируется, права можно выдать на процедуру, не открывая таблицы, а SQL меняется без перекомпиляции приложения. Для кода на VB.NET процедура выглядит как функция с параметрами, которая возвращает один или несколько наборов строк.

Чтобы положить результат в DataSet, нужны три объекта ADO.NET: SqlConnection для подключения, SqlCommand с именем процедуры и параметрами и SqlDataAdapter, который выполнит команду и разложит строки по таблицам. Ниже разберём каждый шаг на примере базы Northwind: список товаров выбранной категории.

Процедура для примера

Процедура принимает идентификатор категории и возвращает товары. Обратите внимание, что параметр объявлен как обычный входной. Помечать его OUTPUT здесь незачем: процедура его не меняет, а лишний OUTPUT только запутывает читателя и требует другого объявления на стороне ADO.NET.

CREATE PROCEDURE dbo.ListProductsByCategoryID
    @CategoryID int
AS
BEGIN
    SET NOCOUNT ON;   -- не отправлять счётчики строк клиенту
    SELECT CategoryID, ProductID, ProductName, UnitPrice
    FROM Products
    WHERE CategoryID = @CategoryID
    ORDER BY ProductName;
END
GO
-- Проверка в Management Studio
EXEC dbo.ListProductsByCategoryID @CategoryID = 1;

Команда и параметры

У SqlCommand задаём CommandType.StoredProcedure, а в CommandText пишем только имя процедуры, без EXEC. Параметр лучше добавлять с явным типом через Parameters.Add(имя, SqlDbType). Метод AddWithValue выводит тип по значению, и строка из TextBox уйдёт как nvarchar, после чего SQL Server будет неявно преобразовывать её в int. В лучшем случае это лишняя работа, в худшем ошибка конвертации на пустой строке.

Значение из поля ввода сначала проверяем. Если пользователь ввёл не число, нет смысла обращаться к базе: достаточно показать сообщение.

Заполнение DataSet через SqlDataAdapter

Адаптеру передаём готовую команду и вызываем Fill. Открывать соединение вручную не обязательно: если оно закрыто, Fill откроет его сам и закроет после чтения. Блок Using гарантирует, что соединение вернётся в пул даже при исключении. В исходных примерах часто встречается свойство, которое открывает новое соединение при каждом обращении и никогда его не закрывает. Под нагрузкой такой код быстро исчерпывает пул подключений.

Imports System.Data
Imports System.Data.SqlClient

Private Function GetProducts(categoryId As Integer) As DataSet
    Dim cs = ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
    Dim ds As New DataSet("Catalog")
    Using cn As New SqlConnection(cs), _
          cmd As New SqlCommand("dbo.ListProductsByCategoryID", cn), _
          da As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryId
        da.Fill(ds, "Products")   ' Fill сам откроет и закроет соединение
    End Using
    Return ds
End Function

Protected Sub btnList_Click(sender As Object, e As EventArgs) _
        Handles btnList.Click
    Dim id As Integer
    If Not Integer.TryParse(txtCategoryID.Text, id) Then
        lblError.Text = "Введите числовой код категории"
        Return
    End If
    GridView1.DataSource = GetProducts(id).Tables("Products")
    GridView1.DataBind()
End Sub

Несколько наборов строк за один вызов

Одна процедура может вернуть несколько SELECT подряд, например категорию и её товары. Fill разложит их по таблицам Table, Table1 и так далее. Чтобы дать таблицам осмысленные имена, используйте TableMappings: сопоставление задаётся до вызова Fill.

После загрузки таблицы можно связать через DataRelation и выводить иерархию, например во вложенном Repeater или TreeView. Это экономит обращения к базе: вместо запроса на каждую категорию выполняется один вызов процедуры.

da.TableMappings.Add("Table", "Category")
da.TableMappings.Add("Table1", "Products")
da.Fill(ds)

ds.Relations.Add("CategoryProducts", _
    ds.Tables("Category").Columns("CategoryID"), _
    ds.Tables("Products").Columns("CategoryID"))

DataSet, DataTable или DataReader

DataSet не всегда лучший контейнер. Выбирайте по задаче:

• DataSet: несколько связанных таблиц, отношения между ними, сериализация в XML, работа без подключения;
• DataTable: один набор строк для GridView или отчёта, меньше накладных расходов, загрузка через da.Fill(table);
• SqlDataReader: потоковое чтение большого объёма только вперёд, когда данные сразу превращаются в объекты;
• типизированный DataSet из дизайнера: проверка имён колонок при компиляции, но сгенерированный код тяжело сопровождать при изменении схемы.

Как это делают сегодня

Вопросы и ответы