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

shoumangli 2008-04-28 02:42:04
我从文本框里读出多个数据,我要在数据库里面查询是否符合多个数据条件的数据并显示
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>");
}
}

}
...全文
1487 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
whynot2012 2011-04-24
  • 打赏
  • 举报
回复
都是高手啊
wellst 2010-04-24
  • 打赏
  • 举报
回复
2楼 第三种方法 是不是数据多了不好用呢?
tqqlyl 2009-09-14
  • 打赏
  • 举报
回复
您好,问题解决了吗
andylauhai 2008-04-29
  • 打赏
  • 举报
回复
方法总比问题多
shoumangli 2008-04-28
  • 打赏
  • 举报
回复
谢谢各位,准备结贴了
yunfeng007 2008-04-28
  • 打赏
  • 举报
回复
如何使用?
/// <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>
yunfeng007 2008-04-28
  • 打赏
  • 举报
回复
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 2008-04-28
  • 打赏
  • 举报
回复
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 2008-04-28
  • 打赏
  • 举报
回复
多条件查询组合类
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
*/
}
}
makel 2008-04-28
  • 打赏
  • 举报
回复
再简单不过的一个问题了,直接根据输入值用字符串组合成一个where条件,组合完后再一次送进sqlserver里去查一把就可以了,象你这样有几个条件值就查几次,肯定慢了。。。
kakajya 2008-04-28
  • 打赏
  • 举报
回复
写 查询的sql语句用stringBuilder的对象,别用string
也能在一定程度上提高性能。
kakajya 2008-04-28
  • 打赏
  • 举报
回复
多条件查询?
那就要涉及到算法的问题了。
wang520d 2008-04-28
  • 打赏
  • 举报
回复
你可以写个方法先拼接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保存
}
shoumangli 2008-04-28
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 zccmy22 的回复:]
你的是效率慢.还是报错啊.感觉你写的语句有问题.
[/Quote]
shoumangli 2008-04-28
  • 打赏
  • 举报
回复
没有报错的,我感觉如果条件一多,我这个东西肯定是不行的
zhenglimv 2008-04-28
  • 打赏
  • 举报
回复
力争成为中国最大的架构师群联盟,架构师1群:43545919已满,2群:59048426的招募也近尾声,现3群:17303696正式开放,各群内容基本相同,高手也经过群主进行了调整,希望大家不必重复加入!培训经过筛选才会进行,请大家不必着急!!!

已经上传的顶级软件产品的架构分析,本群资料仅供研究学习,不得商用!!!
google 、
eBay、
Youtube、
淘宝等
......
技术文章包括:
《自己动手写操作系统》
《搜索引擎-原理、技术与系统》
《企业应用架构模式》
......
重要的RUP实例
设计模式精解
......
资料陆续上传中
conan304 2008-04-28
  • 打赏
  • 举报
回复
看看邹老大写的:
--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)
zccmy22 2008-04-28
  • 打赏
  • 举报
回复
你的是效率慢.还是报错啊.感觉你写的语句有问题.

111,094

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧