Xây dựng các đối tượng cơ sở dữ liệu với .NET Common Language Runtime (CLR) trong SQL 2005

Imports System.Runtime.InteropServices

…

Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

using System;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

   [Microsoft.SqlServer.Server.SqlProcedure]

   public static void PriceSum(out SqlInt32 value)

   {

      using(SqlConnection connection = new SqlConnection("context connection=true"))

      {

         value = 0;

         connection.Open();

         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);

         SqlDataReader reader = command.ExecuteReader();

         using (reader)

         {

            while( reader.Read() )

            {

               value += reader.GetSqlInt32(0);

            }

         }        

      }

   }

}

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

Imports System.Runtime.InteropServices

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

    ''' <summary>

    ''' Executes a query and iterates over the results to perform a summation.

    ''' </summary>

    <Microsoft.SqlServer.Server.SqlProcedure> _

    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)

        Using connection As New SqlConnection("context connection=true")

           value = 0

           Connection.Open()

           Dim command As New SqlCommand("SELECT Price FROM Products", connection)

           Dim reader As SqlDataReader

           reader = command.ExecuteReader()

           Using reader

              While reader.Read()

                 value += reader.GetSqlInt32(0)

              End While

           End Using

        End Using       

    End Sub

End Class

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

   /// <summary>

   /// Execute a command and send the results to the client directly.

   /// </summary>

   [Microsoft.SqlServer.Server.SqlProcedure]

   public static void ExecuteToClient()

   {

   using(SqlConnection connection = new SqlConnection("context connection=true"))

   {

      connection.Open();

      SqlCommand command = new SqlCommand("select @@version", connection);

      SqlContext.Pipe.ExecuteAndSend(command);

      }

   }

}

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

    ''' <summary>

    ''' Execute a command and send the results to the client directly.

    ''' </summary>

    <Microsoft.SqlServer.Server.SqlProcedure> _

    Public Shared Sub ExecuteToClient()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            Dim command As New SqlCommand("SELECT @@VERSION", connection)

            SqlContext.Pipe.ExecuteAndSend(command)

        End Using

    End Sub

End Class

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

   /// <summary>

   /// Execute a command and send the resultig reader to the client

   /// </summary>

   [Microsoft.SqlServer.Server.SqlProcedure]

   public static void SendReaderToClient()

   {

      using(SqlConnection connection = new SqlConnection("context connection=true"))

      {

         connection.Open();

         SqlCommand command = new SqlCommand("select @@version", connection);

         SqlDataReader r = command.ExecuteReader();

         SqlContext.Pipe.Send(r);

      }

   }

}

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

    ''' <summary>

    ''' Execute a command and send the results to the client directly.

    ''' </summary>

    <Microsoft.SqlServer.Server.SqlProcedure> _

    Public Shared Sub SendReaderToClient()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            Dim command As New SqlCommand("SELECT @@VERSION", connection)

            Dim reader As SqlDataReader

            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub

End Class

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class StoredProcedures

{

   /// <summary>

   /// Create a result set on the fly and send it to the client.

   /// </summary>

   [Microsoft.SqlServer.Server.SqlProcedure]

   public static void SendTransientResultSet()

   {

      // Create a record object that represents an individual row, including it's metadata.

      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));

      // Populate the record.

      record.SetSqlString(0, "Hello World!");

      // Send the record to the client.

      SqlContext.Pipe.Send(record);

   }

}

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

    ''' <summary>

    ''' Create a result set on the fly and send it to the client.

    ''' </summary>

    <Microsoft.SqlServer.Server.SqlProcedure> _

    Public Shared Sub SendTransientResultSet()

        ' Create a record object that represents an individual row, including it's metadata.

        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )

        ' Populate the record.

        record.SetSqlString(0, "Hello World!")

        ' Send the record to the client.

        SqlContext.Pipe.Send(record)       

    End Sub

End Class

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

   [Microsoft.SqlServer.Server.SqlProcedure]

   public static void HelloWorld()

   {

      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");

      using(SqlConnection connection = new SqlConnection("context connection=true"))

      {

         connection.Open();

         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);

         SqlDataReader reader = command.ExecuteReader();

         SqlContext.Pipe.Send(reader);

      }

   }

}

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

    ''' <summary>

    ''' Execute a command and send the results to the client directly.

    ''' </summary>

    <Microsoft.SqlServer.Server.SqlProcedure> _

    Public Shared Sub HelloWorld()

        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)

            Dim reader As SqlDataReader

            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub

End Class

CREATE PROCEDURE HelloWorld() AS

BEGIN

PRINT('Hello world!')

SELECT ProductNumber FROM ProductMaster

END

Ghi chú:

Các thông điệp và các tập kết quả được lấy về theo những cách khác nhau trong các ứng dụng khách. Lây ví dụ cụ thể, SQL Server Management Studio hiển thị các tập kết quả trong cửa sổ Results, còn các thông điệp sẽ được hiển thị trong cửa sổ Messages.

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'

CREATE PROCEDURE HelloWorld

AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld

EXEC MyFirstUdp