多条件查询?怎么参数化?

[javaoraspx]

     private string GetWhere()
        {
            StringBuilder strSql = new StringBuilder();
            if (ddlCOMP_STATUS.SelectedValue != "-1")
            {
                strSql.Append(" and ENABLE_STATE = '" + ddlCOMP_STATUS.SelectedValue + "'");
            }
            if (ddlist_customs.SelectedValue != "-1")
            {
                strSql.Append(" AND CUSTOMS  = '" + ddlist_customs.SelectedValue + "'");
            }
            if (txtCO_CODE.Text.Trim() != "")
            {
                strSql.Append(string.Format(" AND CO_CODE ={0}", txtCO_CODE.Text.Trim()));
            }
            if (txtCO_NAME.Text.Trim() != "")
            {
                strSql.Append(" AND CO_NAME LIKE '%" + txtCO_NAME.Text.Trim() + "%'");
            }
}

这样多条件查询,怎么申明方法?和参数化呢?

  object[] str = list.ToArray();
  SqlParameter[] sp =(SqlParameter[]) str;

问题: 一般你怎么处理这种多条件查询,然后参数化? 不想使用 replace 去替换.
求解????

[winner2050]

我以前的公司用的是，把所有条件都写上。

没有使用的条件用 null
比如
select * form tbperson where (sex=@sex or @sex is null) and (Age>=@Age or @Age is null)
。。。。

[子夜__]

[Quote=引用 1 楼 winner2050 的回复:]

我以前的公司用的是，把所有条件都写上。

没有使用的条件用 null
比如
select * form tbperson where (sex=@sex or @sex is null) and (Age>=@Age or @Age is null)
。。。。
[/Quote]
也是好办法啊。
也可以在存储过程里把拼接条件逻辑写完 直接传参进去

[haiziguo]

可以通过哈希表，一下仅供参考例如
public DataTable List(Hashtable htParam)
{
string sql="select * from tb where (@IsDown='' or IsDown = @IsDown)
and (@FileName='' or IsDown = @FileName)

"
当参数为空的时间即不用判断or后面的语句 如果不为空开始判断or后面的语句
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@IsDown", htParam[IsDown]),
new SqlParameter("@FileName",htParam[FileName])}

return SQLHelper.Query(strSql, parameters);}

[bdmh]

string.Format的第二种重载不就是可以根据参数数组格式化字符串吗

[haiziguo]

汗 还是晚了一步

[javaoraspx]

[Quote=引用 4 楼 bdmh 的回复:]

string.Format的第二种重载不就是可以根据参数数组格式化字符串吗
[/Quote]

关键得 防止 sql 注入?

存储过程写的有点麻烦 呵呵.

[javaoraspx]

[Quote=引用 4 楼 bdmh 的回复:]

string.Format的第二种重载不就是可以根据参数数组格式化字符串吗
[/Quote]

这样吗?
strSql.Append(string.Format(" AND CO_CODE =@co", new SqlParameter("@co", txtCO_CODE.Text.Trim())));

调试 得到 AND CO_CODE =@co ,然后就异常. 哥们,能说详细点吗? 谢谢

[sunll0129]

private string GetWhere()
{
string where="";
StringBuilder strSql = new StringBuilder();
if (ddlCOMP_STATUS.SelectedValue != "-1")
{
where+=" and ENABLE_STATE = @ENABLE_STATE");
}
if (ddlist_customs.SelectedValue != "-1")
{
where+=" AND CUSTOMS = @CUSTOMS");
}
if (txtCO_CODE.Text.Trim() != "")
{
where+=" AND CO_CODE ={0}", @CO_CODE));
}
if (txtCO_NAME.Text.Trim() != "")
{
where+=" AND CO_NAME LIKE %@CO_NAME%");
}

SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@ENABLE_STATE",ddlCOMP_STATUS.SelectedValue),
new SqlParameter("@CUSTOMS",ddlist_customs.SelectedValue),
new SqlParameter("@CO_CODE",txtCO_CODE.Text.Trim()),
new SqlParameter("@CO_NAME",txtCO_NAME.Text.Trim())

};
}

[javaoraspx]

[Quote=引用 8 楼 sunll0129 的回复:]
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@ENABLE_STATE",ddlCOMP_STATUS.SelectedValue),
new SqlParameter("@CUSTOMS",ddlist_customs.SelectedValue),
new SqlParameter("@CO_CODE",txtCO_CODE.Text.Trim()),
new SqlParameter("@CO_NAME",txtCO_NAME.Text.Trim())
[/Quote]

如果if里面的语句没有执行? 参数是不是就多了? 呵呵 ..

[javaoraspx]

自己顶下

[飞猪大飞]

public static DataTable GetSilverExchangeTable(string userid, int tradetype, int Page)
{

int pagesize = 40;
int start = (Page - 1) * pagesize + 1;
int end = Page * pagesize;
// string sql = "SELECT TOP " + pagesize + " * FROM Shop20_SilverExchange_" + userid.Substring(userid.Length - 1) + " WHERE id > (select max (id) from(select top " + end + " id from Shop20_SilverExchange_" + userid.Substring(userid.Length - 1) + " where userid=@userid and tradetype=@tradetype order by id) as T ) order by id";
string sql = "SELECT TOP " + pagesize + " * FROM Shop20_SilverExchange_" + userid.Substring(userid.Length - 1) + " WHERE id NOT IN(SELECT TOP " + pagesize * (Page - 1) + " id FROM Shop20_SilverExchange_" + userid.Substring(userid.Length - 1) + " ORDER BY id ) and userid=@userid and tradetype=@tradetype ORDER BY id";
DataLayer.DBCParmeterList plist = new DataLayer.DBCParmeterList();
plist.Add("@userid", userid);
plist.Add("@tradetype", tradetype);
DataTable dt = DataLayer.DB.DBCParmaeterExecuteDataSet(sql, plist, DataLayer.DB.Bank).Tables[0];
return dt;
}


这是我才做得， 新鲜的 代码 ，

[hebaobao19880921]

给你一个例子

    public DataSet FN_SerchByDateAndType(NRModel.File model, string createdate, string endate)
        {
            string strSql = "select * from t_File where 1 =1";
            string strWhere = "";
            if (!string.IsNullOrEmpty(model.FileNam))
            {
                strWhere += " and FileNam like @FileNam";
            }
            //if (!string.IsNullOrEmpty(model.Decription)k)
            //{
            //    strWhere += " and Decription like @Decription";
            //}
            if (!string.IsNullOrEmpty(createdate) || !string.IsNullOrEmpty(endate))
            {
                strWhere += " and CreateOn between @createdate and @endate order by CreateOn desc";
            }
            strSql += strWhere;
            SqlParameter[] parameters = {
                new SqlParameter("@FileNam",SqlDbType.NVarChar,256),
                new SqlParameter("@createdate",SqlDbType.NVarChar),
                new SqlParameter("@endate",SqlDbType.NVarChar)
            };
            parameters[0].Value = "%" + model.FileNam + "%";
            //parameters[1].Value = "%" + model.Decription + "%";
            parameters[1].Value = createdate;
            parameters[2].Value = endate;
            return DbHelperSQL.Query(strSql, parameters);
            //SqlParameter[] parameters = new SqlParameter[4];
            //parameters[0] = new SqlParameter("@FileNam", model.FileNam);
            //parameters[1] = new SqlParameter("@stardate", createdate);
            //parameters[2] = new SqlParameter("@enddate", endate);
            ////执行存储过程
            //return DbHelperSQL.RunProcedure("P_UserSerch", parameters, "t_File");
        }

[javaoraspx]

再顶下.

[hebaobao19880921]

我倒 上面写那么清楚了 楼主

[javaoraspx]

自己搞定了..呵呵
先申明全局变量
1.

     List<SqlParameter> list = new List<SqlParameter>();

2.

        if (txtCO_CODE.Text.Trim() != "")
        {
            strSql.Append(" AND CO_CODE = @code ");
            SqlParameter p1 = new SqlParameter("@code", txtCO_CODE.Text.Trim());
            list.Add(p1);
        }

3.

   SqlParameter[] str = list.ToArray();

[小D2013]

string sql=string.Empty;
string strWhere=string.Empty;

if(!string.IsNullOrEmpty(this.TextBox1.Text))
{
if(!string.IsNullOrEmpty(strWhere))
sql += " and ";
sql += string.Format( "where id={0}",this.TextBox1.Text)
}
if(!string.IsNullOrEmpty(this.TextBox2.Text))
{
if(!string.IsNullOrEmpty(strWhere))
sql += " and ";
sql += string.Format( "where id={0}",this.TextBox2.Text)
}

sql = "select * from yourtable " + sql;

[javaoraspx]

我都要结贴了 看到你的回复, 我又来看看 呵呵,,问题搞定了 结贴!!!