天气渐冷,闲来无事就把业余时间自己使用的数据访问库凉一凉.这个库本人自己使用了2年多，主要用于个人学习时需要操作数据库时使用,非组织和商业性质的使用.记得上学的时候，在网络上看到SqlServerHelper操作ADO.NET甚方便，一下子就着了迷不管学习还是工作都会带上这个Class，甚至我的U盘里面也会存上这个Class，哈哈.随着工作经验和沿街的开阔，我渐渐的有了自己的ADO.NET操作习惯以及封装使用方式.因此就写了Basic.Data.dll,言归正传.
1.Database:主要用于存储数据连接字符串，配置节名称，ADO.NET数据基础工厂,Database由IDatabaseProvide接口提供.
2.DatabaseConnection:用于数据库连接，和数据库连接状态等提供者,主要由IDatabaseConnectionProvide接口提供.
3.DatabaseCommandText,DatabaseCommandProcedure:用于执行Command和Procedure操作.分别由IDatabaseCommandTextAsyncProvide，IDatabaseCommandProcedure接口提供.
4.DatabaseCommandTextAsync,DatabaseCommandProcedureAsync:用于异步执行Command和Procedure操作.分别由IDatabaseCommandTextAsyncProvide和IDatabaseCommandProcedureAsyncProvide接口提供.
5.DatabaseOperation:是过程执行SQL操作的封装.这个会提供代码演示.
6.OperationValue:所有Command操作返回的结果，存储在该类的Value属性上.
*******************************************************************************************************************************
Database:
1 public class Database : IDatabaseProvide 2 { 3 /// <summary> 4 /// 内置默认识别Config Key:DatabaseProvide和DatabaseProvideConnectionString 5 /// </summary> 6 public Database() 7 { 8 this.DatabaseProvideName = ConfigurationManager.AppSettings["DatabaseProvide"]; 9 this.ConnectionString = ConfigurationManager.AppSettings["DatabaseProvideConnectionString"];10 this.Initialization();11 }12 13 public Database(string argument_ProvideName)14 {15 this.DatabaseProvideName = argument_ProvideName;16 this.ConnectionString = ConfigurationManager.AppSettings["DatabaseProvideConnectionString"];17 this.Initialization();18 }19 20 public Database(string argument_ProvideName, string argument_ConnectionString)21 {22 this.DatabaseProvideName = argument_ProvideName;23 this.ConnectionString = argument_ConnectionString;24 this.Initialization();25 }26 27 public string DatabaseProvideName { get; set; }28 internal string ConnectionString { get; set; }29 internal DbProviderFactory Factory { get; set; }30 31 public void Initialization()32 {33 this.Factory = DbProviderFactories.GetFactory(this.DatabaseProvideName);34 }35 }
数据连接配置只需要在Config配置文件上配置如下：
<appSettings>
<add key="DatabaseProvide" value="System.Data.SqlClient"/>
<add key="DatabaseProvideConnectionString" value="Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=;"/>
<!--<add key="DatabaseProvide_ODBC" value="System.Data.Odbc"/>-->
<!--<add key="DatabaseProvideConnectionString" value="DRIVER={SQL SERVER};SERVER=127.0.0.1;DATABASE=TestDB;UID=sa;PWD=;"/>-->
</appSettings>
Database分别提供了三个构造方式，第一种是默认走配置创建(key:DatabaseProvide,DatabaseProvideConnectionString必须).第二种提供ProvideName配置名称,这个构造主要一般可能使用的ODBC驱动等连接方式，但DatabaseProvideConnectionString必须指定.第三种构造主要自己实现的Provide和连接.这个我没有提供相应的扩展,需要自己去敲定.这样Database引擎就创建出来.提供的接口也很简单:
1 public interface IDatabaseProvide2 {3 void Initialization();4 }
DatabaseConnection:
1 public sealed class DatabaseConnection : IDatabaseConnectionProvide 2 { 3 public DatabaseConnection(Database argument_Provide) 4 { 5 this.Initialization(argument_Provide); 6 } 7 8 internal DbConnection Connection { get; private set; } 9 internal Database DatabaseProvide10 {11 get; set;12 }13 14 public ConnectionState DatabaseConnectionState15 {16 get17 {18 return this.Connection.State;19 }20 }21 22 private void Initialization(Database argument_DatabaseProvide)23 {24 if (null == this.Connection)25 {26 this.DatabaseProvide = argument_DatabaseProvide;27 this.Connection = this.DatabaseProvide.Factory.CreateConnection();28 this.Connection.ConnectionString = this.DatabaseProvide.ConnectionString;29 }30 31 this.Initialization();32 }33 34 public void Initialization() //初始化默认执行过一次35 {36 this.Open();37 }38 39 public void Open()40 {41 if (ConnectionState.Closed == this.Connection.State)42 {43 this.Connection.Open();44 }45 }46 47 public void Change(string argument_DatabaseName)48 {49 if (null != this.DatabaseProvide && null != this.Connection)50 {51 this.Connection.ChangeDatabase(argument_DatabaseName);52 this.DatabaseProvide.ConnectionString = this.Connection.ConnectionString;53 }54 }55 }
它只有一个构造，必须提供Database,因为Connection需要Database来提供创建.提供了如下操作：
1 public interface IDatabaseConnectionProvide2 {3 void Initialization();4 void Change(string argument_DatabaseName);5 void Open();6 }
DatabaseCommandText 和 DatabaseCommandProcedure:
1 public sealed class DatabaseCommandText : IDatabaseCommandTextProvide 2 { 3 public DatabaseCommandText(DatabaseConnection argument_Connection) 4 { 5 this.Connection = argument_Connection; 6 } 7 8 public DatabaseConnection Connection { get; private set; } 9 public string CommandName { get; set; } 10 11 public DbParameter NewParameter() 12 { 13 return this.Connection.DatabaseProvide.Factory.CreateParameter(); 14 } 15 16 public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction) 17 { 18 DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter(); 19 _Parameter.ParameterName = argument_ParameterName; 20 _Parameter.Value = argument_Value; 21 _Parameter.DbType = argument_DbType; 22 _Parameter.Direction = Direction; 23 return _Parameter; 24 } 25 26 public OperationValue TransactionDelete(string argument_SqlText, DbParameter[] argument_Parameters) 27 { 28 int _CommandResult = 0; 29 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 30 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction(); 31 _Command.Connection = this.Connection.Connection; 32 _Command.Transaction = _Transaction; 33 _Command.CommandText = argument_SqlText; 34 _Command.CommandType = CommandType.Text; 35 if (null != argument_Parameters) 36 { 37 _Command.Parameters.AddRange(argument_Parameters); 38 } 39 40 try 41 { 42 this.Connection.Open(); 43 _CommandResult = _Command.ExecuteNonQuery(); 44 _Transaction.Commit(); 45 } 46 catch (Exception) 47 { 48 _Transaction.Rollback(); 49 } 50 51 OperationValue _Value = new OperationValue(); 52 _Value.Value = _CommandResult; 53 54 return _Value; 55 } 56 57 public OperationValue Delete(string argument_SqlText, DbParameter[] argument_Parameters) 58 { 59 int _CommandResult = 0; 60 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 61 _Command.Connection = this.Connection.Connection; 62 _Command.CommandText = argument_SqlText; 63 _Command.CommandType = CommandType.Text; 64 if (null != argument_Parameters) 65 { 66 _Command.Parameters.AddRange(argument_Parameters); 67 } 68 69 try 70 { 71 this.Connection.Open(); 72 _CommandResult = _Command.ExecuteNonQuery(); 73 } 74 catch (Exception) 75 { 76 } 77 78 OperationValue _Value = new OperationValue(); 79 _Value.Value = _CommandResult; 80 81 return _Value; 82 } 83 84 public OperationValue Delete(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters) 85 { 86 this.Connection.Open(); 87 int _CommandResult = 0; 88 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 89 DbTransaction _Transaction = argument_Transaction; 90 _Command.Connection = this.Connection.Connection; 91 _Command.Transaction = _Transaction; 92 _Command.CommandText = argument_SqlText; 93 _Command.CommandType = CommandType.Text; 94 if (null != argument_Parameters) 95 { 96 _Command.Parameters.AddRange(argument_Parameters); 97 } 98 99 try100 {101 _CommandResult = _Command.ExecuteNonQuery();102 //_Transaction.Commit();103 }104 catch (Exception)105 {106 _Transaction.Rollback();107 }108 109 OperationValue _Value = new OperationValue();110 _Value.Value = _CommandResult;111 112 return _Value;113 }114 115 public DataTableReader Reader(string argument_SqlText, DbParameter[] argument_Parameters)116 {117 this.Connection.Open();118 DataTable _QueryTable = new DataTable();119 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();120 _Command.Connection = this.Connection.Connection;121 _Command.CommandText = argument_SqlText;122 _Command.CommandType = CommandType.Text;123 if (null != argument_Parameters)124 {125 _Command.Parameters.AddRange(argument_Parameters);126 }127 128 DbDataReader _Reader = _Command.ExecuteReader();129 _QueryTable.Load(_Reader);130 return _QueryTable.CreateDataReader();131 }132 133 public OperationValue Query(string argument_SqlText, DbParameter[] argument_Parameters)134 {135 this.Connection.Open();136 DataTable _QueryTable = new DataTable();137 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();138 _Command.Connection = this.Connection.Connection;139 _Command.CommandText = argument_SqlText;140 _Command.CommandType = CommandType.Text;141 if (null != argument_Parameters)142 {143 _Command.Parameters.AddRange(argument_Parameters);144 }145 146 DbDataReader _Reader = _Command.ExecuteReader();147 _QueryTable.Load(_Reader);148 _Reader.Close();149 OperationValue _Value = new OperationValue();150 _Value.Value = _QueryTable;151 return _Value;152 }153 154 public OperationValue Query(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)155 {156 this.Connection.Open();157 DataTable _QueryTable = new DataTable();158 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();159 _Command.Connection = this.Connection.Connection;160 _Command.Transaction = argument_Transaction;161 _Command.CommandText = argument_SqlText;162 _Command.CommandType = CommandType.Text;163 if (null != argument_Parameters)164 {165 _Command.Parameters.AddRange(argument_Parameters);166 }167 168 DbDataReader _Reader = _Command.ExecuteReader();169 _QueryTable.Load(_Reader);170 _Reader.Close();171 OperationValue _Value = new OperationValue();172 _Value.Value = _QueryTable;173 return _Value;174 }175 176 public OperationValue TransactionSave(string argument_SqlText, DbParameter[] argument_Parameters)177 {178 this.Connection.Open();179 int _CommandResult = 0;180 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();181 _Command.Connection = this.Connection.Connection;182 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();183 _Command.Transaction = _Transaction;184 _Command.CommandText = argument_SqlText;185 _Command.CommandType = CommandType.Text;186 if (null != argument_Parameters)187 {188 _Command.Parameters.AddRange(argument_Parameters);189 }190 191 try192 {193 _CommandResult = _Command.ExecuteNonQuery();194 _Transaction.Commit();195 }196 catch (Exception)197 {198 _Transaction.Rollback();199 }200 201 OperationValue _Value = new OperationValue();202 _Value.Value = _CommandResult;203 return _Value;204 }205 206 public OperationValue Save(string argument_SqlText, DbParameter[] argument_Parameters)207 {208 this.Connection.Open();209 int _CommandResult = 0;210 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();211 _Command.Connection = this.Connection.Connection;212 _Command.CommandText = argument_SqlText;213 _Command.CommandType = CommandType.Text;214 if (null != argument_Parameters)215 {216 _Command.Parameters.AddRange(argument_Parameters);217 }218 219 try220 {221 _CommandResult = _Command.ExecuteNonQuery();222 }223 catch (Exception)224 {225 }226 227 OperationValue _Value = new OperationValue();228 _Value.Value = _CommandResult;229 return _Value;230 }231 232 public OperationValue Save(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)233 {234 this.Connection.Open();235 int _CommandResult = 0;236 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();237 _Command.Connection = this.Connection.Connection;238 DbTransaction _Transaction = argument_Transaction;239 _Command.Transaction = _Transaction;240 _Command.CommandText = argument_SqlText;241 _Command.CommandType = CommandType.Text;242 if (null != argument_Parameters)243 {244 _Command.Parameters.AddRange(argument_Parameters);245 }246 247 try248 {249 _CommandResult = _Command.ExecuteNonQuery();250 //_Transaction.Commit();251 }252 catch (Exception)253 {254 _Transaction.Rollback();255 }256 257 OperationValue _Value = new OperationValue();258 _Value.Value = _CommandResult;259 return _Value;260 }261 262 public OperationValue TransactionModify(string argument_SqlText, DbParameter[] argument_Parameters)263 {264 this.Connection.Open();265 int _CommandResult = 0;266 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();267 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();268 _Command.Transaction = _Transaction;269 _Command.Connection = this.Connection.Connection;270 _Command.CommandText = argument_SqlText;271 _Command.CommandType = CommandType.Text;272 if (null != argument_Parameters)273 {274 _Command.Parameters.AddRange(argument_Parameters);275 }276 277 try278 {279 _CommandResult = _Command.ExecuteNonQuery();280 _Transaction.Commit();281 }282 catch (Exception)283 {284 _Transaction.Rollback();285 }286 287 OperationValue _Value = new OperationValue();288 _Value.Value = _CommandResult;289 290 return _Value;291 }292 293 public OperationValue Modify(string argument_SqlText, DbParameter[] argument_Parameters)294 {295 this.Connection.Open();296 int _CommandResult = 0;297 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();298 _Command.Connection = this.Connection.Connection;299 _Command.CommandText = argument_SqlText;300 _Command.CommandType = CommandType.Text;301 if (null != argument_Parameters)302 {303 _Command.Parameters.AddRange(argument_Parameters);304 }305 306 try307 {308 _CommandResult = _Command.ExecuteNonQuery();309 }310 catch (Exception)311 {312 }313 314 OperationValue _Value = new OperationValue();315 _Value.Value = _CommandResult;316 317 return _Value;318 }319 320 public OperationValue Modify(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)321 {322 this.Connection.Open();323 int _CommandResult = 0;324 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();325 DbTransaction _Transaction = argument_Transaction;326 _Command.Transaction = _Transaction;327 _Command.Connection = this.Connection.Connection;328 _Command.CommandText = argument_SqlText;329 _Command.CommandType = CommandType.Text;330 if (null != argument_Parameters)331 {332 _Command.Parameters.AddRange(argument_Parameters);333 }334 335 try336 {337 _CommandResult = _Command.ExecuteNonQuery();338 //_Transaction.Commit();339 }340 catch (Exception)341 {342 _Transaction.Rollback();343 }344 345 OperationValue _Value = new OperationValue();346 _Value.Value = _CommandResult;347 348 return _Value;349 }350 }
1 public sealed class DatabaseCommandProcedure : IDatabaseCommandProcedure 2 { 3 public DatabaseCommandProcedure(DatabaseConnection argument_Connection) 4 { 5 this.Connection = argument_Connection; 6 } 7 8 public DatabaseConnection Connection { get; private set; } 9 public string CommandName { get; set; }10 11 public DbParameter NewParameter()12 {13 return this.Connection.DatabaseProvide.Factory.CreateParameter();14 }15 16 public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)17 {18 DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();19 _Parameter.ParameterName = argument_ParameterName;20 _Parameter.Value = argument_Value;21 _Parameter.DbType = argument_DbType;22 _Parameter.Direction = Direction;23 return _Parameter;24 }25 26 public OperationValue Query(string argument_ProcedureName, DbParameter[] argument_Parameters)27 {28 this.Connection.Open();29 DataTable _QueryTable = new DataTable();30 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();31 _Command.Connection = this.Connection.Connection;32 _Command.CommandText = argument_ProcedureName;33 _Command.CommandType = CommandType.StoredProcedure;34 if (null != argument_Parameters)35 {36 _Command.Parameters.AddRange(argument_Parameters);37 }38 39 DbDataReader _Reader = _Command.ExecuteReader();40 _QueryTable.Load(_Reader);41 _Reader.Close();42 OperationValue _Value = new OperationValue();43 _Value.Value = _QueryTable;44 return _Value;45 }46 47 public OperationValue ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters)48 {49 this.Connection.Open();50 int _CommandResult = 0;51 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();52 _Command.Connection = this.Connection.Connection;53 _Command.CommandText = argument_ProcedureName;54 _Command.CommandType = CommandType.StoredProcedure;55 if (null != argument_Parameters)56 {57 _Command.Parameters.AddRange(argument_Parameters);58 }59 60 _CommandResult = _Command.ExecuteNonQuery();61 OperationValue _Value = new OperationValue();62 _Value.Value = _CommandResult;63 return _Value;64 }65 }
以上提供的接口操作：
1 public interface IDatabaseCommandTextProvide 2 { 3 string CommandName { get; set; } 4 DatabaseConnection Connection { get; } 5 DbParameter NewParameter(); 6 DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction); 7 OperationValue Query(string argument_SqlText, DbParameter[] argument_Parameters); 8 OperationValue Query(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters); 9 DataTableReader Reader(string argument_SqlText, DbParameter[] argument_Parameters);10 OperationValue TransactionSave(string argument_SqlText, DbParameter[] argument_Parameters);11 OperationValue Save(string argument_SqlText, DbParameter[] argument_Parameters);12 OperationValue Save(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);13 OperationValue TransactionModify(string argument_SqlText , DbParameter[] argument_Parameters);14 OperationValue Modify(string argument_SqlText, DbParameter[] argument_Parameters);15 OperationValue Modify(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);16 OperationValue TransactionDelete(string argument_SqlText, DbParameter[] argument_Parameters);17 OperationValue Delete(string argument_SqlText, DbParameter[] argument_Parameters);18 OperationValue Delete(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);19 }
1 public interface IDatabaseCommandProcedure2 {3 string CommandName { get; set; }4 DatabaseConnection Connection { get; }5 OperationValue Query(string argument_ProcedureName, DbParameter[] argument_Parameters);6 OperationValue ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters);7 DbParameter NewParameter();8 DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);9 }
通过IDatabaseCommandTextProvide接口可以看出有Transaction开头命名的方法，很显然它实现了事物操作.有人可能对CommandName不是很了解.它用于记录命令操作结果的名称,最能体现的是使用IOperation接口实现的DatabaseOperation操作.后期会提供案例.关于存储过程在Code上加上事物这里没有做，主要是考虑既然编写了存储过程脚本，那么你的事物应该在存储过程上处理，而不是在Code上.
DatabaseCommandTextAsync 和 DatabaseCommandProcedureAsync:
1 public sealed class DatabaseCommandTextAsync : IDatabaseCommandTextAsyncProvide 2 { 3 public DatabaseCommandTextAsync(DatabaseConnection argument_Connection) 4 { 5 this.Connection = argument_Connection; 6 } 7 8 public string CommandName 9 { 10 get; 11 set; 12 } 13 14 public DatabaseConnection Connection 15 { 16 get; 17 private set; 18 } 19 20 public DbParameter NewParameter() 21 { 22 return this.Connection.DatabaseProvide.Factory.CreateParameter(); 23 } 24 25 public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction) 26 { 27 DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter(); 28 _Parameter.ParameterName = argument_ParameterName; 29 _Parameter.Value = argument_Value; 30 _Parameter.DbType = argument_DbType; 31 _Parameter.Direction = Direction; 32 return _Parameter; 33 } 34 35 public async Task<OperationValue> DeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters) 36 { 37 this.Connection.Open(); 38 int _CommandResult = 0; 39 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 40 _Command.Connection = this.Connection.Connection; 41 _Command.CommandText = argument_SqlText; 42 _Command.CommandType = CommandType.Text; 43 if (null != argument_Parameters) 44 { 45 _Command.Parameters.AddRange(argument_Parameters); 46 } 47 48 try 49 { 50 _CommandResult = await _Command.ExecuteNonQueryAsync(); 51 } 52 catch (Exception) 53 { 54 } 55 56 OperationValue _Value = new OperationValue(); 57 _Value.Value = _CommandResult; 58 59 return _Value; 60 } 61 62 public async Task<OperationValue> DeleteAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters) 63 { 64 this.Connection.Open(); 65 int _CommandResult = 0; 66 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 67 DbTransaction _Transaction = argument_Transaction; 68 _Command.Connection = this.Connection.Connection; 69 _Command.Transaction = _Transaction; 70 _Command.CommandText = argument_SqlText; 71 _Command.CommandType = CommandType.Text; 72 if (null != argument_Parameters) 73 { 74 _Command.Parameters.AddRange(argument_Parameters); 75 } 76 77 try 78 { 79 _CommandResult = await _Command.ExecuteNonQueryAsync(); 80 //_Transaction.Commit(); 81 } 82 catch (Exception) 83 { 84 _Transaction.Rollback(); 85 } 86 87 OperationValue _Value = new OperationValue(); 88 _Value.Value = _CommandResult; 89 90 return _Value; 91 } 92 93 public async Task<OperationValue> ModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters) 94 { 95 this.Connection.Open(); 96 int _CommandResult = 0; 97 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand(); 98 _Command.Connection = this.Connection.Connection; 99 _Command.CommandText = argument_SqlText;100 _Command.CommandType = CommandType.Text;101 if (null != argument_Parameters)102 {103 _Command.Parameters.AddRange(argument_Parameters);104 }105 106 try107 {108 _CommandResult = await _Command.ExecuteNonQueryAsync();109 }110 catch (Exception)111 {112 }113 114 OperationValue _Value = new OperationValue();115 _Value.Value = _CommandResult;116 117 return _Value;118 }119 120 public async Task<OperationValue> ModifyAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)121 {122 this.Connection.Open();123 int _CommandResult = 0;124 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();125 DbTransaction _Transaction = argument_Transaction;126 _Command.Transaction = _Transaction;127 _Command.Connection = this.Connection.Connection;128 _Command.CommandText = argument_SqlText;129 _Command.CommandType = CommandType.Text;130 if (null != argument_Parameters)131 {132 _Command.Parameters.AddRange(argument_Parameters);133 }134 135 try136 {137 _CommandResult = await _Command.ExecuteNonQueryAsync();138 //_Transaction.Commit();139 }140 catch (Exception)141 {142 _Transaction.Rollback();143 }144 145 OperationValue _Value = new OperationValue();146 _Value.Value = _CommandResult;147 148 return _Value;149 }150 151 public async Task<OperationValue> QueryAsync(string argument_SqlText, DbParameter[] argument_Parameters)152 {153 this.Connection.Open();154 DataTable _QueryTable = new DataTable();155 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();156 _Command.Connection = this.Connection.Connection;157 _Command.CommandText = argument_SqlText;158 _Command.CommandType = CommandType.Text;159 DbDataReader _Reader = null;160 if (null != argument_Parameters)161 {162 _Command.Parameters.AddRange(argument_Parameters);163 }164 165 try166 {167 _Reader = await _Command.ExecuteReaderAsync();168 _QueryTable.Load(_Reader);169 }170 catch (Exception)171 {172 173 }174 finally175 {176 _Reader.Close();177 }178 179 OperationValue _Value = new OperationValue();180 _Value.Value = _QueryTable;181 return _Value;182 }183 184 public async Task<OperationValue> QueryAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)185 {186 this.Connection.Open();187 DataTable _QueryTable = new DataTable();188 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();189 _Command.Connection = this.Connection.Connection;190 _Command.Transaction = argument_Transaction;191 _Command.CommandText = argument_SqlText;192 _Command.CommandType = CommandType.Text;193 DbDataReader _Reader = null;194 if (null != argument_Parameters)195 {196 _Command.Parameters.AddRange(argument_Parameters);197 }198 199 try200 {201 _Reader = await _Command.ExecuteReaderAsync();202 _QueryTable.Load(_Reader);203 }204 catch (Exception)205 {206 207 }208 finally209 {210 _Reader.Close();211 }212 213 OperationValue _Value = new OperationValue();214 _Value.Value = _QueryTable;215 return _Value;216 }217 218 public async Task<OperationValue> SaveAsync(string argument_SqlText, DbParameter[] argument_Parameters)219 {220 this.Connection.Open();221 int _CommandResult = 0;222 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();223 _Command.Connection = this.Connection.Connection;224 _Command.CommandText = argument_SqlText;225 _Command.CommandType = CommandType.Text;226 if (null != argument_Parameters)227 {228 _Command.Parameters.AddRange(argument_Parameters);229 }230 231 try232 {233 _CommandResult = await _Command.ExecuteNonQueryAsync();234 }235 catch (Exception)236 {237 }238 239 OperationValue _Value = new OperationValue();240 _Value.Value = _CommandResult;241 return _Value;242 }243 244 public async Task<OperationValue> SaveAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)245 {246 this.Connection.Open();247 int _CommandResult = 0;248 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();249 _Command.Connection = this.Connection.Connection;250 DbTransaction _Transaction = argument_Transaction;251 _Command.Transaction = _Transaction;252 _Command.CommandText = argument_SqlText;253 _Command.CommandType = CommandType.Text;254 if (null != argument_Parameters)255 {256 _Command.Parameters.AddRange(argument_Parameters);257 }258 259 try260 {261 _CommandResult = await _Command.ExecuteNonQueryAsync();262 //_Transaction.Commit();263 }264 catch (Exception)265 {266 _Transaction.Rollback();267 }268 269 OperationValue _Value = new OperationValue();270 _Value.Value = _CommandResult;271 return _Value;272 }273 274 public async Task<OperationValue> TransactionDeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters)275 {276 int _CommandResult = 0;277 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();278 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();279 _Command.Connection = this.Connection.Connection;280 _Command.Transaction = _Transaction;281 _Command.CommandText = argument_SqlText;282 _Command.CommandType = CommandType.Text;283 if (null != argument_Parameters)284 {285 _Command.Parameters.AddRange(argument_Parameters);286 }287 288 try289 {290 this.Connection.Open();291 _CommandResult = await _Command.ExecuteNonQueryAsync();292 _Transaction.Commit();293 }294 catch (Exception)295 {296 _Transaction.Rollback();297 }298 299 OperationValue _Value = new OperationValue();300 _Value.Value = _CommandResult;301 302 return _Value;303 }304 305 public async Task<OperationValue> TransactionModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters)306 {307 this.Connection.Open();308 int _CommandResult = 0;309 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();310 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();311 _Command.Transaction = _Transaction;312 _Command.Connection = this.Connection.Connection;313 _Command.CommandText = argument_SqlText;314 _Command.CommandType = CommandType.Text;315 if (null != argument_Parameters)316 {317 _Command.Parameters.AddRange(argument_Parameters);318 }319 320 try321 {322 _CommandResult = await _Command.ExecuteNonQueryAsync();323 _Transaction.Commit();324 }325 catch (Exception)326 {327 _Transaction.Rollback();328 }329 330 OperationValue _Value = new OperationValue();331 _Value.Value = _CommandResult;332 333 return _Value;334 }335 336 public async Task<OperationValue> TransactionSaveAsync(string argument_SqlText, DbParameter[] argument_Parameters)337 {338 this.Connection.Open();339 int _CommandResult = 0;340 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();341 _Command.Connection = this.Connection.Connection;342 DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();343 _Command.Transaction = _Transaction;344 _Command.CommandText = argument_SqlText;345 _Command.CommandType = CommandType.Text;346 if (null != argument_Parameters)347 {348 _Command.Parameters.AddRange(argument_Parameters);349 }350 351 try352 {353 _CommandResult = await _Command.ExecuteNonQueryAsync();354 _Transaction.Commit();355 }356 catch (Exception)357 {358 _Transaction.Rollback();359 }360 361 OperationValue _Value = new OperationValue();362 _Value.Value = _CommandResult;363 return _Value;364 }365 }
1 public sealed class DatabaseCommandProcedureAsync : IDatabaseCommandProcedureAsyncProvide 2 { 3 public DatabaseCommandProcedureAsync(DatabaseConnection argument_Connection) 4 { 5 this.Connection = argument_Connection; 6 } 7 8 public string CommandName { get; set; } 9 10 public DatabaseConnection Connection { get; private set; }11 12 public async Task<OperationValue> ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters)13 {14 this.Connection.Open();15 int _CommandResult = 0;16 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();17 _Command.Connection = this.Connection.Connection;18 _Command.CommandText = argument_ProcedureName;19 _Command.CommandType = CommandType.StoredProcedure;20 if (null != argument_Parameters)21 {22 _Command.Parameters.AddRange(argument_Parameters);23 }24 25 try26 {27 _CommandResult = await _Command.ExecuteNonQueryAsync();28 }29 catch (Exception)30 {31 32 }33 34 OperationValue _Value = new OperationValue();35 _Value.Value = _CommandResult;36 return _Value;37 }38 39 public DbParameter NewParameter()40 {41 return this.Connection.DatabaseProvide.Factory.CreateParameter();42 }43 44 public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)45 {46 DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();47 _Parameter.ParameterName = argument_ParameterName;48 _Parameter.Value = argument_Value;49 _Parameter.DbType = argument_DbType;50 _Parameter.Direction = Direction;51 return _Parameter;52 }53 54 public async Task<OperationValue> Query(string argument_ProcedureName, DbParameter[] argument_Parameters)55 {56 this.Connection.Open();57 DataTable _QueryTable = new DataTable();58 DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();59 _Command.Connection = this.Connection.Connection;60 _Command.CommandText = argument_ProcedureName;61 _Command.CommandType = CommandType.StoredProcedure;62 DbDataReader _Reader = null;63 if (null != argument_Parameters)64 {65 _Command.Parameters.AddRange(argument_Parameters);66 }67 68 try69 {70 _Reader = await _Command.ExecuteReaderAsync();71 _QueryTable.Load(_Reader);72 }73 catch (Exception)74 {75 76 }77 finally78 {79 _Reader.Close();80 }81 82 OperationValue _Value = new OperationValue();83 _Value.Value = _QueryTable;84 return _Value;85 }86 }
以上提供的接口操作：
1 public interface IDatabaseCommandTextAsyncProvide 2 { 3 string CommandName { get; set; } 4 DatabaseConnection Connection { get; } 5 DbParameter NewParameter(); 6 DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction); 7 Task<OperationValue> QueryAsync(string argument_SqlText, DbParameter[] argument_Parameters); 8 Task<OperationValue> QueryAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters); 9 Task<OperationValue> TransactionSaveAsync(string argument_SqlText, DbParameter[] argument_Parameters);10 Task<OperationValue> SaveAsync(string argument_SqlText, DbParameter[] argument_Parameters);11 Task<OperationValue> SaveAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);12 Task<OperationValue> TransactionModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters);13 Task<OperationValue> ModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters);14 Task<OperationValue> ModifyAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);15 Task<OperationValue> TransactionDeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters);16 Task<OperationValue> DeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters);17 Task<OperationValue> DeleteAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);18 }
1 public interface IDatabaseCommandProcedureAsyncProvide2 {3 string CommandName { get; set; }4 DatabaseConnection Connection { get; }5 Task<OperationValue> Query(string argument_ProcedureName, DbParameter[] argument_Parameters);6 Task<OperationValue> ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters);7 DbParameter NewParameter();8 DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);9 }
很简单，异步的操作也是如此.
OperationValue：
前面介绍过，所有的操作结果都存储在OperationValue.Value上.Value这个属性是object类型. 操作的时候，可能出现大量的装箱和拆箱的操作,这个可以自己去细化按需实现，我只是用它来学习他用,并没有考虑到性能之类的考量.
DatabaseOperation:
1 public interface IOperation 2 { 3 DatabaseOperation BeginTransaction(); 4 DatabaseOperation EndTransaction(); 5 DatabaseOperation CancelTransaction(); 6 DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command); 7 DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command); 8 DatabaseOperation ExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command); 9 DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command);10 DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command);11 DatabaseOperation WidthExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command);12 }
1 public sealed class DatabaseOperation : IOperation 2 { 3 public DatabaseOperation() 4 { 5 this._Provide = new Database(); 6 this.Initialization(); 7 } 8 9 public DatabaseOperation(string argument_ProvideName) 10 { 11 this._Provide = new Database(argument_ProvideName); 12 this.Initialization(); 13 } 14 15 public DatabaseOperation(string argument_ProvideName,string argument_ConnectionString) 16 { 17 this._Provide = new Database(argument_ProvideName, argument_ConnectionString); 18 this.Initialization(); 19 } 20 21 public DatabaseOperation(Database argument_Provide) 22 { 23 this._Provide = argument_Provide; 24 this.Initialization(); 25 } 26 27 private DatabaseConnection _ConnectionProvide = null; 28 private DatabaseCommandText _NextTextCommand = null; 29 private DatabaseCommandProcedure _NextProcedureProvide = null; 30 private Database _Provide = null; 31 private DbTransaction _Transaction = null; 32 33 34 public Dictionary<string, OperationValue> CommandValues { get; private set; } 35 36 private void Initialization() 37 { 38 this.CommandValues = new Dictionary<string, OperationValue>(); 39 this._ConnectionProvide = new DatabaseConnection(this._Provide); 40 } 41 42 public DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command) 43 { 44 _NextTextCommand = new DatabaseCommandText(this._ConnectionProvide); 45 OperationValue _Value = argument_Command.Invoke(_NextTextCommand); 46 this.CommandValues.Clear(); 47 this.CommandValues.Add(_NextTextCommand.CommandName, _Value); 48 return this; 49 } 50 51 public DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command) 52 { 53 if (null == this._Transaction) 54 { 55 throw new NullReferenceException("没有检测到事务的开始"); 56 } 57 58 _NextTextCommand = new DatabaseCommandText(this._ConnectionProvide); 59 OperationValue _Value = argument_Command.Invoke(_NextTextCommand, this._Transaction); 60 this.CommandValues.Clear(); 61 this.CommandValues.Add(_NextTextCommand.CommandName, _Value); 62 return this; 63 } 64 65 public DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command) 66 { 67 _NextTextCommand = new DatabaseCommandText(this._ConnectionProvide); 68 OperationValue _Value = argument_Command.Invoke(_NextTextCommand); 69 CommandValues.Add(_NextTextCommand.CommandName, _Value); 70 return this; 71 } 72 73 public DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command) 74 { 75 if (null == this._Transaction) 76 { 77 throw new NullReferenceException("没有检测到事务的开始"); 78 } 79 80 this._NextTextCommand = new DatabaseCommandText(this._ConnectionProvide); 81 OperationValue _Value = argument_Command.Invoke(this._NextTextCommand, this._Transaction); 82 CommandValues.Add(_NextTextCommand.CommandName, _Value); 83 return this; 84 } 85 86 public DatabaseOperation ExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command) 87 { 88 _NextProcedureProvide = new DatabaseCommandProcedure(this._ConnectionProvide); 89 OperationValue _Value = argument_Command.Invoke(_NextProcedureProvide); 90 this.CommandValues.Clear(); 91 this.CommandValues.Add(_NextProcedureProvide.CommandName, _Value); 92 return this; 93 } 94 95 public DatabaseOperation WidthExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command) 96 { 97 _NextProcedureProvide = new DatabaseCommandProcedure(this._ConnectionProvide); 98 OperationValue _Value = argument_Command.Invoke(_NextProcedureProvide); 99 CommandValues.Add(_NextProcedureProvide.CommandName, _Value);100 return this;101 }102 103 public DatabaseOperation BeginTransaction()104 {105 this._Transaction = this._ConnectionProvide.Connection.BeginTransaction();106 return this;107 }108 109 public DatabaseOperation EndTransaction()110 {111 this._Transaction.Commit();112 return this;113 }114 115 public DatabaseOperation CancelTransaction()116 {117 this._Transaction.Rollback();118 return this;119 }120 }
这个主要用于执行过程SQL.设计此类的灵感来源于一次工作任务,当时要整理和搬迁Oracle,SQL SERVER , DB2等服务器上的数据，存储过程和函数等很多操作搬迁特别麻烦.因此设计了一个过程式执行SQL.
*************************************************************************************************************************************************
案例1 （备注：这个用的是System.Data.ODBC）：
1 Database _Provide = new Database();2 DatabaseConnection _Connection = new DatabaseConnection(_Provide);3 IDatabaseCommandProcedure _CommandProcedure = new DatabaseCommandProcedure(_Connection);4 DbParameter _IDParameter = _CommandProcedure.NewParameter("ID", 1, DbType.Int32, ParameterDirection.Input);5 OperationValue _Value = _CommandProcedure.Query("Query ?", new DbParameter[] { _IDParameter });
案例2：
1 DatabaseOperation _database_operation = new DatabaseOperation(); 2 _database_operation = _database_operation.ExecuteTextCommand(command => 3 { 4 command.CommandName = "INSERT"; 5 return command.TransactionSave("INSERT INTO DatabaseUser(Name) VALUES('五哥哥')", null); 6 7 }).ExecuteProcedureCommand((_command) => 8 { 9 bool _res = Convert.ToBoolean(_database_operation.CommandValues["INSERT"].Value);10 if (_res)11 {12 DbParameter _IDParameter = _command.NewParameter("ID", 2, DbType.Int32, ParameterDirection.Input);13 _command.CommandName = "Query";14 return _command.Query("Query", new DbParameter[] { _IDParameter });15 }16 else17 {18 return null;19 }20 }).WidthExecuteTextCommand(_command =>21 {22 _command.CommandName = "UPDATE";23 return _command.Modify("UPDATE DatabaseUser SET Name = '张三丰'", null);24 });25 26 DataTable _dt = (DataTable)_database_operation.CommandValues["Query"].Value;27 for (int i = 0; i < _dt.Rows.Count; i++)28 {29 Console.WriteLine("ID:{0}\tName:{1}", _dt.Rows[i]["ID"], _dt.Rows[i]["Name"]);30 }
案例3：
1 DatabaseOperation _database_operation = new DatabaseOperation(); 2 _database_operation.BeginTransaction(); 3 _database_operation.ExecuteTextCommand((command, transaction) => 4 { 5 command.CommandName = "新增"; 6 return command.Save("INSERT INTO DatabaseUser(Name) VALUES('五哥哥')", null); 7 }).WidthExecuteTextCommand((command, transaction) => 8 { 9 command.CommandName = "查询";10 return command.Query("SELECT * FROM DatabaseUser WHERE Name = '五哥哥'", transaction, null);11 });12 13 DataTable _Dt = _database_operation.CommandValues["查询"].Value as DataTable;14 if ("五哥哥".Equals((_Dt.Rows[0]["Name"])))15 {16 _database_operation.CancelTransaction();17 }
案例就提供到这里了...
之前有的人会问我，为甚么不去用EF框架，其实这个萝卜白菜各有所爱。不管ADO.NET还是EF都会让你在工作上开发得到便利就行.以上提供的是精简版本的,用于生产环境的，我就不提供了，支持Redis我也没有提供.这个比较复杂而且麻烦，但主要的思想和框架体系呈现出来了，可能有的人用于工作投入生产，这个精简版本的我没有测试过，有经验的人可以根据这个思想进行扩展。第一次拿出来自己写的东西，哈，原来写博客这么好玩，以后有时间的话，就写一写. 各位晚安...
- 4楼井传红
- 现在都没有用到ado了，ef自动生成 主要是业务不复杂 简单的程序没必要
- 3楼Moon.Orm塑造Orm经典
- 做Orm,交流一下，http://files.cnblogs.com/files/humble/d.pdf
- 2楼清风君
- haha
- 1楼牛腩
- ADO.NET万岁。。。现在我做的所有项目也都是自己用ADO.NET拼接SQL。。