/// <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); }