17 maja 2011

Table-Valued Parameters czyli panaceum na komunikację pomiędzy aplikacją i bazą danych

TVP bez abonamentu


Table Valued Parameters (TVP - parametry tabelaryczne?) zostały wprowadzone w wersji MS SQL Server 2008. Umożliwiają one przekazywanie zmiennych tabelarycznych jako parametry procedur składowanych lub bezpośrednich zapytań. Niestety nie można przekazać TVP jako parametr funkcji.

W poprzednich wersjach SQL Servera programiści nie mogli wykorzystać tabeli do przekazania danych od klienta. Musieli albo przekazać XML który trzeba było sparsować w procedurze składownej lub przekazać tekst z delimiterami albo po prostu przesłać wiele instert'ów/update'ów.

Table Valued Parameter umożliwia bardziej ustrukturyzowane i eleganckie rozwiązanie. Programista definiuje typ danych tabelaraczny, który później wykorzysta do zdefiniowania TVP.

CREATE TYPE dbo.ShoppingBasketType AS TABLE 
( 
  ProductCode VARCHAR(10) NOT NULL PRIMARY KEY, 
  Quantity Int NOT NULL
)
GO
SELECT * FROM sys.table_types
GO


Nie tylko procedury


Oczywiście użycie zdefiniowanego tabelarycznego typu danych nie ogranicza się do parametrów procedur składowanych. Można użyć go np. w skrypcie z wykorzystaniem zmiennej tabelarycznej.

DECLARE @table dbo.ShoppingBasketType

INSERT INTO @table ( ProductCode, Quantity)
VALUES('SOFTWR', 1)

SELECT *
 FROM @table

Elegancki koszyk zakupów


Aby zaprezentować użycie TVP zaimplementuję przypadek, w którym koszyk zakupów (shopping basket) należy zmaterializować w bazie danych w tabelach Orders, OrderLines. Przykład został zrealizowany w C# przy użyciu ADO.NET.

class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=.;
                Initial Catalog=TableValuedParameters;Integrated Security=True"))
            using (SqlCommand cmd = new SqlCommand("dbo.usp_InsertOrder", 
                connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var orderIdParameter = new SqlParameter("@orderId", SqlDbType.Char);
                orderIdParameter.Value = "ORD"+new Random().Next(10, 99);
                cmd.Parameters.Add(orderIdParameter);

                var shoppingBasketParameter = new 
                    SqlParameter("@shoppingBasket", SqlDbType.Structured);
                shoppingBasketParameter.TypeName = "dbo.ShoppingBasketType";
                shoppingBasketParameter.Value = GetShoppingBasket();
                cmd.Parameters.Add(shoppingBasketParameter);

                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        private static DataTable GetShoppingBasket()
        {
            //recreate DataTable structure
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ProductCode", typeof(string)));
            dt.Columns.Add(new DataColumn("Quantity", typeof (int)));

            dt.Rows.Add("MADDVD", 7);
            dt.Rows.Add("GRCOOKER", 8);
            dt.Rows.Add("GRHOOD", 10);

            return dt;
        }
     }

Table Valued Parameter obsługiwany jest jako typ SqlDbType.Structured i jest on dostępny od .NET Framework 3.5.  Aby przekazać parametr typu   SqlDbType.Structured musimy przygotować zmienną typu DataTable, której schemat odwzoruje zdefiniowany w bazie danych typ tabelaryczny (patrz metoda GetShoppingBasket). Zamieszczam też skrypt instalujący wszystkie wymagane obiekty po stronie SQL Servera.

--DROP DATABASE TableValuedParameters
--GO
CREATE DATABASE TableValuedParameters
GO
USE TableValuedParameters
GO
CREATE TYPE dbo.ShoppingBasketType AS TABLE 
( 
  ProductCode VARCHAR(10) NOT NULL PRIMARY KEY, 
  Quantity Int NOT NULL
)
GO
SELECT * FROM sys.table_types
GO

CREATE TABLE dbo.Orders
(
  OrderId CHAR(5) PRIMARY KEY
)

CREATE TABLE dbo.OrderLines(
  OrderId CHAR(5) NOT NULL, 
  ProductCode VARCHAR(10) NOT NULL,
  Quantity Int NOT NULL
)

GO
CREATE PROCEDURE dbo.usp_InsertOrder( 
 @orderId CHAR(5), 
@shoppingBasket dbo.ShoppingBasketType READONLY)
AS
BEGIN
   INSERT INTO Orders(OrderId)
   SELECT @orderId
   
   INSERT INTO OrderLines(OrderId, ProductCode, Quantity)
   SELECT @orderId, sb.ProductCode, sb.Quantity
  FROM @shoppingBasket sb
END
GO
SELECT * FROM Orders
SELECT * FROM OrderLines

Brak komentarzy:

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.