Multidelete z użyciem zmiennej tablicowej
Przekazanie wielu parametrów na raz do procedury składowanej można załatwić zmiennymi tablicowymi - Table Valued Parameter.
Prezentuje przypadek usunięcia wielu elementów według ich idków, jednak mechanizm jest identyczny np dla insertów updatów czy dowolnych operacji - jednak wtedy zazwyczaj potrzebna są bardziej rozbudowane typy danych (tutaj mam typ który ma tylko jedną kolumne).
Typ z jedną kolumną
Ważne jest aby DataTable przekazywany do procedurki nazywał się jak zmienna która procedurka oczekuje.
Ale
Nie byłabym sobą gdybym gdzieś nie znalazła ale. Nie wiem na ile to ale jest prawdziwe gdyż sama nie sprawdziłam, jednak jest cokolwiek niepokojące.
Mianowicie wywołania takie mogą powodować problemy wydajnościowe ze względu na każdorazowe kompilowanie planu wykonania procedury.
Tutaj więcej info:
Using ADO.NET SqlDbType.Structured TVP (Table Valued Parameters) causes SQL Compilation for every call;
The interesting case of TVPs and plan compilation
I jeszcze na zakończenie mały art z technet o tablicach tymczasowych i zmiennych tablicowych
Prezentuje przypadek usunięcia wielu elementów według ich idków, jednak mechanizm jest identyczny np dla insertów updatów czy dowolnych operacji - jednak wtedy zazwyczaj potrzebna są bardziej rozbudowane typy danych (tutaj mam typ który ma tylko jedną kolumne).
Typ z jedną kolumną
CREATE TYPE [dbo].[_ContactIDs] AS TABLE( [ID] [bigint] NOT NULL )Procedurka która przyjmuje zmienna tablicową i wykorzystuje do operacji usuwania.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[ContactMultiDelete] ( @TableVariable _ContactIDs READONLY ) AS begin tran delete from Contact from Contact a inner join @TableVariable t on t.ID = a.ID commit tranNo i fragment kodu C# wywołującego procedurkę za pomocą ADO.
ListtoDelete = GetListOfContactToDelete(); //dataTable musi się nazywać tak jak typ tablicowy w sqlu DataTable dt = new DataTable("_ContactIDs"); dt.Columns.Add(new DataColumn("ID")); if (toDelete != null && toDelete.Count > 0) { //przerabiamy listę idków na DataTable toDelete.ToList().ForEach(a => { DataRow dr = dt.NewRow(); dr["ID"] = a.ID; dt.Rows.Add(dr); }); System.Data.SqlClient.SqlConnection sql1 = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand("dbo.ContactMultiDelete", sql1); cmd1.CommandType = CommandType.StoredProcedure; //Parametr typu tablicowego System.Data.SqlClient.SqlParameter sp1 = cmd1.Parameters.AddWithValue("@TableVariable", dt); sp1.SqlDbType = SqlDbType.Structured; sql1.Open(); cmd1.ExecuteNonQuery(); sql1.Close();
Ale
Nie byłabym sobą gdybym gdzieś nie znalazła ale. Nie wiem na ile to ale jest prawdziwe gdyż sama nie sprawdziłam, jednak jest cokolwiek niepokojące.
Mianowicie wywołania takie mogą powodować problemy wydajnościowe ze względu na każdorazowe kompilowanie planu wykonania procedury.
Tutaj więcej info:
Using ADO.NET SqlDbType.Structured TVP (Table Valued Parameters) causes SQL Compilation for every call;
The interesting case of TVPs and plan compilation
I jeszcze na zakończenie mały art z technet o tablicach tymczasowych i zmiennych tablicowych
Komentarze
Prześlij komentarz