search
HomeDatabaseMysql Tutorial用于Access数据库的DB操作类AccessHelper.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;namespace JonseTool{ public abstract class AccessHelper { // string s = Application.StartupPath.Replace(@\bin\Debug

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OleDb;

namespace JonseTool
{
   public abstract class AccessHelper
    {
            // string s = Application.StartupPath.Replace(@"\bin\Debug", "") + @"\Data\#test.mdb";
            ////JonseTool.AccessHelper.ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + s + @";Persist Security Info=false;User Id=admin;Password=";
            //JonseTool.AccessHelper.ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + s + @";";

        public static string _sConnString = string.Empty;
        public static string ConnString
        {
            get{return _sConnString;}
            set{_sConnString = value;}
        }

        public static bool TestConn(string sConnStr="")
        {
            OleDbConnection myConn = null;
            bool bResult = false;
            try
            {
                if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;
                myConn = new OleDbConnection(_sConnString);
                myConn.Open();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (myConn != null && myConn.State.ToString() == "Open")
                    bResult = true;
            }

            myConn.Close();

            return bResult;
        }

        public static DataTable GetDataTable(out string sErr, string sSQL,string sConnStr="",params OleDbParameter[] cmdParams)
        {
            DataTable dt = null;
            sErr = string.Empty;

            if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;

            OleDbConnection accConn = null;
            try
            {
                accConn = new OleDbConnection(sConnStr);
                OleDbCommand accCmd = new OleDbCommand(sSQL, accConn);
                accConn.Open();

                if (cmdParams != null)  
               {
                   foreach (OleDbParameter parm in cmdParams)
                       accCmd.Parameters.Add(parm);  
                }
 
                OleDbDataAdapter adapter = new OleDbDataAdapter(accCmd);
                dt = new DataTable();
                adapter.Fill(dt);
                accConn.Close();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return dt;
        }

        // 取dataset  
        public static DataSet GetDataSet(out string sError, string sSQL, string sConnStr = "", params OleDbParameter[] cmdParams)
        {
            DataSet ds = null;
            sError = string.Empty;
            try
            {
                if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;

                OleDbConnection conn = new OleDbConnection(sConnStr);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = sSQL;

                if (cmdParams != null)
                {
                    foreach (OleDbParameter parm in cmdParams)
                        cmd.Parameters.Add(parm);
                }

                OleDbDataAdapter dapter = new OleDbDataAdapter(cmd);
                ds = new DataSet();
                dapter.Fill(ds);
            }
            catch (Exception ex)
            {
                sError = ex.Message;
            }
            return ds;
        }

        // 取某个单一的元素   
        public static object GetSingle(out string sError, string sSQL,string sConnStr)
        {
            DataTable dt = GetDataTable(out sError, sSQL,sConnStr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }

            return null;
        }

        // 取最大的ID  
        public static Int32 GetMaxID(out string sError, string sKeyField, string sTableName,string sConnStr=null)
        {
            DataTable dt = GetDataTable(out sError, "select iif(isnull(max([" + sKeyField + "])),0,max([" + sKeyField + "])) as MaxID from [" + sTableName + "]", sConnStr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }

            return 0;
        }

        // 执行 insert,update,delete 动作,也可以使用事务  
        public static bool UpdateData(out string sError, string sSQL,string sConnStr="",OleDbParameter[] cmdParams = null, bool bUseTransaction = false)
        {
            int iResult = 0;
            sError = string.Empty;
            if (string.IsNullOrEmpty(sConnStr)) sConnStr = _sConnString;

            if (!bUseTransaction)
            {
                try
                {
                    OleDbConnection conn = new OleDbConnection(sConnStr);
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sSQL;

                    if (cmdParams != null)
                    {
                        foreach (OleDbParameter parm in cmdParams)
                            cmd.Parameters.Add(parm);
                    }

                    iResult = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    sError = ex.Message;
                    iResult = -1;
                }
            }
            else // 使用事务  
            {
                OleDbTransaction trans = null;
                try
                {
                    OleDbConnection conn = new OleDbConnection(sConnStr);
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    trans = conn.BeginTransaction();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sSQL;

                    if (cmdParams != null)
                    {
                        foreach (OleDbParameter parm in cmdParams)
                            cmd.Parameters.Add(parm);
                    }

                    cmd.Transaction = trans;
                    iResult = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    sError = ex.Message;
                    iResult = -1;
                    trans.Rollback();
                }
            }

            return iResult > 0;
        } 
 
    }
}


示意图:

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
access如何设置验证规则access如何设置验证规则Apr 10, 2024 am 10:59 AM

Access 验证规则是一种数据验证工具,用于确保数据符合特定条件,防止输入无效数据。设置验证规则的步骤:1. 选择要设置验证规则的字段;2. 打开“字段属性”对话框并切换到“查找”选项卡;3. 在“验证规则”字段中输入验证规则;4. 在“验证文本”字段中输入不符合规则时的错误消息;5. 单击“确定”保存更改。

microsoft access是什么软件microsoft access是什么软件Mar 03, 2023 am 11:37 AM

microsoft access是由微软发布的关系数据库管理系统;它结合了MicrosoftJet Database Engine和图形用户界面两项特点,是Microsoft Office的系统程序之一。

access数据库的扩展名是什么access数据库的扩展名是什么Apr 10, 2024 am 11:10 AM

Access 数据库文件的扩展名为 .accdb,自 Microsoft Access 2007 起开始使用,用于识别包含结构化数据的容器文件,如表、查询和窗体。

access中如何导入excel数据access中如何导入excel数据Apr 10, 2024 pm 01:44 PM

将 Excel 数据导入 Access 的步骤:1. 打开 Access 数据库。2. 创建链接的表,选择 Excel 工作簿。3. 选择 Excel 表格。4. 映射字段。5. 选择数据选项。6. 导入数据。7. 管理链接的数据。

vb中怎么连接access数据库vb中怎么连接access数据库Oct 09, 2023 am 11:38 AM

vb中连接access数据库的步骤包括引用必要的命名空间、创建连接字符串、创建连接对象、打开连接、执行SQL语句和关闭连接。详细介绍:1、引用必要的命名空间,在VB项目中,首先需要引用“System.Data`和`Microsoft.Office.Interop.Access”命名空间,以便使用ADO.NET和Access相关的类和方法,可以在VB项目的引用中添加这些命名等等。

access和trunk端口的区别是什么access和trunk端口的区别是什么Oct 31, 2023 pm 05:59 PM

access和trunk端口的区别:1、Access端口用于连接终端设备,提供单个VLAN的接入,而Trunk端口用于连接交换机之间,提供多个VLAN的传输;2、Access端口只传输属于指定VLAN的数据,而Trunk端口可以传输多个VLAN的数据,并使用VLAN标签进行区分。

access是什么软件access是什么软件Apr 10, 2024 am 10:55 AM

Microsoft Access 是一款关系型数据库管理系统 (RDBMS),用于存储、管理和分析数据。它主要用于数据管理、导入/导出、查询/报表生成、用户界面设计和应用程序开发。Access 优势包括易用性、集成数据库管理、强大灵活、与 Office 集成和可扩展性。

access数据库有什么功能access数据库有什么功能Apr 10, 2024 pm 12:29 PM

Microsoft Access 是一款用于创建、管理和查询数据库的关系型数据库管理系统,提供以下功能:数据存储和管理数据查询和检索表单和报表创建数据分析和可视化关系数据库管理自动化和宏多用户支持数据库安全可移植性

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.