±ÜÃâSQL×¢ÈëºÍÌØÊâ×Ö·ûµÄÒ»ÖÖ·½·¨
µÝ¹éС̸×Ô±¸C#¸¨Öúº¯Êý
ʮ08
±ÜÃâSQL×¢ÈëºÍÌØÊâ×Ö·ûµÄÒ»ÖÖ·½·¨
C#Add comments
±ÜÃâSQL×¢ÈëºÍÌØÊâ×Ö·ûµÄ°ì·¨Óкܶ࣬²»Í¬Êý¾Ý¿âÒ²Óв»Í¬Êý¾Ý¿âµÄ½â¾ö·½°¸£¬ADO.NETÖÐʹÓÃDbCommand.Parameters½â¾öÕâ¸öÎÊÌ⣬ΪÁËÁ˽âËûµÄÔÀí£¬ÎÒ²éÁËÒ»ÏÂ.NETÖÐSQLCommandµÄÔ´´úÂëºÍMySQL.NETÖÐMySQLCommandµÄÔ´´úÂë¡£
.NETÔ´´úÂ볬¼¶ÄѸú×Ù£¬Ã²ËÆÊÇÓÃÁËSQL Server¶ÀÓеÄÌØÐÔ£¬°Ñ²ÎÊýµÄÃû³Æ¡¢ÀàÐÍ¡¢°ÑÖµµÄ¶þ½øÖƶ¼´«¸øÁËSQL Server API£¬¾ßÌåûÓиúµ½¡£
MySQLʵÏ־ͺܼòµ¥ÁË£¬Ëû»á°Ñ×Ö·û´®ÀàÐ͵IJÎÊývalueʹÓÃÒ»¸ö¹ýÂËÊý×éÀ´¹ýÂË£¬ÔÚ¹ýÂË×Ö·ûÇ°Ãæ¼ÓÉÏ“\”£¬ÎÒÈÏΪÕâÊÇÒ»ÖÖͨÓõÄ×ö·¨£¬ËäȻЧÂÊÉϲ»ÈçSQLCommandºÃ£¬µ«ÊǺÍÈÝÒ×ÒÆÖ²¸üÈÝÒ׿´¶®£¡
ʹÓÃPHP+MySQL×öÍøÕ¾µÄͬѧҲ¿ÉÒÔÀûÓÃ×ÅÀàËƵÄ˼ÏëÉú³É°²È«µÄSQLÁ¬½Ó´®£¬ÎÒ¿´ÁËPHPWindÊý¾Ý¿â´¦Àíº¯Êý£¬Ã²ËƲ¢Ã»ÓжÔËùÓеĹýÂË×Ö·û½øÐд¦Àí£¬Õâ¾Í´æÔÚ×ÅһЩ°²È«Òþ»¼¡£
Ô´´úÂë
private static string stringOfBackslashChars = "\u005c\u00a5\u0160\u20a9\u2216\ufe68\uff3c";
private static string stringOfQuoteChars =
"\u0027\u00b4\u02b9\u02ba\u02bb\u02bc\u02c8\u02ca\u02cb\u02d9\u0300\u0301\u2018\u2019\u201a\u2032\u2035\u275b\u275c\uff07";
/// <summary>
/// Escapes the string.
/// </summary>
/// <param name="value" />The string to escape</param>
/// <returns>The string with all quotes escaped.</returns>
public static string EscapeString(string value)
{
StringBuilder sb = new StringBuilder();
foreach (char c in value)
{
if (stringOfQuoteChars.IndexOf(c) >= 0 ||
stringOfBackslashChars.IndexOf(c) >= 0)
sb.Append("\\");
sb.Append(c);
}
return sb.ToString();
}
ÈçºÎÔÚVS2008ÖпªÆô.NETÔ´´úÂëµ÷ÊÔ
¹¤¾ß->Ñ¡Ïî->
µ÷ÊÔ->ÆôÓÃÔ´·þÎñÆ÷Ö§³Ö
·ûºÅ->Ìí¼ÓPDBλÖÃhttp://referencesource.microsoft.com/symbols
±¾µØ»º´æĿ¼
Ïà¹ØÎĵµ£º
1 :ÆÕͨSQLÓï¾ä¿ÉÒÔÓÃexecÖ´ÐÐ
Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- Çë×¢Òâ×Ö·û´®Ç°Ò»¶¨Òª¼ÓN
2:×Ö¶ÎÃû£¬±íÃû£¬Êý¾Ý¿âÃûÖ®Àà×÷Ϊ±äÁ¿Ê±£¬±ØÐëÓö¯Ì¬SQL
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname fr ......
ÈÕÆÚÀàÐ͸ÅÊö
SQL ServerÖеÄÈÕÆÚÀàÐÍ°üÀ¨datetimeºÍsmalldatetime£¬½öÄÜ´¦Àí¿ÉÒÔʶ±ðΪ1753Äê¡«9999Äê¼äµÄÈÕÆÚµÄÖµ£¬Ã»Óе¥¶ÀµÄÈÕÆÚÐÍ»òʱ¼äÐÍ¡£
1£®datetime
datetimeÀàÐÍ´¦Àí´Ó1753Äê1ÔÂ1ÈÕ¡«9999Äê12ÔÂ31ÈÕµÄÈÕÆÚºÍʱ¼äÊý¾Ý£¬¾«È·¶ÈΪ°Ù·ÖÖ®ÈýÃë¡£
¼´£º¶ÔÓÚ0.000¡«0.001¡¢0.009µÄÈÕÆÚÖµ£¬µ÷ÕûΪ0.000£»¶ÔÓÚ ......
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûÇ°£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï ......
SQLº¯ÊýÖ®ËÄÉáÎåÈ루ת×Ôhttp://ln1058.javaeye.com/blog/191502£©
ÎÊÌâ1£º
SELECT CAST('123.456' as decimal) ½«»áµÃµ½ 123£¨Ð¡ÊýµãºóÃæµÄ½«»á±»Ê¡ÂÔµô£©¡£
Èç¹ûÏ£ÍûµÃµ½Ð¡ÊýµãºóÃæµÄÁ½Î»¡£
ÔòÐèÒª°ÑÉÏÃæµÄ¸ÄΪ
SELECT CAST('123.456' as decimal(38, 2)) ===>123.46
×Ô¶¯ËÄÉáÎåÈëÁË£ ......
SQLÓï¾äµ¼Èëµ¼³ö´óÈ«
/******* µ¼³öµ½excel
EXEC master..xp_cmdshell ¡¯bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""¡¯
/*********** µ¼ÈëExcel
SELECT *
from OpenDataSource( ¡¯Microsoft.Jet.OLEDB.4.0¡¯,
¡¯Data Source="c:\test.xls ......