SQLHelper

public class SqlHelper
{
private readonly string _constr = ConfigurationManager.ConnectionStrings["key"].ConnectionString;

    #region 增删改通用方法  
    public int ExecuteNonQuery(string sql, CommandType type, params SqlParameter\[\] pms)  
    {  
        //创建链接对象  
        //创建命令对象  
        //打开链接  
        //执行  
        using (SqlConnection con = new SqlConnection(\_constr))  
        {  
            using (SqlCommand cmd = new SqlCommand(sql, con))  
            {  
                if (pms != null)  
                {  
                    cmd.Parameters.AddRange(pms);  
                }  
                cmd.CommandType = type;  
                con.Open();  
                return cmd.ExecuteNonQuery();  
            }  
        }  
    }  
    #endregion

    #region 返回单行单列

    public object ExecuteScalar(string sql, CommandType type, params SqlParameter\[\] pms)  
    {  
        using (SqlConnection con = new SqlConnection(\_constr))  
        {  
            using (SqlCommand cmd = new SqlCommand(sql, con))  
            {  
                if (pms != null)  
                {  
                    cmd.Parameters.AddRange(pms);  
                }  
                cmd.CommandType = type;  
                con.Open();  
                return cmd.ExecuteScalar();  
            }  
        }  
    }

    #endregion

    #region datatable查询  
    public DataTable ExecuteTable(string sql, CommandType type, params SqlParameter\[\] pms)  
    {  
        DataTable dt = new DataTable();  
        using (SqlConnection con = new SqlConnection(\_constr))  
        {  
            using (SqlDataAdapter cmd = new SqlDataAdapter(sql, con))  
            {  
                if (pms != null)  
                {  
                    cmd.SelectCommand.Parameters.AddRange(pms);  
                }  
                cmd.SelectCommand.CommandType = type;  
                con.Open();  
                cmd.Fill(dt);  
            }  
        }  
        return dt;  
    }  
    #endregion

    #region Reader查询  
    public SqlDataReader ExecuteReader(string sql, CommandType type, params SqlParameter\[\] pms)  
    {  
        SqlConnection con = new SqlConnection(\_constr);  
        SqlCommand cmd = new SqlCommand(sql, con);  
        if (pms != null)  
        {  
            cmd.Parameters.AddRange(pms);  
        }  
        cmd.CommandType = type;  
        try  
        {  
            con.Open();  
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);  
        }  
        catch (Exception)  
        {  
            cmd.Dispose();  
            con.Close();  
            con.Dispose();  
            throw;  
        }  
    }  
    #endregion  
}

分页

public DataTable GetDataByPage(int start,int end)
{
string sql = "select * from(select *,num=ROW_NUMBER() over (order by id) from UserInfo) as t where t.num>@start and t.num<=@end";
SqlParameter[] pms =
{
new SqlParameter("@start",start),
new SqlParameter("@end",end),
};
return helper.ExecuteTable(sql, CommandType.Text, pms);
}

存储过程

private void LoadData(int index)
{
string sql = "usp_UserInfoPage";
SqlParameter[] pms = {
new SqlParameter("@pageIndex",index),
new SqlParameter("@pageSize",PageSize),
new SqlParameter("@pageCount",SqlDbType.Int){Direction=ParameterDirection.Output},
new SqlParameter("@totalCount",SqlDbType.Int){Direction=ParameterDirection.Output}
};

        DataTable dt = db.ExecuteTable(sql, CommandType.StoredProcedure, pms);  
        List<ClassInfo> list = new List<ClassInfo>();  
        foreach (DataRow item in dt.Rows)  
        {  
            ClassInfo model = new ClassInfo();  
            model.Id = Convert.ToInt32(item\["Id"\]);  
            model.name = item\["name"\].ToString();  
            model.pwd = item\["pwd"\].ToString();  
            list.Add(model);  
        }  
        dataGridView1.DataSource = list;  
        pageCount = Convert.ToInt32(pms\[2\].Value);  
        totalCounnt = Convert.ToInt32(pms\[3\].Value);  
        label6.Text = string.Format("{0}/{1}", index, pageCount);  
    }

create database UserDB
go
use UserDB
go
create table Userinfo(
ID int primary key identity ,
name varchar(20) not null,
pwd varchar(20) not null
)
insert into Userinfo values('sasas','1231231')
insert into Userinfo values('sasas','1231231')
insert into Userinfo values('sasas','1231231')
insert into Userinfo values('admin','123456')
select id,name,pwd from userinfo
go
create proc usp_UserInfoPage
@pageIndex int,
@pageSize int,
@pageCount int output,
@totalCount int output
as
begin
--查询总条数
select @totalCount = COUNT(*) from UserInfo
--查询总页数
set @pageCount = CEILING(@totalCount*1.0/@pageSize)
--查询语句
select * from (select *,num=ROW_NUMBER() over(order by Id) from UserInfo) as t where t.num>(@pageIndex-1)*@pageSize and t.num<=@pageIndex*@pageSize end select * from (select *,num=row_number() over(order by id) from userInfo) as t where t.num>0 and t.num<=4