İşlem Data Access örnek

Makale
10/29/2012

InProcessDataAccessÖrnek bir dizi çeşitli özelliklerini gösteren basit işlevler içeren SQL Serverclr işlem içindeki veri erişim sağlayıcısı.

Ön Koşullar

Oluşturmak ve bu proje aşağıdaki çalıştırmak için aşağıdaki yazılım yüklenmiş olmalıdır:

SQL Serverveya SQL Serverhızlı. Elde edebilirsiniz SQL Serverhızlı şarj ücretsiz SQL ServerExpress belgeleri ve örnekleri Web sitesi
İs available at AdventureWorks veritabanını SQL Servergeliştiricisi Web sitesi
.net Framework sdk 2.0 veya sonraki sürümünü ya da Microsoft Visual Studio 2005 veya sonraki bir sürümü. Elde edebilirsiniz.net Framework sdk ücretsiz.
Ayrıca, aşağıdaki koşulların karşılanması gerekir:
SQL ServerKullanıyorsanız örneğinin clr tümleştirmesi etkinleştirilmiş olması gerekir.
clr tümleştirme etkinleştirmek için aşağıdaki adımları gerçekleştirin:

clr tümleştirme etkinleştirmek
- Aşağıdaki yürütme Transact-SQLkomutları:
sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

[!NOT]

clr etkinleştirmek için olmalıdır ALTER SETTINGSörtülü üyeleri tarafından düzenlenen sunucu düzeyi izni sysadminve serveradminsabit sunucu rolü.
AdventureWorks veritabanı yüklenmesi gerekir SQL Serverkullanıyorsanız örneği.
Yönetici değilseniz SQL Serverkullandığınız örnek verdiğiniz bir yönetici olması gerekir CreateAssembly yüklemeyi tamamlamak için izni.

Örnek oluşturma

Oluşturun ve aşağıdaki yönergeleri kullanarak örnek çalıştırın:

Visual Studio'nun açın veya.net Framework buyurmak-e sevketmek.
Gerekirse, örnek bir dizin oluşturun. Bu örnekte, C:\MySample kullanacağız.
C:\MySample içinde oluşturmak inprocda.vb(için Visual Basic örnek) veya inprocda.cs (için C# örnek) ve uygun Visual Basic veya C# örnek kod (aşağıda) dosyasına kopyalayın.
Komut satırı isteminden gerekli derleme dil seçiminize bağlı olarak, aşağıdakilerden biri yürüterek örnek kod derlemesi.
- Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library InProcDA.vb
- Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library inprocda.cs
Kopya Transact-SQLyükleme kodu olarak kaydedin ve dosyayı içine Install.sqlörnek Rehberi.
Örnek bir dizindeki diğer sonra yüklü olup olmadığını C:\MySample\, dosya düzenleme Install.sqlBu konumu göstermesi için belirtildiği gibi.
Kurul, saklı yordam ve işlevlerini yürüterek dağıtma
- sqlcmd -E -I -i install.sql
Kopya Transact-SQLyükleme kodu olarak kaydedin ve dosyayı içine test.sqlörnek Rehberi.
Komut isteminde aşağıdaki satırı çalıştırarak uygulamayı sınayın:
- sqlcmd -E -I -i test.sql
Kopya Transact-SQLTemizleme komut dosyası olarak kaydedin ve dosyayı içine cleanup.sqlörnek Rehberi.
Aşağıdaki komutu komut dosyasını yürütün
- sqlcmd -E -I -i cleanup.sql

Örnek kod

Bu örnek kod listeleri şunlardır:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public sealed class DataAccessDemo
{
        private DataAccessDemo()
        {
        }

        /// <summary>
/// Simple example to send a message to the client.
/// </summary>
public static void SendMessage(string msg)
{
SqlContext.Pipe.Send("Message from server: " + msg);
}

/// <summary>
/// Simple example of performing data access within
/// a function
/// </summary>
/// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static string ReportSqlVersion()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //create a command from the current context
                SqlCommand cmd = conn.CreateCommand();

                //execute something
                cmd.CommandText = "select @@version";

                conn.Open();
                //return results as scalar
                return (string)cmd.ExecuteScalar();
            }
}


/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
public static void SendTransientResultSet()
{
//create the metadata for the columns
            Microsoft.SqlServer.Server.SqlMetaData[] columnSchema 
                = new Microsoft.SqlServer.Server.SqlMetaData[] {
new Microsoft.SqlServer.Server.SqlMetaData("stringcol", SqlDbType.NVarChar, 128)
};

//create a record based on that metadata
            SqlDataRecord newRecord = new SqlDataRecord(columnSchema);

//populate it
newRecord.SetString(0, "Hello World!");

//send it
SqlContext.Pipe.Send(newRecord);
}

/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
public static void ExecuteToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
}

/// <summary>
/// Execute a command and send the resultig reader to the client
/// </summary>
public static void SendReaderToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                try
                {
                    SqlContext.Pipe.Send(rdr);
                }
                finally
                {
                    rdr.Close();
                }
            }
}

};

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public sealed class DataAccessDemo
{
        private DataAccessDemo()
        {
        }

        /// <summary>
/// Simple example to send a message to the client.
/// </summary>
public static void SendMessage(string msg)
{
SqlContext.Pipe.Send("Message from server: " + msg);
}

/// <summary>
/// Simple example of performing data access within
/// a function
/// </summary>
/// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static string ReportSqlVersion()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //create a command from the current context
                SqlCommand cmd = conn.CreateCommand();

                //execute something
                cmd.CommandText = "select @@version";

                conn.Open();
                //return results as scalar
                return (string)cmd.ExecuteScalar();
            }
}


/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
public static void SendTransientResultSet()
{
//create the metadata for the columns
            Microsoft.SqlServer.Server.SqlMetaData[] columnSchema 
                = new Microsoft.SqlServer.Server.SqlMetaData[] {
new Microsoft.SqlServer.Server.SqlMetaData("stringcol", SqlDbType.NVarChar, 128)
};

//create a record based on that metadata
            SqlDataRecord newRecord = new SqlDataRecord(columnSchema);

//populate it
newRecord.SetString(0, "Hello World!");

//send it
SqlContext.Pipe.Send(newRecord);
}

/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
public static void ExecuteToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
}

/// <summary>
/// Execute a command and send the resultig reader to the client
/// </summary>
public static void SendReaderToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                try
                {
                    SqlContext.Pipe.Send(rdr);
                }
                finally
                {
                    rdr.Close();
                }
            }
}

};

Visual Basic

Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Partial Public NotInheritable Class DataAccessDemo
    Private Sub New()
    End Sub

    ''' <summary>
    ''' Simple example of performing data access within a function
    ''' </summary>
    ''' <returns></returns>
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReportSqlVersion() As SqlString
        Using conn As New SqlConnection("context connection=true")
            'create a command from the current context
            Dim cmd As SqlCommand = conn.CreateCommand()

            'execute something
            cmd.CommandText = "SELECT @@VERSION"

            conn.Open()

            'return results as scalar
            Return CType(cmd.ExecuteScalar(), String)
        End Using
    End Function

    ''' <summary>
    ''' Simple example to send a message to the client.
    ''' </summary>
    Public Shared Sub SendMessage(ByVal msg As String)
        SqlContext.Pipe.Send(("Message from server: " & msg))
    End Sub

    ''' <summary>
    ''' Create a result set on the fly and send it to the client.
    ''' </summary>
    Public Shared Sub SendTransientResultSet()
        'create the metadata for the columns
        Dim columnSchema() As Microsoft.SqlServer.Server.SqlMetaData _
            = {New SqlMetaData("stringcol", SqlDbType.NVarChar, 128)}

        'create a record based on that metadata
        Dim newRecord As New SqlDataRecord(columnSchema)

        'populate it
        newRecord.SetString(0, "Hello World!")

        'send it
        SqlContext.Pipe.Send(newRecord)
    End Sub

    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    Public Shared Sub ExecuteToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()

            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            SqlContext.Pipe.ExecuteAndSend(cmd)
        End Using
    End Sub

    ''' <summary>
    ''' Execute a command and send the resulting reader to the client
    ''' </summary>
    Public Shared Sub SendReaderToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            Try
                SqlContext.Pipe.Send(rdr)
            Finally
                rdr.Close()
            End Try
        End Using
    End Sub

End Class

Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Partial Public NotInheritable Class DataAccessDemo
    Private Sub New()
    End Sub

    ''' <summary>
    ''' Simple example of performing data access within a function
    ''' </summary>
    ''' <returns></returns>
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReportSqlVersion() As SqlString
        Using conn As New SqlConnection("context connection=true")
            'create a command from the current context
            Dim cmd As SqlCommand = conn.CreateCommand()

            'execute something
            cmd.CommandText = "SELECT @@VERSION"

            conn.Open()

            'return results as scalar
            Return CType(cmd.ExecuteScalar(), String)
        End Using
    End Function

    ''' <summary>
    ''' Simple example to send a message to the client.
    ''' </summary>
    Public Shared Sub SendMessage(ByVal msg As String)
        SqlContext.Pipe.Send(("Message from server: " & msg))
    End Sub

    ''' <summary>
    ''' Create a result set on the fly and send it to the client.
    ''' </summary>
    Public Shared Sub SendTransientResultSet()
        'create the metadata for the columns
        Dim columnSchema() As Microsoft.SqlServer.Server.SqlMetaData _
            = {New SqlMetaData("stringcol", SqlDbType.NVarChar, 128)}

        'create a record based on that metadata
        Dim newRecord As New SqlDataRecord(columnSchema)

        'populate it
        newRecord.SetString(0, "Hello World!")

        'send it
        SqlContext.Pipe.Send(newRecord)
    End Sub

    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    Public Shared Sub ExecuteToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()

            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            SqlContext.Pipe.ExecuteAndSend(cmd)
        End Using
    End Sub

    ''' <summary>
    ''' Execute a command and send the resulting reader to the client
    ''' </summary>
    Public Shared Sub SendReaderToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            Try
                SqlContext.Pipe.Send(rdr)
            Finally
                rdr.Close()
            End Try
        End Using
    End Sub

End Class

Bu Transact-SQLyükleme komut dosyasını (Install.sql), hangi derleme dağıtır ve bu örnek tarafından gerekli işlevini ve saklı yordamlar oluşturur.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
DECLARE @SamplesPath nvarchar(1024)

-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'
CREATE ASSEMBLY InProcDA FROM @SamplesPath + 'InProcDA.dll'
WITH permission_set = SAFE;
GO

CREATE PROCEDURE [SendMessage] @msg nvarchar(4000)
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendMessage];
GO

CREATE FUNCTION [ReportSqlVersion]() RETURNS nvarchar(4000)
AS EXTERNAL NAME [InProcDA].[DataAccessDemo].[ReportSqlVersion];
GO

CREATE PROCEDURE [SendTransientResultSet]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendTransientResultSet];
GO

CREATE PROCEDURE [ExecuteToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[ExecuteToClient];
GO

CREATE PROCEDURE [SendReaderToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendReaderToClient];
GO

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
DECLARE @SamplesPath nvarchar(1024)

-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'
CREATE ASSEMBLY InProcDA FROM @SamplesPath + 'InProcDA.dll'
WITH permission_set = SAFE;
GO

CREATE PROCEDURE [SendMessage] @msg nvarchar(4000)
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendMessage];
GO

CREATE FUNCTION [ReportSqlVersion]() RETURNS nvarchar(4000)
AS EXTERNAL NAME [InProcDA].[DataAccessDemo].[ReportSqlVersion];
GO

CREATE PROCEDURE [SendTransientResultSet]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendTransientResultSet];
GO

CREATE PROCEDURE [ExecuteToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[ExecuteToClient];
GO

CREATE PROCEDURE [SendReaderToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendReaderToClient];
GO

Aşağıdaki Transact-SQL(test.sql) saklı yordamlar ve bu örnekte tanımlanan işlev egzersiz tarafından örnek sınamaları.

USE AdventureWorks;
GO

-- send a message to the client
EXEC SendMessage  N'This is a test message.';

-- exec a function that does data access
SELECT dbo.ReportSqlVersion();

-- exec the proc that sends a result set to the client
EXEC SendTransientResultSet;

EXEC ExecuteToClient;

EXEC SendReaderToClient;

USE master;
GO

USE AdventureWorks;
GO

-- send a message to the client
EXEC SendMessage  N'This is a test message.';

-- exec a function that does data access
SELECT dbo.ReportSqlVersion();

-- exec the proc that sends a result set to the client
EXEC SendTransientResultSet;

EXEC ExecuteToClient;

EXEC SendReaderToClient;

USE master;
GO

Aşağıdaki Transact-SQLKurul, fonksiyon ve saklı yordamlar veritabanından kaldırır.

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO

Ayrıca bkz.

Kavramlar

Kullanım senaryoları ve ortak dil çalışma zamanı (clr) tümleştirme örnekleri

Aracılığıyla paylaş