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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了.net(C#)在Access数据库中执行sql脚本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15207字，纯文字阅读大概需要22分钟。

内容图文

/// <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("\r\n", ""); 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("\r\n", "");
                    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("\r\n", "");
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("\r\n", "");
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>
        <span style="color: #


           
                本文系统来源：http://www.cnblogs.com/nozer1993/p/7398129.html                            

内容总结

以上是互联网集市为您收集整理的.net(C#)在Access数据库中执行sql脚本全部内容，希望文章能够帮你解决.net(C#)在Access数据库中执行sql脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---