/// <summary>
/// Clone a command
/// </summary>
/// <param name="dbCommand"></param>
/// <returns></returns>
protected DbCommand CloneCommand(DbCommand dbCommand)
{
DbCommand res = ProviderFactory.CreateCommand();
if (res != null)
{
res.CommandText = dbCommand.CommandText;
res.CommandTimeout = dbCommand.CommandTimeout;
res.CommandType = dbCommand.CommandType;
res.Connection = dbCommand.Connection;
res.Transaction = dbCommand.Transaction;
foreach (DbParameter param in dbCommand.Parameters)
{
DbParameter newParam = res.CreateParameter();
newParam.DbType = param.DbType;
newParam.Direction = param.Direction;
newParam.ParameterName = param.ParameterName;
newParam.Size = param.Size;
newParam.SourceColumn = param.SourceColumn;
newParam.SourceColumnNullMapping = param.SourceColumnNullMapping;
newParam.SourceVersion = param.SourceVersion;
newParam.Value = param.Value;
res.Parameters.Add(newParam);
}
return(res);
}
return(null);
}
protected DbCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters, out bool Result)
{
Result = false;
try
{
var command = ProviderFactory.CreateCommand();
command.Connection = ConnectionObj;
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (IDataParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
Result = true;
return(command);
}
catch (System.Exception e)
{
SetLastExceptionError(e);
Result = false;
return(null);
}
}
/// <summary>
/// Creates a new save command using the provided connection.
/// </summary>
/// <returns>A new, configured <see cref="DbCommand">database command</see>.</returns>
public virtual DbCommand NewSaveSnapshotCommand()
{
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.ParameterName = "AggregateId";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "Version";
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "Type";
parameter.Size = 256;
parameter.DbType = DbType.String;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "Snapshot";
parameter.DbType = DbType.Binary;
command.Parameters.Add(parameter);
command.CommandText = Sql.Snapshots.Save;
return(command);
}
/// <summary>
/// Creates a command to query a saga by a correlated property value.
/// </summary>
/// <param name="dataType">The qualified name of the saga data type to build the command for.</param>
/// <param name="propertyName">The saga property to query.</param>
/// <param name="propertyValue">The saga property value to match.</param>
/// <returns>A new, configured <see cref="DbCommand">command</see>.</returns>
public virtual DbCommand NewQueryByPropertyCommand(string dataType, string propertyName, object propertyValue)
{
Arg.NotNullOrEmpty(dataType, nameof(dataType));
Arg.NotNullOrEmpty(propertyName, nameof(propertyName));
Arg.NotNull(propertyValue, nameof(propertyValue));
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.DbType = DbType.String;
parameter.Size = 256;
parameter.ParameterName = "DataType";
parameter.Value = dataType;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.String;
parameter.Size = 90;
parameter.ParameterName = "PropertyName";
parameter.Value = propertyName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Binary;
parameter.Size = 200;
parameter.ParameterName = "PropertyValue";
parameter.Value = ValueAsBinary(propertyValue);
command.Parameters.Add(parameter);
command.CommandText = Sql.QueryByProperty;
return(command);
}
/// <summary>
/// Creates and returns a new command to enqueue a message.
/// </summary>
/// <param name="newMessage">Indicates whether the command is for a brand new message.
/// The default value is true.</param>
/// <returns>A new, configured <see cref="DbCommand">command</see> that can be used to enqueue a message.</returns>
public virtual DbCommand NewEnqueueCommand(bool newMessage = true)
{
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.DbType = DbType.DateTime2;
parameter.ParameterName = "EnqueueTime";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.DateTime2;
parameter.ParameterName = "DueTime";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.String;
parameter.Size = 256;
parameter.ParameterName = "Type";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Int32;
parameter.ParameterName = "Revision";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Binary;
parameter.ParameterName = "Message";
command.Parameters.Add(parameter);
if (newMessage)
{
command.CommandText = Sql.MessageQueue.Enqueue;
}
else
{
command.CommandText = Sql.SubscriptionQueue.Enqueue;
parameter = command.CreateParameter();
parameter.DbType = DbType.Guid;
parameter.ParameterName = "SubscriptionId";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Guid;
parameter.ParameterName = "MessageId";
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Int32;
parameter.ParameterName = "DequeueAttempts";
command.Parameters.Add(parameter);
}
return(command);
}
/// <summary>
/// Creates a new <see cref="DbCommand"/> with <see cref="Connection"/>, <see cref="Transaction"/> and <see cref="CommandTimeout"/>.
/// </summary>
/// <returns>A new <see cref="DbCommand"/> with <see cref="Connection"/>, <see cref="Transaction"/> and <see cref="CommandTimeout"/>.</returns>
public DbCommand NewCommand()
{
var result = ProviderFactory.CreateCommand();
result.Connection = Connection;
result.Transaction = Transaction;
result.CommandTimeout = CommandTimeout;
return(result);
}
//************************************************************************
/// <summary>
/// DBプロバイダファクトリに関連付けられたCommandを作成する。
/// </summary>
/// <param name="argCommandText">Commandに設定するSQL文</param>
/// <param name="argDbContext">DbContext</param>
/// <returns>DBプロバイダファクトリに関連付けられたCommand</returns>
//************************************************************************
protected IDbCommand CreateCommand(string argCommandText, DbContext argDbContext)
{
IDbCommand cmd = ProviderFactory.CreateCommand();
cmd.CommandText = argCommandText;
cmd.Connection = argDbContext.Database.Connection;
return(cmd);
}
private DbCommand CreateCommand(CommandType commandType, string commandText = "")
{
DbCommand dm = ProviderFactory.CreateCommand();
{
if (dm != null)
{
dm.CommandType = commandType;
dm.CommandText = commandText;
}
return(dm);
}
}
public virtual DbCommand CreateDbCommand(string sql)
{
DbCommand command = ProviderFactory.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction == null ? null : Transaction;
command.CommandText = sql;
command.CommandTimeout = CommandTimeout;
return(command);
}
/// <summary>
/// Creates a command to query a saga by its unique identifier.
/// </summary>
/// <param name="sagaId">The identifier of the saga to create the command for.</param>
/// <returns>A new, configured <see cref="DbCommand">command</see>.</returns>
public virtual DbCommand NewQueryByIdCommand(Guid sagaId)
{
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.DbType = DbType.Guid;
parameter.ParameterName = "SagaId";
parameter.Value = sagaId;
command.Parameters.Add(parameter);
command.CommandText = Sql.QueryById;
return(command);
}
DbCommand NewEventQueryCommand <TKey>(TKey primaryKey, string sql)
{
Contract.Requires(!string.IsNullOrEmpty(sql));
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.ParameterName = "AggregateId";
parameter.Value = primaryKey;
command.Parameters.Add(parameter);
command.CommandText = sql;
return(command);
}
/// <summary>
/// Creates a command to store a saga.
/// </summary>
/// <param name="saga">The <see cref="ISagaData">saga data</see> to create the command for.</param>
/// <param name="correlationProperty">The property used to correlate the stored data.</param>
/// <param name="data">The serialized saga data.</param>
/// <returns>A new, configured <see cref="DbCommand">command</see>.</returns>
public virtual DbCommand NewStoreCommand(ISagaData saga, CorrelationProperty correlationProperty, Stream data)
{
Arg.NotNull(saga, nameof(saga));
Arg.NotNull(correlationProperty, nameof(correlationProperty));
Arg.NotNull(data, nameof(data));
Contract.Ensures(Contract.Result <DbCommand>() != null);
var command = ProviderFactory.CreateCommand();
var parameter = command.CreateParameter();
parameter.DbType = DbType.Guid;
parameter.ParameterName = "SagaId";
parameter.Value = saga.Id;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.String;
parameter.Size = 256;
parameter.ParameterName = "DataType";
parameter.Value = saga.GetType().GetAssemblyQualifiedName();
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.String;
parameter.Size = 90;
parameter.ParameterName = "PropertyName";
parameter.Value = correlationProperty.Property.Name;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Binary;
parameter.Size = 200;
parameter.ParameterName = "PropertyValue";
parameter.Value = ValueAsBinary(correlationProperty.Value);
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.DbType = DbType.Binary;
parameter.ParameterName = "Data";
parameter.Value = data;
command.Parameters.Add(parameter);
command.CommandText = Sql.Store;
return(command);
}
protected virtual DbCommand GetDbCommand()
{
var command = ProviderFactory.CreateCommand();
command.Connection = Connection;
command.CommandText = SqlQuery.Sql;
if (SqlQuery.CommandTimeout > 0)
{
command.CommandTimeout = SqlQuery.CommandTimeout;
}
if (SqlQuery.StoredProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (SqlQuery.Parameters != null)
{
foreach (var key in SqlQuery.Parameters.Keys)
{
var obj = SqlQuery.Parameters[key];
var pso = obj as PSObject;
if (pso != null)
{
obj = pso.ImmediateBaseObject;
}
var param = ProviderFactory.CreateParameter();
param.ParameterName = key.ToString();
param.Value = obj;
command.Parameters.Add(param);
WriteVerbose("Adding parameter " + param.ParameterName + "=" + param.Value.ToString());
}
}
if (Transaction == null && SqlQuery.CUD && TransactionContext == null && !NoTrans)
{
WriteVerbose("Starting transaction.");
Transaction = Connection.BeginTransaction();
}
if (Transaction != null)
{
command.Transaction = Transaction;
}
return(command);
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="action"></param>
/// <returns></returns>
public bool ExecuteTransaction(Action <DbCommand> action)
{
bool isSuccess = false;
using (DbConnection connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
using (DbTransaction trans = connection.BeginTransaction())
{
using (DbCommand command = ProviderFactory.CreateCommand())
{
try
{
command.Transaction = trans;
command.Connection = connection;
action(command);
trans.Commit();
isSuccess = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (command.Connection != null)
{
command.Connection.Close();
}
}
}
}
}
return(isSuccess);
}
/// <summary>
/// Create a new command instance
/// </summary>
/// <param name="pTransaction">A specific transaction</param>
/// <returns></returns>
public DbCommand CreateCommand(DbTransaction pTransaction)
{
return(ProviderFactory.CreateCommand());
}
/// <summary>
/// Retorna dados para a leitura das informações do banco de dados
/// </summary>
/// <param name="Command"></param>
/// <param name="param"></param>
/// <param name="TypeCommando"></param>
/// <param name="TimeOut"></param>
/// <returns></returns>
//public virtual bool LerProcedure(string pstrCommand, object[] parrParam, CommandType TypeCommando, out SqlDataReader pobjDataReader, int TimeOut)
//{
// int intIndexParams = 0;
// int intErrorParamIndex = -1;
// bool blnRet = true;
// SqlParameter objParamErroRetorno;
// string strValorParamErro;
// try
// {
// SqlCommand objCmd = new SqlCommand();
// objCmd.CommandText = pstrCommand;
// objCmd.CommandType = TypeCommando;
// if (TimeOut != 0)
// objCmd.CommandTimeout = TimeOut;
// if (SqlConn.Trans != null)
// objCmd.Transaction = SqlConn.Trans;
// DeriveParameters(objCmd);
// if (parrParam != null)
// {
// for (intIndexParams = 0; intIndexParams < parrParam.Length; intIndexParams++)
// {
// SqlParameter iDataParam = new SqlParameter();
// iDataParam = (SqlParameter)objCmd.Parameters[intIndexParams + 1];
// if (parrParam[intIndexParams] != null)
// iDataParam.Value = parrParam[intIndexParams];
// else
// iDataParam.Value = DBNull.Value;
// }
// }
// objCmd.Prepare();
// pobjDataReader = objCmd.ExecuteReader();
// //testar retorno erro
// if (intErrorParamIndex > -1)
// {
// objParamErroRetorno = (SqlParameter)objCmd.Parameters[intErrorParamIndex];
// strValorParamErro = objParamErroRetorno.Value.ToString();
// blnRet = (strValorParamErro == "0");
// }
// return blnRet;
// }
// catch (SystemException e)
// {
// throw e;
// }
//}
public virtual bool LerProcedure(string pstrCommand, object[] parrParam, out IDataReader pobjDataReader, int timeout)
{
int intIndexParams = 0;
// Para o provider nativo SQL, verificamos que o primeiro parâmetro (indice 0)
//é o código de retorno da procedure
//Para o provider OleDB não existe esse parametro 0. Testamos apenas com IBMDA400
int intParamOleDB;
int intErrorParamIndex = -1;
bool blnRet = true;
IDataParameter objParamErroRetorno;
string strValorParamErro;
try
{
IDbCommand objCmd = pf.CreateCommand();
objCmd.CommandText = pstrCommand;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandTimeout = timeout;
objCmd.Connection = SqlConn.Conexao;
if (SqlConn.Trans != null)
{
objCmd.Transaction = SqlConn.Trans;
}
DeriveParameters(ref objCmd);
// Como não existe uma interface para o CommandBuilder,
// precisamos determinar que tipo utilizaremos.
switch (menuProvider)
{
case ProviderType.SqlClient:
intParamOleDB = 1;
break;
case ProviderType.OracleClient:
case ProviderType.ODBC:
case ProviderType.OleDb:
case ProviderType.MySqlClient:
intParamOleDB = 0;
break;
default:
intParamOleDB = 0;
break;
}
if (parrParam != null)
{
for (intIndexParams = 0; intIndexParams < parrParam.Length; intIndexParams++)
{
IDataParameter iDataParam = pf.CreateDataParameter();
iDataParam = (IDataParameter)objCmd.Parameters[intIndexParams + intParamOleDB];
if (parrParam[intIndexParams] != null)
{
iDataParam.Value = parrParam[intIndexParams];
}
else
{
iDataParam.Value = DBNull.Value;
}
}
}
objCmd.Prepare();
if (menuProvider.Equals(ProviderType.MySqlClient))
{
pobjDataReader = objCmd.ExecuteReader(CommandBehavior.SequentialAccess);
}
else
{
pobjDataReader = objCmd.ExecuteReader();
}
//testar retorno erro
if (intErrorParamIndex > -1)
{
objParamErroRetorno = (IDataParameter)objCmd.Parameters[intErrorParamIndex];
strValorParamErro = objParamErroRetorno.Value.ToString();
blnRet = (strValorParamErro == "0");
}
return(blnRet);
}
catch (SystemException e)
{
throw e;
}
}
protected override DbCommand CreateCommand()
{
return(ProviderFactory.CreateCommand());
}
