你的位置:首页 > 数据库

[数据库]我与ADO.NET二三事


  天气渐冷,闲来无事就把业余时间自己使用的数据访问库凉一凉.这个库本人自己使用了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   }

View Code

数据连接配置只需要在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   }

View Code

 

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   }

View Code

 它只有一个构造,必须提供Database,因为Connection需要Database来提供创建.提供了如下操作:

1 public interface IDatabaseConnectionProvide2   {3     void Initialization();4     void Change(string argument_DatabaseName);5     void Open();6   }

View Code

 

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   }

View Code

 

 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   }

View Code

 以上提供的接口操作:

 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   }

View Code

 

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   }

View Code

 

 通过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   }

View Code

 

 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   }

View Code

 以上提供的接口操作:

 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   }

View Code

 

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   }

View Code

 

很简单,异步的操作也是如此.

 

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   }

View Code

 

 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   }

View Code

 

这个主要用于执行过程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 });

View Code

 

案例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       }

View Code

 

 案例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       }

View Code

 

 案例就提供到这里了...

之前有的人会问我,为甚么不去用EF框架,其实这个萝卜白菜各有所爱。不管ADO.NET还是EF都会让你在工作上开发得到便利就行.以上提供的是精简版本的,用于生产环境的,我就不提供了,支持Redis我也没有提供.这个比较复杂而且麻烦,但主要的思想和框架体系呈现出来了,可能有的人用于工作投入生产,这个精简版本的我没有测试过,有经验的人可以根据这个思想进行扩展。第一次拿出来自己写的东西,哈,原来写博客这么好玩,以后有时间的话,就写一写.  各位晚安...