• 连接ACCESS数据库[原创]


    一般连接数据库的有好多种写法,现在只写两种,第二种感觉较好。
    第一:

     1       /// <summary>
     2        /// 获取ACCESS数据库连接参数
     3        /// 数据库的相对路径
     4        /// </summary>
     5        /// <returns></returns>

     6        public static OleDbConnection getConn()
     7        {
     8            try
     9            {
    10                string connstr = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + Application.StartupPath + @"\Resources\BOM_log.mdb";
    11                OleDbConnection tempconn = new OleDbConnection(connstr);
    12                return (tempconn);
    13            }

    14            catch (Exception ex)
    15            {
    16                MessageBox.Show("Error:\n\n"+ex.Message);
    17                return null;
    18            }

    19        }
     1/// <summary>
     2       /// 通过SQL语句得到数据集
     3        /// 参数是SQL语句
     4        /// </summary>
     5        /// <param name="GetSql"></param>
     6        /// <returns></returns>

     7        public DataView GetData(string GetSql)
     8        {
     9            DataView dataview;
    10            System.Data.DataSet dataset = new DataSet();
    11            try
    12            {
    13                OleDbConnection conn = getConn();
    14                OleDbDataAdapter adp = new OleDbDataAdapter();                
    15                adp.SelectCommand = new OleDbCommand(GetSql, conn);
    16                adp.Fill(dataset, "mytable");
    17                conn.Close();
    18            }

    19            catch (Exception ee)
    20            {
    21                MessageBox.Show("错误提示 : \n\n" + ee.Message);
    22            }

    23            dataview = new DataView(dataset.Tables["mytable"]);
    24            return dataview;
    25        }
     1       /// <summary>
     2        /// 通过SQL语句修改数据库
     3        /// </summary>
     4        /// <param name="UpdateSql"></param>
     5        /// <returns></returns>

     6        public bool UpdateData(string UpdateSql)
     7        {
     8            //在此判断配置文件参数是否为true,使用方便
     9            if (System.Configuration.ConfigurationManager.AppSettings["OperateOrNot"].ToString() == "true")
    10            {
    11                OleDbConnection conn = getConn();
    12                OleDbCommand mycmd = new OleDbCommand(UpdateSql, conn);
    13                try
    14                {
    15                    conn.Open();
    16                    mycmd.ExecuteNonQuery();
    17                    return true;
    18                }

    19                catch (Exception ex)
    20                {
    21                    throw (new Exception(ex.Message));
    22                    return false;
    23                }

    24                finally
    25                {
    26                    mycmd.Dispose();
    27                    conn.Close();
    28                }

    29            }

    30            else
    31            {
    32                return false ;  
    33            }

    34        }
     


    第二种:
      1using System;
      2using System.Collections;
      3using System.Collections.Generic;
      4using System.Configuration;
      5using System.Data;
      6using System.Data.Common;
      7using System.Data.OleDb;
      8using System.Text;
      9using System.Text.RegularExpressions;
     10using log4net;
     11
     12namespace AFC.BOM.Common.DB
     13{
     14    /// <summary>
     15    /// OLE DB数据库访问接口。
     16    /// </summary>

     17    public class DBA : IDisposable
     18    {
     19        private bool throwException = true ;
     20        private OleDbConnection conn ;
     21        
     22        private string dbName ;
     23        
     24        private ILog log;
     25        private OleDbTransaction txn;
     26
     27        /// <summary>
     28        /// 创建DBA对象。
     29        /// </summary>
     30        /// <param name="dbName">MDB文件的路径名称</param>

     31        public DBA(string dbName) : this (dbName, true)
     32        {
     33        }

     34
     35        public DBA(string dbName, bool throwException)
     36        {
     37            this.throwException = throwException;
     38            log = LogManager.GetLogger("DB::" + dbName);
     39            this.dbName = dbName;
     40            GetConnection();
     41        }

     42
     43        private int GetConnection ()
     44        {
     45            if (string.IsNullOrEmpty(dbName))
     46            {
     47                log.Error("dbName is NULL ot EMPTY.");
     48                return -1;
     49            }

     50            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + dbName;
     51
     52            log.Info("Connect to [" + connString + "].");
     53            try
     54            {
     55                conn = new OleDbConnection(connString);
     56                conn.Open();
     57            }

     58            catch (Exception e)
     59            {
     60                HandleOleError(e);
     61                log.Error("Open Database Error." + e);
     62                if (! throwException)
     63                    return -1;
     64                if (e is DbException)
     65                    throw e;
     66                else
     67                    throw new DBAException("Open Database Error.", e);
     68            }

     69
     70            log.Info("Database opened sucessfully.");
     71            return 0;
     72            
     73        }

     74        
     75        /// <summary>
     76        /// 启动一个事务。
     77        /// </summary>
     78        /// <returns></returns>

     79        public int StartTransaction ()
     80        {
     81            if (conn == null && GetConnection() != 0)
     82            {
     83                log.Error("Can't start transaction.");
     84                return -1;
     85            }

     86
     87            if (txn != null)
     88            {
     89                log.Warn("Previous transaction not completed, will rollback it.");
     90                txn.Rollback();
     91            }

     92            try
     93            {
     94                txn = conn.BeginTransaction();
     95            }

     96            catch (Exception e)
     97            {
     98                HandleOleError(e);
     99                log.Error("Start Transaction Error.", e);
    100                if (!throwException)
    101                    return -1;
    102                if (e is DbException)
    103                    throw e;
    104                else
    105                    throw new DBAException("Start Transaction Error.", e);
    106            }
                
    107            return 0;
    108        }

    109        
    110        /// <summary>
    111        /// 提交事务。
    112        /// </summary>
    113        /// <returns></returns>

    114        public int Commit ()
    115        {
    116            if (txn == null)
    117            {
    118                log.Error("Transaction not start yet.");
    119                return -1;
    120            }

    121            
    122            try
    123            {
    124                txn.Commit(); 
    125            }

    126            catch (Exception e)
    127            {
    128                HandleOleError(e);
    129                log.Error("Commit transaction Error." , e);
    130                if (!throwException)
    131                    return -1;
    132                if (e is DbException)
    133                    throw e;
    134                else
    135                    throw new DBAException("Commit transaction Error.", e);
    136            }

    137            finally
    138            {
    139                txn = null;
    140            }

    141            
    142            return 0;
    143        }

    144
    145
    146        /// <summary>
    147        /// 事务回滚。
    148        /// </summary>
    149        /// <returns></returns>

    150        public int Rollback()
    151        {
    152            if (txn == null)
    153            {
    154                log.Error("Transaction not start yet.");
    155                return -1;
    156            }

    157
    158            try
    159            {
    160                txn.Rollback();
    161            }

    162            catch (Exception e)
    163            {
    164                HandleOleError(e);
    165                log.Error("Rollback transaction Error.", e);
    166                if (!throwException)
    167                    return -1;
    168                if (e is DbException)
    169                    throw e;
    170                else
    171                    throw new DBAException("Rollback transaction Error.", e);
    172            }

    173            finally
    174            {
    175                txn = null;
    176            }

    177
    178            return 0;
    179        }

    180        
    181        /// <summary>
    182        /// 销毁连接对象。
    183        /// </summary>

    184        public void Dispose ()
    185        {
    186            if (conn == null)
    187            {
    188                log.Warn("Connection is NULL.");
    189                return;
    190            }

    191            try
    192            {
    193                conn.Close();
    194                conn.Dispose();
    195            }

    196            catch (Exception e)
    197            {
    198                HandleOleError(e);
    199                log.Error("Dispose Connection Error.", e);
    200                
    201            }
                
    202            log.Info("Connection Disposed.");
    203        }

    204
    205        /// <summary>
    206        /// 执行非查询语句。
    207        /// </summary>
    208        /// <param name="sqlString">SQL格式串</param>
    209        /// <param name="args">SQL参数</param>
    210        /// <returns></returns>

    211        public int ExecuteNonQuery(string sqlString, params object[] args)
    212        {
    213            sqlString = BuildSql(sqlString, args);
    214
    215            if (sqlString == null)
    216                return -1;
    217            return ExecuteNonQuery(sqlString);
    218        }

    219        
    220        /// <summary>
    221        /// 执行非查询语句。
    222        /// </summary>
    223        /// <param name="sqlString">SQL语句</param>
    224        /// <returns></returns>

    225        public int ExecuteNonQuery (string sqlString)
    226        {
    227            if (conn == null && GetConnection() != 0)
    228            {
    229                log.Error("Can't ExecuteNonQuery.");
    230                return -1;
    231            }

    232            
    233            if (log.IsDebugEnabled)
    234                log.Debug("Will ExecuteNonQuery (" + sqlString + ").");
    235            try
    236            {
    237                OleDbCommand cmd = conn.CreateCommand();
    238                
    239                using (cmd)
    240                {
    241                    if (txn != null)
    242                    {
    243                        cmd.Transaction = txn;
    244                    }

    245                    cmd.CommandText = sqlString;
    246                    cmd.Connection = conn;
    247                    int count = cmd.ExecuteNonQuery();
    248                    
    249                    log.Debug("ExecuteNonQuery effect row count: " + count);
    250                    return count;
    251                }

    252            }

    253            catch (Exception e)
    254            {
    255                HandleOleError(e);
    256                log.Error("ExecuteNonQuery[" + sqlString + "] Error.", e);
    257                if (!throwException)
    258                    return -1;
    259                if (e is DbException)
    260                    throw e;
    261                else
    262                    throw new DBAException("ExecuteNonQuery Error.", e);
    263            }

    264        }

    265        
    266        /// <summary>
    267        /// 执行查询语句,返回DataTable。
    268        /// </summary>
    269        /// <param name="sqlString">SQL格式串</param>
    270        /// <param name="args">SQL参数</param>
    271        /// <returns></returns>

    272        public DataTable ExecuteQuery (string sqlString, params object [] args)
    273        {
    274            sqlString = BuildSql(sqlString, args);
    275            
    276            if (sqlString == null)
    277                return null;
    278            return ExecuteQuery(sqlString);
    279        }

    280
    281        /// <summary>
    282        /// 执行查询语句,返回DataTable。
    283        /// </summary>
    284        /// <param name="sqlString">SQL语句</param>
    285        /// <returns></returns>

    286        public DataTable ExecuteQuery (string sqlString)
    287        {
    288            if (conn == null && GetConnection() != 0)
    289            {
    290                log.Error("Can't ExecuteQuery.");
    291                return null;
    292            }

    293            
    294            if (log.IsDebugEnabled)
    295                log.Debug("Will ExecuteQuery (" + sqlString + ").");
    296
    297            try
    298            {
    299                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlString, conn);
    300
    301                using (adapter)
    302                {
    303                    DataTable table = new DataTable("Table");
    304
    305                    adapter.Fill(table);
    306                    log.Debug("ExecuteQuery return table with rows: " + table.Rows.Count);
    307                    return table;
    308                }

    309            }

    310            catch (Exception e)
    311            {
    312                HandleOleError(e);
    313                log.Error("ExecuteQuery[" + sqlString + "] Error.", e);
    314                if (!throwException)
    315                    return null;
    316                if (e is DbException)
    317                    throw e;
    318                else
    319                    throw new DBAException("ExecuteQuery Error.", e);
    320            }

    321        }

    322
    323        /// <summary>
    324        /// 执行查询语句,返回DataSet。
    325        /// </summary>
    326        /// <param name="sqlString">SQL格式串</param>
    327        /// <param name="args">SQL参数</param>
    328        /// <returns></returns>

    329        public DataSet ExecuteDataSetQuery(string sqlString, params object[] args)
    330        {
    331            sqlString = BuildSql(sqlString, args);
    332
    333            if (sqlString == null)
    334                return null;
    335            return ExecuteDataSetQuery(sqlString);
    336        }

    337
    338        /// <summary>
    339        /// 执行查询语句,返回DataSet。
    340        /// </summary>
    341        /// <param name="sqlString">SQL格式串</param>
    342        /// <returns></returns>

    343        public DataSet ExecuteDataSetQuery (string sqlString)
    344        {
    345            DataTable table = ExecuteQuery(sqlString);
    346            
    347            if (table == null)
    348                return null;
    349            else
    350            {
    351                DataSet ds = new DataSet();
    352                
    353                ds.Tables.Add(table);
    354                return ds;
    355            }

    356        }

    357
    358        /// <summary>
    359        /// 执行标量查询语句。
    360        /// </summary>
    361        /// <param name="sqlString">SQL格式串</param>
    362        /// <param name="args">SQL参数</param>
    363        /// <returns></returns>

    364        public object ExecuteScalar(string sqlString, params object[] args)
    365        {
    366            sqlString = BuildSql(sqlString, args);
    367
    368            if (sqlString == null)
    369                return null;
    370            return ExecuteScalar(sqlString);
    371        }

    372
    373        /// <summary>
    374        /// 执行标量查询语句。
    375        /// </summary>
    376        /// <param name="sqlString">SQL语句</param>
    377        /// <returns></returns>

    378        public object ExecuteScalar (string sqlString)
    379        {
    380            if (conn == null && GetConnection() != 0)
    381            {
    382                log.Error("Can't ExecuteScalar.");
    383                return null;
    384            }

    385            
    386            if (log.IsDebugEnabled)
    387                log.Debug("Will ExecuteScalar (" + sqlString + ").");
    388            try
    389            {
    390                OleDbCommand cmd = conn.CreateCommand();
    391
    392                using (cmd)
    393                {
    394                    if (txn != null)
    395                    {
    396                        cmd.Transaction = txn;
    397                    }

    398                    cmd.CommandText = sqlString;
    399                    cmd.Connection = conn;
    400                    object ret = cmd.ExecuteScalar();
    401
    402                    log.Debug("ExecuteScalar return value: [" + ret + "].");
    403                    return ret;
    404                }

    405            }

    406            catch (Exception e)
    407            {
    408                HandleOleError(e);
    409                log.Error("ExecuteScalar[" + sqlString + "] Error.", e);
    410                if (!throwException)
    411                    return null;
    412                if (e is DbException)
    413                    throw e;
    414                else
    415                    throw new DBAException("ExecuteScalar Error.", e);
    416            }

    417        }

    418        
    419        /// <summary>
    420        /// 创建SQL语句
    421        /// </summary>
    422        /// <param name="sqlString"></param>
    423        /// <param name="args"></param>
    424        /// <returns></returns>

    425        public string BuildSql (string sqlString, params object [] args)
    426        {
    427            if (string.IsNullOrEmpty(sqlString))
    428            {
    429                log.Error("SqlString is NULL or EMPTY." , new ArgumentNullException("sqlString"));
    430                return null;
    431            }
                
    432            try
    433            {
    434                return string.Format(sqlString, args);
    435            }

    436            catch (Exception e)
    437            {
    438                log.Error("Format sql Error[" + sqlString + "]" , e);
    439                if (!throwException)
    440                    return null;
    441                if (e is DbException)
    442                    throw e;
    443                else
    444                    throw new DBAException("Format sql Erro.", e);
    445                
    446            }

    447        }

    448
    449        /// <summary>
    450        /// 执行参数化语句,仅用于非查询语句(兼容UD部分的SQL语句)。
    451        /// </summary>
    452        /// <param name="sqlString">SQL格式串</param>
    453        /// <param name="args">SQL参数</param>
    454        /// <returns></returns>

    455        public int ExecuteParamSql (string sqlString, SqlParamList args)
    456        {
    457            if (conn == null && GetConnection() != 0)
    458            {
    459                log.Error("Can't ExecuteParamSql.");
    460                return -1;
    461            }

    462            log.Debug("Will ExecuteParamSql [" + sqlString + "].");
    463            if (CheckParamSql (sqlString, args) != 0)
    464                return 0;
    465            
    466            try
    467            {
    468                OleDbCommand cmd = conn.CreateCommand();
    469
    470                using (cmd)
    471                {
    472                    if (txn != null)
    473                    {
    474                        cmd.Transaction = txn;
    475                    }

    476                    cmd.CommandText = sqlString;
    477                    cmd.Connection = conn;
    478
    479                    for (int i = 0; args != null && args.list != null && i < args.list.Count; i++)
    480                    {
    481                        SqlParam sqlParam = args.list[i];
    482
    483                        string key = sqlParam.name;
    484                        object value = sqlParam.value;
    485
    486                        if (log.IsDebugEnabled)
    487                            log.Debug("Param[" + i + "] Name[" + key + "], Type[" +
    488                                      (value == null ? "null" : value.GetType().ToString()) +
    489                                      "], Value[" + value + "].");
    490
    491                        OleDbParameter param = cmd.CreateParameter();
    492                        param.ParameterName = key;
    493                        param.OleDbType = GetDbType(value);
    494                        param.Value = value;
    495                        cmd.Parameters.Add(param);
    496                    }

    497
    498                    int count = cmd.ExecuteNonQuery();
    499                    log.Debug("ExecuteParamSql effect row count: " + count);
    500                    return count;
    501                }

    502            }

    503            catch (Exception e)
    504            {
    505                HandleOleError(e);
    506                log.Error("ExecuteParamSql[" + sqlString + "] Error.", e);
    507                if (!throwException)
    508                    return -1;
    509                if (e is DbException)
    510                    throw e;
    511                else
    512                    throw new DBAException("ExecuteParamSql Error.", e);
    513            }

    514        }

    515
    516        private int CheckParamSql (string sqlString, SqlParamList list)
    517        {
    518            if (! log.IsDebugEnabled)
    519                return 0;
    520
    521            Regex r = new Regex(@"@(?<x>[0-9a-zA-Z]*)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
    522            MatchCollection mc = r.Matches(sqlString);
    523            int err = 0;
    524            for (int i = 0; i < mc.Count; i++)
    525            {
    526                string paramName = mc[i].Result("$1");
    527                
    528                if (i >= list.list.Count)
    529                {
    530                    log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList.Count = " + list.list.Count + ".");
    531                    err ++;
    532                    continue;
    533                }

    534                
    535                if (paramName != list.list [i].name)
    536                {
    537                    log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList [" + i + "] = " + list.list[i].name + ".");
    538                    err++;
    539                    continue;
    540                }
                    
    541            }
                
    542            return err;
    543        }

    544        
    545        private OleDbType GetDbType(object value)
    546        {
    547            if (value == null)
    548                return OleDbType.Empty;
    549
    550            Type type = value.GetType();
    551            
    552            if (type.IsEnum)
    553                return OleDbType.Integer; 
    554            
    555            if (type == typeof (bool))
    556                return OleDbType.TinyInt;
    557            
    558            if (type == typeof (string))
    559                return OleDbType.VarChar;
    560            
    561            if (type == typeof (uint))    
    562                return OleDbType.UnsignedInt;
    563
    564            if (type == typeof(int))
    565                return OleDbType.Integer;
    566            
    567            if (type == typeof (byte []))
    568                return OleDbType.VarBinary;
    569            
    570            if (type == typeof (short))
    571                return OleDbType.SmallInt;
    572
    573            if (type == typeof(ushort))
    574                return OleDbType.UnsignedSmallInt;
    575            
    576            if (type == typeof (byte))
    577                return OleDbType.UnsignedTinyInt; 
    578                
    579            return OleDbType.IUnknown; 
    580        }

    581        
    582        private void HandleOleError (Exception e)
    583        {
    584            if (! (e is OleDbException))
    585            {
    586                return;
    587            }
                    
    588            
    589            OleDbException ex = (OleDbException) e;
    590
    591            string errorMessages = "OleDbException::" + ex.ErrorCode + "\n";
    592
    593            for (int i = 0; i < ex.Errors.Count; i++)
    594            {
    595                errorMessages += "\tIndex #" + i + "\n" +
    596                                 "\tMessage: " + ex.Errors[i].Message + "\n" +
    597                                 "\tNativeError: " + ex.Errors[i].NativeError + "\n" +
    598                                 "\tSource: " + ex.Errors[i].Source + "\n" +
    599                                 "\tSQLState: " + ex.Errors[i].SQLState + "\n";
    600            }

    601
    602            log.Error(errorMessages);
    603        }

    604    }

    605}

    606

     1using System;
     2using System.Collections.Generic;
     3using System.Data.Common;
     4using System.Text;
     5
     6namespace AFC.BOM.Common.DB
     7{
     8    public class DBAException : DbException
     9    {
    10        public DBAException(string message, Exception innerException) : base(message, innerException)
    11        {
    12        }

    13    }

    14}

    15

    在第二种中还有一些相关数据库操作,同时还有记录操作相关信息,是通过引用log4net来实现的,
  • 相关阅读:
    Spring Cloud微服务安全实战_5-4_认证服务器使用spring session
    Spring Cloud微服务安全实战_5-3_基于session的SSO
    Spring Cloud微服务安全实战_5-2_实现授权码认证流程&实现SSO初步
    Spring Cloud微服务安全实战_5-1_单点登录基本架构
    Linux启动Spring boot项目命令
    Spring Cloud微服务安全实战_4-10_用spring-cloud-zuul-ratelimit做限流
    Spring Cloud微服务安全实战_4-9_用zuul网关解耦安全逻辑和业务逻辑
    Spring Cloud微服务安全实战_4-8_网关安全_01
    Spring Cloud微服务安全实战_4-7_token&client信息持久化到数据库
    几个好用Maven 镜像仓库地址
  • 原文地址:https://www.cnblogs.com/winnxm/p/918635.html
Copyright © 2020-2023  润新知