/// <summary>
/// 针对<see cref="System.Data.IDbConnection"/>执行<see cref="System.Data.IDbCommand.CommandText"/>,并生成
/// <see cref="System.Data.IDataReader"/>
/// </summary>
/// <param name="connString">A ConnectionString.</param>
/// <param name="commandType">the CommandType.</param>
/// <param name="commandText">A execute Sql.</param>
/// <param name="timeoutInSeconds">the dbcommand timeout.</param>
/// <param name="parameterValues">the sql parameters.</param>
/// <returns>a datareader.</returns>
public IDataReader ExecuteReader(string connString, CommandType commandType, string commandText,
int timeoutInSeconds, IDbParameters parameterValues)
{
AssertUtils.StringNotNullOrEmpty(connString, "connString");
ConnectionTxPair connectionTxPair = GetConnectionTxPair(connString);
try
{
IDbCommand dbCommand = DbProvider.CreateCommand();
PrepareCommand(dbCommand, connectionTxPair.Connection, connectionTxPair.Transaction, commandType, commandText,
parameterValues);
ApplyTransactionTimeout(dbCommand, timeoutInSeconds);
IDataReader executeReader = dbCommand.ExecuteReader();
ParameterUtils.CopyParameters(parameterValues, dbCommand);
return(executeReader);
}
catch (Exception)
{
ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
connectionTxPair.Connection = null;
throw;
}
finally
{
//ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
}
}
/// <summary>
/// 执行SQL，并返回影响的行数
/// </summary>
/// <param name="connString">A ConnectionString.</param>
/// <param name="commandType">the Sql CommandType.</param>
/// <param name="commandText">the execute Sql.</param>
/// <param name="timeOutInSeconds">the dbcommand timeout.</param>
/// <param name="parameters">the sql parameters.</param>
/// <returns>返回受影响的行数.</returns>
public int ExecuteNonQuery(string connString, CommandType commandType, string commandText, int timeOutInSeconds,
IDbParameters parameters)
{
AssertUtils.StringNotNullOrEmpty(connString, "connString");
ConnectionTxPair connectionTxPair = GetConnectionTxPair(connString);
try
{
using (IDbCommand dbCommand = DbProvider.CreateCommand())
{
PrepareCommand(dbCommand, connectionTxPair.Connection, connectionTxPair.Transaction, CommandType.Text, commandText,
parameters);
ApplyTransactionTimeout(dbCommand, timeOutInSeconds);
int rowCount = dbCommand.ExecuteNonQuery();
ParameterUtils.CopyParameters(parameters, dbCommand);
return(rowCount);
}
}
catch (Exception ex)
{
ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
connectionTxPair.Connection = null;
throw ex;
}
finally
{
ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
}
}
/// <summary>
/// 设置<see cref="DbCommand"/>.
/// </summary>
/// <param name="dbCommand">The command.</param>
/// <param name="dbConnection">The connection.</param>
/// <param name="dbTransaction">The transaction</param>
/// <param name="commandType">The<see cref="CommandType"/>.</param>
/// <param name="commandText">The commandText</param>
/// <param name="commandParameters">The SQL Parameters</param>
private void PrepareCommand(IDbCommand dbCommand, IDbConnection dbConnection, IDbTransaction dbTransaction,
CommandType commandType, string commandText,
IDbParameters commandParameters)
{
AssertUtils.StringNotNullOrEmpty(commandText, "commandText", "commandText must not be Null or Empty.");
AssertUtils.ArgumentNotNull(dbCommand, "dbCommand");
AssertUtils.ArgumentNotNull(dbConnection, "dbConnection");
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
dbCommand.Connection = dbConnection;
if (dbTransaction != null)
{
AssertUtils.ArgumentNotNull(dbTransaction.Connection, "dbTransaction.Connection",
"The transaction was rollbacked or commited, please provide an open transaction.");
dbCommand.Transaction = dbTransaction;
}
dbCommand.CommandType = commandType;
dbCommand.CommandText = commandText;
if (commandParameters != null)
{
ParameterUtils.CopyParameters(dbCommand, commandParameters);
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="commandType">指定如何解释命令字符串</param>
/// <param name="parameters">DbCommand 的参数</param>
/// <returns>结果集中第一行的第一列</returns>
public object ExecuteScalar(System.Data.CommandType cmdType, string cmdText, IDbParameters parameters)
{
object result = null;
try
{
DbConnection connection = (DbConnection)this.Session.Connection;
ITransaction trans = this.Session.Transaction;
DbCommand command = connection.CreateCommand();
command.CommandText = cmdText;
command.CommandType = cmdType;
if (null != parameters)
{
ParameterUtils.CopyParameters(command, parameters);
}
trans.Enlist(command);
result = command.ExecuteScalar();
}
catch (DbException e)
{
ExceptionHandler.AsynchronousThreadExceptionHandler = new DbExceptionHandler();
ExceptionHandler.AsynchronousThreadExceptionHandler.HandleException(e);
}
return(result);
}
public void DoUpdateDataSet()
{
String sql = "select TestObjectNo, Age, Name from TestObjects";
DataSet dataSet = new DataSet();
adoOperations.DataSetFill(dataSet, CommandType.Text, sql, new string[] { "TestObjects" });
//Create and add new row.
DataRow myDataRow = dataSet.Tables["TestObjects"].NewRow();
myDataRow["Age"] = 101;
myDataRow["Name"] = "OldManWinter";
dataSet.Tables["TestObjects"].Rows.Add(myDataRow);
IDbCommand insertCommand = dbProvider.CreateCommand();
insertCommand.CommandText = "insert into TestObjects(Age,Name) values (@Age,@Name)";
IDbParameters parameters = adoOperations.CreateDbParameters();
parameters.Add("Name", DbType.String, 12, "Name");
//TODO - remembering the -1 isn't all that natural... add string name, dbtype, string sourceCol)
//or AddSourceCol("Age", SqlDbType.Int); would copy into source col?
parameters.Add("Age", SqlDbType.Int, -1, "Age");
//TODO - this isn't all that natural...
ParameterUtils.CopyParameters(insertCommand, parameters);
//insertCommand.Parameters.Add()
adoOperations.DataSetUpdate(dataSet, "TestObjects",
insertCommand,
null,
null);
//TODO avoid param Utils copy by adding argument...
//adoOperations.DataSetUpdate(dataSet, "TestObjects",
// insertCommand, parameters,
// null, null,
// null, null);
//adoOperations.DataSetUpdate(dataSet, "TestObjects",
// CommandType type, string sql, parameters,
// null, null,
// null, null);
//TODO how about breaking up the operations...
}
/// <summary>
/// 执行查询，将结果集返回到一个DataTable
/// </summary>
/// <param name="commandType">指定如何解释命令字符串</param>
/// <param name="cmdText">sql语句</param>
/// <param name="parameters">DbCommand 的参数</param>
/// <returns>结果集</returns>
public System.Data.DataTable QueryDataTable(System.Data.CommandType commandType, string cmdText, IDbParameters parameters)
{
System.Data.DataTable result = null;
try
{
DbConnection connection = (DbConnection)this.Session.Connection;
ITransaction trans = this.Session.Transaction;
DbCommand command = connection.CreateCommand();
command.CommandText = cmdText;
command.CommandType = commandType;
if (null != parameters)
{
ParameterUtils.CopyParameters(command, parameters);
}
trans.Enlist(command);
result = new System.Data.DataTable();
System.Data.DataRow dt_dr = null;
using (DbDataReader dr = command.ExecuteReader())
{
int index = 0;
while (dr.Read())
{
if (index == 0)
{
for (int i = 0; i < dr.FieldCount; i++)
{
result.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
}
}
dt_dr = result.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
dt_dr[i] = dr[i];
}
result.Rows.Add(dt_dr);
index++;
}
}
}
catch (DbException e)
{
ExceptionHandler.AsynchronousThreadExceptionHandler = new DbExceptionHandler();
ExceptionHandler.AsynchronousThreadExceptionHandler.HandleException(e);
}
return(result);
}
/// <summary>
/// 执行SQL, 返回Dataset
/// </summary>
/// <param name="connString">the ConnectionString</param>
/// <param name="commandType">the sql CommandType.</param>
/// <param name="commandText">the execute sql</param>
/// <param name="timeoutInSeconds">the dbcommand timeout.</param>
/// <param name="parameterValues">the sql parameters</param>
/// <returns>return a Dataset</returns>
/// <exception cref="ArgumentNullException">如果参数connString为null或空，则抛出此异常</exception>
public DataSet ExecuteDataset(string connString, CommandType commandType, string commandText,
int timeoutInSeconds, IDbParameters parameterValues)
{
AssertUtils.StringNotNullOrEmpty(connString, "connString");
ConnectionTxPair connectionTxPair = GetConnectionTxPair(connString);
try
{
using (IDbCommand dbCommand = DbProvider.CreateCommand())
{
PrepareCommand(dbCommand, connectionTxPair.Connection, connectionTxPair.Transaction, commandType, commandText,
parameterValues);
ApplyTransactionTimeout(dbCommand, timeoutInSeconds);
IDbDataAdapter dbDataAdapter = DbProvider.CreateDataAdapter();
dbDataAdapter.SelectCommand = dbCommand;
var ds = new DataSet();
dbDataAdapter.Fill(ds);
ParameterUtils.CopyParameters(parameterValues, dbCommand);
return(ds);
}
}
catch (Exception)
{
ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
connectionTxPair.Connection = null;
throw;
}
finally
{
ConnectionUtils.DisposeConnection(connectionTxPair.Connection, DbProvider);
}
}
public void SetValues(IDbCommand dbCommand)
{
((OracleCommand)dbCommand).ArrayBindCount = _bindCount;
ParameterUtils.CopyParameters(dbCommand, _batchParameters);
}
public void SetValues(IDbCommand dbCommand)
{
ParameterUtils.CopyParameters(dbCommand, Parameters);
}