如何使用变量给数据源控件参数赋值、如何动态增减数据源控件参数并支持分页
一、如何使用变量给数据源控件参数赋值
在VS2005中数据源控件的参数类型有六种：
方法如下：
1、在前台代码中写好带参数的查询语句：
<asp:SqlDataSource ID="SqlDataSourcedown" runat="server" ConnectionString="<%$ ConnectionStrings:articleConnectionString %>"
SelectCommand="SELECT TOP (15) id, name, type FROM kejian WHERE (isfabu = 1) AND (kemuid = @kemuid) AND (lianjiid=@lianjiid) ORDER BY downn DESC">
<SelectParameters>
<asp:Parameter Name="kemuid" />
<asp:Parameter Name="lianjiid" />
</SelectParameters>
</asp:SqlDataSource>
2、在后台代码中加入如下语句：
int kemuid = 0;
int lianjiid = 0;
protected void Page_Load(object sender, EventArgs e)
{
int id = Convert.ToInt32(Request.QueryString["id"]);
string sql = "select kemuid,lianjiid from kejian where id=" + id;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["articleConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
kemuid = (int)reader["kemuid"];
lianjiid = (int)reader["lianjiid"];
}
}
}
}
this.SqlDataSourcedown.SelectParameters.Clear();
this.SqlDataSourcedown.SelectParameters.Add("kemuid", System.TypeCode.Int32, kemuid.ToString());
this.SqlDataSourcedown.SelectParameters.Add("lianjiid", System.TypeCode.Int32, lianjiid.ToString());
}
请注意前后台代码中的红色部分，前后台代码中参数名一定要一致。就能实现使用变量来给参数赋值了。
是不是很简单很方便呢？
二、如何动态增减数据源控件参数并支持分页
适于环境：
页面search.aspx接受两个查询变量kemu及name，当kemu=0时进行全部科目的查询，当kemu！=0时进行当前科目的查询，并且要求对查询的结果进行分页。
实现方法：
在前台代码中加入：
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:articleConnectionString %>"
SelectCommand="SELECT kejian.id, kejian.name, kejian.huanjing, kejian.type, kejian.downn, kejian.isfabu, kejian.stars, kejian.size, kejian.kemuid, kejian.updowntime, kemu.name AS kemu, lianji.name AS lianji FROM kejian INNER JOIN kemu ON kejian.kemuid = kemu.id INNER JOIN lianji ON kejian.lianjiid = lianji.id WHERE (kejian.name LIKE N'%' + @name + N'%') AND (kejian.kemuid = @kemu) AND (kejian.isfabu = 1) ORDER BY kejian.updowntime DESC">
<SelectParameters>
<asp:Parameter Name="name" />
<asp:Parameter Name="kemu" />
</SelectParameters>
</asp:SqlDataSource>
在后台代码中加入：
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridViewDataBind(Convert.ToInt32(Request.QueryString["kemu"]), Request.QueryString["name"]);
}
}
private void GridViewDataBind(int kemu, string name)
{
string sql = "SELECT kejian.id, kejian.name, kemu.name AS kemu, lianji.name AS lianji, kejian.huanjing, kejian.type, kejian.downn, kejian.stars, kejian.size, kejian.kemuid ";
sql += " FROM kejian INNER JOIN kemu ON kejian.kemuid = kemu.id INNER JOIN lianji ON kejian.lianjiid = lianji.id ";
sql += " WHERE (kejian.isfabu = 1) ";
if (kemu == 0)
{
sql += " AND (kejian.name LIKE N'%' + @name + N'%') ORDER BY kejian.updownuserid DESC";
this.SqlDataSource1.SelectParameters.Clear();
this.SqlDataSource1.SelectParameters.Add("name", System.TypeCode.String, name);
}
else
{
sql += " AND (kejian.kemuid = @kemu) AND (kejian.name LIKE N'%' + @name + N'%') ORDER BY kejian.updownuserid DESC";
this.SqlDataSource1.SelectParameters.Clear();
this.SqlDataSource1.SelectParameters.Add("kemu", System.TypeCode.String, kemu.ToString());
this.SqlDataSource1.SelectParameters.Add("name", System.TypeCode.String, name);
}
SqlDataSource1.SelectCommand = sql;
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
GridViewDataBind(Convert.ToInt32(Request.QueryString["kemu"]), Request.QueryString["name"]);
}
