Aracılığıyla paylaş

Tablo Değerli Parametreler

Tablo değerli parametreler, bir istemci uygulamasından SQL Server'a birden fazla veri satırını kolayca aktarmanın bir yolunu sunar ve bu da veriyi işlemek için birden çok gidiş-dönüş ya da özel sunucu tarafı mantığı gerektirmez. Tablo değerli parametreleri kullanarak bir istemci uygulamasındaki veri satırlarını kapsülleyebilir ve verileri tek bir parametreli komutla sunucuya gönderebilirsiniz. Gelen veri satırları, transact-SQL kullanılarak üzerinde çalıştırılabilir bir tablo değişkeninde depolanır.

Tablo değerli parametrelerdeki sütun değerlerine standart Transact-SQL SELECT deyimleri kullanılarak erişilebilir. Tablo değerli parametreler kesin olarak yazılır ve yapıları otomatik olarak doğrulanır. Tablo değerli parametrelerin boyutu yalnızca sunucu belleğiyle sınırlıdır.

Uyarı

Tablo değerli bir parametrede veri döndüremezsiniz. Tablo değerli parametreler yalnızca giriştir; OUTPUT anahtar kelimesi desteklenmez.

Tablo değerli parametreler hakkında daha fazla bilgi için aşağıdaki kaynaklara bakın.

Kaynak Açıklama
Table-Valued Parametreleri (Veritabanı Altyapısı) Kullanma Tablo değerli parametrelerin nasıl oluşturulacağını ve kullanılacağını açıklar.
User-Defined Tablo Türleri Tablo değerli parametreleri bildirmek için kullanılan kullanıcı tanımlı tablo türlerini açıklar.

SQL Server'ın Önceki Sürümlerinde Birden Çok Satır Geçirme

SQL Server 2008'de tablo değerli parametreler tanıtılmadan önce, birden çok veri satırını saklı yordam veya parametreli SQL komutuna geçirme seçenekleri sınırlıydı. Bir geliştirici, sunucuya birden çok satır geçirmek için aşağıdaki seçenekler arasından seçim yapabilir:

  • Birden çok sütundaki ve veri satırındaki değerleri göstermek için bir dizi bağımsız parametre kullanın. Bu yöntem kullanılarak geçirilebilen veri miktarı, izin verilen parametre sayısıyla sınırlıdır. SQL Server yordamlarında en fazla 2100 parametre olabilir. Bu tek tek değerleri işlemek üzere bir tablo değişkenine veya geçici bir tabloya birleştirmek için sunucu tarafı mantığı gereklidir.

  • Birden çok veri değerini sınırlandırılmış dizelerde veya XML belgelerinde paketleyin ve sonra bu metin değerlerini bir yordama veya deyime geçirin. Bunun için yordam veya deyimin veri yapılarını doğrulamak ve değerlerin ayrıştırılması için gerekli olan mantığı içermesi gerekir.

  • Bir Update yönteminin SqlDataAdapter çağrılmasıyla oluşturulanlar gibi birden çok satırı etkileyen veri değişiklikleri için bir dizi bireysel SQL deyimi oluşturun. Değişiklikler sunucuya tek tek gönderilebilir veya gruplar halinde toplu olarak oluşturulabilir. Ancak, birden çok deyim içeren toplu olarak gönderildiğinde bile, her deyim sunucuda ayrı olarak yürütülür.

  • Bir tabloya bcp çok sayıda veri satırı yüklemek için yardımcı programı veya SqlBulkCopy nesnesini kullanın. Bu teknik çok verimli olsa da, veriler geçici bir tablo veya tablo değişkenine yüklenmediği sürece sunucu tarafı işlemeyi desteklemez.

Table-Valued Parametre Türleri Oluşturma

Tablo değerli parametreler, Transact-SQL CREATE TYPE ifadeleri kullanılarak tanımlanan sıkı biçimde belirlenmiş tablo yapılarını temel alır. İstemci uygulamalarınızda tablo değerli parametreleri kullanabilmek için önce bir tablo türü oluşturmanız ve SQL Server'da yapıyı tanımlamanız gerekir. Tablo türleri oluşturma hakkında daha fazla bilgi için bkz. User-Defined Tablo Türleri.

Aşağıdaki deyim CategoryID ve CategoryName sütunlarından oluşan CategoryTableType adlı bir tablo türü oluşturur:

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )

Bir tablo türü oluşturduktan sonra, tablo değerli parametreleri bu türe göre bildirebilirsiniz. Aşağıdaki Transact-SQL kod parçası, saklı yordam tanımında tablo değerli bir parametrenin nasıl bildirileceğini gösterir. Bir tablo değerli parametre bildirmek için READONLY anahtar sözcüğünün gerektiğini unutmayın.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Table-Valued Parametreleriyle Verileri Değiştirme (Transact-SQL)

Tablo değerli parametreler, tek bir ifade yürütülerek birden çok satırı etkileyen küme temelli veri değişikliklerinde kullanılabilir. Örneğin, tablo değerli bir parametredeki tüm satırları seçip bir veritabanı tablosuna ekleyebilir veya güncelleştirmek istediğiniz tabloya tablo değerli bir parametre ekleyerek bir update deyimi oluşturabilirsiniz.

Aşağıdaki Transact-SQL UPDATE deyimi, tablo değerli bir parametreyi Kategoriler tablosuyla ilişkilendirerek nasıl kullanılacağını gösterir. FROM yan tümcesinde JOIN ile tablo değerli bir parametre kullandığınızda, burada gösterildiği gibi, tablo değerli parametreye "ec" şeklinde bir takma ad vermeniz gerekir.

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;

Bu Transact-SQL örneği, tek bir set tabanlı işlemde INSERT işlemi gerçekleştirmek için tablo değerli bir parametreden satır seçmeyi gösterir.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Table-Valued Parametrelerinin Sınırlamaları

Tablo değerli parametrelerle ilgili çeşitli sınırlamalar vardır:

  • Tablo değerli parametreleri CLR kullanıcı tanımlı işlevlere geçiremezsiniz.

  • Tablo değerli parametreler yalnızca UNIQUE veya PRIMARY KEY kısıtlamalarını desteklemek için dizinlenebilir. SQL Server tablo değerli parametrelerle ilgili istatistikleri korumaz.

  • Tablo değerli parametreler Transact-SQL kodunda salt okunurdur. Tablo değerli bir parametrenin satırlarındaki sütun değerlerini güncelleştiremezsiniz ve satır ekleyemez veya silemezsiniz. Tablo değerli parametresinde saklı yordama veya parametreli deyime geçirilen verileri değiştirmek için, verileri geçici bir tabloya veya tablo değişkenine eklemeniz gerekir.

  • Tablo değerli parametrelerin tasarımını değiştirmek için ALTER TABLE deyimlerini kullanamazsınız.

SqlParameter Örneği Yapılandırma

System.Data.SqlClient, DataTable, DbDataReader veya IEnumerable<T> \ SqlDataRecord nesneleri ile tablo değerli parametreleri doldurmayı destekler. TypeName özelliğini kullanarak tablo değerli parametresi için bir tür adı belirtmeniz gerekir. , TypeName daha önce sunucuda oluşturulan uyumlu bir türün adıyla eşleşmelidir. Aşağıdaki kod parçası, veri ekleme işlemi için SqlParameter nasıl yapılandırılacağını gösterir.

Aşağıdaki örnekte addedCategories değişkeni bir DataTableiçerir. Değişkenin nasıl doldurulduğunu görmek için sonraki bölümdeki örneklere bakın: Saklı Prosedüre Table-Valued Parametresi Geçirme.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";  

' Configure the command and parameter.  
Dim insertCommand As New SqlCommand(sqlInsert, connection)  
Dim tvpParam As SqlParameter = _  
   insertCommand.Parameters.AddWithValue( _  
  "@tvpNewCategories", addedCategories)  
tvpParam.SqlDbType = SqlDbType.Structured  
tvpParam.TypeName = "dbo.CategoryTableType"

Bu parçada gösterildiği gibi, veri satırlarını tablo değerli bir parametreye iletmek için DbDataReader'den türetilmiş herhangi bir nesneyi de kullanabilirsiniz.

// Configure the SqlCommand and table-valued parameter.  
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
insertCommand.CommandType = CommandType.StoredProcedure;  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);  
tvpParam.SqlDbType = SqlDbType.Structured;  

' Configure the SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  dataReader)  
tvpParam.SqlDbType = SqlDbType.Structured

Saklı Yordama Bir Table-Valued Parametresi Geçirme

Bu örnek, tablo değerli parametre verilerinin bir saklı yordamına nasıl geçirileceğini göstermektedir. Kod, DataTable yöntemini kullanarak eklenen satırları yeni bir GetChanges içine ayıklar. Kod daha sonra SqlCommand olarak CommandType özelliğini ayarlayan bir StoredProcedure tanımlar. SqlParameter, AddWithValue yöntemi kullanılarak doldurulur ve SqlDbType, Structured olarak ayarlanır. SqlCommand, ExecuteNonQuery yöntemi kullanılarak daha sonra yürütülür.

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  

' Assumes connection is an open SqlConnection object.  
Using connection  
   '  Create a DataTable with the modified rows.  
   Dim addedCategories As DataTable = _  
     CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Configure the SqlCommand and SqlParameter.  
   Dim insertCommand As New SqlCommand( _  
     "usp_InsertCategories", connection)  
   insertCommand.CommandType = CommandType.StoredProcedure  
   Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
     "@tvpNewCategories", addedCategories)  
   tvpParam.SqlDbType = SqlDbType.Structured  
  
   '  Execute the command.  
   insertCommand.ExecuteNonQuery()  
End Using

Parametreleştirilmiş SQL Deyimine Table-Valued Parametresi Geçirme

Aşağıdaki örnek, veri kaynağı olarak tablo değerli bir parametre kullanan bir SELECT alt sorgusuyla, dbo.Categories tablosuna nasıl veri eklenebileceğini bir INSERT deyimi aracılığıyla göstermektedir. Tablo değerli bir parametreyi parametreli bir SQL deyimine geçirirken, bir TypeName tür adı belirtmek için yeni SqlParameter özelliğini kullanmanız gerekir. Bu TypeName , daha önce sunucuda oluşturulan uyumlu bir türün adıyla eşleşmelidir. Bu örnekteki kod, dbo.CategoryTableType'da tanımlanan tür yapısına başvurmak için TypeName özelliğini kullanır.

Uyarı

Tablo değerli bir parametrede bir kimlik sütunu için bir değer girerseniz, oturum için SET IDENTITY_INSERT komutunu çalıştırmanız gerekir.

// Assumes connection is an open SqlConnection.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Define the INSERT-SELECT statement.  
  string sqlInsert =
      "INSERT INTO dbo.Categories (CategoryID, CategoryName)"  
      + " SELECT nc.CategoryID, nc.CategoryName"  
      + " FROM @tvpNewCategories AS nc;"  

  // Configure the command and parameter.  
  SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  tvpParam.TypeName = "dbo.CategoryTableType";  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  

' Assumes connection is an open SqlConnection.  
Using connection  
  ' Create a DataTable with the modified rows.  
  Dim addedCategories As DataTable = _  
    CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Define the INSERT-SELECT statement.  
  Dim sqlInsert As String = _  
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _  
  & " SELECT nc.CategoryID, nc.CategoryName" _  
  & " FROM @tvpNewCategories AS nc;"  
  
  ' Configure the command and parameter.  
  Dim insertCommand As New SqlCommand(sqlInsert, connection)  
  Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
    "@tvpNewCategories", addedCategories)  
  tvpParam.SqlDbType = SqlDbType.Structured  
  tvpParam.TypeName = "dbo.CategoryTableType"  
  
  ' Execute the query  
  insertCommand.ExecuteNonQuery()  
End Using

DataReader ile Satır Akışı

DbDataReader öğesinden türetilmiş herhangi bir nesneyi, veri satırlarını tablo değerli bir parametreye aktarmak için de kullanabilirsiniz. Aşağıdaki kod parçası, bir OracleCommand ve bir OracleDataReader kullanarak Oracle veritabanından veri almayı gösterir. Kod ardından bir SqlCommand saklı yordamı tek bir giriş parametresi kullanarak çağırmak için yapılandırılır. SqlDbType özelliği SqlParameter olarak Structuredayarlanır. AddWithValue, OracleDataReader sonuç kümesini saklı yordama tablo değerli bir parametre olarak geçirir.

// Assumes connection is an open SqlConnection.  
// Retrieve data from Oracle.  
OracleCommand selectCommand = new OracleCommand(  
   "Select CategoryID, CategoryName FROM Categories;",  
   oracleConnection);  
OracleDataReader oracleReader = selectCommand.ExecuteReader(  
   CommandBehavior.CloseConnection);  
  
 // Configure the SqlCommand and table-valued parameter.  
 SqlCommand insertCommand = new SqlCommand(  
   "usp_InsertCategories", connection);  
 insertCommand.CommandType = CommandType.StoredProcedure;  
 SqlParameter tvpParam =  
    insertCommand.Parameters.AddWithValue(  
    "@tvpNewCategories", oracleReader);  
 tvpParam.SqlDbType = SqlDbType.Structured;  
  
 // Execute the command.  
 insertCommand.ExecuteNonQuery();  

' Assumes connection is an open SqlConnection.  
' Retrieve data from Oracle.  
Dim selectCommand As New OracleCommand( _  
  "Select CategoryID, CategoryName FROM Categories;", _  
  oracleConnection)  
Dim oracleReader As OracleDataReader = _  
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)  
  
' Configure SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  oracleReader)  
tvpParam.SqlDbType = SqlDbType.Structured  
  
' Execute the command.  
insertCommand.ExecuteNonQuery()

Ayrıca bakınız

  • Last updated on