.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("\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 }
/// <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语句组合成一个数组，依次进行执行即可。在执行过程中使用事务处理，当错误发生时，能够进行回滚操作。下面是完整代码：
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 }
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 }
本工具中还定义了两个自定义的异常类：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) { } } }
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 }
注意：
1.使用本代码时，需要配置config文件，需要添加AccessRuntimeConnectionString的ConnectionString节点，进行Access数据库配置，当然你也可以根据自己的需要进行调整。
2.在本工具中各个sql语句间使用/*go*/进行分隔，类似mssql中的go（批处理）一样。
