SQL ServerÖÐʹÓÃCLRµ÷ÓÃ.NET·½·¨
½éÉÜ
ÎÒÃÇÒ»ÆðÀ´×ö¸öʾÀý£¬ÔÚ.NETÖÐн¨Ò»¸öÀ࣬²¢ÔÚÕâ¸öÀàÀïн¨Ò»¸ö·½·¨£¬È»ºóÔÚSQL ServerÖе÷ÓÃÕâ¸ö·½·¨¡£°´ÕÕ΢ÈíËùÊö£¬Í¨¹ýËÞÖ÷ Microsoft .NET Framework 2.0 ¹«¹²ÓïÑÔÔËÐпâ (CLR)£¬SQL Server 2005ÏÔÖøµØÔöÇ¿ÁËÊý¾Ý¿â±à³ÌÄ£ÐÍ¡£ ÕâʹµÃ¿ª·¢ÈËÔ±¿ÉÒÔÓÃÈκÎCLRÓïÑÔ£¨ÈçC#¡¢VB.NET»òC++µÈ£©À´Ð´´æ´¢¹ý³Ì¡¢´¥·¢Æ÷ºÍÓû§×Ô¶¨Ò庯Êý¡£
ÎÒÃÇÈçºÎʵÏÖÕâЩ¹¦ÄÜÄØ£¿
ΪÁËʹÓÃCLR£¬ÎÒÃÇÐèÒª×öÈçϼ¸²½£º
1¡¢ÔÚ.NETÖÐн¨Ò»¸öÀ࣬²¢ÔÚÕâ¸öÀàÀïн¨Ò»¸öpublic·½·¨¡£
2¡¢±àÒëÕâ¸öÀàΪһ¸öDLL¡£
3¡¢ÔÚSQL ServerÖÐ×¢²áÕâ¸öDLL¡£
4¡¢Ð½¨Ò»¸öSQL Serverº¯ÊýÀ´·ÃÎÊÖ¸¶¨µÄ.NET·½·¨¡£
½ÓÏÂÀ´£¬ÎÒÃÇÒ»ÆðÀ´Íê³ÉÒ»¸öʾÀý
Ê×ÏÈ£¬ÔÚVisual StudioÖÐн¨Ò»¸öÃûΪ“SQLServerCLRTest”µÄÀà¿âÏîÄ¿¡£ È»ºó£¬Ð½¨Ò»¸öÃûΪ“CLRFunctions”µÄÀ࣬²¢ÔÚÆäÄÚÌí¼ÓÒ»¸öÃûΪ“HelloWold”µÄ·½·¨£¬´úÂëÈçÏ£º
public class CLRFunctions
{
public static string HelloWorld(string Name)
{
return ("Hello " + Name);
}
}
ÕâÊÇÒ»¸ö·Ç³£¼òµ¥µÄ·½·¨£¨ÎªÁËÈÃSQL Server¿ÉÒÔµ÷ÓÃËü£¬Ëü±ØÐëÒªÊÇpublicºÍstaticµÄ£©£¬Õâ¸ö·½·¨ÓÐÒ»¸östringÀàÐ͵IJÎÊý£¬·µ»ØÐÅϢΪ“Hello”¼ÓÉÏÄã´«ÈëµÄ²ÎÊý¡£
ÏÖÔÚ£¬ÎÒÃÇÐèÒª±àÒëÕâ¸öÏîĿΪһ¸öDLL£¬²¢ÔÚSQL ServerÖÐ×¢²áËü¡£ ÕâÒ²ÊDZȽϼòµ¥µÄ£¬ÔÚVSÖÐÓÒ¼üµ¥»÷ÏîÄ¿£¬Ñ¡Ôñ“Éú³É”ºó³ÌÐò¾Í»áÉú³ÉÒ»¸öDLL¡£ Èç¹ûÄãµÄÏîÄ¿Êǵ÷ÊÔģʽµÄ»°£¬ÄÇô¾Í¿ÉÒÔÔÚÈçÏÂËùʾÄÇÑùµÄ·¾¶ÀïÕÒµ½±àÒëºÃµÄDLL¡£
C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\SQLServerCLRTest\SQLServerCLRTest\bin\Debug\SQLServerCLRTest.dll
ÕÒµ½Õâ¸öDLLºó£¬ÎÒÃǾͿÉÒÔ°ÑËü¿½±´µ½ÎÒÃǵÄSQL Server»úÆ÷ÉÏÁË£¬Èç¹ûÊÇÏàͬ»úÆ÷µÄ»°ÎÒÃÇÖ»Òª¼ÇסÕâ¸ö·¾¶¼´¿É¡£
ÆôÓÃCLR¹¦ÄÜ
ĬÈÏÇé¿öÏ£¬SQL ServerÖеÄCLRÊǹرյģ¬ËùÒÔÎÒÃÇÐèÒªÖ´ÐÐÈçÏÂÃüÁî´ò¿ªCLR£º
exec sp_configure 'clr enabled',1
reconfigure
go
×¢²áDLL
ΪÁ˵÷ÓÃÎÒÃÇдµÄÄǸö·½·¨£¬ÐèÒªÔÚSQL ServerÖÐ×¢²áÎÒÃǸոձàÒëºÃµÄÄǸöDLL¡£ ÎÒÃÇ¿ÉÒÔÔÚÊý¾Ý¿âÖÐʹÓÃÈçÏÂÃüÁîÀ´×¢²áDLL£¨Â·¾¶ÎªÄãµÄDLLÎļþµÄ·¾¶£©
CREATE ASSEMBLY asmHelloWorld from 'C:\SQLServerCLRTest.dll'
ÔÚSQL ServerÖе÷ÓÃÎÒÃǵÄ.NET·½·¨
ΪÁ˵÷ÓÃ.NET·½·¨£¬ÎÒÃÇ¿ÉÒÔдһ¸öSQL Server×Ô¶¨Ò庯Êý£¬²¢ÔÚÆäÄÚʹÓÃ&ld
Ïà¹ØÎĵµ£º
ÔÚÈí¼þ¿ª·¢ÖУ¬³£³£ÐèҪΪ³ÌÐò½¨Á¢Sql ServerÊý¾Ý¿âµÄÔËÐл·¾³¡£Íê³ÉÈçÔÚSQL ServerÊý¾Ý¿âÖн¨Á¢É豸£¬½¨Á¢Êý¾Ý¿â£¬½¨Á¢±í¸ñ£¬·ÖÅäȨÏ޵ȹ¦ÄÜ£¬ÈçºÎ·½±ãµÄ½¨Á¢Ó¦ÓóÌÐòËùÐèSql Server»·¾³µÄÊý¾Ý¿â»·¾³£¬¶ø²»ÓÃÆô¶¯SQL Enterprise ManagerÄØ£¿
ÏÂÃæÀ´¿´£º
¡¡¡¡Æô¶¯VB6.0£¬Ð½¨Ò»¸ö¹¤ ......
·ÀSQL×¢È뺯Êý
³ÌÐò´úÂ룺
º¯Êý²¿·Ö========================================================================
'------------------------------------------------
'ÓÃ;:¼ì²éÊÇ·ñΪÊý×Ö,ÒÔ¼°Êý×ÖÊÇ·ñ³¬³ö·¶Î§
'ÊäÈë:¼ì²é×Ö·û,´«Öµ·½Ê½(0Ö±½Ó´«,1È¡Form,2È¡QueryString,3È¡cookies,4Ö±½ÓReqeust),¿ªÊ¼Êý×Ö(ĬÈÏÊý× ......
½â¾öSQL Injection©¶´µÄÒ»¸öº¯Êý
http://blog.csdn.net/cncco/archive/2006/04/07/654254.aspx
º¯Êý
<%
Function CheckInput(str,strType)
'º¯Êý¹¦ÄÜ£º¹ýÂË×Ö·û²ÎÊýÖеĵ¥ÒýºÅ£¬¶ÔÓÚÊý×Ö²ÎÊý½øÐÐÅжϣ¬Èç¹û²»ÊÇÊýÖµÀàÐÍ£¬Ôò¸³Öµ0
'²ÎÊýÒâÒ壺 str ......
/*»ñÈ¡ÖØ¸´¼Ç¼ÖнÏСµÄÄǸöID*/ create table tmp_Repeat as select min(id) as id from poi group by idcode having count(*) >1; /*±¸·Ýɾ³ýµÄÊý¾Ý*/ select * from poi where id in (select id from tmp_repeat) /*ɾ³ýÖØ¸´¼Ç¼ÖÐID½ÏСµÄÄÇÌõ
select replace('°¢¹ðÊǸöºÃº¢×Ó','°¢¹ð','СÏÍ') from du ......
MS SQLÈëÃÅ»ù´¡:Ë÷ÒýÊÓͼ
ÊÕ²Ø
¡¡
¡¡ÔÚÇ°ÃæÎÒÃÇÒѾÌáµ½¹ýÓйØÊÓͼµÄÏÂÊö¹Ûµã£ºÓÉÓÚÊÓͼ·µ»ØµÄ½á¹û¼¯Óë¾ßÓÐÐÐÁнṹµÄ±íÓÐ×ÅÏàͬµÄ±í¸ñÐÎʽ£¬²¢ÇÒÎÒÃÇ¿ÉÒÔÔÚSQL
Óï¾äÖÐÏñÒýÓñíÄÇÑùÒýÓÃÊÓͼ£¬ËùÒÔÎÒÃdz£°ÑÊÓͼ³ÆÎªÐé±í¡£±ê×¼ÊÓͼµÄ½á¹û¼¯²¢²»ÒÔ ......