经验讨论--sql语句问题
大家在开发 asp.net C# 项目时候，都会有做 数据库 操作这一块的
像通常写 sql 语句都很长，有时候 里面参数也多，类型也不一样的情况下
写的会很长 ，不好维护，也容易出错~ 如:
update admin_db set [password]= ' "+TB_new_password.Text.ToString()+ " ' where [username]= ' "+TB_username.Text.ToString().Trim()+ " ' and [password]= ' "+TB_old_password.Text.ToString()+ " '
那么谁有好的方法可以 改进这个操作呢
[解决办法]
如果不考虑存储过程的话
参数化SQL语句
比如
public const string SQL_SELECT = "SELECT * FROM Orders WHERE orderID = @orderid ";
publi const string PARM_ORDERID = "@orderid ";
[解决办法]
正规商业项目，近乎不拼接 SQL
假如你使用 应用程序内联方式 SQL， 最好使用命令参数， 如 LS
即使，你要拼接的花，我建议你这样改写，可读性好一点，不容易犯 引号不成对匹配的低级错误
string sqlUpdate = "update admin_db set [password]= '{0} ' where [username]= '{1} ' and [password]= '{2} ' ", TB_new_password.Text.ToString(), TB_username.Text.ToString().Trim(), TB_old_password.Text.ToString());
假如你 SQL 拼接起来很长，其频繁的拼接，
那么你应该考虑使用 StringBuilder 提供性能
[解决办法]
OMG, sorry,
string sqlUpdate = "update admin_db set [password]= '{0} ' where [username]= '{1} ' and [password]= '{2} ' ", TB_new_password.Text.ToString(), TB_username.Text.ToString().Trim(), TB_old_password.Text.ToString());
> > >
string sqlUpdate = String.Format( "update admin_db set [password]= '{0} ' where [username]= '{1} ' and [password]= '{2} ' ", TB_new_password.Text.ToString(), TB_username.Text.ToString().Trim(), TB_old_password.Text.ToString());
[解决办法]
我在的企业，都写成存储过程，传递参数
[解决办法]
System.Text.StringBuilder sb_Sql_Insert_CustomerLog = new System.Text.StringBuilder();
sb_Sql_Insert_CustomerLog.Append( "INSERT INTO ");
sb_Sql_Insert_CustomerLog.Append( " b_Customer_logInfo ");
sb_Sql_Insert_CustomerLog.Append( "( ");
sb_Sql_Insert_CustomerLog.Append( "customerID ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "customerlog_Id ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "begin_Date ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "end_Date ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "question_Des ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "acceptance_Date ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "accomplishYN ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "resolvent ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "fee ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "acceptanc_Person ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "create_Reacord_Date ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( "cmemo ");
sb_Sql_Insert_CustomerLog.Append( ") ");
sb_Sql_Insert_CustomerLog.Append( " VALUES ");
sb_Sql_Insert_CustomerLog.Append( "( ");
sb_Sql_Insert_CustomerLog.Append( " ' " + customerID + " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( " ' " + customerlog_Id + " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( " ' " + begin_Date + " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( " ' " + end_Date + " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( " ' " + question_Des+ " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
sb_Sql_Insert_CustomerLog.Append( " ' " + acceptance_Date + " ' ");
sb_Sql_Insert_CustomerLog.Append( ", ");
我以前这样写，想想也忒麻烦了，
[解决办法]
1. 使用参数的方式，使用字符串拼接的方式是非常不正规的
2. 使用存储过程封装SQL语句
