public partial class DisplayMessage : Form { #region 分页 public delegate int EventPagingHandler(EventPagingArg e); PageData pd = new PageData(); public event EventPagingHandler EventPaging; /// <summary> /// 每页显示记录数 /// </summary> private int _pageSize = 10; /// <summary> /// 每页显示记录数 /// </summary> public int PageSize { get { return _pageSize; } set { _pageSize = value; GetPageCount(); } } private int _nMax = 0; /// <summary> /// 总记录数 /// </summary> public int NMax { get { return _nMax; } set { _nMax = value; GetPageCount(); } } private int _pageCount = 1; /// <summary> /// 页数=总记录数/每页显示记录数 /// </summary> public int PageCount { get { return _pageCount; } set { _pageCount = value; } } private int _pageCurrent = 1; /// <summary> /// 当前页号 /// </summary> public int PageCurrent { get { return _pageCurrent; } set { _pageCurrent = value; } } public BindingNavigator ToolBar { get { return this.bindingNavigator; } } private void GetPageCount() { if (this.NMax > 0) { this.PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(this.NMax) / Convert.ToDouble(this.PageSize))); } else { this.PageCount = 0; } } /// <summary> /// 翻页控件数据绑定的方法 /// </summary> public void Bind() { this.NMax = pd.GetTotalCount(); this.PageCurrent = pd.PageIndex; if (this.EventPaging != null) { this.NMax = this.EventPaging(new EventPagingArg(this.PageCurrent)); } if (this.PageCurrent > this.PageCount) { this.PageCurrent = this.PageCount; } if (this.PageCount == 1) { this.PageCurrent = 1; } lblPageCount.Text = "总共页数为:" + this.PageCount.ToString() + "页"; this.lblMaxPage.Text = "共" + this.NMax.ToString() + "条记录"; this.txtCurrentPage.Text = this.PageCurrent.ToString(); if (this.PageCurrent == 1) { btnLast.Enabled = true; btnNext.Enabled = true; btnFirst.Enabled = false; btnPrev.Enabled = false; } else if (this.PageCurrent == this.PageCount) { btnLast.Enabled = false; btnNext.Enabled = false; btnFirst.Enabled = true; btnPrev.Enabled = true; } else { this.btnLast.Enabled = true; this.btnNext.Enabled = true; this.btnPrev.Enabled = true; this.btnFirst.Enabled = true; } if (this.NMax == 0) { btnNext.Enabled = false; btnPrev.Enabled = false; btnFirst.Enabled = false; btnLast.Enabled = false; } var dt = pd.QueryDataTable(); pager1.DataSource = dt; dataGridView1.DataSource = pager1; } private void btnFirst_Click(object sender, EventArgs e) { pd.PageIndex = 1; this.Bind(); } private void btnPrev_Click(object sender, EventArgs e) { pd.PageIndex -= 1; if (pd.PageIndex <= 0) { pd.PageIndex = 1; } this.Bind(); } private void btnNext_Click(object sender, EventArgs e) { pd.PageIndex += 1; if (pd.PageIndex > pd.PageCount) { pd.PageIndex = pd.PageCount; } this.Bind(); } private void btnLast_Click(object sender, EventArgs e) { pd.PageIndex = pd.PageCount; this.Bind(); } private void btnGo_Click(object sender, EventArgs e) { if (this.txtCurrentPage.Text != null && txtCurrentPage.Text != "") { if (Int32.TryParse(txtCurrentPage.Text, out _pageCurrent)) { pd.PageIndex = _pageCurrent; this.Bind(); } else { MessageBox.Show("输入数字格式错误！"); } } } #endregion } public class EventPagingArg : EventArgs { private int _intPageIndex; public EventPagingArg(int PageIndex) { _intPageIndex = PageIndex; } } public class PageData { public int authority = -1; private int _PageSize = 10; private int _PageIndex = 1; private int _PageCount = 0; private int _TotalCount = 0; private string _TableName = "Process";//表名 private string _QueryFieldName = "*";//表字段FieldStr private string _OrderStr = "S.ID Desc"; //排序_SortStr private string _QueryCondition = "S.ID";//查询的条件 RowFilter private string _PrimaryKey = "ID";//主键 #region 构造函数 字段 /// <summary> /// 显示页数 /// </summary> public int PageSize { get { return _PageSize; } set { _PageSize = value; } } /// <summary> /// 当前页 /// </summary> public int PageIndex { get { return _PageIndex; } set { _PageIndex = value; } } /// <summary> /// 总页数 /// </summary> public int PageCount { get { return _PageCount; } set { _PageCount = value; } } /// <summary> /// 总记录数 /// </summary> public int TotalCount { get { return _TotalCount; } set { _TotalCount = value; } } /// <summary> /// 表名，包括视图 /// </summary> public string TableName { get { return _TableName; } set { _TableName = value; } } /// <summary> /// 表字段FieldStr /// </summary> public string QueryFieldName { get { return _QueryFieldName; } set { _QueryFieldName = value; } } /// <summary> /// 排序字段 /// </summary> public string OrderStr { get { return _OrderStr; } set { _OrderStr = value; } } /// <summary> /// 查询条件 /// </summary> public string QueryCondition { get { return _QueryCondition; } set { _QueryCondition = value; } } /// <summary> /// 主键 /// </summary> public string PrimaryKey { get { return _PrimaryKey; } set { _PrimaryKey = value; } } #endregion public SqlDataReader QueryDataTable() { SqlParameter[] parameters = { new SqlParameter("@TotalCount", SqlDbType.Int), new SqlParameter("@TotalPage", SqlDbType.Int), new SqlParameter("@Table", SqlDbType.NVarChar, 500), new SqlParameter("@Column", SqlDbType.NVarChar, 500), new SqlParameter("@OrderColumn", SqlDbType.NVarChar,50), new SqlParameter("@GroupColumn", SqlDbType.NVarChar , 50 ), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@Group", SqlDbType.TinyInt), new SqlParameter("@Condition" ,SqlDbType.NVarChar , 1000 ) }; parameters[0].Value = _TotalCount; parameters[1].Value = _PageCount; parameters[2].Value = " Process AS S LEFT JOIN UserTable AS C ON C.ID = S.Level1 LEFT JOIN UserTable AS B ON B.ID = S.Level2 LEFT JOIN UserTable AS E ON E.ID = S.next "; parameters[3].Value = " C.Name ,B.Name as Name1,S.Level3 , E.Name as Name2,S.DocumentsID,S.Info,S.Note ,S.ID"; parameters[4].Value = _OrderStr; parameters[5].Value = _QueryCondition; parameters[6].Value = _PageSize; parameters[7].Value = _PageIndex; parameters[8].Value = false; parameters[9].Value = " ( Level3 =" + DbHelperSQL.Authority(Form1.userid) + " or next = " + Form1.userid + ") "; SqlDataReader ds = DbHelperSQL.RunProcedure("Pager", parameters); if (_TotalCount==0) _TotalCount = GetTotalCount(); if (_TotalCount == 0) { _PageIndex = 0; _PageCount = 0; } else { _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1; if (_PageIndex > _PageCount) { _PageIndex = _PageCount; parameters[4].Value = _PageSize; ds = QueryDataTable(); } } return ds; } public int GetTotalCount() { string strSql = " select count(1) from " + _TableName; strSql += " where next = " + Form1.userid + " or Level3 =" + DbHelperSQL.Authority(Form1.userid); return DbHelperSQL.ExecuteScalar(strSql); } }
再附上一个比较简陋的sql操作
public class DbHelperSQL { public static readonly string connectionString = @""; #region ExecuteNonQuery命令 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); return returnReader; } private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { command.Parameters.Add(parameter); } return command; } /// <summary> /// 对数据库执行增、删、改命令 /// </summary> /// <param name="safeSql">T-Sql语句</param> /// <returns>受影响的记录数</returns> public static int ExecuteNonQuery(string safeSql) { using (SqlConnection Connection = new SqlConnection(connectionString)) { Connection.Open(); SqlTransaction trans = Connection.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(safeSql, Connection); cmd.Transaction = trans; if (Connection.State != ConnectionState.Open) { Connection.Open(); } int result = cmd.ExecuteNonQuery(); trans.Commit(); return result; } catch { trans.Rollback(); return 0; } } } public static string GetDocuments(int id) { string strxml = ""; SqlConnection myConn = new SqlConnection(connectionString); myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlstring = "select ID,[CONTENT] from Documents where ID='" + id + "'"; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command = new SqlCommand(sqlstring, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader = command.ExecuteReader(); while (thisReader.Read()) { strxml = thisReader.GetValue(1).ToString(); } //用完后关闭连接，以免影响其他程序访问 myConn.Close(); return strxml; } public static string NewDocuments() { string strxml = ""; SqlConnection myConn = new SqlConnection(connectionString); myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlstring = "select ID,[CONTENT] from Documents where ID='4'"; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command = new SqlCommand(sqlstring, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader = command.ExecuteReader(); while (thisReader.Read()) { strxml = thisReader.GetValue(1).ToString(); } //用完后关闭连接，以免影响其他程序访问 myConn.Close(); return strxml; } public static int Count(int id) { int count = -1; SqlConnection myConn = new SqlConnection(connectionString); myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlcount = "select count(*) from Process where next=" + id; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command6 = new SqlCommand(sqlcount, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader6 = command6.ExecuteReader(); while (thisReader6.Read()) { count = int.Parse(thisReader6.GetValue(0).ToString()); } myConn.Close(); return count; } public static int CountUser(string name) { int count = -1; SqlConnection myConn = new SqlConnection(connectionString); myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlcount = "select count(*) from UserTable where Name='" + name + "'"; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command6 = new SqlCommand(sqlcount, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader6 = command6.ExecuteReader(); while (thisReader6.Read()) { count = int.Parse(thisReader6.GetValue(0).ToString()); } myConn.Close(); return count; } public static int NameID(string name) { SqlConnection myConn = new SqlConnection(connectionString); int uid=-1; #region 查询当前用户ID myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlstring = "select ID,Name,Authority from UserTable where Name='" + name + "'"; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command = new SqlCommand(sqlstring, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader = command.ExecuteReader(); while (thisReader.Read()) { if ((thisReader.GetValue(1).ToString().Trim()) == (name.ToString().Trim())) { uid = int.Parse(thisReader.GetValue(0).ToString()); } } //用完后关闭连接，以免影响其他程序访问 myConn.Close(); #endregion return uid; } public static string IDName(int id) { SqlConnection myConn = new SqlConnection(connectionString); string name = ""; myConn.Open(); //将连接打开 string sqlstring = "select ID,Name from UserTable where ID=" + id; SqlCommand command = new SqlCommand(sqlstring, myConn); SqlDataReader thisReader = command.ExecuteReader(); while (thisReader.Read()) { name = thisReader.GetValue(1).ToString(); } myConn.Close(); return name; } public static int Authority(int id) { int authority = -1; string str = @""; SqlConnection myConn = new SqlConnection(str); #region 查询权限等级 myConn.Open(); //将连接打开 //SQL语句：从数据库的登录表中搜索登录名，密码 string sqlstring4 = "select ID,Authority from UserTable where ID=" + id; //执行con对象的函数，返回一个SqlCommand类型的对象 SqlCommand command4 = new SqlCommand(sqlstring4, myConn); //用cmd的函数执行语句，返回SqlDataReader对象thisReader,thisReader就是返回的结果集（也就是数据库中查询到的表数据） SqlDataReader thisReader4 = command4.ExecuteReader(); while (thisReader4.Read()) { authority = int.Parse(thisReader4.GetValue(1).ToString()); } //用完后关闭连接，以免影响其他程序访问 myConn.Close(); #endregion return authority; } /// <summary> /// 对数据库执行增、删、改命令 /// </summary> /// <param name="sql">T-Sql语句</param> /// <param name="values">参数数组</param> /// <returns>受影响的记录数</returns> public static int ExecuteNonQuery(string sql, SqlParameter[] values) { using (SqlConnection Connection = new SqlConnection(connectionString)) { Connection.Open(); SqlTransaction trans = Connection.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Transaction = trans; cmd.Parameters.AddRange(values); if (Connection.State != ConnectionState.Open) { Connection.Open(); } int result = cmd.ExecuteNonQuery(); trans.Commit(); return result; } catch (Exception ex) { trans.Rollback(); return 0; } } } #endregion #region ExecuteScalar命令 /// <summary> /// 查询结果集中第一行第一列的值 /// </summary> /// <param name="safeSql">T-Sql语句</param> /// <returns>第一行第一列的值</returns> public static int ExecuteScalar(string safeSql) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } } /// <summary> /// 查询结果集中第一行第一列的值 /// </summary> /// <param name="sql">T-Sql语句</param> /// <param name="values">参数数组</param> /// <returns>第一行第一列的值</returns> public static int ExecuteScalar(string sql, SqlParameter[] values) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } } #endregion #region ExecuteReader命令 /// <summary> /// 创建数据读取器 /// </summary> /// <param name="safeSql">T-Sql语句</param> /// <param name="Connection">数据库连接</param> /// <returns>数据读取器对象</returns> public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection) { if (Connection.State != ConnectionState.Open) Connection.Open(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; } /// <summary> /// 创建数据读取器 /// </summary> /// <param name="sql">T-Sql语句</param> /// <param name="values">参数数组</param> /// <param name="Connection">数据库连接</param> /// <returns>数据读取器</returns> public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection) { if (Connection.State != ConnectionState.Open) Connection.Open(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; } #endregion #region ExecuteDataTable命令 /// <summary> /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable /// </summary> /// <param name="type">命令类型(T-Sql语句或者存储过程)</param> /// <param name="safeSql">T-Sql语句或者存储过程的名称</param> /// <param name="values">参数数组</param> /// <returns>结果集DataTable</returns> public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); cmd.CommandType = type; SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } } /// <summary> /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable /// </summary> /// <param name="safeSql">T-Sql语句</param> /// <returns>结果集DataTable</returns> public static DataTable ExecuteDataTable(string safeSql) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); try { da.Fill(ds); } catch (Exception ex) { } return ds.Tables[0]; } } /// <summary> /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable /// </summary> /// <param name="sql">T-Sql语句</param> /// <param name="values">参数数组</param> /// <returns>结果集DataTable</returns> public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.CommandTimeout = 0; cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } } #endregion #region GetDataSet命令 /// <summary> /// 取出数据 /// </summary> /// <param name="safeSql">sql语句</param> /// <param name="tabName">DataTable别名</param> /// <param name="values"></param> /// <returns></returns> public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values) { using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); if (values != null) cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); try { da.Fill(ds, tabName); } catch (Exception ex) { } return ds; } } #endregion #region ExecureData 命令 /// <summary> /// 批量修改数据 /// </summary> /// <param name="ds">修改过的DataSet</param> /// <param name="strTblName">表名</param> /// <returns></returns> public static int ExecureData(DataSet ds, string strTblName) { try { //创建一个数据库连接 using (SqlConnection Connection = new SqlConnection(connectionString)) { if (Connection.State != ConnectionState.Open) Connection.Open(); //创建一个用于填充DataSet的对象 SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection); SqlDataAdapter myAdapter = new SqlDataAdapter(); //获取SQL语句，用于在数据库中选择记录 myAdapter.SelectCommand = myCommand; //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应 SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); return myAdapter.Update(ds, strTblName); //更新ds数据 } } catch (Exception err) { throw err; } } #endregion }