Access 数据库封装类

最新推荐文章于 2022-06-11 11:11:08 发布
最新推荐文章于 2022-06-11 11:11:08 发布
阅读量271 收藏
点赞数
分类专栏： [随笔分类]知识学习 文章标签： Access SQL Linq

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Collections;
using System.Configuration;

namespace Common.Dba
{
    public class DbAccessData:DbAccess
    {
       
        new OleDbCommand cmd = null;
        public DbAccessData()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            conn = new OleDbConnection();
            conn.ConnectionString = ConnectionString; //Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
            //conn.ConnectionString = "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt;Connect Timeout=5000";
            cmd = new OleDbCommand();
            cmd.Connection = conn as OleDbConnection;
            cmd.CommandTimeout = 0;

        }
        public DbAccessData(string constr)
        {

            //
            // TODO: 在此处添加构造函数逻辑
            //
            conn = new OleDbConnection();
            //conn.ConnectionString = "initial catalog=pubs;data source=.;user id=sa;password=";
            //conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
            conn.ConnectionString = constr;// "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt";
            cmd = new OleDbCommand();
            cmd.Connection = conn as OleDbConnection;
            cmd.CommandTimeout = 0;

        }
        /// <summary>
        /// 获取数据根据OleDb语句
        /// </summary>
        /// <param name="OleDb"></param>
        /// <returns></returns>
        public override DataTable GetTable(string sql)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;

            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 获取数据根据sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;

                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;

            }
            return ds;
        }
        /// <summary>
        /// 获取数据根据sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataSet GetDataSet(string sql, DbParameter[] pas)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                foreach (OleDbParameter pa in pas)
                {
                    cmd.Parameters.Add(pa);
                }
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;

                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;

            }
            return ds;
        }
        /// <summary>
        /// 获取数据根据sql语句 带参数 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetTable(string sql, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }


                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 获取数据根据sql语句 带参数 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetProcTable(string procname, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                //cmd.CommandText = sql;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

 

                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 获取数据根据sql语句 带参数 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetProcCursorTable(string procname, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                //cmd.CommandText = sql;
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

 

                da.Fill(ds);
            }
            catch (Exception ex)
            {


                return null;
            }
            return ds.Tables[1] ?? new DataTable();
        }
        /// <summary>
        /// 获取数据根据sql语句 带参数 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override int GetProcState(string procname, params DbParameter[] pas)
        {
            int state = 0;
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.ExecuteNonQuery();
                CloseConn();
                state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {
                return 0;
            }
            return state;
        }
        /// <summary>
        /// 获取数据根据sql语句 带参数 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override int GetProcStateNo(string procname, params DbParameter[] pas)
        {
            int state = 0;
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.ExecuteNonQuery();
                CloseConn();
                state = 1;

                // state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {

                return 0;
            }
            return state;
        }
        /// <summary>
        /// 存储过程返回值的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override string GetProcStateReturnValue(string procname, params DbParameter[] pas)
        {
            string state = "";
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.Parameters.AddWithValue("@Return_Value", "").Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                state = Convert.ToString(cmd.Parameters["@Return_Value"].Value);
                state = state == null ? ("") : (state);
                CloseConn();


                // state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {

                return "";
            }
            return state;
        }
        /// <summary>
        /// 根据sql语句返回跟新状态
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override bool GetState(string sql)
        {
            bool succ = false;
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                OpenConn();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                CloseConn();
            }
            catch (Exception ex)
            {


                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根据sql语句返回跟新状态带参数的
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="pas">参数的集合</param>
        /// <returns></returns>
        public override bool GetState(string sql, params DbParameter[] pas)
        {
            bool succ = false;
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                OpenConn();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                CloseConn();
            }
            catch
            {

                // this.ShowError(ex.Message);
                //using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\error2008.txt"))
                //{
                //    sw.Write(ex.Message);
                //    sw.Flush();
                //}
                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根据sql语句返回第一个单元格的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override string GetOne(string sql)
        {
            string res = "";
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                OpenConn();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                CloseConn();
            }
            catch (Exception ex)
            {


                return null;
            }
            return res;
        }
        /// <summary>
        ///  根据sql语句返回第一个单元格的数据带参数的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override string GetOne(string sql, params DbParameter[] pas)
        {
            string res = "";
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                OpenConn();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();

                return null;
            }
            return res;
        }
        /// <summary>
        /// 返回数据的DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DbDataReader GetDataReader(string sql)
        {
            OleDbDataReader dr = null;
            try
            {
                cmd.CommandType = CommandType.Text;
                conn.Open();
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {


                return null;
            }
            return dr;
        }
        /// <summary>
        /// 返回数据的DataReader带参数的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DbDataReader GetDataReader(string sql, params DbParameter[] pas)
        {
            OleDbDataReader dr = null;
            try
            {
                cmd.CommandType = CommandType.Text;
                conn.Open();
                cmd.Parameters.Clear();

                foreach (OleDbParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                cmd.CommandText = sql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {


                return null;
            }
            return dr;
        }
        /// <summary>
        /// 事务处理函数
        /// </summary>
        /// <param name="al"></param>
        /// <returns></returns>
        public override bool GetTranState(ArrayList al)
        {
            cmd.CommandType = CommandType.Text;
            OpenConn();
            OleDbTransaction tran = (conn as OleDbConnection).BeginTransaction();
            cmd.Transaction = tran;
            try
            {
                for (int i = 0; i < al.Count; i++)
                {
                    cmd.CommandText = Convert.ToString(al[i]);
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
                CloseConn();
            }
            catch
            {

                tran.Rollback();
                CloseConn();
                return false;
            }
            return true;

        }
      
        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="order"></param>
        /// <param name="current"></param>
        /// <returns></returns>
        public override DataTable GetPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order)
        {
            current = current - 1 >= 0 ? (current - 1) : (0);
            string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
            return GetTable(sql);
          
        }
        /// <summary>
        /// 分页存储过程的调用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public override DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
        {
            OleDbParameter[] pas = { new OleDbParameter("@PageIndex", current), new OleDbParameter("@PageSize", pagesize), new OleDbParameter("@Columns", columns), new OleDbParameter("@Tablename", tablename), new OleDbParameter("@Where", where), new OleDbParameter("@Order", order), new OleDbParameter("@OrderType", ordertype), new OleDbParameter("@Pid", pid) };
            return GetProcTable("Pages", pas);
            //current = current - 1 >= 0 ? (current - 1) : (0);
            //string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
            //return GetTable(sql);
        }
        /// <summary>
        /// 分页存储过程的调用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public override DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {
            OleDbParameter[] pas = { new OleDbParameter("@TableNames", OleDbType.VarChar, 200), new OleDbParameter("@PrimaryKey", OleDbType.VarChar, 100), new OleDbParameter("@Order", OleDbType.VarChar, 200), new OleDbParameter("@CurrentPage", OleDbType.Integer), new OleDbParameter("@PageSize", OleDbType.Integer), new OleDbParameter("@Fields", OleDbType.VarChar, 800), new OleDbParameter("@Filter", OleDbType.VarChar, 1000), new OleDbParameter("@ResultCount", OleDbType.VarChar, 12), new OleDbParameter("@distinct", OleDbType.VarChar, 12) };
            pas[0].Value = tablename;
            pas[1].Value = pid;
            pas[2].Value = order;
            pas[3].Value = current;
            pas[4].Value = pagesize;
            pas[5].Value = columns;
            pas[6].Value = where;
            pas[7].Value = resultCount;
            pas[8].Value = distinct;
            return this.GetProcTable("Pages", pas);
      
        }
        /// <summary>
        /// 分页存储过程的调用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public override DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {
            OleDbParameter[] pas = { new OleDbParameter("@TableNames", OleDbType.VarChar, 200), new OleDbParameter("@PrimaryKey", OleDbType.VarChar, 100), new OleDbParameter("@Order", OleDbType.VarChar, 200), new OleDbParameter("@CurrentPage", OleDbType.Integer), new OleDbParameter("@PageSize", OleDbType.Integer), new OleDbParameter("@Fields", OleDbType.VarChar, 800), new OleDbParameter("@Filter", OleDbType.VarChar, 200), new OleDbParameter("@ResultCount", OleDbType.VarChar, 24), new OleDbParameter("@Distinct", OleDbType.VarChar, 12) };
            pas[0].Value = tablename;
            pas[1].Value = pid;
            pas[2].Value = order;
            pas[3].Value = current;
            pas[4].Value = pagesize;
            pas[5].Value = columns;
            pas[6].Value = where;
            pas[7].Value = resultCount;
            pas[8].Value = distinct;
            return this.GetProcTable("Pages", pas);

        }
        public override DbParameter[] MakeParameters(params string[] str)
        {
            OleDbParameter[] pas = new OleDbParameter[str.Length / 2];
            for (int i = 0; i < str.Length / 2; i++)
            {
                pas[i] = new OleDbParameter(string.Format("@{0}",str[2*i]),str[2*i+1]);
            }
            return pas;
        }
       
    }
}

iteye_20949
关注
专栏目录
Access数据库的简单封装
03-21
Access数据库的简单封装 Access数据库的简单封装 Access数据库的简单封装
ACCESS数据库访问类封装
周尚冬的专栏
07-22 1214
 using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using
Access数据库连接封装类
weixin_30776545的博客
09-16 142
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.OleDb; using System.Data.SqlClient; using System.Data; namespace AccessSQLHel...
VS 2019 MFC 通过ACE引擎连接并访问Access数据库类库封装
lzc881012的博客
06-11 1423
VS 2019 MFC 通过ACE引擎连接并访问Access数据库类库封装
为什么一个开源项目这么难 No.124
u010459192的博客
12-08 224
今天刚好群里有朋友在问，你们为什么开源了 Apache Dubbo，你们内部是不是有更好的闭源的实现吖？看过《淘宝技术这十年》的小伙伴都知道，内部其实很多部门都在使用一个...
ado_class.rar_ ADO_class_ACCESS 封装类_ADO数据库_ado封装类_数据库封装类
09-20
在给定的“ado_class.rar”压缩包中，包含了一个名为“ADO_class_ACCESS”的封装类，用于简化对Access数据库的操作。下面将详细解释ADO、封装类的概念以及如何利用这些文件进行数据库操作。 **ADO（ActiveX Data ...
C# ACCESS 数据库封装类 DBHELPER
01-26
C# ACCESS 数据库封装类全部封装完毕 大家 拿去用吧
C#数据库操作类AccessHelper实例
09-04
综上所述，AccessHelper是C#中处理Access数据库的一个实用工具，通过封装数据库连接和SQL执行，提供了方便且安全的数据库操作接口。在实际开发中，可以根据需要扩展这个类，添加更多功能，如参数化查询、存储过程...
ADO.zip_ado 封装类_ado封装类_数据库封装类
09-23
本篇将详细讲解标题为“ADO.zip_ado 封装类_ado封装类_数据库封装类”的压缩包所包含的ADO封装类，以及其在数据库连接中的应用。 首先，了解ADO的基本概念。ADO是.NET框架早期版本中的数据访问技术，它提供了与数据...
c#.NET万能数据库访问封装类(ACCESS、SQLServer、Oracle)
04-11
### c#.NET万能数据库访问封装类：ACCESS、SQLServer、Oracle 在现代软件开发中，数据持久化是至关重要的一个环节，而数据库操作作为数据持久化的基础，其效率和可维护性直接影响到整个应用程序的性能和稳定性。...
access生成EXE方法
09-16
access生成EXE方法。 access生成EXE方法
c++封装ADO连接各种数据库并进行操作，MySQL，SqlServer，Access， Oracle
08-05
适合初学数据库的小伙伴使用ADO用于连接和操作各种数据库
Access数据库转C++类
06-04
在用VC开发数据库程序中，经常用类来维护数据库中的表，重复性的劳动太多，写这个小程序的目的就是依据数据库初步生成类的模板文件。用于数据库程序的开发。文件提供了源代码！
Altium Designer 基于access数据库的集成库设计
12-16
Altium Designer 基于access数据库的集成库设计，Excel也类似。 基于数据库的集成库设计，可以很方便管理元器件，和导出待料号，单价等信息的BOM表。 具体详解见博客： https://blog.csdn.net/wybliw/article/details/85041494
access制作封装软件_制作exe安装程序图文教程，多个文件打包工具，软件封装发布程序...
weixin_39637920的博客
11-05 3668
你好，我是老盖，首先感谢你观看本文，本篇文章我做的有视频，视频讲述的比较详细，也可以看我发布的视频。制作exe安装文件，除了比较专业大型的软件，我们电脑上安装的压缩解压缩软件也带这个功能，所以今天我就用我电脑上安装的win rar制作一个exe自解压安装程序。首先我准备了几个文件放在桌面，文件名1和2和3作为，桌面这几个文件是用来测试准备打包制作的。我同时选择这三个文件，对着他们点右键选择添加到压...
access制作封装软件_可变数据软件如何批量生成可变datamatrix码
weixin_39850697的博客
10-28 342
众所周知，中琅可变数据软件是一款专业的可变数据打印软件，支持各种条码二维码标签的生成，重要的是支持可变数据打印，今天给大家介绍的就是用中琅可变数据软件批量生成可变datamatrix码。在中琅可变数据软件中新建一个标签，打开“数据库设置”，添加“Excel数据源”，通过数据库导入Excel表中的datamatrix码数据。利用中琅可变数据软件左侧工具栏中的“绘制二维码”工具，在白色画布上添加一个二...
通过ADO方法读写Access数据库封装好的类（4）
qq_16334327的博客
08-22 825
#pragma once #include "../stdafx.h" #include "DBSqlite3.h" class CDBAccess { public: CDBAccess(void); ~CDBAccess(void); bool OpenConnection(CString pathname); int OpenCommandSql(CString sql); /...
QT封装类实现ADO与ACCESS数据库交互操作
资源摘要信息:"本资源是一套QT框架下的封装类，主要功能是通过ADO（ActiveX Data Objects）技术实现对Microsoft Access数据库的操作。通过本封装类，开发者能够更加便捷地在使用QT编程时连接和操作Access数据库，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值