1 / 56

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

Lập trình SQL Server với .Net. Vu Tuyet Trinh trinhvt-fit@mail.hut.edu.vn Hanoi University of Technology. What is .NET?. An application development platform from Microsoft Rapidly develop secure and robust software Web and Windows Full support for object-oriented programming.

serina-holder
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lập trình SQL Server với .Net Vu Tuyet Trinh trinhvt-fit@mail.hut.edu.vn Hanoi University of Technology

  2. What is .NET? • An application development platform from Microsoft • Rapidly develop secure and robust software • Web and Windows • Full support for object-oriented programming

  3. 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)

  4. Outline • .Net Framework • ADO.net • CLR Integration • Enabling CLR Integration • CLT Integration: Pros & Cons

  5. Win Forms Web Services Web Forms … ADO.NET and XML Base Framework Classes Common Language Runtime .Net Framework

  6. Command Builder DataAdapter DataSet Command DataReader Transaction Connection ADO.NET Architecture Disconnected Layer Connected Layer Data Layer

  7. ADO.NET Managed Provider System.data.dll +{System.data} …. IDbConnection IDbCommand IDataReader IDbTransaction IDbDataAdapter …. System.data.dll +{System.data.SqlClient} …. SqlConnection SqlCommand SqlDataReader SqlTransaction SqlDataAdapter SqlCommandBuilder Warning: There is no IDbCommandBuilder

  8. 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)

  9. Table Column Constraint Relation Row DataSet DataSet • In-memory cache of data • Relational view of data • Data source agnostic • Keeps track of changes • Contains an XSD schema • MarshalByValue object Schema

  10. TypedDataSet Without TypedDataSet With TypedDataSet Code is more readable Introduces compile-time checking Intellisense under Visual Studio dataset.Tables[“Orders”].Rows[0][“CustomerID"] = 1023; dataset.Orders[0].CustomerID = 1023; dataset.Tables[0].Rows[0][1] = 1023;

  11. Generated TypedDataSet DataSet Orders Tables[0] OrdersDetails Tables[1] • Class OrdersDataSet: DataSet { • InitClass() { • // create relations, constraints • } • class OrdersDataTable: DataTable { • void AddOrdersRow(OrderRow row) • void AddOrderRow(int OrderID, int CustomerID, ..) • OrderRow FindOrderByID(int OrderID) • OrderRow NewOrderRow() • } • class OrdersRow: DataRow { • int OrderID {get; set} • int CustomerID {get; set} • ..... • } • …… • }

  12. DataAdapter CommandBuilder DataAdapter SelectCommand Fill Fill InsertCommand DataSet UpdateCommand DeleteCommand Update Update TableMapping

  13. DataSet Interaction XmlTextReader DataView Filter/Sort XML File XmlTextWriter DataControl DataGrid ComboBox… DB SqlDataAdapter

  14. Outline • .Net Framework • ADO.net • CLR Integration • Enabling CLR Integration • CLT Integration: Pros & Cons

  15. 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

  16. 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 … )

  17. 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

  18. Enabling CLR Integration • Enabled on an instance • SQL Script • Execute sp_configure ‘clr enabled’, ‘1’ • Sp_reconfigure • Surface Area Configuration (features)

  19. Assembly Management • Adding an assembly from file • Note: must have permissions (NT Security) • Adding an assembly from bitstream CREATE ASSEMBLY AssemlyExample FROM 'd:\AssemlyExample.dll' CREATE ASSEMBLY AssemlyExample FROM 0x4D5A90000300000004000000FFFF0000B8000000000000...

  20. 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

  21. 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

  22. 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

  23. 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 } }

  24. 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

  25. 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; }

  26. 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

  27. 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

  28. Managed Functions (2) • Code is similar to Stored Procedures • [SqlFunction] Parameters • Helps SQL Server know what the function does • IsDeterministic • IsPrecise • DataAccess • SystemDataAccess [SqlFunction] public static int Addition(int x, int y) { return x + y; }

  29. Managed Triggers • DML Triggers • DDL 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 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

  30. 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 } }

  31. 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

  32. 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.

  33. 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) { /*...*/ } }

  34. InProc Managed Provider • Inside the Server, a new Provider • Very similar to SqlClient Provider • Follows Managed Provider Model • Supports • IDbConnection • IDbCommand • IDbDataReader • Etc.

  35. 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

  36. InProc Managed Provider (3) • InProc you can assume the connection using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "..."; SqlContext.Pipe.ExecuteAndSend(cmd); }

  37. 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(); } }

  38. InProc Managed Provider (5) • SqlContext • Used to get active server objects • Pipe • WindowsIdentity • Etc.

  39. 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);

  40. 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);

  41. 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();

  42. 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); }

  43. Managed User-Defined Datatypes • Before SQL Server 2005 • User Datatypes were aliases or restrictions • Since SQL Server 2005 • Can store CLR Type as a Datatype • Rules are embedded in the CLR Type EXEC sp_addtype N'age', N'tinyint', N'not null' GO CREATE RULE age_range AS @age >= 0 AND @age <=140 GO EXEC sp_bindrule N'age_range', N'age' GO

  44. 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)

  45. 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

  46. 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; } // ... }

  47. 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

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

  49. Outline • .Net Framework • ADO.net • CLR Integration • Enabling CLR Integration • CLR Integration: Pros & Cons

  50. 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

More Related