Dil

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ş için kullanılır; OUTPUT anahtar sözcüğü desteklenmez.

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

Kaynak Açıklama
Tablo Değerli Parametreler (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.

  • Update öğesinin SqlDataAdapter yöntemi tarafından oluşturulanlar gibi birden çok satırı etkileyen veri değişiklikleri için bir dizi bağımsız SQL deyimi oluşturun. Değişiklikleri tek tek veya toplu olarak gönderin. Bir toplu iş içinde bile, sunucu her deyimi ayrı olarak yürütü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 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. 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. Tablo değerli bir parametreyi bildirmek için READONLY anahtar sözcüğünün gerekli olduğunu 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 tablosuna birleştirerek nasıl kullanılacağını gösterir. Bir JOIN yan tümcesinde FROM ile tablo değerli parametre kullandığınızda, burada gösterildiği gibi, ona "ec" diğer adını da 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 küme tabanlı işlemde INSERT gerçekleştirmek için tablo değerli bir parametreden satırların nasıl seçileceğini 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 dizine eklenebilir. 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

Microsoft.Data.SqlClient NuGet paketini yükleyin ve Microsoft.Data.SqlClient ile System.Data içeri aktarın. İstemci tarafı kayıt dizileri için Microsoft.Data.SqlClient.Server.SqlMetaData ve Microsoft.Data.SqlClient.Server.SqlDataRecord kullanın. SQL CLR sunucu tarafı türlerini Microsoft.SqlServer.Server ile değiştirmeyin.

Microsoft.Data.SqlClient, DataTable nesnelerinin DbDataReader, IEnumerable<T> veya SqlDataRecord öğelerinden tablo değerli parametre değerlerini destekler. Parametrenin TypeName değerini, daha önce sunucuda oluşturulan uyumlu bir tablo türünün adına ayarlayın. Aşağıdaki parça, veri eklemek için parametreyi yapılandırıyor.

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 verilerini saklı prosedüre geçirir. Kod, eklenen satırları yeni GetChanges içine ayıklamak için DataTable öğesini çağırır. CommandType, SqlCommand olarak ayarlanmış şekilde StoredProcedure oluşturur. AddWithValue ile parametreyi ekler ve Structured öğesini SqlDbType olarak ayarlar. Son olarak, kod ExecuteNonQuery çağırı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, tablo değerli parametreyi okuyan bir dbo.Categories alt sorgusu ve bir SELECT deyimi ile INSERT içine veri ekler. Parametreli SQL deyimi için, TypeName değerini daha önce sunucuda oluşturulan uyumlu bir tablo türünün adına ayarlayın. Bu örnekte dbo.CategoryTableType kullanılmıştır.

Uyarı

Tablo değerli bir parametrede bir kimlik sütunu için değer sağlarsanız, oturum için SET IDENTITY_INSERT deyimini ç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ışı

Satırları tablo değerli bir parametreye aktarmak için DbDataReader'dan türetilen herhangi bir nesneyi kullanabilirsiniz. Aşağıdaki kod parçası, OracleDataReader ve OracleCommand ile bir Oracle veritabanından veri alır. Kod, SqlCommand öğesini bir giriş parametresiyle saklı yordamı çağıracak şekilde yapılandırır. SqlDbType, OracleDataReader olarak ayarlanır ve AddWithValue sonuç kümesini tablo değerli parametre olarak Structured içinden 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

  • Son güncelleştirme: