我与ADO.NET二三事
2024-08-23 07:17:41
天气渐冷,闲来无事就把业余时间自己使用的数据访问库凉一凉.这个库本人自己使用了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:
public class Database : IDatabaseProvide
{
/// <summary>
/// 内置默认识别Config Key:DatabaseProvide和DatabaseProvideConnectionString
/// </summary>
public Database()
{
this.DatabaseProvideName = ConfigurationManager.AppSettings["DatabaseProvide"];
this.ConnectionString = ConfigurationManager.AppSettings["DatabaseProvideConnectionString"];
this.Initialization();
} public Database(string argument_ProvideName)
{
this.DatabaseProvideName = argument_ProvideName;
this.ConnectionString = ConfigurationManager.AppSettings["DatabaseProvideConnectionString"];
this.Initialization();
} public Database(string argument_ProvideName, string argument_ConnectionString)
{
this.DatabaseProvideName = argument_ProvideName;
this.ConnectionString = argument_ConnectionString;
this.Initialization();
} public string DatabaseProvideName { get; set; }
internal string ConnectionString { get; set; }
internal DbProviderFactory Factory { get; set; } public void Initialization()
{
this.Factory = DbProviderFactories.GetFactory(this.DatabaseProvideName);
}
}
数据连接配置只需要在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引擎就创建出来.提供的接口也很简单:
public interface IDatabaseProvide
{
void Initialization();
}
DatabaseConnection:
public sealed class DatabaseConnection : IDatabaseConnectionProvide
{
public DatabaseConnection(Database argument_Provide)
{
this.Initialization(argument_Provide);
} internal DbConnection Connection { get; private set; }
internal Database DatabaseProvide
{
get; set;
} public ConnectionState DatabaseConnectionState
{
get
{
return this.Connection.State;
}
} private void Initialization(Database argument_DatabaseProvide)
{
if (null == this.Connection)
{
this.DatabaseProvide = argument_DatabaseProvide;
this.Connection = this.DatabaseProvide.Factory.CreateConnection();
this.Connection.ConnectionString = this.DatabaseProvide.ConnectionString;
} this.Initialization();
} public void Initialization() //初始化默认执行过一次
{
this.Open();
} public void Open()
{
if (ConnectionState.Closed == this.Connection.State)
{
this.Connection.Open();
}
} public void Change(string argument_DatabaseName)
{
if (null != this.DatabaseProvide && null != this.Connection)
{
this.Connection.ChangeDatabase(argument_DatabaseName);
this.DatabaseProvide.ConnectionString = this.Connection.ConnectionString;
}
}
}
它只有一个构造，必须提供Database,因为Connection需要Database来提供创建.提供了如下操作：
public interface IDatabaseConnectionProvide
{
void Initialization();
void Change(string argument_DatabaseName);
void Open();
}
DatabaseCommandText 和 DatabaseCommandProcedure:
public sealed class DatabaseCommandText : IDatabaseCommandTextProvide
{
public DatabaseCommandText(DatabaseConnection argument_Connection)
{
this.Connection = argument_Connection;
} public DatabaseConnection Connection { get; private set; }
public string CommandName { get; set; } public DbParameter NewParameter()
{
return this.Connection.DatabaseProvide.Factory.CreateParameter();
} public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)
{
DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();
_Parameter.ParameterName = argument_ParameterName;
_Parameter.Value = argument_Value;
_Parameter.DbType = argument_DbType;
_Parameter.Direction = Direction;
return _Parameter;
} public OperationValue TransactionDelete(string argument_SqlText, DbParameter[] argument_Parameters)
{
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Connection = this.Connection.Connection;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
this.Connection.Open();
_CommandResult = _Command.ExecuteNonQuery();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public OperationValue Delete(string argument_SqlText, DbParameter[] argument_Parameters)
{
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
this.Connection.Open();
_CommandResult = _Command.ExecuteNonQuery();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public OperationValue Delete(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = argument_Transaction;
_Command.Connection = this.Connection.Connection;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public DataTableReader Reader(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} DbDataReader _Reader = _Command.ExecuteReader();
_QueryTable.Load(_Reader);
return _QueryTable.CreateDataReader();
} public OperationValue Query(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} DbDataReader _Reader = _Command.ExecuteReader();
_QueryTable.Load(_Reader);
_Reader.Close();
OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
} public OperationValue Query(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.Transaction = argument_Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} DbDataReader _Reader = _Command.ExecuteReader();
_QueryTable.Load(_Reader);
_Reader.Close();
OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
} public OperationValue TransactionSave(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public OperationValue Save(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public OperationValue Save(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
DbTransaction _Transaction = argument_Transaction;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public OperationValue TransactionModify(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Transaction = _Transaction;
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public OperationValue Modify(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public OperationValue Modify(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = argument_Transaction;
_Command.Transaction = _Transaction;
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = _Command.ExecuteNonQuery();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
}
}
public sealed class DatabaseCommandProcedure : IDatabaseCommandProcedure
{
public DatabaseCommandProcedure(DatabaseConnection argument_Connection)
{
this.Connection = argument_Connection;
} public DatabaseConnection Connection { get; private set; }
public string CommandName { get; set; } public DbParameter NewParameter()
{
return this.Connection.DatabaseProvide.Factory.CreateParameter();
} public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)
{
DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();
_Parameter.ParameterName = argument_ParameterName;
_Parameter.Value = argument_Value;
_Parameter.DbType = argument_DbType;
_Parameter.Direction = Direction;
return _Parameter;
} public OperationValue Query(string argument_ProcedureName, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_ProcedureName;
_Command.CommandType = CommandType.StoredProcedure;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} DbDataReader _Reader = _Command.ExecuteReader();
_QueryTable.Load(_Reader);
_Reader.Close();
OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
} public OperationValue ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_ProcedureName;
_Command.CommandType = CommandType.StoredProcedure;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} _CommandResult = _Command.ExecuteNonQuery();
OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
}
}
以上提供的接口操作：
public interface IDatabaseCommandTextProvide
{
string CommandName { get; set; }
DatabaseConnection Connection { get; }
DbParameter NewParameter();
DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);
OperationValue Query(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Query(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
DataTableReader Reader(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue TransactionSave(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Save(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Save(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
OperationValue TransactionModify(string argument_SqlText , DbParameter[] argument_Parameters);
OperationValue Modify(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Modify(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
OperationValue TransactionDelete(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Delete(string argument_SqlText, DbParameter[] argument_Parameters);
OperationValue Delete(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
}
public interface IDatabaseCommandProcedure
{
string CommandName { get; set; }
DatabaseConnection Connection { get; }
OperationValue Query(string argument_ProcedureName, DbParameter[] argument_Parameters);
OperationValue ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters);
DbParameter NewParameter();
DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);
}
通过IDatabaseCommandTextProvide接口可以看出有Transaction开头命名的方法，很显然它实现了事物操作.有人可能对CommandName不是很了解.它用于记录命令操作结果的名称,最能体现的是使用IOperation接口实现的DatabaseOperation操作.后期会提供案例.关于存储过程在Code上加上事物这里没有做，主要是考虑既然编写了存储过程脚本，那么你的事物应该在存储过程上处理，而不是在Code上.
DatabaseCommandTextAsync 和 DatabaseCommandProcedureAsync:
public sealed class DatabaseCommandTextAsync : IDatabaseCommandTextAsyncProvide
{
public DatabaseCommandTextAsync(DatabaseConnection argument_Connection)
{
this.Connection = argument_Connection;
} public string CommandName
{
get;
set;
} public DatabaseConnection Connection
{
get;
private set;
} public DbParameter NewParameter()
{
return this.Connection.DatabaseProvide.Factory.CreateParameter();
} public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)
{
DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();
_Parameter.ParameterName = argument_ParameterName;
_Parameter.Value = argument_Value;
_Parameter.DbType = argument_DbType;
_Parameter.Direction = Direction;
return _Parameter;
} public async Task<OperationValue> DeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> DeleteAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = argument_Transaction;
_Command.Connection = this.Connection.Connection;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> ModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> ModifyAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = argument_Transaction;
_Command.Transaction = _Transaction;
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> QueryAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
DbDataReader _Reader = null;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_Reader = await _Command.ExecuteReaderAsync();
_QueryTable.Load(_Reader);
}
catch (Exception)
{ }
finally
{
_Reader.Close();
} OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
} public async Task<OperationValue> QueryAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.Transaction = argument_Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
DbDataReader _Reader = null;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_Reader = await _Command.ExecuteReaderAsync();
_QueryTable.Load(_Reader);
}
catch (Exception)
{ }
finally
{
_Reader.Close();
} OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
} public async Task<OperationValue> SaveAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
}
catch (Exception)
{
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public async Task<OperationValue> SaveAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
DbTransaction _Transaction = argument_Transaction;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
//_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public async Task<OperationValue> TransactionDeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Connection = this.Connection.Connection;
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
this.Connection.Open();
_CommandResult = await _Command.ExecuteNonQueryAsync();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> TransactionModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Transaction = _Transaction;
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult; return _Value;
} public async Task<OperationValue> TransactionSaveAsync(string argument_SqlText, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
DbTransaction _Transaction = this.Connection.Connection.BeginTransaction();
_Command.Transaction = _Transaction;
_Command.CommandText = argument_SqlText;
_Command.CommandType = CommandType.Text;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
_Transaction.Commit();
}
catch (Exception)
{
_Transaction.Rollback();
} OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
}
}
public sealed class DatabaseCommandProcedureAsync : IDatabaseCommandProcedureAsyncProvide
{
public DatabaseCommandProcedureAsync(DatabaseConnection argument_Connection)
{
this.Connection = argument_Connection;
} public string CommandName { get; set; } public DatabaseConnection Connection { get; private set; } public async Task<OperationValue> ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters)
{
this.Connection.Open();
int _CommandResult = ;
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_ProcedureName;
_Command.CommandType = CommandType.StoredProcedure;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_CommandResult = await _Command.ExecuteNonQueryAsync();
}
catch (Exception)
{ } OperationValue _Value = new OperationValue();
_Value.Value = _CommandResult;
return _Value;
} public DbParameter NewParameter()
{
return this.Connection.DatabaseProvide.Factory.CreateParameter();
} public DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction)
{
DbParameter _Parameter = this.Connection.DatabaseProvide.Factory.CreateParameter();
_Parameter.ParameterName = argument_ParameterName;
_Parameter.Value = argument_Value;
_Parameter.DbType = argument_DbType;
_Parameter.Direction = Direction;
return _Parameter;
} public async Task<OperationValue> Query(string argument_ProcedureName, DbParameter[] argument_Parameters)
{
this.Connection.Open();
DataTable _QueryTable = new DataTable();
DbCommand _Command = this.Connection.DatabaseProvide.Factory.CreateCommand();
_Command.Connection = this.Connection.Connection;
_Command.CommandText = argument_ProcedureName;
_Command.CommandType = CommandType.StoredProcedure;
DbDataReader _Reader = null;
if (null != argument_Parameters)
{
_Command.Parameters.AddRange(argument_Parameters);
} try
{
_Reader = await _Command.ExecuteReaderAsync();
_QueryTable.Load(_Reader);
}
catch (Exception)
{ }
finally
{
_Reader.Close();
} OperationValue _Value = new OperationValue();
_Value.Value = _QueryTable;
return _Value;
}
}
以上提供的接口操作：
public interface IDatabaseCommandTextAsyncProvide
{
string CommandName { get; set; }
DatabaseConnection Connection { get; }
DbParameter NewParameter();
DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);
Task<OperationValue> QueryAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> QueryAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
Task<OperationValue> TransactionSaveAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> SaveAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> SaveAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
Task<OperationValue> TransactionModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> ModifyAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> ModifyAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
Task<OperationValue> TransactionDeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> DeleteAsync(string argument_SqlText, DbParameter[] argument_Parameters);
Task<OperationValue> DeleteAsync(string argument_SqlText, DbTransaction argument_Transaction, DbParameter[] argument_Parameters);
}
public interface IDatabaseCommandProcedureAsyncProvide
{
string CommandName { get; set; }
DatabaseConnection Connection { get; }
Task<OperationValue> Query(string argument_ProcedureName, DbParameter[] argument_Parameters);
Task<OperationValue> ExecuteNonQuery(string argument_ProcedureName, DbParameter[] argument_Parameters);
DbParameter NewParameter();
DbParameter NewParameter(string argument_ParameterName, object argument_Value, DbType argument_DbType, ParameterDirection Direction);
}
很简单，异步的操作也是如此.
OperationValue：
前面介绍过，所有的操作结果都存储在OperationValue.Value上.Value这个属性是object类型. 操作的时候，可能出现大量的装箱和拆箱的操作,这个可以自己去细化按需实现，我只是用它来学习他用,并没有考虑到性能之类的考量.
DatabaseOperation:
public interface IOperation
{
DatabaseOperation BeginTransaction();
DatabaseOperation EndTransaction();
DatabaseOperation CancelTransaction();
DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command);
DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command);
DatabaseOperation ExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command);
DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command);
DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command);
DatabaseOperation WidthExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command);
}
public sealed class DatabaseOperation : IOperation
{
public DatabaseOperation()
{
this._Provide = new Database();
this.Initialization();
} public DatabaseOperation(string argument_ProvideName)
{
this._Provide = new Database(argument_ProvideName);
this.Initialization();
} public DatabaseOperation(string argument_ProvideName,string argument_ConnectionString)
{
this._Provide = new Database(argument_ProvideName, argument_ConnectionString);
this.Initialization();
} public DatabaseOperation(Database argument_Provide)
{
this._Provide = argument_Provide;
this.Initialization();
} private DatabaseConnection _ConnectionProvide = null;
private DatabaseCommandText _NextTextCommand = null;
private DatabaseCommandProcedure _NextProcedureProvide = null;
private Database _Provide = null;
private DbTransaction _Transaction = null; public Dictionary<string, OperationValue> CommandValues { get; private set; } private void Initialization()
{
this.CommandValues = new Dictionary<string, OperationValue>();
this._ConnectionProvide = new DatabaseConnection(this._Provide);
} public DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command)
{
_NextTextCommand = new DatabaseCommandText(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(_NextTextCommand);
this.CommandValues.Clear();
this.CommandValues.Add(_NextTextCommand.CommandName, _Value);
return this;
} public DatabaseOperation ExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command)
{
if (null == this._Transaction)
{
throw new NullReferenceException("没有检测到事务的开始");
} _NextTextCommand = new DatabaseCommandText(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(_NextTextCommand, this._Transaction);
this.CommandValues.Clear();
this.CommandValues.Add(_NextTextCommand.CommandName, _Value);
return this;
} public DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, OperationValue> argument_Command)
{
_NextTextCommand = new DatabaseCommandText(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(_NextTextCommand);
CommandValues.Add(_NextTextCommand.CommandName, _Value);
return this;
} public DatabaseOperation WidthExecuteTextCommand(Func<IDatabaseCommandTextProvide, DbTransaction, OperationValue> argument_Command)
{
if (null == this._Transaction)
{
throw new NullReferenceException("没有检测到事务的开始");
} this._NextTextCommand = new DatabaseCommandText(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(this._NextTextCommand, this._Transaction);
CommandValues.Add(_NextTextCommand.CommandName, _Value);
return this;
} public DatabaseOperation ExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command)
{
_NextProcedureProvide = new DatabaseCommandProcedure(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(_NextProcedureProvide);
this.CommandValues.Clear();
this.CommandValues.Add(_NextProcedureProvide.CommandName, _Value);
return this;
} public DatabaseOperation WidthExecuteProcedureCommand(Func<IDatabaseCommandProcedure, OperationValue> argument_Command)
{
_NextProcedureProvide = new DatabaseCommandProcedure(this._ConnectionProvide);
OperationValue _Value = argument_Command.Invoke(_NextProcedureProvide);
CommandValues.Add(_NextProcedureProvide.CommandName, _Value);
return this;
} public DatabaseOperation BeginTransaction()
{
this._Transaction = this._ConnectionProvide.Connection.BeginTransaction();
return this;
} public DatabaseOperation EndTransaction()
{
this._Transaction.Commit();
return this;
} public DatabaseOperation CancelTransaction()
{
this._Transaction.Rollback();
return this;
}
}
这个主要用于执行过程SQL.设计此类的灵感来源于一次工作任务,当时要整理和搬迁Oracle,SQL SERVER , DB2等服务器上的数据，存储过程和函数等很多操作搬迁特别麻烦.因此设计了一个过程式执行SQL.
*************************************************************************************************************************************************
案例1 （备注：这个用的是System.Data.ODBC）：
Database _Provide = new Database();
DatabaseConnection _Connection = new DatabaseConnection(_Provide);
IDatabaseCommandProcedure _CommandProcedure = new DatabaseCommandProcedure(_Connection);
DbParameter _IDParameter = _CommandProcedure.NewParameter("ID", , DbType.Int32, ParameterDirection.Input);
OperationValue _Value = _CommandProcedure.Query("Query ?", new DbParameter[] { _IDParameter });
案例2：
DatabaseOperation _database_operation = new DatabaseOperation();
_database_operation = _database_operation.ExecuteTextCommand(command =>
{
command.CommandName = "INSERT";
return command.TransactionSave("INSERT INTO DatabaseUser(Name) VALUES('五哥哥')", null); }).ExecuteProcedureCommand((_command) =>
{
bool _res = Convert.ToBoolean(_database_operation.CommandValues["INSERT"].Value);
if (_res)
{
DbParameter _IDParameter = _command.NewParameter("ID", , DbType.Int32, ParameterDirection.Input);
_command.CommandName = "Query";
return _command.Query("Query", new DbParameter[] { _IDParameter });
}
else
{
return null;
}
}).WidthExecuteTextCommand(_command =>
{
_command.CommandName = "UPDATE";
return _command.Modify("UPDATE DatabaseUser SET Name = '张三丰'", null);
}); DataTable _dt = (DataTable)_database_operation.CommandValues["Query"].Value;
for (int i = ; i < _dt.Rows.Count; i++)
{
Console.WriteLine("ID:{0}\tName:{1}", _dt.Rows[i]["ID"], _dt.Rows[i]["Name"]);
}
案例3：
DatabaseOperation _database_operation = new DatabaseOperation();
_database_operation.BeginTransaction();
_database_operation.ExecuteTextCommand((command, transaction) =>
{
command.CommandName = "新增";
return command.Save("INSERT INTO DatabaseUser(Name) VALUES('五哥哥')", null);
}).WidthExecuteTextCommand((command, transaction) =>
{
command.CommandName = "查询";
return command.Query("SELECT * FROM DatabaseUser WHERE Name = '五哥哥'", transaction, null);
}); DataTable _Dt = _database_operation.CommandValues["查询"].Value as DataTable;
if ("五哥哥".Equals((_Dt.Rows[]["Name"])))
{
_database_operation.CancelTransaction();
}
案例就提供到这里了...
之前有的人会问我，为甚么不去用EF框架，其实这个萝卜白菜各有所爱。不管ADO.NET还是EF都会让你在工作上开发得到便利就行.以上提供的是精简版本的,用于生产环境的，我就不提供了，支持Redis我也没有提供.这个比较复杂而且麻烦，但主要的思想和框架体系呈现出来了，可能有的人用于工作投入生产，这个精简版本的我没有测试过，有经验的人可以根据这个思想进行扩展。第一次拿出来自己写的东西，哈，原来写博客这么好玩，以后有时间的话，就写一写. 各位晚安...
最新文章
- R12供应商地点层付款方法SQL
- linux 最小安装 需要的后续操作
- Thinkphp .htaccess 与 httpd.ini文件重定向转换问题
- Inno setup 简单打包教程
- shell脚本实例(2)
- js调用.net后台事件，和后台调用前台等方法以及js调用服务器控件的方法
- build opencv with python support
- CSS入门教程——定位(positon)
- ocp认证考试指南第一章
- SZU:B85 Alec's Eggs
- webstorm下的sass自动编译和移动端自适应实践
- RibbonForm使用技巧
- mysql select column default value if is null
- Hello 2019
- Forward团队-爬虫豆瓣top250项目-项目进度
- WCF快速搭建Demo
- PyCharm更改字体和界面样式
- gitlab 服务器的搭建与使用全过程（一）
- what's the difference between grouping and facet in lucene 3.5
- Android 引用库项目，Debug 库项目