有些知识忘了UserList.aspx:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCount();
}
}
private void BindCount()
{
Gridview1.DataSource = BLL.User.GetModel();
Gridview1.DataBind();
}
BLL User.cs
public static DataSet GetModel()
{
string sqlstr = "select UserId, LoginName,UserName,DepartMentName, RoleName from [User] a,[DepartMent] b,[Role] c where a.DepartMentId=b.DepartMentId and a.RoleId=c.RoleId";
return SqlData.getDataSet(sqlstr, "User");
}
在UserList.aspx上面加了个姓名TEXTBOX部门DropDownList BtnSearch
查询按钮怎么写BtnSearch
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCount();
}
}
private void BindCount()
{
Gridview1.DataSource = BLL.User.GetModel();
Gridview1.DataBind();
}
BLL User.cs
public static DataSet GetModel()
{
string sqlstr = "select UserId, LoginName,UserName,DepartMentName, RoleName from [User] a,[DepartMent] b,[Role] c where a.DepartMentId=b.DepartMentId and a.RoleId=c.RoleId";
return SqlData.getDataSet(sqlstr, "User");
}
在UserList.aspx上面加了个姓名TEXTBOX部门DropDownList BtnSearch
查询按钮怎么写BtnSearch
strSql.Append("Select * from A Where 1=1 ");
if (TEXTBOX.text!= "")
{
strSql.Append(" and TEXTBOX ='"+TEXTBOX.text+"' ");
}
if (DropDownList1.SelectedValue!="-1") //根据需要自行修改
{
strSql.Append(" and drop='" + DropDownList1.SelectedValue+ "' ");
}
strSql.Append(" Order by Name desc "); //根据需要自行修改
public static DataSet GetModel(string Name,string DepartMentName)
{
string sqlstr = "select UserId, LoginName,UserName,DepartMentName, RoleName from [User] a,[DepartMent] b,[Role] c where a.DepartMentId=b.DepartMentId and a.RoleId=c.RoleId and a.UserName='"+Name+"' and b.DepartMentName='"+DepartMentName+"'";
return SqlData.getDataSet(sqlstr, "User");
}另外……BLL里写SQL语句这种做法不可取,应该放到DAL里
GetModel(string Name,string DepartMentName)再加个方法,方法名相同,参数不同,将页面的TEXTBOX和下拉框值传入这个方法
public static DataSet GetModel(string Name,string DepartMentName)
{
string sqlstr = "select UserId, LoginName,UserName,DepartMentName, RoleName from [User] a,[DepartMent] b,[Role] c where a.DepartMentId=b.DepartMentId and a.RoleId=c.RoleId and a.UserName='"+Name+"' and b.DepartMentName='"+DepartMentName+"'";
//return SqlData.getDataSet(sqlstr, "User");
}
不知道你的SQLDATA是怎么写的
{
public class SqlData
{ /// <summary>
/// 建立数据库连接
/// </summary>
/// <returns>返回SqlConnection对象</returns>
public static SqlConnection getConnection()
{
SqlConnection myCon;
try
{ string strSqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["OADate"].ConnectionString.ToString();
//SqlConnection类用来连接数据库
myCon = new SqlConnection(strSqlCon);
}
catch (Exception e)
{
throw e;
}
return myCon;
}
/// <summary>
/// 执行SqlCommand命令
/// </summary>
/// <param name="strSqlCommand">SQL语句</param>
public static void getCommand(string strSqlCommand)
{
SqlConnection sqlcon = getConnection();
try
{
//SqlConnection类的Open()方法用来打开数据库连接
sqlcon.Open();
//声明将对数据库执行一个SQL语句或存储过程
SqlCommand sqlcom = new SqlCommand(strSqlCommand, sqlcon);
//执行SqlCommand命令
sqlcom.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
//关闭数据库连接
sqlcon.Close();
//sqlcon.Dispose();
}
}
/// <summary>
/// 创建一个DataSet对象
/// </summary>
/// <param name="strSqlCommand">SQL语句</param>
/// <param name="strTable">表名</param>
/// <returns>返回DataSet对象</returns>
public static DataSet getDataSet(string strSqlCommand, string strTable)
{
SqlConnection sqlcon = getConnection();
DataSet myds;
try
{
sqlcon.Open();
//用于填充DataSet和更新数据库的一组数据命令和一个数据库连接
SqlDataAdapter sqlda = new SqlDataAdapter(strSqlCommand, sqlcon);
//DataSet是数据的内存驻留表示形式,他表示一个数据集
myds = new DataSet();
//填充DataSet数据集
sqlda.Fill(myds, strTable);
}
catch (Exception e)
{
throw e;
}
finally
{
sqlcon.Close();
//sqlcon.Dispose();
}
return myds;
} /// <summary>
/// 创建一个SqlDataReader对象
/// </summary>
/// <param name="strSqlCommand">SQL语句</param>
/// <returns>返回SqlDataReader对象</returns>
public static SqlDataReader getRead(string strSqlCommand)
{
SqlConnection sqlcon = getConnection();
SqlDataReader sqlread;
try
{
sqlcon.Open();
//实例化一个SqlCommand对象
SqlCommand sqlcom = new SqlCommand(strSqlCommand, sqlcon);
//ExecuteReader创建SqlDataReader类对象,CommandBehavior .
//CloseConnection说明在关闭SqlDataReader对象的时候,关闭其关联的SqlConnection对象
sqlread = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw e;
}
return sqlread;
}
//**************************************以下是调用存储过程的方法************************************ /// <summary>
/// 执行存储过程,返回SqlDataReader对象
/// 在SqlDataReader对象关闭的同时,数据库连接自动关闭
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="paraValues">要传递给给存储过程的参数值类表</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader getRead(string proName, SqlParameter[] paraValues)
{
SqlConnection sqlcon = getConnection();
SqlDataReader dr;
SqlCommand comm = new SqlCommand(proName, sqlcon);
comm.CommandType = CommandType.StoredProcedure;
// 添加所有参数
if (paraValues != null)
{
foreach (SqlParameter para in paraValues)
{
comm.Parameters.Add(para);
}
}
try
{
sqlcon.Open();
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw e;
}
finally
{
comm.Parameters.Clear();
}
return dr;
}
/// <summary>
/// 执行存储过程,不返回值。
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="paraValues">参数值列表。</param>
public static void getCommand(string proName, SqlParameter[] paraValues)
{
SqlConnection sqlcon = getConnection();
SqlCommand comm = new SqlCommand(proName, sqlcon);
comm.CommandType = CommandType.StoredProcedure;
// 添加所有参数
if (paraValues != null)
{
foreach (SqlParameter para in paraValues)
{
comm.Parameters.Add(para);
}
}
try
{
sqlcon.Open();
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
comm.Parameters.Clear();
sqlcon.Close();
//sqlcon.Dispose();
}
}
/// <summary>
/// 执行存储过程返回一个表。
/// </summary>
/// <param name="proName">存储过程名</param>
/// <param name="paraValues">参数值列表</param>
/// <returns>DataTable对象</returns>
public static DataTable getDataTable(string proName, SqlParameter[] paraValues)
{
SqlConnection sqlcon = getConnection();
DataTable dt;
SqlCommand comm = new SqlCommand(proName, sqlcon);
comm.CommandType = CommandType.StoredProcedure;
// 添加所有参数
if (paraValues != null)
{
foreach (SqlParameter para in paraValues)
{
comm.Parameters.Add(para);
}
}
try
{
sqlcon.Open();
SqlDataAdapter sqlda = new SqlDataAdapter(comm);
dt = new DataTable();
sqlda.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
comm.Parameters.Clear();
sqlcon.Close();
//sqlcon.Dispose();
}
return dt;
}
/// <summary>
/// 返回查询结果的第一行第一列对象
/// </summary>
/// <param name="strSqlCommand"></param>
/// <returns></returns>
public static object getExecuteScale(string proName, SqlParameter[] paraValues)
{
object result;
SqlConnection sqlcon = getConnection();
//实例化一个SqlCommand对象
SqlCommand comm = new SqlCommand(proName, sqlcon);
comm.CommandType = CommandType.StoredProcedure;
// 添加所有参数
if (paraValues != null)
{
foreach (SqlParameter para in paraValues)
{
comm.Parameters.Add(para);
}
}
try
{
sqlcon.Open();
result = comm.ExecuteScalar();
}
catch (Exception e)
{
throw e;
}
finally
{
comm.Parameters.Clear();
sqlcon.Close();
//sqlcon.Dispose();
}
return result;
}
}
}
{
string sqlstr = "select UserId, LoginName,UserName,DepartMentName, RoleName from [User] a,[DepartMent] b,[Role] c where a.DepartMentId=b.DepartMentId and a.RoleId=c.RoleId";
if (!string.IsNullOrEmpty(Name))
{
sqlstr +=" and a.UserName='"+Name+"'";
}
if (!string.IsNullOrEmpty(DepartMentName))
{
sqlstr +=" and b.DepartMentName='"+DepartMentName+"'";
}
return SqlData.getDataSet(sqlstr, "User");
}
VIEWbtnSearch点击事件
{
Gridview1.DataSource = BLL.User.GetModel(姓名.text,部门.text);
Gridview1.DataBind();
}