表单提交，联合查询的问题
一个表单或查询有好多内容，而且有的是必添项有的是选添项，如何动态的判断用户写了那些项，然后进行数据库操作？
[解决办法]
存储过程还是自己组合sql语句？
string sql = "select * from table ";
string sqlwhere = " ";
if(aaa.text!= " ")
sqlwhere += " xxx = ' " + aaa.text + " ' and ";
if(bbb.text!= " ")
sqlwhere += " ccc = ' " + bbb.text + " ' and ";
..
if(sqlwhere!= " ")
//所sqlwhere最后3个字符去掉
sql += " where "+ sqlwhere;
[解决办法]
动态组合语句呀
[解决办法]
declare @V1 int, @V2 int
select * from table where
( @V1 is null or V1 = @V1 ) or
( @V2 is null or V2 = @V2 )
-------------------------------
明白？
[解决办法]
考虑到性能问题，一般使用命令参数，特别是存储过程实现的查询，这样数据库引擎可以根据SQL语句是否变化，而不需要重新编译SQL语句
-- SQL Sample
SELECT Field1, Field2, ...
FROM tbl
WHERE (@Param1 IS NULL OR Field1 = @Param1)
AND (@Param2 IS NULL OR Field2 = @Param1)
OR ... AND ...
实在过于动态，还是拼接 SQL 吧
-- code snippets
string whereClause = "WHERE 1=1 ";
whereClause += TextBox1.Text == " " ? " " : " AND Field1 = " + TextBox1.Text;
whereClause += TextBox2.Text == " " ? " " : " AND Field2 LIKE % " + TextBox2.Text + "% ";
whereClause += CheckBox1.Checked == " " ? " " : " AND Field3 = SomeValue ";
// ....
[解决办法]
In client, you can use javascript or asp.net validation to confine the page.
In server, it 's better to use stored procedure to communicate with database, at least use sql parameter to avoid common sql injection attack.It 's very dangerous to append strings together for the hackers can add some snippet like ';delete from user ' into the sql statement.
Good luck!
[解决办法]
拼接sql
[解决办法]
mark
