多条件查询数据库并显示（c#与ASP.NET）

[shoumangli]

我从文本框里读出多个数据,我要在数据库里面查询是否符合多个数据条件的数据并显示
string loginName = TextBoxLoginName.Text;
string。。。
string。。。。。
。。。
string Title = TextBoxTitle.Text;

这多个条件中随便满足多少条件多可以(即，一个，二个....或全部满足)；
我已经实现两个条件的查询并显示了，但是条件一多的话，那我那个代码效率是非常低的，请高手指点我该怎么改进我的代码。谢谢

我写的代码如下：

string loginName = TextBoxLoginName.Text;
string Title = TextBoxTitle.Text;

if (TextBoxTitle.Text == "" && TextBoxLoginName.Text == "")
{
Response.Write("<Script Language=JavaScript>alert('请输入查询条件！');</Script>");
}

else
{
try
{
//连接数据库
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=SERVER;Database=mydata;User Id=sa;Pwd=";

conn.Open();

if (TextBoxTitle.Text != "" && TextBoxLoginName.Text != "")
{

string strSql = "select * from [Topic] where [UserLoginName] =" + SqlStringFormat.GetQuotedString(loginName) + "and Title = " + SqlStringFormat.GetQuotedString(Title);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");

GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();

}

else
{
if (TextBoxTitle.Text != "")
{

string strSql = "select * from [Topic] where Title = " + SqlStringFormat.GetQuotedString(Title);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");

GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();
}

if (TextBoxLoginName.Text != "")
{
string strSql = "select * from [Topic] where [UserLoginName] =" + SqlStringFormat.GetQuotedString(loginName);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");


GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();
}
}
}

catch
{
Response.Write("<FONT STYLE='Z-INDEX:100;LEFT:80;POSITION:absolute;TOP:60;'>该用户或该文章不存在</FONT>");
}
}

}

[zccmy22]

你的是效率慢．还是报错啊．感觉你写的语句有问题．

[conan304]

看看邹老大写的:

--1. 使用 EXEC 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 varchar(10)=null,
@para3 varchar(10)=null,
@para4 varchar(10)=null
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
IF @para1 IS NOT NULL
	SET @sql=@sql+' AND col1='''+@para1+''''
IF @para2 IS NOT NULL
	SET @sql=@sql+' AND col2='''+@para2+''''
IF @para3 IS NOT NULL
	SET @sql=@sql+' AND col3='''+@para3+''''
IF @para4 IS NOT NULL
	SET @sql=@sql+' AND col4='''+@para4+''''
EXEC(@sql)
GO


/*======================================================*/

--2. 使用 sp_executesql 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 datetime=null,
@para3 varchar(10)=null,
@para4 int=null
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
	+CASE WHEN @para1 IS NULL THEN '' ELSE ' AND col1=@para1' END
	+CASE WHEN @para2 IS NULL THEN '' ELSE ' AND col2=@para2' END
	+CASE WHEN @para3 IS NULL THEN '' ELSE ' AND col3=@para3' END
	+CASE WHEN @para4 IS NULL THEN '' ELSE ' AND col4=@para4' END
EXEC sp_executesql @sql,N'
	@para1 varchar(10)=null,
	@para2 datetime=null,
	@para3 varchar(10)=null,
	@para4 int=null
',@para1,@para2,@para3,@para4
GO


/*======================================================*/

--3. 不使用动态 Transact-SQL 语句实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 datetime=null,
@para3 varchar(10)=null,
@para4 int=null
AS
SET NOCOUNT ON
SELECT * FROM tbname 
WHERE (@para1 IS NULL OR col1=@para1)
	AND (@para2 IS NULL OR col2=@para2)
	AND (@para3 IS NULL OR col3=@para3)
	AND (@para4 IS NULL OR col4=@para4)

[zhenglimv]

力争成为中国最大的架构师群联盟，架构师1群：43545919已满，2群：59048426的招募也近尾声，现3群：17303696正式开放，各群内容基本相同，高手也经过群主进行了调整，希望大家不必重复加入！培训经过筛选才会进行，请大家不必着急！！！

已经上传的顶级软件产品的架构分析,本群资料仅供研究学习，不得商用！！！
google 、
eBay、
Youtube、
淘宝等
......
技术文章包括：
《自己动手写操作系统》
《搜索引擎-原理、技术与系统》
《企业应用架构模式》
......
重要的RUP实例
设计模式精解
......
资料陆续上传中

[shoumangli]

没有报错的，我感觉如果条件一多，我这个东西肯定是不行的

[shoumangli]

[Quote=引用 1 楼 zccmy22 的回复:]
你的是效率慢．还是报错啊．感觉你写的语句有问题．
[/Quote]

[wang520d]

你可以写个方法先拼接SQL条件如：

/// <summary>
    /// 获取DG显示控件的数据集SQL字符串
    /// </summary>
    /// <returns></returns>
    ///  
    private void BuildSearch()
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(" where 1 = 1  ");
        //类型
        if (!String.IsNullOrEmpty(CatchType))
        {
            sql.Append(" and Type=" + CatchType + "");
        }

        //状态
        if (!String.IsNullOrEmpty(Status))
        {
            sql.Append(" and status=" + Status + "");
        }
       
        this.setFormParm("sqlWhere", sql.ToString());//用VIEWSTATE保存
    }

[kakajya]

多条件查询？
那就要涉及到算法的问题了。

[kakajya]

写 查询的sql语句用stringBuilder的对象，别用string
也能在一定程度上提高性能。

[makel]

再简单不过的一个问题了，直接根据输入值用字符串组合成一个where条件，组合完后再一次送进sqlserver里去查一把就可以了，象你这样有几个条件值就查几次，肯定慢了。。。

[yunfeng007]

多条件查询组合类
1、

using System;
using System.Collections.Generic;
using System.Text;

namespace DBUtility
{
    public enum SqlOperator
    {
         /// <summary>
        /// Like 模糊查询
        /// </summary>
        Like,

        /// <summary>
        /// ＝ is equal to 等于号 
        /// </summary>
        Equal,

        /// <summary>
        /// <> (≠) is not equal to 不等于号
        /// </summary>
        NotEqual,

        /// <summary>
        /// ＞ is more than 大于号
        /// </summary>
        MoreThan,

        /// <summary>
        /// ＜ is less than 小于号 
        /// </summary>
        LessThan,

        /// <summary>
        /// ≥ is more than or equal to 大于或等于号 
        /// </summary>
        MoreThanOrEqual,

        /// <summary>
        /// ≤ is less than or equal to 小于或等于号
        /// </summary>
        LessThanOrEqual,

        /*
        不支持下面两个符号
        
        /// <summary>
        /// 在某个值的中间，拆成两个符号 >= 和 <=
        /// </summary>
        Between,

        /// <summary>
        /// 在某个字符串值中
        /// </summary>
        In
        */
    }
}

[yunfeng007]

2、

using System;
using System.Collections.Generic;
using System.Text;

namespace DBUtility
{
    /// <summary>
    /// 查询信息实体类
    /// </summary>
    public class SearchInfo
    {
        public SearchInfo() {}

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="fieldName">字段名称</param>
        /// <param name="fieldValue">字段的值</param>
        /// <param name="sqlOperator">字段的Sql操作符号</param>
        public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator)
            : this(fieldName, fieldValue, sqlOperator, false)
        { }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="fieldName">字段名称</param>
        /// <param name="fieldValue">字段的值</param>
        /// <param name="sqlOperator">字段的Sql操作符号</param>
        /// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
        public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty) 
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
            this.sqlOperator = sqlOperator; 
            this.excludeIfEmpty = excludeIfEmpty;
        }

        private string fieldName;
        private object fieldValue;
        private SqlOperator sqlOperator;
        private bool excludeIfEmpty = false;


        /// <summary>
        /// 字段名称
        /// </summary>
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }

        /// <summary>
        /// 字段的值
        /// </summary>
        public object FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }

        /// <summary>
        /// 字段的Sql操作符号
        /// </summary>
        public SqlOperator SqlOperator
        {
            get { return sqlOperator; }
            set { sqlOperator = value; }
        }

        /// <summary>
        /// 如果字段为空或者Null则不作为查询条件
        /// </summary>
        public bool ExcludeIfEmpty
        {
            get { return excludeIfEmpty; }
            set { excludeIfEmpty = value; }
        }
    }
}

[yunfeng007]

3、

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;

namespace DBUtility
{
    public class SearchCondition
    {
        private Hashtable conditionTable = new Hashtable();
        public Hashtable ConditionTable
        {
            get { return this.conditionTable; }
        }
        /// <summary>
        /// 为查询添加条件
        /// <example>
        /// 用法一：
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);
        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// 
        /// 用法二：AddCondition函数可以串起来添加多个条件
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// </example>
        /// </summary>
        /// <param name="fielName">字段名称</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="sqlOperator">SqlOperator枚举类型</param>
        /// <returns>增加条件后的Hashtable</returns>
        public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator)
        {
            this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue, sqlOperator));
            return this;
        }

        /// <summary>
        /// 为查询添加条件
        /// <example>
        /// 用法一：
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false);
        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// 
        /// 用法二：AddCondition函数可以串起来添加多个条件
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false).AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// </example>
        /// </summary>
        /// <param name="fielName">字段名称</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="sqlOperator">SqlOperator枚举类型</param>
        /// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
        /// <returns></returns>
        public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty)
        {
            this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue, sqlOperator, excludeIfEmpty));
            return this;
        }

        /// <summary>
        /// 根据对象构造相关的条件语句（不使用参数），如返回的语句是:
        /// <![CDATA[
        /// Where (1=1)  AND Test4  <  'Value4' AND Test6  >=  'Value6' AND Test7  <=  'value7' AND Test  <>  '1' AND Test5  >  'Value5' AND Test2  Like  '%Value2%' AND Test3  =  'Value3'
        /// ]]>
        /// </summary>
        /// <returns></returns> 
        public string BuildConditionSql()
        {
            string sql = " Where (1=1) ";
            string fieldName = string.Empty;
            SearchInfo searchInfo = null;

            StringBuilder sb = new StringBuilder();
            foreach (DictionaryEntry de in this.conditionTable)
            {
                searchInfo = (SearchInfo)de.Value;

                //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
                if (searchInfo.ExcludeIfEmpty && string.IsNullOrEmpty((string)searchInfo.FieldValue))
                {
                    continue;
                }

                if (searchInfo.SqlOperator == SqlOperator.Like)
                {
                    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                }
                else
                {
                    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                        this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                }
            }

            sql += sb.ToString();

            return sql;
        }

        #region 辅助函数

        /// <summary>
        /// 转换枚举类型为对应的Sql语句操作符号
        /// </summary>
        /// <param name="sqlOperator">SqlOperator枚举对象</param>
        /// <returns><![CDATA[对应的Sql语句操作符号（如 ">" "<>" ">=")]]></returns>
        private string ConvertSqlOperator(SqlOperator sqlOperator)
        {
            string stringOperator = " = ";
            switch (sqlOperator)
            {
                case SqlOperator.Equal:
                    stringOperator = " = ";
                    break;
                case SqlOperator.LessThan:
                    stringOperator = " < ";
                    break;
                case SqlOperator.LessThanOrEqual:
                    stringOperator = " <= ";
                    break;
                case SqlOperator.Like:
                    stringOperator = " Like ";
                    break;
                case SqlOperator.MoreThan:
                    stringOperator = " > ";
                    break;
                case SqlOperator.MoreThanOrEqual:
                    stringOperator = " >= ";
                    break;
                case SqlOperator.NotEqual:
                    stringOperator = " <> ";
                    break;
                default:
                    break;
            }

            return stringOperator;
        }

        /// <summary>
        /// 根据传入对象的值类型获取其对应的DbType类型
        /// </summary>
        /// <param name="fieldValue">对象的值</param>
        /// <returns>DbType类型</returns>
        private DbType GetFieldDbType(object fieldValue)
        {
            DbType type = DbType.String;

            switch (fieldValue.GetType().ToString())
            {
                case "System.Int16":
                    type = DbType.Int16;
                    break;
                case "System.UInt16":
                    type = DbType.UInt16;
                    break;
                case "System.Single":
                    type = DbType.Single;
                    break;
                case "System.UInt32":
                    type = DbType.UInt32;
                    break;
                case "System.Int32":
                    type = DbType.Int32;
                    break;
                case "System.UInt64":
                    type = DbType.UInt64;
                    break;
                case "System.Int64":
                    type = DbType.Int64;
                    break;
                case "System.String":
                    type = DbType.String;
                    break;
                case "System.Double":
                    type = DbType.Double;
                    break;
                case "System.Decimal":
                    type = DbType.Decimal;
                    break;
                case "System.Byte":
                    type = DbType.Byte;
                    break;
                case "System.Boolean":
                    type = DbType.Boolean;
                    break;
                case "System.DateTime":
                    type = DbType.DateTime;
                    break;
                case "System.Guid":
                    type = DbType.Guid;
                    break;
                default:
                    break;
            }
            return type;
        }
        #endregion
    }
}

[yunfeng007]

如何使用？

/// <summary>
        /// 为查询添加条件
        /// <example>
        /// 用法一：
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);
        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// 
        /// 用法二：AddCondition函数可以串起来添加多个条件
        /// SearchCondition searchObj = new SearchCondition();
        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);
        /// string conditionSql = searchObj.BuildConditionSql();
        /// </example>
        /// </summary>
        /// <param name="fielName">字段名称</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="sqlOperator">SqlOperator枚举类型</param>
        /// <returns>增加条件后的Hashtable</returns>

[shoumangli]

谢谢各位，准备结贴了

[andylauhai]

方法总比问题多

[tqqlyl]

您好，问题解决了吗

[wellst]

2楼 第三种方法 是不是数据多了不好用呢？

[whynot2012]

都是高手啊