ASP.Net存储过程实现分页+三层架构

转载于 2020-12-02 23:16:51 发布 · 422 阅读

5 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/WuLex

ASP.NET Webform 专栏收录该内容

77 篇文章

订阅专栏

博客展示了存储过程使用示例，包含数据库表、项目的数据模块图和结构图。还介绍了项目各层，如BLL层、DAL层、Model层（Entity类），以及UI层的前台代码（WebForm1.aspx）和后台代码（WebForm1.aspx.cs）。

在这里插入图片描述

CREATE PROCEDURE Sp_page @strTable      VARCHAR(50),--表名 
                         @strColumn     VARCHAR(50),--按该列来进行分页 
                         @intColType    INT, 
                         --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型 
                         @intOrder      BIT,--排序,0-顺序,1-倒序 
                         @strColumnlist VARCHAR(800), 
                         --要查询出的字段列表,*表示全部字段 
                         @intPageSize   INT,--每页记录数 
                         @intPageNum    INT,--指定页 
                         @strWhere      VARCHAR(800),--查询条件 
                         @intPageCount  INT output --总页数 
AS 
    DECLARE @sql NVARCHAR(4000) --用于构造SQL语句 
    DECLARE @where1 VARCHAR(800) --构造条件语句 
    DECLARE @where2 VARCHAR(800) --构造条件语句 

    IF @strWhere IS NULL 
        OR Rtrim(@strWhere) = '' 
      -- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格 
      BEGIN --没有查询条件 
          SET @where1=' WHERE ' 
          SET @where2=' ' 
      END 
    ELSE 
      BEGIN --有查询条件 
          SET @where1=' WHERE (' + @strWhere + ') AND ' 
          SET @where2=' WHERE (' + @strWhere + ') ' 
      END 

    SET @strColumn = ' ' + @strColumn + ' ' 
    SET @strColumnlist = ' ' + @strColumnlist + ' ' 
    --构造SQL语句，计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 ) 
    SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/' 
             + Cast(@intPageSize AS VARCHAR) + ') FROM ' 
             + @strTable + @where2 

    --执行SQL语句，计算总页数，并将其放入@intPageCount变量中 
    EXEC Sp_executesql 
      @sql, 
      N'@intPageCount int OUTPUT', 
      @intPageCount output 

    --将总页数放到查询返回记录集的第一个字段前，此语句可省略 
    SET @strColumnlist= Cast(@intPageCount AS VARCHAR(30)) 
                        + ' as PageCount,' + @strColumnlist 

    IF @intOrder = 0 --构造升序的SQL 
      SET @sql='SELECT TOP ' 
               + Cast(@intPageSize AS VARCHAR) + ' ' 
               + @strColumnlist + ' FROM ' + @strTable + @where1 
               + @strColumn + '>(SELECT MAX(' + @strColumn + ') ' 
               + ' FROM (SELECT TOP ' 
               + Cast(@intPageSize*(@intPageNum - 1) AS VARCHAR) 
               + @strColumn + ' FROM ' + @strTable + @where2 
               + 'ORDER BY ' + @strColumn + ') t) ORDER BY ' 
               + @strColumn 
    ELSE --构造降序的SQL 
      SET @sql='SELECT TOP ' 
               + Cast(@intPageSize AS VARCHAR) + ' ' 
               + @strColumnlist + ' FROM ' + @strTable + @where1 
               + @strColumn + '<(SELECT MIN(' + @strColumn + ') ' 
               + ' FROM (SELECT TOP ' 
               + Cast(@intPageSize*(@intPageNum - 1) AS VARCHAR) 
               + @strColumn + ' FROM ' + @strTable + @where2 
               + 'ORDER BY ' + @strColumn 
               + ' DESC) t) ORDER BY ' + @strColumn + ' DESC' 

    IF @intPageNum = 1--第一页 
      SET @sql='SELECT TOP ' 
               + Cast(@intPageSize AS VARCHAR(10)) + ' ' 
               + @strColumnlist + ' FROM ' + @strTable + @where2 
               + ' ORDER BY ' + @strColumn + CASE @intOrder WHEN 0 THEN '' ELSE 
               ' DESC' END 

    --PRINT @sql 
    EXEC(@sql) 

go

存储过程使用示例

DECLARE @intPageCount INT 

EXEC Sp_page 
  'stuInfo',--表名 
  'stuNO',--按该列来进行分页 
  0,-- 0-数字类型,1-字符类型,2-日期时间类型 
  0,-- 0-顺序,1-倒序 
  '*',--要查询出的字段列表,*表示全部字段 
  2,--每页记录数 
  1,--指定页 
  '',--查询条件 
  @intPageCount output 

PRINT @intPageCount

数据库表：

数据库表

项目的数据模块图：

项目结构图

BLL层：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3.BLL
{
   
    public class UserInfoBLL
    {
        UserInfoDAL userDal = new UserInfoDAL();
        public object loadData(UserInfo user)
        {
           return userDal.loadData(user);
        }
        public int Count()
        {
            int count = UserInfoDAL.count;
            return count;
        }
    }
}

DAL层：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebApplication3.Model;
namespace WebApplication3.DAL
{
    public class UserInfoDAL
    {
        SQLHelper sqlHelp = new SQLHelper();
        public static int  count;
        public object loadData(UserInfo user)
        {
            SqlParameter[] paraValues = {
                                        new SqlParameter("@strTable",SqlDbType.VarChar),
                                        new SqlParameter("@strColumn",SqlDbType.VarChar),
                                        new SqlParameter("@intColType",SqlDbType.Int),
                                        new SqlParameter("@intOrder",SqlDbType.Int),
                                        new SqlParameter("@strColumnlist",SqlDbType.VarChar),
                                        new SqlParameter("@intPageSize",SqlDbType.Int),
                                        new SqlParameter("@intPageNum",SqlDbType.Int),
                                        new SqlParameter("@strWhere",SqlDbType.VarChar),
                                        new SqlParameter("@intPageCount",SqlDbType.Int)
                                        };
            paraValues[0].Value = user.tableName;
            paraValues[1].Value = user.Column;
            paraValues[2].Value = user.ColType;
            paraValues[3].Value = user.Order;
            paraValues[4].Value =user.Columnlist;
            paraValues[5].Value = user.PageSize;
            paraValues[6].Value = user.PageNum;
            paraValues[7].Value = user.Where;
            paraValues[8].Direction = ParameterDirection.Output;
            object obj = sqlHelp.ExecuteProcTable("sp_page", paraValues);
            count = Convert.ToInt32(paraValues[8].Value);
            return obj;
        }
    }
}

Model层（Entity类）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebApplication3.Model
{
    public class UserInfo
    {
        public string tableName { get; set; }
        public string Column { get; set; }
        public int ColType { get; set; }
        public int Order { get; set; }
        public string Columnlist { get; set; }
        public int PageSize { get; set; }
        public int PageNum { get; set; }
        public string Where { get; set; }
        //public string PageCount { get; set; }
    }
}

UI层：前台代码（WebForm1.aspx）

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication3.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <script type="text/javascript">
　　function integer() {
　　　　var num = document.getElementById("TextBox1").value;
　　　　if (num=="") {
　　　　　　alert('请输入内容');
　　　　　　return false;
　　　　}
　　　　if (!(/(^[1-9]\d*$)/.test(num))) {
　　　　　　alert('输入的不是正整数');
　　　　　　return false;
    } else {
        return true;
　　　　}
　　}
</script>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="stuNO" HeaderText="学号" />
                <asp:BoundField DataField="stuName" HeaderText="姓名" />
            </Columns>
        </asp:GridView>
        <div>
            <asp:Button ID="btnFirst" runat="server" OnClick="btnFirst_Click" Text="首页" style="height: 21px" />
            <asp:Button ID="btnPre" runat="server" OnClick="btnPre_Click" Text="上一页" />
            <asp:Button ID="btnNext" runat="server" OnClick="btnNext_Click" Text="下一页" style="height: 21px" />
            <asp:Button ID="btnLast" runat="server" OnClick="btnLast_Click" Text="最后一页" />
            <asp:TextBox ID="TextBox1" runat="server" Width="83px"></asp:TextBox>
            <asp:Button ID="btnGO" runat="server" OnClientClick="integer()" OnClick="btnGO_Click" Text="跳转" />
        </div>
        <p>
            <asp:Label ID="Label1" runat="server"></asp:Label>
            <asp:Label ID="Label2" runat="server"></asp:Label>
            <asp:Label ID="Label3" runat="server" Text="每页显示条数"></asp:Label>
            <asp:DropDownList ID="DropDownList1" runat="server">
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem Selected="True">2</asp:ListItem>
                <asp:ListItem>5</asp:ListItem>
                <asp:ListItem>10</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="btnOK" runat="server" OnClick="btnOK_Click" Text="确认" />
        </p>
    </form>
</body>
</html>

UI层：后台代码（WebForm1.aspx.cs）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using WebApplication3.BLL;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        UserInfoBLL userBll = new UserInfoBLL();
        UserInfo user = new UserInfo();
        int count = 1;
        private void loadData()
        {
            count = Convert.ToInt32(ViewState["count"]);
            user.tableName = "stuInfo";
            user.Column = "stuNO";
            user.ColType = 0;
            user.Order = 0;
            user.Columnlist = "*";
            user.PageSize = Convert.ToInt32(DropDownList1.SelectedItem.Text);
            user.PageNum = count;
            user.Where = "";
            GridView1.DataSource = userBll.loadData(user);
            GridView1.DataBind();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["count"] = 1;
                loadData();
                Label1.Text  = "当前页：" + count;
                Label2.Text = "总页数：" + userBll.Count();
            }
        }
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            Label1.Text  = "当前页：" + count;
            Label2.Text = "总页数：" + userBll.Count();
        }
        protected void btnPre_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(ViewState["count"]) > 1)
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) - 1;
                loadData();
                Label1.Text = "当前页：" + count;
            }
        }
        protected void btnNext_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(ViewState["count"]) < userBll.Count())
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) + 1;
                loadData();
                Label1.Text = "当前页：" + count;
            }
        }
        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["count"] = userBll.Count();
            loadData();
            Label1.Text = "当前页：" + count;
        }
        protected void btnGO_Click(object sender, EventArgs e)
        {
            try
            {
                ViewState["count"] = Convert.ToInt32(TextBox1.Text);
            }
            catch
            {
                return;
            }
            if (Convert.ToInt32(ViewState["count"]) <= userBll.Count())
            {
                loadData();
                Label1.Text = "当前页：" + count;
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "", "alert('超出范围');", true);
            }
        }
        protected void btnOK_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            Label1.Text = "当前页：" + count;
            Label2.Text = "总页数：" + userBll.Count();
        }
    }
}

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Webapp.DBHelper
{
    class SQLHelper
    {
        private string connStr = null;
        private SqlConnection conn = null;
        private SqlTransaction trans = null;
        public SQLHelper()
        {
            connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void OpenDB()
        {
            try
            {
                if (conn != null && conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void CloseDB()
        {
            try
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行带参非查询SQL命令
        /// </summary>
        /// <param name="cmdtxt">带参非查询SQL命令</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>影响的行数</returns>
        private SqlCommand CreateSqlCommand(string sql, SqlParameter[] parmas = null, CommandType cmdType = CommandType.Text)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = sql;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            if (parmas != null)
            {
                cmd.Parameters.AddRange(parmas);
            }
            return cmd;
        }
        /// <summary>
        /// 执行一般非查询SQL命令
        /// </summary>
        /// <param name="cmdtxt">非查询SQL命令</param>
        /// <returns>影响的行数</returns>
        public int ExecuteSqlNonQuery(string sql)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (trans == null)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 根据SQL指令返回第一行第一列结果
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">cmd.Parameters.AddWithValue</param>
        /// <returns>返回第一行第一列结果</returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql, parameters);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (trans == null)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 执行不带参非查询存储过程
        /// </summary>
        /// <param name="proctxt">带参非查询存储过程名</param>        
        /// <returns>影响的行数</returns>
        public int ExecuteProcNonQuery(string proc)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (trans == null)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 执行带参非查询存储过程
        /// </summary>
        /// <param name="proctxt">带参非查询存储过程名</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>影响的行数</returns>
        public int ExecuteProcNonQuery(string proc, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (trans == null)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 执行一般查询SQL命令，返回DataReader对象
        /// </summary>
        /// <param name="cmdtxt">一般查询SQL命令</param>
        /// <returns>返回DataReader对象</returns>
        public SqlDataReader ExecuteSqlReader(string sql)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql);
            try
            {
                return cmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行带参查询SQL命令，返回DataReader对象
        /// </summary>
        /// <param name="cmdtxt">带参非查询SQL命令</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>返回DataReader对象</returns>
        public SqlDataReader ExecuteSqlReader(string sql, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql, parameters);
            try
            {
                return cmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行不带参查询存储过程
        /// </summary>
        /// <param name="proctxt">不参查询存储过程名</param>        
        /// <returns>返回DataReader对象</returns>
        public SqlDataReader ExecuteProcReader(string proc)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行带参查询存储过程，返回DataReader对象
        /// </summary>
        /// <param name="cmdtxt">带参查询存储过程</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>返回DataReader对象</returns>
        public SqlDataReader ExecuteProcReader(string proc, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行普通汇总查询SQL命令，返回object对象
        /// </summary>
        /// <param name="cmdtxt">普通汇总查询SQL命令</param>
        /// <returns>汇总结果，object对象</returns>
        public object ExecuteScalar(string sql)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB();
            }
        }
        /// <summary>
        /// 执行带参汇总查询SQL命令，返回object对象
        /// </summary>
        /// <param name="cmdtxt">带参汇总查询SQL命令</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>汇总结果，object对象</returns>  
        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql, parameters);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB();
            }
        }
        /// <summary>
        /// 执行不带参汇总查询存储过程，返回object对象
        /// </summary>
        /// <param name="cmdtxt">存储过程名</param>       
        /// <returns>汇总结果，object对象</returns>        
        public object ExecuteProcScalar(string proc)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB();
            }
        }
        /// <summary>
        /// 执行带参汇总查询存储过程，返回object对象
        /// </summary>
        /// <param name="cmdtxt">存储过程名</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>汇总结果，object对象</returns>   
        public object ExecuteProcScalar(string proc, params SqlParameter[] parameters)
        {
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB();
            }
        }
        /// <summary>
        /// 执行普通查询SQL命令，返回结果集DataTable
        /// </summary>
        /// <param name="cmdtxt">普通查询SQL命令</param>
        /// <returns>结果集DataTable</returns>
        public DataTable ExecuteTable(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = CreateSqlCommand(sql);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行带参查询SQL命令，返回结果集DataTable
        /// </summary>
        /// <param name="cmdtxt">带参查询SQL命令</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>结果集DataTable</returns>
        public DataTable ExecuteSqlTable(string sql, params SqlParameter[] parameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = CreateSqlCommand(sql, parameters);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行不带参查询存储过程，返回结果集DataTable
        /// </summary>
        /// <param name="cmdtxt">存储过程名</param>        
        /// <returns>结果集DataTable</returns>
        public DataTable ExecuteProcTable(string proc)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = CreateSqlCommand(proc, null, CommandType.StoredProcedure);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行带参查询存储过程，返回结果集DataTable
        /// </summary>
        /// <param name="cmdtxt">存储过程名</param>
        /// <param name="parmas">参数对象数组</param>
        /// <returns>结果集DataTable</returns>
        public DataTable ExecuteProcTable(string proc, params SqlParameter[] parameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = CreateSqlCommand(proc, parameters, CommandType.StoredProcedure);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        public DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            //conn = new SqlConnection(connStr);
            OpenDB();
            SqlCommand cmd = CreateSqlCommand(sql, parameters);
            DataSet dataset = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            try
            {
                adapter.Fill(dataset);
                return dataset.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 开始一个事务
        /// </summary>
        public void BeginSqlTranscation()
        {
            OpenDB();
            trans = conn.BeginTransaction();
        }

        /// <summary>
        /// 提交一个事务
        /// </summary>
        public void CommitSqlTranscation()
        {
            trans.Commit();
            CloseDB();
            trans = null;
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollBackSqlTranscation()
        {
            trans.Rollback();
            CloseDB();
            trans = null;
        }
    }
}