Tablo Değerli Parametreler

Tablo değerli parametreler, verileri işlemek için birden çok gidiş dönüş veya özel sunucu tarafı mantığı gerektirmeden bir istemci uygulamasından SQL Server'a birden çok veri satırı hazırlamanın kolay bir yolunu sağlar. 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.

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

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

SQL Server 2008'e tablo değerli parametreler kullanılmadan önce, birden çok veri satırını saklı yordama 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 kaldırılmaları için gerekli mantığı içermesi gerekir.

• bir yöntemini SqlDataAdapterçağırarak Update oluşturulanlar gibi birden çok satırı etkileyen veri değişiklikleri için bir dizi tek 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.

Tablo Değerli Parametre Türleri Oluşturma

Tablo değerli parametreler, Transact-SQL CREATE TYPE deyimleri kullanılarak tanımlanan kesin olarak 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 . Kullanıcı Tanımlı 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 parçası, saklı yordam tanımında tablo değerli parametrenin nasıl bildirileceği gösterilmektedir. Tablo değerli bir parametre bildirmek için READONLY anahtar sözcüğü gerektiğini unutmayın.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Tablo Değerli Parametrelerle Verileri Değiştirme (Transact-SQL)

Tablo değerli parametreler, tek bir deyim yürütülerek birden çok satırı etkileyen küme tabanlı 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 tablosuna birleştirerek 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 parametrenin "ec" olarak diğer adlandırıldığı diğer ad da kullanmanız 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 ayar tabanlı işlemde INSERT 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;  

Tablo Değerli Parametrelerin 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, DbDataReader veya IEnumerable<T> \ SqlDataRecord nesnelerinden DataTabletablo değerli parametreleri doldurmayı destekler. bir özelliğini kullanarak TypeName tablo değerli parametresi için bir SqlParametertü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 eklemek için yapılandırmayı SqlParameter gösterir.

Aşağıdaki örnekte addedCategories değişkeni bir DataTableiçerir. Değişkenin nasıl dolduruldiğini görmek için sonraki bölümdeki Tablo Değerli Parametreyi Saklı Yordama Geçirme örneklerine bakın.

// 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 akışla aktarmak için öğesinden DbDataReader türetilen 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  

Tablo Değerli Parametreyi Saklı Yordama Geçirme

Bu örnekte tablo değerli parametre verilerinin saklı yordama nasıl geçirileceği gösterilmektedir. Kod, yöntemini kullanarak eklenen satırları yeni DataTable bir satıra GetChanges ayıklar. Kod daha sonra özelliğini olarak StoredProcedureayarlayan CommandType bir SqlCommandtanımlar. SqlParameter yöntemi kullanılarak AddWithValue doldurulur ve SqlDbType olarak ayarlanırStructured. SqlCommand daha sonra yöntemi kullanılarak ExecuteNonQuery 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  

Tablo Değerli Bir Parametreyi Parametreli SQL Deyimine Geçirme

Aşağıdaki örnekte dbo'ya veri ekleme işlemi gösterilmektedir. Veri kaynağı olarak tablo değerli parametresi olan SELECT alt sorgusuna sahip bir INSERT deyimi kullanarak tabloyu kategoriler. Tablo değerli bir parametreyi parametreli bir SQL deyimine geçirirken, yeni özelliğini kullanarak TypeName tablo değerli parametre için bir SqlParametertür adı belirtmeniz gerekir. Bu TypeName , daha önce sunucuda oluşturulan uyumlu bir türün adıyla eşleşmelidir. Bu örnekteki kod, dbo'da tanımlanan tür yapısına başvurmak için özelliğini kullanır TypeName . CategoryTableType.

// 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ışı

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

// 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 bkz.

• Parametreleri ve Parametre Veri Türlerini Yapılandırma
• Komutlar ve Parametreler
• DataAdapter Parametreleri
• ADO.NET’te SQL Server Veri İşlemleri
• ADO.NET’e Genel Bakış