经验讨论-sql语句有关问题

经验讨论--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( ", ");