public class DBHelper
{

    //string connString = "Data Source=SYT-HP\\SQLEXPRESS;Initial Catalog=AwardPoint;Integrated Security=True";

     string connString = "Data Source=.;Initial Catalog=AwardPoint;Persist Security Info=True;User ID=sa;pwd=123";
    /// <summary>
    /// 分页使用的查询
    /// </summary>
    /// <param name="sql">要执行的SQL语句</param>
    /// <param name="pageSize">每页显示的数据量</param>
    /// <param name="pageIndex">当前页数</param>
    /// <param name="sort">排序的字段</param>
    /// <param name="recordCount">返回总数据量</param>
    /// <returns></returns>
    public DataTable GetListByPage(string sql, int pageSize, int pageIndex, string sort, out int recordCount)
    {
        //设定存储过程需要的参数
        SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@Source",SqlDbType.NVarChar),
            new SqlParameter("@PageSize",SqlDbType.Int),
            new SqlParameter("@CurrentPage",SqlDbType.Int),
            new SqlParameter("@FieldList",SqlDbType.NVarChar),
            new SqlParameter("@Sort",SqlDbType.NVarChar),
            new SqlParameter("@RecordCount",SqlDbType.Int),
            new SqlParameter("@FdName",SqlDbType.NVarChar)
        };

        //给存储过程参数赋值
        paras[0].Value = sql;
        paras[1].Value = pageSize;
        paras[2].Value = pageIndex;
        paras[3].Value = DBNull.Value;
        paras[4].Value = sort;
        paras[5].Value = DBNull.Value;
        paras[6].Value = DBNull.Value;

        //指明哪个是输出参数
        paras[5].Direction = ParameterDirection.Output;

        //打开连接对象
        SqlConnection conn = new SqlConnection(connString);
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }

        //执行存储过程
        SqlCommand cmd = new SqlCommand("pro_sys_GetRecordByPage", conn);
        //告诉SqlCommand它执行的是存储过程
        cmd.CommandType = CommandType.StoredProcedure;

        //使用存储过程参数参数
        for (int i = 0; i < paras.Length; i++)
        {
            cmd.Parameters.Add(paras[i]);
        }

        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);

            //给存储过程的输出参数赋值
            recordCount = (int)paras[5].Value;

            return dt;
        }

    }
    public DataTable ExecuteQuery(string sql)
    {
        using (SqlDataAdapter dat = new SqlDataAdapter(sql, connString))
        {
            DataTable dt = new DataTable();
            dat.Fill(dt);
            return dt;

        }


    }
    public void ExecuteNonQuery(string sql)
    {
        SqlTransaction trans = null;
        SqlConnection con = new SqlConnection(connString);
        try
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            trans = con.BeginTransaction();
            SqlCommand com = new SqlCommand(sql, con, trans);
            com.ExecuteNonQuery();
            trans.Commit();

        }
        catch (Exception ex)
        {
            trans.Rollback();

            throw;
        }
        finally
        {
            con.Close();
            con.Dispose();

        }


    }
}