OleDBHelper

最新推荐文章于 2023-01-12 20:51:31 发布

别有滋味

最新推荐文章于 2023-01-12 20:51:31 发布

阅读量1.7k

点赞数

分类专栏： C# 文章标签： exception dataset string button sql input

C# 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

 public class OleDbHelper
    {
        public OleDbHelper() { }

        private static OleDbConnection Conn;
        private static OleDbCommand Cmd;
        private static OleDbDataAdapter Da;
        private static DataSet Ds;
        private static DataTable Dt;
        private static string strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="
        + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["Conn"].ToString());

        /* Web.Config配置
          <connectionStrings>
          <!--数据库连接字符-->
          <add name="ConnStr" connectionString="~/App_Data/Data.mdb"/>
          </connectionStrings>
        */

        /// <summary>
        /// 打开连接
        /// </summary>
        public static void Open()
        {
            Conn = new OleDbConnection();
            Cmd = new OleDbCommand();
            if (Conn.State.Equals(ConnectionState.Closed))
            {
                Conn.ConnectionString = strConn;
                Conn.Open();
            }
            Cmd.Connection = Conn;
        }


        /// <summary>
        /// 关闭连接
        /// </summary>
        public static void Close()
        {
            if (Conn.State.Equals(ConnectionState.Open))
            {
                Conn.Close();
                Conn.Dispose();
            }
        }

        /// <summary>
        /// 执行ExecuteNonQuery()
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteCmd(string sql)
        {
            try
            {
                Open();
                Cmd.CommandText = sql;
                return Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
            }
        }


        /// <summary>
        /// DataSet类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            try
            {
                Open();
                Cmd.CommandText = sql;
                Da = new OleDbDataAdapter();
                Da.SelectCommand = Cmd;
                Ds = new DataSet();
                Da.Fill(Ds);
                return Ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
            }
        }

        /// <summary>
        /// DataTable 类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            try
            {
                Open();
                Cmd.CommandText = sql;
                Da = new OleDbDataAdapter();
                Da.SelectCommand = Cmd;
                Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
            }
        }

        /// <summary>
        /// 执行 ExecuteScalar
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql)
        {
            try
            {
                Open();
                Cmd.CommandText = sql;
                return (int)Cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
            }
        }
    }

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
     <style type="text/css">
        body{font-size:12px;font-family:宋体;}
        span{font-weight:bold;}
        a{color:green;}
        a:hover{ color:Blue;}
    </style>
    
    <script type="text/javascript">
        function getID(id) {
            return document.getElementById(id);
        }

        //列表
        function listpage() {
            var rep = "";
            var list = CheckDelete._Default.get_list().value;
            for (var i = 0; i < list.length; i++) {
                rep += list[i].name + " <input name=" + list[i].id + " type='checkbox' value=" + list[i].name + " > <br/> ";
            }
            getID("str").innerHTML = rep;
        }


        // 全选
        function checkall() {
            var all = document.getElementsByTagName("input");
            for (var i = 0; i < all.length; i++) {
                if (all[i].type == "checkbox") {
                    all[i].checked = true;
                }
            }
        }

        //反选
        function checknull() {
            var all = document.getElementsByTagName("input");
            for (var i = 0; i < all.length; i++) {
                if (all[i].type == "checkbox") {
                    all[i].checked = false;
                }
            }
        }

        //删除
        function deleteAll() {
            var all = document.getElementsByTagName("input");
            if (confirm('确定删除?')) {
                for (var i = 0; i < all.length; i++) {
                    if (all[i].checked) {
                        CheckDelete._Default.deleteall(all[i].name);
                    }
                }
                listpage()
            }
        }

        //添加
        function insert() {
            CheckDelete._Default.insert();
            listpage();
        }
    
    </script>
</head>
<body οnlοad="listpage();">
    <form id="form1" runat="server">
    <div>
     <br />
     <div id="str"></div>   
     <input id="Button1" type="button" value="全选" οnclick="checkall()" /> 
     <input id="Button2" type="button" value="反选" οnclick="checknull()" />
     <input id="Button3" type="button" value="删除" οnclick="deleteAll()" /> 
     <input id="Button4" type="button" value="添加10条记录" οnclick="insert()" />
     
    </div>
    </form>
</body>
</html>

namespace CheckDelete
{
    public partial class _Default : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            AjaxPro.Utility.RegisterTypeForAjax(typeof(_Default));
        }
        //列表
        [AjaxPro.AjaxMethod]
        public List<Model> get_list()
        {
            string sql = "select * from [Book]";
            DataTable Dt = OleDbHelper.GetDataTable(sql);
            List<Model> list = new List<Model>();
            foreach (DataRow Dr in Dt.Rows)
            {
                Model mode = new Model();
                mode.id = Convert.ToInt32(Dr["id"]);
                mode.name = Dr["name"].ToString();
                list.Add(mode);
            }
            return list;
        }

        //删除
        [AjaxPro.AjaxMethod]
        public void deleteall(string id)
        {
            string sql = "delete from [Book] where id=" + id;
            CheckDelete.OleDbHelper.ExecuteCmd(sql);
        }

        //无刷新添加数据
        [AjaxPro.AjaxMethod]
        //由于使用的通用的数据库操作类,循环的时候,循环几次就打开几次数据库连接,性能很差,实际项目开发应用时,建议重新写数据操作类
        //只打开一次数据库连接,循环执行完毕后再关闭连接
        public void insert()
        {
            for (int i = 0; i < 10; i++)
            {
                string sql = "insert into [Book](name) values('最新书籍" + i.ToString() + "')";
                CheckDelete.OleDbHelper.ExecuteCmd(sql);
            }
        }
    }
    public class Model
    {
        private int _id;
        private string _name;

        public int id
        {
            get { return _id; }
            set { _id = value; }
        }

        public string name
        {
            get { return _name; }
            set { _name = value; }
        }
    }
}