�ݺ�ߣ

SQLCLR TIPS&TRICKS
Daniel Joskovski
Owner Omnis llc, Principal Developer Daenet,
MCT Semos Education
joskovski@t-home.mk

Agenda
Why SQL CLR?
What Is Assembly and How To Get Info About?
Demo1
Create CLR Functions
Demo 2
Discussion
Encrypt/Decrypt

Why SQL CLR?
A better programming model
Improved safety and security
Ability to define data types and aggregate
functions
Streamlined development through a
standardized environment
Potential for improved performance and
scalability

What Is Assembly
Assemblies are DLL files used in an
instance of SQL Server to deploy:
Functions
Stored procedures
Triggers
User-defined aggregates
User-defined types

Getting Info About Assemblies
 ASSEMBLYPROPERTY('assembly_name', 'property_name')

Demo 1
Getting Info About Assemblies

SQL Server In-Process Specific
Extensions to ADO.NET
 SqlContext Object This class provides access to the
other extensions by abstracting the context of a
caller of a SQL Server routine that executes managed
code in-process.
 SqlPipe Object This class contains routines to send
tabular results and messages to the client.
 SqlDataRecord Object The SqlDataRecord class
represents a single row of data, along with its related
metadata, and allows stored procedures to return
custom result sets to the client.

• SqlTriggerContext Object This class provides information on the context in which a
trigger is run.
using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using
System.Data.SqlClient; using System.Data.SqlTypes; using System.Xml; using
System.Text.RegularExpressions;
public class CLRTriggers
{
public static void DropTableTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch(triggContext.TriggerAction)
{
case TriggerAction.DropTable:
SqlContext.Pipe.Send("Table dropped! Here's the EventData:");
SqlContext.Pipe.Send(triggContext.EventData.Value); break;
default: SqlContext.Pipe.Send("Something happened! Here's the
EventData:"); SqlContext.Pipe.Send(triggContext.EventData.Value);
break;
}
}
}

Returning Custom Result Set
Managed stored procedures can send result sets that do not
come from a SqlDataReader. The SendResultsStart method,
along with SendResultsRow and SendResultsEnd, allows stored
procedures to send custom result sets to the client.
SendResultsStart takes a SqlDataRecord as an input. It marks
the beginning of a result set and uses the record metadata to
construct the metadata that describes the result set. It does not
send the value of the record with SendResultsStart. All the
subsequent rows, sent using SendResultsRow, must match that
metadata definition.

DEMO 2
Creating Managed Objects with Visual Studio

public static void TransactionHistoryRunningSum()
{
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = @"" +
"SELECT TransactionID, ActualCost " +
"FROM Production.TransactionHistory " +
"ORDER BY TransactionID";
SqlMetaData[] columns = new SqlMetaData[3];
columns[0] = new SqlMetaData("TransactionID", SqlDbType.Int);
columns[1] = new SqlMetaData("ActualCost", SqlDbType.Money);
columns[2] = new SqlMetaData("RunningTotal", SqlDbType.Money);
decimal RunningSum = 0;
SqlDataRecord record = new SqlDataRecord(columns);
SqlContext.Pipe.SendResultsStart(record);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
decimal ActualCost = (decimal)reader[1];
RunningSum += ActualCost;
record.SetInt32(0, (int)reader[0]);
record.SetDecimal(1, ActualCost);
record.SetDecimal(2, RunningSum);
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
}

Discusion
Other way?
Ideas?

Supported Assemblies











Microsoft.Visualbasic.dll
Mscorlib.dll
System.Data.dll
System.dll
System.Xml.dll
Microsoft.Visualc.dll
Custommarshallers.dll
System.Security.dll
System.Web.Services.dll
System.Data.SqlXml.dll.

Demo
Creating PDF document in SQLCLR using IText

Documentation used
 http://msdn.microsoft.com/en-us/library/ms131102.aspx

• Complete electronic evaluation forms on the
computers in the hall and enter to win!
– Infragistics Ultimate
– Telerik DevCraft
– JetBrains .NET tools
– Semos training vouchers
– Pluralsight subscriptions
– and many more…

�ݺ�ߣ

SQLCLR Tips & Trics

More Related Content

SQLCLR Tips & Trics