Home Database Mysql Tutorial 连接Access数据库的DAL层操作代码

连接Access数据库的DAL层操作代码

Jun 07, 2016 pm 03:44 PM
access using code operate database connect

using System; using System.Data; using System.Configuration; using System.Web; using System.Collections; using System.Collections.Generic; using System.Text; using System.IO; using ACS = System.Data.OleDb; namespace DAL { public class Data

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using ACS = System.Data.OleDb;
namespace DAL
{
    public class DataAccess
    {
        //创建连接access数据库的字符串
        private string AccessCennection = null;

        #region 构造函数
        ///


        /// 构造函数创建连接字符串
        ///

        public DataAccess()
        {
            this.AccessCennection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\\Inetpub\\wwwroot\\biz\\App_Data\\ccc.mdb";//连接数据库字符串
        }
        #endregion

        #region 创建连接数据库的连接
        ///


        /// 创建连接数据库的连接
        ///

        /// 连接数据库的字符串
        /// 返回实体连接
        private ACS.OleDbConnection CreateACS_Connection(string acs_connection)
        {
            return new ACS.OleDbConnection(acs_connection);
        }
        #endregion

        #region 创建操纵数据库的实体命令
        ///


        /// 创建操纵数据库的实体命令
        ///

        /// 数据库操纵语言
        /// 数据库连接实体
        /// 数据库命令实体
        private ACS.OleDbCommand CreateACS_Command(string sql, ACS.OleDbConnection acs_connection)
        {
            return new ACS.OleDbCommand(sql, acs_connection);
        }
        #endregion

        #region 创建填充数据用的数据适配器
        ///


        /// 创建填充数据用的数据适配器
        ///

        /// 数据库命令实体
        /// 数据库适配器的实体
        private ACS.OleDbDataAdapter CreateACS_Adapter(ACS.OleDbCommand acs_command)
        {
            return new ACS.OleDbDataAdapter(acs_command);
        }
        #endregion

        #region 通过查询语句返回用户需要的结果
        ///


        /// 通过查询语句返回用户需要的结果
        ///

        /// 查询语句
        /// 返回的结果集
        public DataSet Select(string sql)
        {
            ACS.OleDbConnection acs_connection = null;
            ACS.OleDbCommand acs_command;
            ACS.OleDbDataAdapter acs_adapter;
            DataSet ds = new DataSet();
            try
            {
                acs_connection = CreateACS_Connection(this.AccessCennection);
                acs_command = CreateACS_Command(sql, acs_connection);
                acs_adapter = CreateACS_Adapter(acs_command);
                acs_connection.Open();
                acs_adapter.Fill(ds);
            }
            catch (Exception acse)
            {

            }
            finally
            {
                acs_connection.Close();
            }
            return ds;
        }
        #endregion

        #region 批处理执行SQL操作
        ///


        /// 批处理执行SQL操作
        ///

        /// SQL语句泛型类
        /// 是否成功
        public Boolean ExecuteSQL(List SqlStrings)
        {
            bool success = true;
            int Rows = 0;
            ACS.OleDbConnection acs_connection = null;
            acs_connection = CreateACS_Connection(this.AccessCennection);
            ACS.OleDbCommand acs_command = new System.Data.OleDb.OleDbCommand();
            acs_connection.Open();
            ACS.OleDbTransaction trans = acs_connection.BeginTransaction();
            acs_command.Connection = acs_connection;
            acs_command.Transaction = trans;
            try
            {
                for (int i = 0; i                 {
                    acs_command.CommandText = SqlStrings[i].ToString();
                    Rows += acs_command.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                acs_connection.Close();
            }
            if (Rows == 0)
                success = false;
            return success;
        }
        public Boolean ExecuteSQL(String SqlString)
        {
            List SqlStrings = new List();
            SqlStrings.Add(SqlString);
            return ExecuteSQL(SqlStrings);
        }
        #endregion
        #region 在一个数据表中插入一条记录
        ///
        /// 在一个数据表中插入一条记录
        ///

        /// 表名
        /// 哈希表,键值(Key)为字段名,值(value)为字段值
        /// 是否成功
        public bool Insert(String TableName, Hashtable Cols)
        {
            int Count = 0;//用于SQL语句创建
            int Rows = 0;//记录受影响的行数
            if (Cols.Count             {
                return true;
            }
            String Fields = "(";
            String Values = " Values('";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += "','";
                }
                Fields += item.Key.ToString();
                Values += item.Value.ToString();
                Count++;
            }
            Fields += ")";
            Values += "')";
            String SqlString = "insert into " + TableName + Fields + Values;
            return ExecuteSQL(SqlString);
        }
        #endregion
        #region 更新一个数据表的一条记录
        ///
        /// 更新一个数据表的一条记录
        ///

        /// 表名
        /// 哈希表,键值为字段名,值为字段值
        /// Where字句
        ///
        public Boolean Update(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;
            if (Cols.Count             {
                return true;
            }
            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                }
                Fields += item.Key.ToString();
                Fields += "=";
                Fields += item.Value.ToString();
            }
            Fields += " ";
            String SqlString = "update " + TableName + " set " + Fields + Where;
            return ExecuteSQL(SqlString);
        }
        #endregion
        #region 获取数据返回一个Dataset
        ///
        /// 获取数据返回一个Dataset
        ///

        /// Sql语句
        /// DataSet
        public DataSet GetDataSet(String SqlString)
        {
            ACS.OleDbConnection acs_connection = null;
            ACS.OleDbCommand acs_command;
            ACS.OleDbDataAdapter acs_adapter;
            DataSet ds = new DataSet();
            try
            {
                acs_connection = CreateACS_Connection(this.AccessCennection);
                acs_command = CreateACS_Command(SqlString, acs_connection);
                acs_adapter = CreateACS_Adapter(acs_command);
                acs_connection.Open();
                acs_adapter.Fill(ds);
            }
            catch (Exception acse)
            {
            }
            finally
            {
                acs_connection.Close();
            }
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds;
        }
        #endregion
        #region 根据用户所给的条件返回用户需要的数据
        ///
        /// 根据用户所给的条件返回用户需要的数据
        ///

        /// 表名
        /// 参数类型
        /// 参数值
        /// DataSet
        public DataSet Select(String TableName, List ParameterTypes, List ParameterValues)
        {
            StringBuilder sbSql = new StringBuilder("SELECT * FROM ");
            sbSql.Append(TableName);
            if (ParameterTypes[0] != null && ParameterValues[0] != null)
            {
                sbSql.Append(" WHERE ").Append(ParameterTypes[0]).Append(" ='").Append(ParameterValues[0]).Append("'");
                //如果还有参数就继续加
                for (int i = 1; i                 {
                    sbSql.Append(" and ").Append(ParameterTypes[i]).Append(" ='").Append(ParameterValues[i]).Append("'");
                }
            }
            String SqlString = sbSql.ToString();
            return GetDataSet(SqlString);
        }
        ///
        /// 根据用户所给的条件返回用户需要的数据(单参数)
        ///

        /// 表名
        /// 参数类型
        /// 参数值
        /// DataSet
        public DataSet Select(String TableName, String ParameterType, String ParameterValue)
        {
            List ParameterTypes = new List();
            List ParameterValues = new List();
            ParameterTypes.Add(ParameterType);
            ParameterValues.Add(ParameterValue);
            return Select(TableName, ParameterTypes, ParameterValues);
        }
        #endregion
        #region 获取数据,返回一个DataRow
        ///
        /// 获取数据,返回一个DataRow
        ///

        /// Sql语句
        /// DataRow
        public DataRow GetDataRow(String SqlString)
        {
            DataSet ds = GetDataSet(SqlString);
            ds.CaseSensitive = false;
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }
        ///
        /// 根据条件返回用户需要的数据
        ///

        /// 表名
        /// 参数类型
        /// 参数值
        /// DataRow
        public DataRow Select_DataRow(String TableName, List ParameterTypes, List ParameterValues)
        {
            DataSet ds = Select(TableName, ParameterTypes, ParameterValues);
            ds.CaseSensitive = false;
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        ///


        /// 根据用户给定的条件返回数据(单参数)
        ///

        /// 表名
        /// 参数类型
        /// 参数值
        /// DataRow
        public DataRow Select_DataRow(String TableName, String ParameterType, String ParameterValue)
        {
            List ParameterTypes = new List();
            List ParameterValues = new List();
            ParameterTypes.Add(ParameterType);
            ParameterValues.Add(ParameterValue);
            return Select_DataRow(TableName, ParameterTypes, ParameterValues);
        }
        #endregion
        #region 从一个DataRow中,安全得到colname中的值,值为字符串类型
        ///
        /// 从一个DataRow中,安全得到colname中的值,值为字符串类型
        ///

        /// 数据行对象
        /// 列名
        /// 如果值存在,返回;否则,返回System.String.Empty
        public static String ValidateDataRow_S(DataRow row, String colname)
        {
            if (row[colname] != DBNull.Value)
                return row[colname].ToString();
            else
                return System.String.Empty;
        }
        #endregion
    }
}

Statement of this Website
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

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to disable background applications in Windows 11_Windows 11 tutorial to disable background applications How to disable background applications in Windows 11_Windows 11 tutorial to disable background applications May 07, 2024 pm 04:20 PM

How to disable background applications in Windows 11_Windows 11 tutorial to disable background applications

How to convert deepseek pdf How to convert deepseek pdf Feb 19, 2025 pm 05:24 PM

How to convert deepseek pdf

How does the Java reflection mechanism modify the behavior of a class? How does the Java reflection mechanism modify the behavior of a class? May 03, 2024 pm 06:15 PM

How does the Java reflection mechanism modify the behavior of a class?

How to cross-domain iframe in vue How to cross-domain iframe in vue May 02, 2024 pm 10:48 PM

How to cross-domain iframe in vue

Common exception types and their repair measures in Java function development Common exception types and their repair measures in Java function development May 03, 2024 pm 02:09 PM

Common exception types and their repair measures in Java function development

Tsinghua University and Zhipu AI open source GLM-4: launching a new revolution in natural language processing Tsinghua University and Zhipu AI open source GLM-4: launching a new revolution in natural language processing Jun 12, 2024 pm 08:38 PM

Tsinghua University and Zhipu AI open source GLM-4: launching a new revolution in natural language processing

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

Detailed tutorial on establishing a database connection using MySQLi in PHP

How to read dbf file in oracle How to read dbf file in oracle May 10, 2024 am 01:27 AM

How to read dbf file in oracle

See all articles