Lập trình SQL Server
với .Net
Vu Tuyet Trinh

Hanoi University of Technology
1
MicrosoftMicrosoft
What is .NET?

An application development platform from Microsoft

Rapidly develop secure and robust software

Web and Windows

Full support for object-oriented programming
MicrosoftMicrosoft
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, …


DataAdapter
ADO.NET Architecture
Command
Transaction
Connection
Command
Builder
Disconnected
Layer
Connected
Layer
Data
Layer
MicrosoftMicrosoft
ADO.NET Managed Provider
System.data.dll
+{System.data}
….
IDbConnection
IDbCommand
IDataReader
IDbTransaction
IDbDataAdapter
….
System.data.dll
+{System.data.SqlClient}
….
SqlConnection
SqlCommand
SqlDataReader


MarshalByValue object
DataSet
Schema
Relation
Table
Column
Constraint
Row
MicrosoftMicrosoft
TypedDataSet

Without TypedDataSet
Without TypedDataSet

With TypedDataSet
With TypedDataSet

Code is more readable

Introduces compile-time checking

Intellisense under Visual Studio
dataset.Tables[0].Rows[0][1] = 1023;
dataset.Tables[“Orders”].Rows[0][“CustomerID"] = 1023;
dataset.Orders[0].CustomerID = 1023;
MicrosoftMicrosoft
Generated TypedDataSet
Class OrdersDataSet: DataSet {
InitClass() {

CommandBuilder
DataSet
Fill
Update
Fill
Update
MicrosoftMicrosoft
DataSet Interaction
SqlDataAdapter
DB
XmlTextReader
XmlTextWriter
XML
File
DataControl
DataControl
DataGrid
DataGrid
ComboBox
ComboBox…
DataView
DataView
Filter/Sort
MicrosoftMicrosoft
Outline
√
.Net Framework
√
ADO.net



User Defined Aggregates (MAX, MIN, SUM … )
MicrosoftMicrosoft
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
MicrosoftMicrosoft
Enabling CLR Integration

Enabled on an instance

SQL Script

Execute sp_configure ‘clr enabled’, ‘1’

Sp_reconfigure

Surface Area Configuration (features)
MicrosoftMicrosoft


UNSAFE

No restrictions; similar to extended stored procedures
MicrosoftMicrosoft
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
MicrosoftMicrosoft
Managed Code

Code isn’t available by default

Must register functions, stored procedures, etc.

Code is not available by default


}
}
MicrosoftMicrosoft
Managed Stored Procedure DML

Uses the CREATE PROCEDURE call

Adds AS EXTERNAL NAME to specify CLR SP

Example
CREATE PROCEDURE <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>
CREATE PROCEDURE MyProc
AS EXTERNAL NAME AssemlyExample.SqlClr.MyProc
MicrosoftMicrosoft
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;