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

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
    }
}