类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
大家可以先看看这个代码，表CodeTier里边的字段id是int型，我要在这个id字段里边用in进行查询，但在我写的方法里边，取出来的字符串s是一个字符串，例如一个可能的结果为"(1,3,2,3,4,-2)",现在我想利用id in s来进行查询，大家帮我看看该怎么办？
namespace AdvQuery
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.CheckBoxList cblTier;
protected System.Web.UI.WebControls.CheckBoxList cblType;
protected System.Web.UI.WebControls.CheckBoxList cblFunc;
protected System.Web.UI.WebControls.CheckBoxList cblOs;
protected System.Web.UI.WebControls.CheckBoxList cblLang;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Button Button2;
protected System.Data.SqlClient.SqlConnection Conn;
protected System.Data.SqlClient.SqlDataAdapter daCategory;
protected System.Data.SqlClient.SqlDataAdapter daCodeLang;
protected System.Data.SqlClient.SqlDataAdapter daCodeType;
protected System.Data.SqlClient.SqlDataAdapter daProduct;
protected System.Data.SqlClient.SqlDataAdapter daCodeOs;
protected System.Data.SqlClient.SqlDataAdapter daCodeFunc;
protected System.Data.SqlClient.SqlDataAdapter daCodeTier;
protected System.Data.DataSet dsData;
protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.TextBox TextBox2;
private string connectionstring ="workstation id=CSOFT-C88C81D3A;packet size=4096;user id=sa;data source=CSOFT-C88C81D3A;persist security info=False;initial catalog=CMS";
private const string PARM_COMP_TIERID = "@TierId";
private void Page_Load(object sender, System.EventArgs e)
{
Conn = new SqlConnection(connectionstring);
daCategory = new SqlDataAdapter("select id,name,Dir from Category",Conn);
daCodeLang = new SqlDataAdapter("select * from CodeLang",Conn);
daCodeType = new SqlDataAdapter("select * from CodeType",Conn);
daCodeOs = new SqlDataAdapter("select * from CodeOs",Conn);
daCodeFunc = new SqlDataAdapter("select * from CodeFunc",Conn);
daCodeTier = new SqlDataAdapter("select * from CodeTier",Conn);
dsData = new DataSet();
//填充数据集
daCategory.Fill(dsData,"Category");
daCodeLang.Fill(dsData,"CodeLang");
daCodeType.Fill(dsData,"CodeType");
daCodeOs.Fill(dsData,"CodeOs");
daCodeFunc.Fill(dsData,"CodeFunc");
daCodeTier.Fill(dsData,"CodeTier");
if(!Page.IsPostBack)
{
// daProduct = new SqlDataAdapter("select id,name from Product where categoryID = 1",Conn);
// daProduct.Fill(dsData,"Product");
// ddlProduct.DataBind();
cblTier.DataBind();
cblType.DataBind();
cblFunc.DataBind();
cblOs.DataBind();
cblLang.DataBind();
}// 在此处放置用户代码以初始化页面
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Button2.Click += new System.EventHandler(this.Button2_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public static string GetQueryCond(CheckBoxList chkbox)
{
string s = "";
for (int i=0; i<chkbox.Items.Count; i++)
{
if (chkbox.Items[i].Selected)
{
s += chkbox.Items[i].Value + ",";
}
}
s="("+s+"-2)";
return ;
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public string GetItemsByAdvQuery(string TierId)
{
string name = "";
System.Text.StringBuilder QueryString = new System.Text.StringBuilder("select CompId,Name,Descn from Item where id in @TierId ");
SqlParameter parm = new SqlParameter(PARM_COMP_TIERID, SqlDbType.BigInt, 8);
parm.Value = TierId;
SqlDataReader rdr = ExecuteReader(connectionstring, CommandType.Text, QueryString.ToString(), parm);
{
while ( rdr.Read())
{
name = rdr.GetString(0);
}
return name;
}
}
private void Button2_Click(object sender, System.EventArgs e)
{
string s= GetQueryCond(cblTier);
this.TextBox1.Text = GetQueryCond(cblTier);
this.TextBox2.Text = GetItemsByAdvQuery("s");
}
网友回答：
where 列 in (1,2,3)
字符串型用where 列 in (1,2,3)，int型用where 列 in (1,2,3)。就象插入一样，字符串型有单引号int型没有。
where in (1,2,3)
将GetQueryCond函数修改成下面那样试试：
public static string GetQueryCond(CheckBoxList chkbox)
{
string s = "(";
for (int i=0; i<chkbox.Items.Count; i++)
{
if (chkbox.Items[i].Selected)
{
s += chkbox.Items[i].Value + ",";
}
}
s="("+s+"-2)";
return ;
}
public static string GetQueryCond(CheckBoxList chkbox)
{
string s = "";
for (int i=0; i<chkbox.Items.Count; i++)
{
if (chkbox.Items[i].Selected)
{
s += "" + chkbox.Items[i].Value + ",";
}
}
s="("+s+"-2)";
return ;
}