.png)
|
이 문서는 수동으로 번역한 것입니다. 원본 텍스트를 보려면 포인터를 문서의 문장 위로 올리십시오. 추가 정보
|
번역
원본
|
CLR 저장 프로시저
EXECUTE @return_status = procedure_name
테이블 반환 매개 변수
OUTPUT 매개 변수 및 CLR 저장 프로시저
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
CREATE PROCEDURE PriceSum (@sum int OUTPUT) AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum -- if StoredProcedures class was inside a namespace, called MyNS, -- you would use: -- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum
테이블 형식 결과 및 메시지 반환
메시지 반환
테이블 형식 결과 반환
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 resulting 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;
 참고 |
|---|
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 HelloWorld;
