简介

返回页首

CLR 集成概述

返回页首

CLR 与 TransacT-SQL

SELECT <complex-calculation>(<column-name>,...)
            FROM <table>
            WHERE <complex-calculation>(<column-name>,...) = ...
            

返回页首

代码位置：数据库与中间层

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public class ProductionSchedule
{
//4-year limit on scheduling
public const int MAXPRODUCTS = 101;
public const int MAXWEEKS = 210;
public const int MAXNAME = 256;
public ProductionSchedule()
{
}
public static int Schedule(SqlDateTime startDate, int numWeeks)
{
SqlDateTime[] week = new SqlDateTime[MAXWEEKS];
int[] quantity;
int[][] Cij;
int[] Fk;
int[] minK = new int[MAXWEEKS];
int product_id, current_product, product_count = 0;
int startPeriod;
// We'll use arrays to keep state about products and forecasts
in memory. This is only viable given that we know we have a small number
of products and weeks.
// For larger data sets, we would have to consider cursors or
temporary tables.
// stored as CLR types since we know they can't be null
int[] h = new int[MAXPRODUCTS];
int[] K = new int[MAXPRODUCTS];
// stored as nullable SqlChars since the table schema allows for null names
SqlChars[] productNames = new SqlChars[MAXPRODUCTS];
bool moreProducts = true;
int optimal_j;
int period;
int sum;
SqlPipe pipe = SqlContext.GetPipe();
SqlDataRecord record;
object[] values = new object[3];
SqlMetaData[] metadata = new SqlMetaData[3];
//Initialize algorithm arrays
Cij = new int[MAXWEEKS][];
for( int l=0;l<MAXWEEKS;l++)
Cij[l] = new int[MAXWEEKS];
Fk = new int[MAXWEEKS];
//Look up K and h for all products
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = @"SELECT pname, InventoryCost, StartupCost from dbo.t_Products ORDER BY PID";
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
productNames[product_count] = reader.GetSqlChars(0); //product name
h[product_count] = reader.GetInt32(1); //holding cost
K[product_count] = reader.GetInt32(2); //startup cost
product_count++;
// if we exceeded number of expected products then bail out with an exception
if (product_count >= MAXPRODUCTS)
{
throw new Exception("Too many products");
}
}
reader.Close();
product_count = 0;
//Get the list of product ids;
cmd = SqlContext.GetCommand();
cmd.CommandText = @"select PID, weekdate, DemandQty from dbo.t_SalesForecast ORDER BY PID, WeekDate";
reader = cmd.ExecuteReader();
moreProducts=reader.Read();
//Set up the record for returning results
metadata[0] = new SqlMetaData( "Product",
SqlDbType.NVarChar,MAXNAME );
metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime );
metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int );
record = new SqlDataRecord( metadata );
while( moreProducts )
{
product_id = current_product = reader.GetInt32(0);
int index = 1;
quantity = new int[MAXWEEKS];
while( current_product == product_id )
{
week[index] = reader.GetSqlDateTime(1);
quantity[index] = reader.GetInt32(2);
index++;
moreProducts = reader.Read();
if( !moreProducts )
break;
current_product = reader.GetInt32(0);
}
//Determine the ordinal start week
startPeriod = 1;
//For each product ID calculate Cij
for( int i = startPeriod; i < (startPeriod + numWeeks); i++ )
{
for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ )
{
Cij[i][j] = GetCij(quantity,i,j,K [product_count],h[product_count]);
}
}
//Calculate Fk
for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--)
{
minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk);
}
//Send the results
record.SetSqlChars(0,productNames[product_count]);
pipe.SendResultsStart(record,false);
for( int k = startPeriod; k < startPeriod + numWeeks; )
{
period = k;
optimal_j = minK[k];
sum = 0;
while( k < optimal_j )
{
sum = sum + quantity[k++];
}
values[1] = week[period];
record.SetValue(1,values[1]);
values[2] = sum;
record.SetValue(2,values[2]);
pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
product_count++;
}
reader.Close();
return 0;
}
private static int GetCij(int[] quantities, int i, int j, int K, int h)
{
if( j == i+1 )
return K;
else
return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h);
}
private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk)
{
int j,min;
j = k+1;
min = j;
if ( k == n+1 )
{
Fk[k] = 0;
return j;
}
Fk[k] = Cij[k][j] + Fk[j];
for(; k <= n ;k++)
{
j = k + 1;
while( j <= n+1 )
{
if( Cij[k][j] + Fk[j] < Fk[k] )
{
min = j;
Fk[k] = Cij[k][j] + Fk[j];
}
j++;
}
}
return min;
}
}

返回页首

处理常见数据库编程任务和问题

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
public partial class StringFunctions
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
return r1.Match(matchString.TrimEnd(null)).Success;
}
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString ExtractAreaCode(string matchString)
{
Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
Match m = r1.Match(matchString);
if (m.Success)
return m.Value.Substring(1, 3);
else return SqlString.Null;
}
};

create table Contacts
(
FirstName nvarchar(30),
LastName  nvarchar(30),
EmailAddress nvarchar(30) CHECK
(dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)',
EmailAddress) = 1),
USPhoneNo nvarchar(30) CHECK
(dbo.RegExMatch('\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]',
UsPhoneNo)=1),
AreaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED
)

create proc proc1 as
select col1 from dbo.table1;
create proc proc2 as
exec proc1;

create proc proc2 as
declare @t table(col1 int);
insert @t (col1) exec proc1;
-- do something with results

...
SqlCommand cmd=SqlContext.GetCommand();
cmd.CommandText= "select col1 from dbo.table1";
SqlDataReader sdr=cmd.ExecuteReader();
while (sdr.Read())
{
// do something with current row
}
...

create proc proc1 as
select col1 from dbo.table1;
The equivalent in C# would be:
public static void proc1()
{
System.Data.SqlServer.SqlCommand cmd=SqlContext.GetCommand();
cmd.CommandText= "select col1 from dbo.table1";
SqlContext.GetPipe().Execute(cmd);
}

// Retrieve the RSS feed
XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select("//item");
// create metadata for four columns
// three of them are string types and one of the is a datetime
SqlMetaData[] rss_results = new SqlMetaData[4];
rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
rss_results[1] = new SqlMetaData("Publication Date", SqlDbType.DateTime);
rss_results[2] = new SqlMetaData("Description", SqlDbType.NVarChar, 2000);
rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(rss_results);
// cache a SqlPipe instance to avoid repeated calls to SqlContext.GetPipe()
SqlPipe sqlpipe = SqlContext.GetPipe();
// send the metadata, do not send the values in the data record
sqlpipe.SendResultsStart(record, false);
// for each xml node returned, extract four pieces
// of information and send back each item as a row
while (i.MoveNext())
{
record.SetString(0, (string)
i.Current.Evaluate("string(title[1]/text())"));
record.SetDateTime(1, DateTime.Parse((string)
i.Current.Evaluate("string(pubDate[1]/text())")));
record.SetString(2, (string)
i.Current.Evaluate("string(description[1]/text())"));
record.SetString(3, (string)
i.Current.Evaluate("string(link[1]/text())"));
sqlpipe.SendResultsRow(record);
}
// signal end of results
sqlpipe.SendResultsEnd();

[SqlFunction]
public static ISqlReader tvf1()
{
return (ISqlReader)new RssReader();
}
public class RssReader : ISqlReader
{
SqlMetaData[] rss_results = null;
XPathDocument doc;
XPathNavigator nav;
XPathNodeIterator i;
// Construct helper class, initializing metadata for the results
// reading from the RSS feed, creating the iterator
public RssReader()
{
rss_results = new SqlMetaData[4];
rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
rss_results[1] = new SqlMetaData("Publication Date",
SqlDbType.DateTime);
rss_results[2] = new SqlMetaData("Description", SqlDbType.NVarChar, 2000);
rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
// Retrieve the RSS feed
doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
nav = doc.CreateNavigator();
i = nav.Select("//item");
}
// # of columns returned by the function
public int FieldCount { get { return rss_results.Length; } }
// metadata for each of the columns
public SqlMetaData GetSqlMetaData(int FieldNo)
{ return rss_results[FieldNo]; }
// Read method positions the navigator iterator on next element
public bool Read() { return i.MoveNext(); }
// methods to return each column
public Object GetValue(int FieldNo)
{
switch (FieldNo)
{
case 0:
return new SqlString((string)
i.Current.Evaluate("string(title[1]/text())"));
case 1:
return new SqlDateTime(DateTime.Parse(
(string)i.Current.Evaluate("string(pubDate[1]/text())")));
case 2:
return new SqlString((string)
i.Current.Evaluate("string(description[1]/text())"));
case 3:
return new SqlString((string)
i.Current.Evaluate("string(link[1]/text())"));
}
return null;
}
public string GetString(int i) { return (string)GetValue(i); }
public DateTime GetDateTime(int i) { return (DateTime)GetValue(i); }
public SqlChars GetSqlCharsRef(int i) {
return new SqlChars((SqlString)GetValue(i)); }
public SqlChars GetSqlChars(int i) {
return new SqlChars((SqlString)GetValue(i)); }
public SqlDateTime GetSqlDateTime(int i) {
return (SqlDateTime)GetValue(i); }
...
}

select title, pubdate, description, link from dbo.GetRssFeed()

select title, pubdate, description, dbo.CanonicalURL(link)
from dbo.tvf1()
order by pubdate

// TVF that cracks a ';' separated list of strings into a result
// set of 1 nvarchar(60)column called Value
public static ISqlReader GetStrings(SqlString str)
{
return (ISqlReader)new MySqlReader(str);
}
public class MySqlReader : ISqlReader
{
private string[]  m_strlist;
private int m_iRow = -1; // # rows read
//The core methods
//Initialize list
public MySqlReader(SqlString str)
{
//Split input string if not database NULL;
//else m_strlist remains NULL
if (!str.IsNull)
{
m_strlist = str.Value.Split(';');
}
}
// SECTION: Metadata related: Provide #, names, types of
// result columns
public int FieldCount { get { return 1; } }
public SqlMetaData GetSqlMetaData(int FieldNo)
{
if (FieldNo==0)
return new SqlMetaData("Value", SqlDbType.NVarChar, 60);
else throw new NotImplementedException();
}
// SECTION: Row navigation. Read is called until it returns
// false. After each Read call, Get<TypeName> for each
// column is called.
public bool Read()
{
//Return empty result set if input is DB NULL
//and hence m_strlist is uninitialized
if (m_strlist==null) return false;
m_iRow++;
if (m_iRow == m_strlist.Length)
return false;
return true;
}
//Column getters
//Implement Get<SqlTypeName> for each column produced by
//the TVF; in this case just one.
public SqlChars GetSqlChars(int i)
{
if (i == 0)
return new SqlChars(m_strlist[m_iRow]);
else
throw new NotImplementedException();
}
//Methods not used by SqlServer omitted;
//Actual implementation should provide an empty
//implementation.
...
} // public class MySqlReader
} // class StringFunctions;

    CREATE PROCEDURE Insert_Order @cust_id int, @lineitems
nvarchar(8000)
AS
BEGIN
...
INSERT LineItems
SELECT * FROM dbo.GetStrings(@lineitems)
...
END

[SqlUserDefinedAggregate(Format.Native)]
public struct Product
{
public void Accumulate(SqlInt32 Value)
{
m_value *= Value;
}
public void Init() {...}
public void Merge(Product Group) {...}
public SqlInt32 Terminate() {...}
}

SELECT dbo.Product(intcol)
FROM tbl
GROUP BY col

[SqlProcedure]
public static void Product(out SqlInt32 value)
{
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "select intcolumn from tbl";
SqlDataReader r = cmd.ExecuteReader();
bool first = true;
using (r)
{
while (r.Read()) //skip to the next row
{
if (first)
{
value = r.GetSqlInt32(0);
first = false;
}
else
{
value *= r.GetSqlInt32(0);
}
}
}
}

EXEC Product @p OUTPUT

create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c insensitive cursor for select intcolumn from tbl
open c
fetch next from c into @sales
if @@FETCH_STATUS = 0
set @product = @sales
while @@FETCH_STATUS = 0
begin
fetch next from c into @sales
set @product = @product * @sales
end
close c
deallocate c
end

返回页首

用户定义的类型 (UDT)

[SqlUserDefinedTypeAttribute(Format.Native)]
public struct SimpleUdt: INullable
{
public override string ToString() {...}
public bool IsNull { get; }
public static SimpleUdt Null { get; }
public static SimpleUdt Parse(SqlString s) {...}
...
}
create type simpleudt from [myassembly].[SimpleUdt]
create table t (mycolumn simpleudt)

select imagetype::MyFunction(@arg1)

[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct UmAlQuraDateTime : INullable
{
//Private state
private long dtTicks;
//Calendar object used for all calendar-specific operations
private static readonly UmAlQuraCalendar s_calendar = new UmAlQuraCalendar();
public static UmAlQuraDateTime Null
{
get
{
UmAlQuraDateTime dt = new UmAlQuraDateTime();
dt.isNull = true;
return dt;
}
}
public bool IsNull
{
get
{
return this.isNull;
}
}
//Convert into CLR DateTime type
public DateTime DateTime
{
get { return new DateTime(this.dtTicks); }
}

public static UmAlQuraDateTime Parse(SqlString s)
...
public override string ToString()
...
public static UmAlQuraDateTime ParseUsingFormat(string data, string fmt)
...
public string ToStringUsingFormat(string format)
...
public static UmAlQuraDateTime Now
{
get
{
return new UmAlQuraDateTime(DateTime.Now);
}
}
public static UmAlQuraDateTime FromSqlDateTime(SqlDateTime d)
{
if (d.IsNull) return Null;
return new UmAlQuraDateTime(d.Value);
}

public int Year
{
get
{
return s_calendar.GetYear(this.DateTime);
}
}
public int Month
...
public int Hour
...
public int Minute
...
public int Second
...
public double Milliseconds
...
public long Ticks
...
public SqlDateTime ToSqlDateTime()
{
return new SqlDateTime(this.DateTime);
}

public UmAlQuraDateTime AddYears(int years)
{
return new UmAlQuraDateTime(s_calendar.AddYears(this.DateTime, years));
}
public UmAlQuraDateTime AddDays(int days)
...
public UmAlQuraDateTime AddMonths(int months)
...
public UmAlQuraDateTime AddHours(int hours)
...
public UmAlQuraDateTime AddMinutes(int minutes)
...
public double DiffDays(UmAlQuraDateTime other)
...

-- convert it to arabic (for testing purposes)
declare @h hijridatetime
set @h = hijridatetime::ParseArabicString('01/02/1400')
select @h.ToArabicString(), @h.ToArabicStringUsingFormat('F')
-- convert sql datetime to hijri and back
declare @h hijridatetime
set @h = hijridatetime::FromSqlDateTime(GetDate())
select @h.ToArabicString(), @h.ToSqlDateTime()
-- get the current hijri datetime, in two ways
select   hijridatetime::Now.ToString(),
hijridatetime::FromSqlDateTime(GetDate()).ToString()
-- do some arithmetic:
declare @h hijridatetime, @d datetime
set @h = hijridatetime::Now -- get the current hijri datetime
set @h = @h.AddDays(10) -- add ten days to it
set @d = GetDate() -- current sql date
set @d = DateAdd(day, 10, @d) -- add ten days to the sql datetime
-- print 'em both, should be the same
select @h.ToSqlDateTime(), @d
-- datepart
declare @h hijridatetime
set @h = hijridatetime::Now -- get the current hijri datetime
select @h.Year as year, @h.Month as month, @h.Day as month

返回页首

小结

返回页首

版权