Lập trình SQL Server

với .Net

Vu Tuyet Trinh
[email protected]
Hanoi University of Technology

1
 What is .NET?

 An application development platform from Microsoft

 Rapidly develop secure and robust software
 Web and Windows
 Full support for object-oriented programming

Microsoft
 Advantages of .Net Framework

 CLR – Common Language Runtime

 Garbage collector, type safety, JIT, …
 Language integration
 C#, VB.net, …
 Built-in support for open standards
 SOAP, …
 Simple development, simple deployment
 Supporting entity data model (ADO.net & entity framework)

Microsoft
 Outline

 .Net Framework
 ADO.net
 CLR Integration
 Enabling CLR Integration
 CLT Integration: Pros & Cons

Microsoft
 .Net Framework

Web Web Win

Services Forms Forms
…

ADO.NET and XML

Base Framework Classes

Common Language Runtime

Microsoft
 ADO.NET Architecture
Disconnected Connected Data
Layer Layer Layer

Command
DataSet DataAdapter
Builder

DataReader Command

Transaction Connection

Microsoft
 ADO.NET Managed Provider
System.data.dll System.data.dll
+{System.data} +{System.data.SqlClient}
…. ….
IDbConnection SqlConnection
IDbCommand SqlCommand
IDataReader SqlDataReader
IDbTransaction SqlTransaction
IDbDataAdapter SqlDataAdapter
…. SqlCommandBuilder

Warning: There is no IDbCommandBuilder

Microsoft
 DataReader
 Fast Forward-Only/Read-Only streams of data
 Returned by IDbCommand.ExecuteReader(...)
 Data is not cached on the client-side
 Must be closed explicitly
 Not serializable
 Cannot be inherited (sealed class)

Microsoft
 DataSet
DataSet
Table
 In-memory cache of data
 Relational view of data
Column
 Data source agnostic
 Keeps track of changes
Constraint
 Contains an XSD schema
 MarshalByValue object
Row

Relation

Microsoft Schema
 TypedDataSet
 Without TypedDataSet

dataset.Tables[0].Rows[0][1] = 1023;

dataset.Tables[“Orders”].Rows[0][“CustomerID"] = 1023;

 With TypedDataSet

dataset.Orders[0].CustomerID = 1023;
 Code is more readable
 Introduces compile-time checking
 Intellisense under Visual Studio
Microsoft
 Generated TypedDataSet
Class OrdersDataSet: DataSet {
InitClass() {
DataSet
// create relations, constraints
} Orders
class OrdersDataTable: DataTable { Tables[0]
void AddOrdersRow(OrderRow row)
void AddOrderRow(int OrderID, int CustomerID, ..) OrdersDetails
OrderRow FindOrderByID(int OrderID)
Tables[1]
OrderRow NewOrderRow()
}
class OrdersRow: DataRow {
int OrderID {get; set}
int CustomerID {get; set}
.....
}
……
Microsoft
}
 DataAdapter
CommandBuilder

DataAdapter

SelectCommand
Fill Fill
InsertCommand
DataSet
UpdateCommand
DeleteCommand
Update Update

TableMapping

Microsoft
 DataSet Interaction

DataView XmlTextReader
XML
Filter/Sort XmlTextWriter File

DataControl
SqlDataAdapter DB
DataGrid
ComboBox…
ComboBox

Microsoft
 Outline

 .Net Framework
 ADO.net
 CLR Integration
 Enabling CLR Integration
 CLT Integration: Pros & Cons

Microsoft
 CLR Integration

 Brand new since SQL Server 2005

(Standard and Express)
 Write database queries using .NET
 Supporting Any .NET language (C#, VB, C++)
 Creating and debugging using VS 2005 IDE

Microsoft
 What can we do with CLR code?

 Common T-SQL objects can be implemented in CLR

code
 User defined functions (and table valued functions)
 Stored procedures
 Triggers
 Additional objects can be implemented in CLR code
 User Defined Types
 User Defined Aggregates (MAX, MIN, SUM … )

Microsoft
 Where do we use CLR code?

Round trip Round trip

 “Distance” between the code and the data

 Scale up/out possibilities of different tiers
 Abstraction of the database technology
 Security requirements
 Set-based versus procedural code
 Possibilities for using shared code libraries in multiple tiers

Microsoft
 Enabling CLR Integration

 Enabled on an instance
 SQL Script
 Execute sp_configure ‘clr enabled’, ‘1’
 Sp_reconfigure

 Surface Area Configuration (features)

Microsoft
 Assembly Management

 Adding an assembly from file

 Note: must have permissions (NT Security)
CREATE ASSEMBLY AssemlyExample
FROM 'd:\AssemlyExample.dll'

 Adding an assembly from bitstream

CREATE ASSEMBLY AssemlyExample
FROM
0x4D5A90000300000004000000FFFF0000B8000000000000...

Microsoft
 Code Access Security for Assemblies

3 Code Access Security (CAS) Buckets

 SAFE
 Access to the CLR only
 No access to external resources, thread management, unsafe code or
interoper
 EXTERNAL_ACCESS
 Access to external systems through the .NET Framework
 E.g. EventLog, FileSystem and Network
 No access unsafe or interop
 UNSAFE
 No restrictions; similar to extended stored procedures

Microsoft
 DML Assembly Commands for CAS
 SAFE
CREATE ASSEMBLY AssemlyExample
FROM 'd:\AssemlyExample.dll'
WITH PERMISSION_SET=SAFE

 EXTERNAL_ACCESS
CREATE ASSEMBLY AssemlyExample
FROM 'd:\AssemlyExample.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS

 UNSAFE
CREATE ASSEMBLY AssemlyExample
FROM 'd:\AssemlyExample.dll'
WITH PERMISSION_SET=UNSAFE

Microsoft
 Managed Code
 Code isn’t available by default
 Must register functions, stored procedures, etc.
 Code is not available by default
 Registration takes certain permissions to allow
 Attributes
 Hints to VS about how to deploy
 [SqlProcedure]
 [SqlFunction]
 [SqlUserDefinedType]
 [SqlUserDefinedAggregate]
 ….
 Also used at runtime for behaviors of objects

Microsoft
 Managed Stored Procedures

 To expose a Stored Procedure:

 The containing class must be public
 The exposed method must be public
 The exposed method must be static
public class SqlClr {
public static void MyProc() {
// Put your code here
}
}

Microsoft
 Managed Stored Procedure DML

 Uses the CREATE PROCEDURE call

 Adds AS EXTERNAL NAME to specify CLR SP

CREATE PROCEDURE <Procedure Name>

AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

 Example

CREATE PROCEDURE MyProc

AS EXTERNAL NAME AssemlyExample.SqlClr.MyProc

Microsoft
 Stored Procedure Parameters

// Input Parameter
public static void InputProcedure(int number) {
}

// Output Parameter
public static void OutputProcedure(out int number) {
number = 42;
}

// In/Out Parameter
public static void InOutProcedure(ref int number) {
number = 42;
}

// Return Parameter
public static int ReturnProcedure() {
return 42;
Microsoft }
 DML must match the parameters

CREATE PROCEDURE InputProcedure

@number int
AS EXTERNAL NAME AssemlyExample.SqlClr.InputProcedure

CREATE PROCEDURE OutputProcedure

@number int OUTPUT
AS EXTERNAL NAME AssemlyExample.SqlClr.OutputProcedure

CREATE PROCEDURE InOutProcedure

@number int OUTPUT
AS EXTERNAL NAME AssemlyExample.SqlClr.InOutProcedure

-- NOTE: You don’t specify ReturnParameters

CREATE PROCEDURE ReturnProcedure
AS EXTERNAL NAME AssemlyExample.SqlClr.ReturnProcedure
Microsoft
 Managed Functions
Using similar DML Syntax:

CREATE FUNCTION <Function Name>

(
<Parameter List>
)
RETURNS <Return Type>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

CREATE FUNCTION Addition

(
@x int,
@y int
)
RETURNS int
AS EXTERNAL NAME ExampleYukon.SqlClr.Addition

Microsoft
 Managed Functions (2)

 Code is similar to Stored Procedures

[SqlFunction]
public static int Addition(int x, int y) {
return x + y;
}

 [SqlFunction] Parameters
 Helps SQL Server know what the function does
 IsDeterministic
 IsPrecise
 DataAccess
 SystemDataAccess

Microsoft
 Managed Triggers
 DML Triggers

CREATE TRIGGER <TriggerName>

ON <Table or View> <FOR|INSTEAD OF> <INSERT|UPDATE|DELETE>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

CREATE TRIGGER AddContact

ON author FOR INSERT
AS EXTERNAL NAME AssemlyExample.SqlClr.AddContact

 DDL Triggers
CREATE TRIGGER <TriggerName>
ON <ALL_SERVER or DATABASE> <FOR or AFTER> <EventName>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>
CREATE TRIGGER AddUser
ON DATABASE FOR CreateUser
AS EXTERNAL NAME AssemlyExample.SqlClr.AddUser

Microsoft
 TriggerContext
 ColumnsUpdates to see what columns changed
public static void AddAuthor() {
SqlTriggerContext ctx = SqlContext.TriggerContext;

if (ctx.TriggerAction == TriggerAction.Insert) {
string msg = "";
// Get the data associated with Event
for (int x = 0; x < ctx.ColumnCount; ++x) {
msg += string.Format("Column {0} {1} been updated{2}",
x,
(ctx.IsColumnsUpdated(x) ? "has" : "has not"),
Environment.NewLine)
}

// Use the message to log it somewhere

}
}

Microsoft
 Custom Aggregates

 Write your own MIN, MAX, COUNT

 Define a CLR Type that does the aggregation
 Use DML command to register
CREATE AGGREGATE <Aggregate Name>
( @param <SQL Type> )
RETURNS <SQL Type>
EXTERNAL NAME <assembly>.<CLR Type>

CREATE AGGREGATE CustomAverage

( @value float )
RETURNS float
EXTERNAL NAME AssemlyExample.CustomAverage

Microsoft
 Custom Aggregates - CLR Class
 called during the aggregation
 Not just passed a set of values, but one at a time
 Must be serializable (for intermediate results)
 Must implement known methods
 Init
 Called at the start of the aggregation of fields
 Accumulate
 Called once for each row being aggregated. It is passed the value from
the column being aggregated.
 Merge
 Called to merge a temporary result into the aggregate
 Terminate
 Called at the end of the aggregation process to retrieve the result of the
aggregation.

Microsoft
 Custom Average Example

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public class CustomAverage : IBinarySerialize {

SqlDouble _total = 0;
ulong _totalCount = 0;

public void Init() { }

public void Accumulate(SqlDouble Value) {
++_totalCount;
if (_total.IsNull) _total = 0;
_total += Value;
}
public void Merge(StdDev grp) {/*...*/ }
public SqlDouble Terminate() { return _total/_totalCount; }

void IBinarySerialize.Read(BinaryReader r) { /*...*/ }

void IBinarySerialize.Write(BinaryWriter w) { /*...*/ }
}
Microsoft
 InProc Managed Provider

 Inside the Server, a new Provider

 Very similar to SqlClient Provider
 Follows Managed Provider Model
 Supports
 IDbConnection
 IDbCommand
 IDbDataReader
 Etc.

Microsoft
 InProc Managed Provider (2)
 Dude, where’s my Connection?
 In Out-of-proc ADO.NET:

using (SqlConnection conn = new SqlConnection("..."))

using (SqlCommand cmd = conn.CreateCommand()) {
try {
cmd.CommandText = "...";
conn.Open();

using (SqlDataReader rdr = cmd.ExecuteReader()) {

while (rdr.Read()) {
Console.WriteLine(rdr.GetString(0));
}
} // Reader is disposed
} finally {
conn.Close()
}
} // using statements ensure Disposed is called
Microsoft
 InProc Managed Provider (3)

 InProc you can assume the connection

using (SqlCommand cmd = new SqlCommand()) {
cmd.CommandText = "...";

SqlContext.Pipe.ExecuteAndSend(cmd);
}

Microsoft
 InProc Managed Provider (4)
 If you need a connection
 Can create additional connections
 Use “context connection=true” for current

using (SqlConnection conn =

new SqlConnection("context connection=true"))
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.CommandText = @"SELECT * FROM Sales.SalesOrderHeader";

try {
conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
total += Convert.ToDecimal(rdr["TotalDue"]);

} finally {
conn.Close();
}
Microsoft
}
 InProc Managed Provider (5)

 SqlContext
 Used to get active server objects
 Pipe
 WindowsIdentity
 Etc.

Microsoft
 InProc Managed Provider (6)
 Returning Data
 Use a Pipe to send data back to the client
 Use the SqlContext’s Pipe
 Use Execute() to Fire a Command into the pipe
 Use Send() to send results back from a Reader

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT * FROM Customer";

// Send the results to the client

SqlPipe pip = SqlContext.Pipe.Execute(cmd);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customer";
SqlDataReader rdr = cmd.ExecuteReader();
// Send the results to the client
SqlContext.Pipe.Send(rdr);
Microsoft
 InProc Managed Provider (7)

 Returning Custom Results

 Use SqlDataRecord
 Must create SqlMetaData to describe the result
SqlMetaData[] columns = new SqlMetaData[3];
columns[0] = new SqlMetaData("ID", SqlDbType.Int);
columns[1] = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
columns[2] = new SqlMetaData("theDate", SqlDbType.DateTime);

SqlDataRecord record = new SqlDataRecord(columns);

record.SetInt32(0, 1);
record.SetString(1, "Bob Higgins");
record.SetDateTime(2, DateTime.Now);

SqlContext.Pipe.Send(record);
Microsoft
 InProc Managed Provider (8)
 Return Custom Results (Multiple Rows)
 Use SendResultsStart to begin
 Must send all rows with SendResultsRow
 Must end with SendResultsEnd

// Start it
SqlContext.Pipe.SendResultsStart(record); // Only primes pump
SqlContext.Pipe.SendResultsRow(record);

SqlDataRecord anotherRecord = new SqlDataRecord(columns);

anotherRecord.SetInt32(0, 2);
anotherRecord.SetString(1, "Mary Roberts");
anotherRecord.SetDateTime(2, DateTime.Now.AddDays(1));
SqlContext.Pipe.SendResultsRow(anotherRecord);

// Finish ResultSet
SqlContext.Pipe.SendResultsEnd();
Microsoft
 InProc Managed Provider (9)
 SQL Server Datatypes
 Use System.Data.SqlTypes namespace
 Each SqlXXXXX type is INullable
 Allows Stored Procs that allow DB Nulls
public static void GetContact(SqlString email) {
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
@"SELECT FirstName, LastName
FROM Person.COntact
WHERE EmailAddress = @email";

cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50);

cmd.Parameters["@email"].Value = email;

SqlContext.Pipe.ExecuteAndSend(cmd);
}
Microsoft
 Managed User-Defined Datatypes
 Before SQL Server 2005
 User Datatypes were aliases or restrictions

EXEC sp_addtype N'age', N'tinyint', N'not null'

GO

CREATE RULE age_range

AS
@age >= 0 AND @age <=140
GO
 Since SQL Server 2005

EXEC sp_bindrule N'age_range', N'age'
Can store CLR Type as a Datatype
GO
 Rules are embedded in the CLR Type

Microsoft
 CLR Data Type - Requirements

 Supports the concept of NULL

 Supports conversion to and from string
 Supports serialization
 Supports a default constructor
 Type and member conform to naming rules
 (128 character maximum)

Microsoft
 Creating a User-Defined Data Type

DML Statement: CREATE TYPE

CREATE TYPE <database type name>

EXTERNAL NAME <assembly name>.<CLR type name>

CREATE TYPE Point

EXTERNAL NAME AssemlyExample.Point

Microsoft
 UDT Example
[Serializable]
[SqlUserDefinedType(Format.UserDefined, MaxByteSize=8)]
public class Point : INullable, IBinarySerialize {
bool isNull = false;
int x;
int y;

// Required constructor
public Point() : this(0, 0) {
}

public Point(int x, int y) {

this.x = x;
this.y = y;
}
// ...
}
Microsoft
 Using a UDT

 Use CONVERT to switch from string to type

DECLARE @p as dbo.Point
SET @p = Convert(dbo.Point, '3,8')
SELECT @p

Microsoft
 UDT Methods

Marking it with SqlMethod can expose it

 IsMutator shows SQL Server that it changes itself:
[SqlMethod(IsMutator=true)]
public void Swap() {
int temp = x;
x = y;
y = temp;
}
 Can use with the point syntax:
DECLARE @p as dbo.Point
SET @p = Convert(dbo.Point, '3,8')
SELECT @p
@p.Swap()
SELECT @p
Microsoft
 Outline

 .Net Framework
 ADO.net
 CLR Integration
 Enabling CLR Integration
 CLR Integration: Pros & Cons

Microsoft
 Benefits from CLR Integration

 Take advantage of the powerful .NET Framework

 .NET is a full-featured programming language
 Supports things like “for each” loops, arrays, collections
 Object Oriented programming model to organise your queries
 Obtaining data from external resources
 The File System
 The Event Log
 A Web Service
 The Registry

Microsoft
 Benefits … (2)

 For complex calculations

 Parsing strings (like the regular expression code)
 User-defined types
 Date, time, currency, and extended numeric types
 Geospatial applications
 Encoded or encrypted data (see books online)
 User-defined aggregates

 Powerful Intellisense and debugging

 Generally faster
 E.g. CLR aggregate 100x faster than cursor

Microsoft
 Week points …

 Lots of programming for simple operations

 Some overhead in communicating with assemblies
 Remember – T-SQL is designed and optimised for data,
use it!
 Potentially costly to rewrite logic
 Companies (including us) have invested a lot in T-SQL

Microsoft
 Calling between T-SQL & SQL-CLR
Having some restrictions
 Must only use T-SQL supported data types.
 Can't use inheritance or polymorphism.

 No equivalent of DB_NULL in .NET

 can use null for reference types, but not for types like int .

 .NET cannot easily represent either VARCHAR or TIMESTAMP

 .NET strings are Unicode, the equivalent of NVARCHAR.

 The CLR decimal type is not the same as SQL_DECIMAL.

Microsoft
 Summary
 Need to manipulate data before it is displayed
 Use .NET code and SQLCLR

 Need to do set-based operations such as pivoting

 Use T-SQL

 Need to do extensive computation or custom algorithms

 Use .NET code and SQLCLR

 Have many complex store procedure

 .NET code and SQLCLR

Microsoft
Microsoft
 Demo

Microsoft