.net(C#)在Access数据库中执行sql脚本

自己写的一个工具类，主要是业务场景的需要。

主要有两个功能：

调用方式

 1 /// <summary>
 2         /// 执行sql语句
 3         /// </summary>
 4         /// <param name="sql">需要执行的sql语句</param>
 5         public bool ExecuteSql(string sql, ref string errorMsg)
 6         {
 7             SetConnOpen();
 8             string[] sqls = serializeSql(sql);
 9             OleDbTransaction tran = conn.BeginTransaction();
10             try
11             {
12                 comm = new OleDbCommand();
13                 comm.Transaction = tran;
14                 comm.Connection = conn;
15                 foreach (string s in sqls)
16                 {
17                     var temps = s.Trim().Replace("
", "");
18                     if (!string.IsNullOrEmpty(temps))
19                     {
20                         comm.CommandText = temps;
21                         comm.ExecuteNonQuery();
22                     }
23                 }
24                 tran.Commit();
25                 return true;
26             }
27             catch(Exception ex)
28             {
29                 tran.Rollback();
30                 errorMsg = ex.Message;
31                 return false;
32             }
33             finally
34             {
35                 conn.Close();
36             }
37         }

执行包含sql语句的字符串

/// <summary>
        /// 从sql脚本文件执行
        /// </summary>
        /// <param name="sqlFilePath">sql脚本文件的路径</param>
        /// <returns></returns>
        public bool ExecuteSqlByFile(string sqlFilePath,ref string errorMsg)
        {
            if(!File.Exists(sqlFilePath))
            {
                throw new FileNotFoundException("未找到该sql脚本，请检查路径是否错误");
            }

            string sourceSql = new StreamReader(sqlFilePath).ReadToEnd();
            string[] sqls = serializeSql(sourceSql);
            SetConnOpen();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                comm = new OleDbCommand();
                comm.Transaction = tran;
                comm.Connection = conn;
                foreach (string s in sqls)
                {
                    var temps = s.Trim().Replace("
", "");
                    if (!string.IsNullOrEmpty(temps))
                    {
                        comm.CommandText = temps;
                        comm.ExecuteNonQuery();
                    }
                }
                tran.Commit();
                return true;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                errorMsg = ex.Message;
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 将sql脚本进行序列化
        /// </summary>
        /// <param name="sql">sql脚本</param>
        /// <returns></returns>
        private string[] serializeSql(string sql)
        {
            string[] ss = sql.Split(new string[1] { "/*go*/" }, StringSplitOptions.RemoveEmptyEntries);
            return ss;
        }

执行包含sql语句的文件

其实思路比较简单，就是将sql语句用字符串进行分割，然后将一条条sql语句组合成一个数组，依次进行执行即可。在执行过程中使用事务处理，当错误发生时，能够进行回滚操作。下面是完整代码：

AccessUtils.cs

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Configuration;
  6 using System.Data.OleDb;
  7 using System.Data.OleDb;
  8 using System.IO;
  9 using System.Data;
 10 
 11 namespace AccessRuntime.Bin
 12 {
 13     /// <summary>
 14     /// Access工具类
 15     /// </summary>
 16     internal sealed class AccessUtils
 17     {
 18         /// <summary>
 19         /// access数据库连接字符串
 20         /// </summary>
 21         private  string accessConnectionString = string.Empty;
 22         /// <summary>
 23         /// access数据库连接对象
 24         /// </summary>
 25         private  OleDbConnection conn;
 26         /// <summary>
 27         /// access数据库命令对象
 28         /// </summary>
 29         private OleDbCommand comm;
 30         /// <summary>
 31         /// access数据库连接字符串
 32         /// </summary>
 33         public  string AccessConnectionString
 34         {
 35             get {
 36                 if (!string.IsNullOrEmpty(accessConnectionString))
 37                     return accessConnectionString;
 38                 else
 39                 {
 40                     string connstr = ConfigurationManager.ConnectionStrings["AccessRuntimeConnectionString"].ConnectionString;
 41                     if (string.IsNullOrEmpty(connstr))
 42                         throw new ConnectionStringElementNotFindException("未找到或未设置AccessRuntimeConnectionString节点");
 43                     else
 44                         return connstr;
 45                 }
 46             }
 47         }
 48         /// <summary>
 49         /// 初始化连接（有密码）
 50         /// </summary>
 51         /// <param name="filepath">可以为空，为空则调用配置文件</param>
 52         /// <param name="pwd">数据库密码</param>
 53         /// <example>
 54         /// public AccessUtils("123",null)
 55         /// </example>
 56         public AccessUtils(string pwd,string filepath)
 57         {
 58             if (string.IsNullOrEmpty(filepath))
 59             {
 60                 filepath = AccessConnectionString;
 61             }
 62             this.conn = new OleDbConnection(filepath + "; Jet OLEDB:Database Password=" + pwd);
 63             conn.Open();
 64         }
 65 
 66         /// <summary>
 67         /// 初始化连接（无密码）
 68         /// </summary>
 69         /// <param name="filepath"></param>
 70         /// <example>
 71         /// 1.public AccessUtils(filepath)
 72         /// 2.public AccessUtils()//不传递参数则调用配置文件
 73         /// </example>
 74         public AccessUtils(string filepath = null)
 75         {
 76             if (string.IsNullOrEmpty(filepath))
 77             {
 78                 filepath = AccessConnectionString;
 79             }
 80             this.conn = new OleDbConnection(filepath);
 81             conn.Open();
 82         }
 83 
 84         /// <summary>
 85         /// 执行sql语句
 86         /// </summary>
 87         /// <param name="sql">需要执行的sql语句</param>
 88         public bool ExecuteSql(string sql, ref string errorMsg)
 89         {
 90             SetConnOpen();
 91             string[] sqls = serializeSql(sql);
 92             OleDbTransaction tran = conn.BeginTransaction();
 93             try
 94             {
 95                 comm = new OleDbCommand();
 96                 comm.Transaction = tran;
 97                 comm.Connection = conn;
 98                 foreach (string s in sqls)
 99                 {
100                     var temps = s.Trim().Replace("
", "");
101                     if (!string.IsNullOrEmpty(temps))
102                     {
103                         comm.CommandText = temps;
104                         comm.ExecuteNonQuery();
105                     }
106                 }
107                 tran.Commit();
108                 return true;
109             }
110             catch(Exception ex)
111             {
112                 tran.Rollback();
113                 errorMsg = ex.Message;
114                 return false;
115             }
116             finally
117             {
118                 conn.Close();
119             }
120         }
121         /// <summary>
122         /// 从sql脚本文件执行
123         /// </summary>
124         /// <param name="sqlFilePath">sql脚本文件的路径</param>
125         /// <returns></returns>
126         public bool ExecuteSqlByFile(string sqlFilePath,ref string errorMsg)
127         {
128             if(!File.Exists(sqlFilePath))
129             {
130                 throw new FileNotFoundException("未找到该sql脚本，请检查路径是否错误");
131             }
132 
133             string sourceSql = new StreamReader(sqlFilePath).ReadToEnd();
134             string[] sqls = serializeSql(sourceSql);
135             SetConnOpen();
136             OleDbTransaction tran = conn.BeginTransaction();
137             try
138             {
139                 comm = new OleDbCommand();
140                 comm.Transaction = tran;
141                 comm.Connection = conn;
142                 foreach (string s in sqls)
143                 {
144                     var temps = s.Trim().Replace("
", "");
145                     if (!string.IsNullOrEmpty(temps))
146                     {
147                         comm.CommandText = temps;
148                         comm.ExecuteNonQuery();
149                     }
150                 }
151                 tran.Commit();
152                 return true;
153             }
154             catch (Exception ex)
155             {
156                 tran.Rollback();
157                 errorMsg = ex.Message;
158                 return false;
159             }
160             finally
161             {
162                 conn.Close();
163             }
164         }
165         /// <summary>
166         /// 将sql脚本进行序列化
167         /// </summary>
168         /// <param name="sql">sql脚本</param>
169         /// <returns></returns>
170         private string[] serializeSql(string sql)
171         {
172             string[] ss = sql.Split(new string[1] { "/*go*/" }, StringSplitOptions.RemoveEmptyEntries);
173             return ss;
174         }
175         /// <summary>
176         /// 获取打开的连接
177         /// </summary>
178         private void SetConnOpen()
179         {
180             if (this.conn.State != ConnectionState.Open)
181             {
182                 this.conn.Open();
183             }
184         }
185     }
186 }

AccessUtils.cs

AccessTool.cs 这个是对AccessUtils类的封装，提供了更加友好的方法。

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 
  6 namespace AccessRuntime.Bin
  7 {
  8     /// <summary>
  9     /// Access工具
 10     /// 注意：语句之间使用 /*go*/ 进行分割
 11     /// </summary>
 12     public static class AccessTool
 13     {
 14         /// <summary>
 15         /// 在Access数据库中执行sql语句
 16         /// </summary>
 17         /// <param name="sql">sql脚本</param>
 18         /// <param name="pwd">数据库密码（如果无密码则不填写此参数）</param>
 19         /// <returns>执行结果</returns>
 20         public static bool ExecuteSql(string sql,string pwd = null)
 21         {
 22             AccessUtils au = null;
 23             if (string.IsNullOrEmpty(pwd)) {
 24                 au = new AccessUtils();
 25                 string msg = null;
 26                 if(au.ExecuteSql(sql, ref msg))
 27                 {
 28                     return true;
 29                 }
 30                 else
 31                 {
 32                     throw new AccessRuntimeException(msg);
 33                 }
 34             }
 35             else
 36             {
 37                 au = new AccessUtils(pwd, null);
 38                 string msg = null;
 39                 if(au.ExecuteSql(sql,ref msg))
 40                 {
 41                     return true;
 42                 }
 43                 else
 44                 {
 45                     throw new AccessRuntimeException(msg);
 46                 }
 47             }
 48             
 49         }
 50 
 51         /// <summary>
 52         /// 在Access数据库中执行sql脚本
 53         /// </summary>
 54         /// <param name="sqlpath">sql脚本路径</param>
 55         /// <param name="pwd">数据库密码（如果无密码则不填写此参数）</param>
 56         /// <returns>执行结果</returns>
 57         public static bool ExecuteSqlByFile(string sqlpath,string pwd = null)
 58         {
 59             AccessUtils au = null;
 60             //判断密码是否填写
 61             if (string.IsNullOrEmpty(pwd))
 62             {
 63                 au = new AccessUtils();
 64                 string msg = null;
 65                 if (au.ExecuteSqlByFile(sqlpath, ref msg))
 66                 {
 67                     return true;
 68                 }
 69                 else
 70                 {
 71                     throw new AccessRuntimeException(msg);
 72                 }
 73             }
 74             else
 75             {
 76                 au = new AccessUtils(pwd, null);
 77                 string msg = null;
 78                 if (au.ExecuteSqlByFile(sqlpath, ref msg))
 79                 {
 80                     return true;
 81                 }
 82                 else
 83                 {
 84                     throw new AccessRuntimeException(msg);
 85                 }
 86             }
 87         }
 88 
 89         /// <summary>
 90         /// 在指定Access数据库中执行sql语句
 91         /// </summary>
 92         /// <param name="sql">sql脚本</param>
 93         /// <param name="dbpath">数据库所在路径</param>
 94         /// <param name="pwd">执行结果</param>
 95         /// <returns></returns>
 96         public static bool OnExecuteSql(string sql,string dbpath,string pwd = null)
 97         {
 98             AccessUtils au = null;
 99             if (string.IsNullOrEmpty(pwd))
100             {
101                 au = new AccessUtils(dbpath);
102                 string msg = null;
103                 if (au.ExecuteSql(sql, ref msg))
104                 {
105                     return true;
106                 }
107                 else
108                 {
109                     throw new AccessRuntimeException(msg);
110                 }
111             }
112             else
113             {
114                 au = new AccessUtils(pwd, dbpath);
115                 string msg = null;
116                 if (au.ExecuteSql(sql, ref msg))
117                 {
118                     return true;
119                 }
120                 else
121                 {
122                     throw new AccessRuntimeException(msg);
123                 }
124             }
125 
126         }
127 
128         /// <summary>
129         /// 在指定Access数据库中执行sql语句
130         /// </summary>
131         /// <param name="sqlpath">sql脚本路径</param>
132         /// <param name="dbpath">数据库所在路径</param>
133         /// <param name="pwd">执行结果</param>
134         /// <returns></returns>
135         public static bool OnExecuteSqlByFile(string sqlpath, string dbpath, string pwd = null)
136         {
137             AccessUtils au = null;
138             //判断密码是否填写
139             if (string.IsNullOrEmpty(pwd))
140             {
141                 au = new AccessUtils(dbpath);
142                 string msg = null;
143                 if (au.ExecuteSqlByFile(sqlpath, ref msg))
144                 {
145                     return true;
146                 }
147                 else
148                 {
149                     throw new AccessRuntimeException(msg);
150                 }
151             }
152             else
153             {
154                 au = new AccessUtils(pwd, dbpath);
155                 string msg = null;
156                 if (au.ExecuteSqlByFile(sqlpath, ref msg))
157                 {
158                     return true;
159                 }
160                 else
161                 {
162                     throw new AccessRuntimeException(msg);
163                 }
164             }
165         }
166     }
167 }

AccessToo.cs

本工具中还定义了两个自定义的异常类：AccessRuntimeException.cs，ConnectionStringElementNotFindException.cs，下付代码：

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

namespace AccessRuntime.Bin
{
    /// <summary>
    /// AccessRuntime异常
    /// </summary>
    public class AccessRuntimeException:Exception
    {
        /// <summary>
        /// 配置文件节点未找到
        /// </summary>
        public AccessRuntimeException()
        {

        }
        /// <summary>
        /// 配置文件节点未找到
        /// </summary>
        /// <param name="message">异常信息</param>
        public AccessRuntimeException(string message):base(message)
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="message">异常信息</param>
        /// <param name="inner">异常类</param>
        public AccessRuntimeException(string message, Exception inner)
        : base(message, inner)
        { }
    }
}

AccessRuntimeException.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace AccessRuntime.Bin
 7 {
 8     /// <summary>
 9     /// 配置文件节点未找到
10     /// </summary>
11     internal class ConnectionStringElementNotFindException:Exception
12     {
13         /// <summary>
14         /// 配置文件节点未找到
15         /// </summary>
16         public ConnectionStringElementNotFindException()
17         {
18 
19         }
20         /// <summary>
21         /// 配置文件节点未找到
22         /// </summary>
23         /// <param name="message">异常信息</param>
24         public ConnectionStringElementNotFindException(string message):base(message)
25         {
26            
27         }
28         /// <summary>
29         /// 
30         /// </summary>
31         /// <param name="message">异常信息</param>
32         /// <param name="inner">异常类</param>
33         public ConnectionStringElementNotFindException(string message, Exception inner)
34         : base(message, inner)
35         { }
36     }
37 }

ConnectionStringElementNotFindException.cs

注意：

1.使用本代码时，需要配置config文件，需要添加AccessRuntimeConnectionString的ConnectionString节点，进行Access数据库配置，当然你也可以根据自己的需要进行调整。

2.在本工具中各个sql语句间使用/*go*/进行分隔，类似mssql中的go（批处理）一样。

相关阅读:
原生JS实现new方法、new一个对象发生的四部、new里面常用的优先级
 svg image标签降级技术
 ReflectionToStringBuilder使用
 记一次未解决的异常：java.lang.NoClassDefFoundError: net/sf/json/JSONObject
eclipse安装Run-Jetty-Run插件，修改实时生效
 jdbcTemplate：包含占位符的SQL无法打印参数信息
 jdbcTemplate异常：like模糊查询报错(Parameter index out of range (1 > number of parameters)
Spring整合MyBatis
springmvc整合slf4j、log4j记录文本日志
 Java环境配置
原文地址：https://www.cnblogs.com/nozer1993/p/7398129.html