ASP.NET Tutorial/ADO.net Database/CLR Database Objects
Содержание
Building a Data Access Layer with a User-Defined Type
<source lang="csharp">
File: App_Code\DBDataLayer.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class DBDataLayer {
private static readonly string _connectionString;
public List<DBProduct> GetAll()
{
List<DBProduct> results = new List<DBProduct>();
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT Product FROM DBProducts", con);
using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DBProduct newProduct = (DBProduct)reader["Product"];
results.Add(newProduct);
}
}
return results;
}
public void Insert(DBProduct productToAdd)
{
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("INSERT DBProducts (Product) VALUES (@Product)", con);
cmd.Parameters.Add("@Product", SqlDbType.Udt);
cmd.Parameters["@Product"].UdtTypeName = "DBProduct";
cmd.Parameters["@Product"].Value = productToAdd;
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}
}
static DBDataLayer()
{
_connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
}
}
File: ShowDBDataLayer.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DBDataLayer</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
Runat="server" />
<fieldset>
<legend>Add Product</legend>
<asp:DetailsView
id="dtlProduct"
DataSourceID="srcProducts"
DefaultMode="Insert"
AutoGenerateInsertButton="true"
AutoGenerateRows="false"
Runat="server">
<Fields>
<asp:BoundField DataField="Title" HeaderText="Title" />
<asp:BoundField DataField="Director" HeaderText="Director" />
<asp:BoundField DataField="Totals"
HeaderText="Box Office Totals" />
</Fields>
</asp:DetailsView>
</fieldset>
<asp:ObjectDataSource
id="srcProducts"
TypeName="DBDataLayer"
DataObjectTypeName="DBProduct"
SelectMethod="GetAll"
InsertMethod="Insert"
Runat="server" />
</form>
</body> </html></source>
Building Database Objects with the .NET Framework
<source lang="csharp">
Enabling CLR Integration You must enable CLR integration by executing the following SQL Server command: sp_configure "clr enabled", 1 RECONFIGURE Creating User-Defined Types with the .NET Framework You can create a new user-defined type. Then use it in exactly the same way as the built-in SQL types such as the Int, NVarChar, or Decimal types. For example, you can create a new type and use the type to define a column in a database table. To create a user-defined type with the .NET Framework 1. Create an assembly that contains the new type. 2. Register the assembly with SQL Server. 3. Create a type based on the assembly.
The class must be decorated with a SqlUserDefinedType attribute. The class must be able to equal NULL. The class must be serializable to/from a byte array. The class must be serializable to/from a string. SqlUserDefinedType supports the following properties: Format specifies how a user-defined type is serialized in SQL Server.
Possible values are Native and UserDefined.
IsByteOrdered marks the user-defined type as ordered in the same way as its byte representation. IsFixedLength specifies that all instances of this type have the same length. MaxByteSize specifies the maximum size of the user-defined type in bytes. Name specifies a name for the user-defined type. File: DBProduct.cs using System; using System.Text; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.Runtime.InteropServices; using System.IO; [SqlUserDefinedType(Format.UserDefined, MaxByteSize = 512, IsByteOrdered = true)] public class DBProduct : INullable, IBinarySerialize {
private bool _isNull;
private string _title;
private string _director;
private decimal totals;
public bool IsNull
{
get { return _isNull; }
}
public static DBProduct Null
{
get
{
DBProduct product = new DBProduct();
product._isNull = true;
return product;
}
}
public string Title
{
get { return _title; }
set { _title = value; }
}
public string Director
{
get { return _director; }
set { _director = value; }
}
[SqlFacet(Precision = 38, Scale = 2)]
public decimal Totals
{
get { return totals; }
set { totals = value; }
}
[SqlMethod(OnNullCall = false)]
public static DBProduct Parse(SqlString s)
{
if (s.IsNull)
return Null;
DBProduct product = new DBProduct();
string[] parts = s.Value.Split(new char[] { "," });
product.Title = parts[0];
product.Director = parts[1];
product.Totals = decimal.Parse(parts[2]);
return product;
}
public override string ToString()
{
if (this.IsNull)
return "NULL";
StringBuilder builder = new StringBuilder();
builder.Append(_title);
builder.Append(",");
builder.Append(_director);
builder.Append(",");
builder.Append(totals.ToString());
return builder.ToString();
}
public void Write(BinaryWriter w)
{
w.Write(_title);
w.Write(_director);
w.Write(totals);
}
public void Read(BinaryReader r)
{
_title = r.ReadString();
_director = r.ReadString();
totals = r.ReadDecimal();
}
public DBProduct()
{
}
}
You need to compile the DBProduct class into a separate assembly (.dll file).
csc /t:library DBProduct.cs
Registering the User-Defined Type Assembly with SQL Server Register the DBProduct assembly by executing the following command: CREATE ASSEMBLY DBProduct FROM "C:\DBProduct.dll"
Check SQL Server by executing the following query: SELECT * FROM sys.assemblies
You can drop any assembly by executing the DROP Assembly command. DROP Assembly DBProduct
Creating the User-Defined Type CREATE TYPE dbo.DBProduct EXTERNAL NAME DBProduct.DBProduct
If you need to delete the type, you can execute the following command: DROP TYPE DBProduct
Create a new database table with the following command: CREATE TABLE DBProducts(Id INT IDENTITY, Product DBProduct) You can insert a new record into this table with the following command: INSERT DBProducts (Product) VALUES ("Star Wars,George Lucas,12.34")
Finally, you can perform queries against the table with queries like the following: SELECT Id, Product FROM DBProducts WHERE Product.Totals > 13.23 SELECT MAX(Product.Totals) FROM DBProducts SELECT Product FROM DBProducts WHERE Product.Director LIKE "g%"</source>
Creating Stored Procedures with the .NET Framework
<source lang="csharp">
You must complete the following steps: 1. Create an assembly that contains the stored procedure method. 2. Register the assembly with SQL Server. 3. Create a stored procedure based on the assembly. Creating the Stored Procedure Assembly File: RandomRows.cs using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class RandomRows {
[SqlProcedure]
public static void GetRandomRow()
{
SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
DataTable dtblProducts = new DataTable();
dad.Fill(dtblProducts);
Random rnd = new Random();
DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];
SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
result.SetSqlInt32(0, (int)ranRow["Id"]);
result.SetSqlString(1, (string)ranRow["Title"]);
SqlContext.Pipe.Send(result);
}
[SqlProcedure]
public static void GetRandomRows(int rowsToReturn)
{
SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
DataTable dtblProducts = new DataTable();
dad.Fill(dtblProducts);
SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
SqlContext.Pipe.SendResultsStart(result);
Random rnd = new Random();
for (int i = 0; i < rowsToReturn; i++)
{
DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];
result.SetSqlInt32(0, (int)ranRow["Id"]);
result.SetSqlString(1, (string)ranRow["Title"]);
SqlContext.Pipe.SendResultsRow(result);
}
SqlContext.Pipe.SendResultsEnd();
}
} csc /t:library RandomRows.cs Registering the Stored Procedure Assembly with SQL Server CREATE ASSEMBLY RandomRows FROM "C:\RandomRows.dll" To remove the assembly: DROP Assembly RandomRows Creating the Stored Procedures CREATE PROCEDURE GetRandomRow AS EXTERNAL NAME RandomRows.RandomRows.GetRandomRow CREATE PROCEDURE GetRandomRows(@rowsToReturn Int) AS EXTERNAL NAME RandomRows.RandomRows.GetRandomRows
Executing the following command displays three random products from the Products database: GetRandomRows 3
If you need to delete these stored procedures, you can execute the following two commands: DROP PROCEDURE GetRandomRow DROP PROCEDURE GetRandomRows</source>
Executing a .NET Stored Procedure from an ASP.NET Page
<source lang="csharp">
File: App_Code\RandomDataLayer.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class RandomDataLayer {
private static readonly string _connectionString;
public List<String> GetRandomProducts()
{ List<String> results = new List<String>();
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("GetRandomRows", con);
cmd.rumandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@rowsToReturn", 5);
using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
results.Add((string)reader["Title"]);
}
return results;
}
public static string GetRandomProduct()
{
string result = String.Empty;
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("GetRandomRow", con);
cmd.rumandType = CommandType.StoredProcedure;
using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
result = (string)reader["Title"];
}
return result;
}
static RandomDataLayer()
{
_connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
}
}
File: ShowRandomDataLayer.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
void Page_Load()
{
lblRandomProduct.Text = RandomDataLayer.GetRandomProduct();
}
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show RandomDataLayer</title>
</head> <body>
<form id="form1" runat="server">
Random Product:
<asp:Label
id="lblRandomProduct"
Runat="server" />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
Runat="server" />
<asp:ObjectDataSource
id="srcProducts"
TypeName="RandomDataLayer"
SelectMethod="GetRandomProducts"
Runat="server" />
</form>
</body> </html></source>
