/// <summary>
/// Executa uma query sem parâmetros, retornando um Int32, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>Int32 indicando a quantidade de linhas afetadas (INSERT, UPDATE ou DELETE).</returns>
public static int Instance(string _connectionKey, string _inQuery, out string _errorMessage)
{
DBInstance dbInstance = new DBInstance();
_errorMessage = String.Empty;
int recordsAffected = 0;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return recordsAffected = -1;
}
try
{
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
dbInstance.DbConnection.Open();
recordsAffected = dbInstance.Command.ExecuteNonQuery();
dbInstance.DbConnection.Close();
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return recordsAffected;
}
/// <summary>
/// Executa uma query com parâmetros, retornando uma DataReader, utilizando a conexão do banco.
/// </summary>
/// <param name="_providerKey">Chave do Provider do AppSettings do web.config.</param>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>System.Data.DataReader com as linhas da Query.</returns>
public static DbDataReader InstanceDataReader(string _providerKey, string _connectionKey, string _inQuery, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
DbDataReader dataReader = null;
DbParameter parameter = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataReader;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_providerKey, _connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory(_providerKey);
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
dbInstance.Command.Connection.Open();
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (!dataReader.HasRows)
{
_errorMessage = "Nenhum registro encontrado. <BR>";
dbInstance.Command.Connection.Close();
return null;
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
}
return (dataReader);
}
/// <summary>
/// Executa uma ou várias querys com parâmetros, retornando um DataSet com o resultado de cada query, utilizando a conexão do banco e transações.
/// </summary>
/// <param name="_providerKey">Chave do Provider do AppSettings do web.config.</param>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuerySet">Querys de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>DataSet com vários DataTables, cada DataTable com o resultado de uma das querys.</returns>
public static DataSet InstanceResultSet(string _providerKey, string _connectionKey, string _inQuerySet, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
DataSet dataSet = new DataSet();
DbParameter parameter = null;
DbDataReader dataReader = null;
_errorMessage = String.Empty;
if (_inQuerySet.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataSet;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_providerKey, _connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory(_providerKey);
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inQuerySet;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
parameter = dbInstance.Factory.CreateParameter();
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dbInstance.Command.Connection.Open();
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (!dataReader.HasRows)
{
_errorMessage = "Nenhum registro encontrado. <BR>";
dbInstance.Command.Connection.Close();
return null;
}
else
{
int count = 0;
while (!dataReader.IsClosed)
{
dataSet.Tables.Add(new DataTable());
dataSet.Tables[count].Load(dataReader, LoadOption.OverwriteChanges);
count++;
}
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataSet);
}
/// <summary>
///
/// </summary>
/// <param name="_inProcedure"></param>
/// <param name="_errorMessage"></param>
/// <param name="_transManager"></param>
/// <param name="_transState"></param>
/// <param name="_params"></param>
/// <returns></returns>
public static Hashtable InstanceProcedureOutData(string _inProcedure, out string _errorMessage, ref DBTransactionManager _transManager, DBTransactionManager.TransactionState _transState, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
Hashtable ht = new Hashtable();
int recordsAffected = 0;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return null;
}
try
{
//Executa uma transação isolada
if (_transState == DBTransactionManager.TransactionState.StandAlone)
{
//Abre a conexão
if (_transManager.DbConnection.State == ConnectionState.Closed)
_transManager.DbConnection.Open();
//Inicia a transação
_transManager.Transaction = _transManager.DbConnection.BeginTransaction();
ProcedureTransactionExecute(_inProcedure, out recordsAffected, _transManager, _params);
//Comita a transação
_transManager.Command.Transaction.Commit();
//Fecha a conexão da transação
_transManager.EndTransaction();
}
else
{
//Bloqueio de execução depois da detecção de um erro
if (!_transManager.TransHold)
{
//Inicia a transação.DBTransactionManager(true)
if (_transState == DBTransactionManager.TransactionState.Begin)
{
//Abre a conexão e inicia a transação
if (_transManager.DbConnection.State == ConnectionState.Closed)
_transManager.DbConnection.Open();
_transManager.Transaction = _transManager.DbConnection.BeginTransaction();
ProcedureTransactionExecute(_inProcedure, out recordsAffected, _transManager, _params);
}
//Permite a execução de + de três comandos em uma transação
else if (_transState == DBTransactionManager.TransactionState.ContinueExecution)
{
ProcedureTransactionExecute(_inProcedure, out recordsAffected, _transManager, _params);
}
//Finaliza a transação.DBTransactionManager(false)
else if (_transState == DBTransactionManager.TransactionState.End)
{
ProcedureTransactionExecute(_inProcedure, out recordsAffected, _transManager, _params);
//Comita a transação
_transManager.Command.Transaction.Commit();
//Fecha a conexão da transação
_transManager.EndTransaction();
}
}
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção de banco ocorreu durante a execução dos comandos. Nenhuma das operações foi executada. <BR>";
_errorMessage = _errorMessage + exp.Message;
if (_transManager.Command.Transaction != null)
_transManager.Command.Transaction.Rollback();
//Bloqueia a execução de qualquer chamada posterior dentro da transação em execução
_transManager.TransHold = true;
//Fecha a conexão da transação
_transManager.EndTransaction();
}
catch (Exception exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução dos comandos. Nenhuma das operações foi executada. <BR>";
_errorMessage = _errorMessage + exp.Message;
if (_transManager.Command.Transaction != null)
_transManager.Command.Transaction.Rollback();
//Bloqueia a execução de qualquer chamada posterior dentro da transação em execução
_transManager.TransHold = true;
//Fecha a conexão da transação
_transManager.EndTransaction();
}
//Recupera os valores de saída e insere numa HashTable
for (int i = 0; i < _params.Length; i++)
{
ht.Add(_params[i].ParameterName, _params[i].Value);
}
return ht;
}
/// <summary>
/// Executa uma query com parâmetros, retornando um Int32, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>Hashtable.</returns>
public static Hashtable InstanceQueryOutData(string _connectionKey, string _inQuery, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
Hashtable ht = new Hashtable();
DbParameter parameter = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return null;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
parameter = dbInstance.Factory.CreateParameter();
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dbInstance.DbConnection.Open();
dbInstance.Command.ExecuteNonQuery();
dbInstance.DbConnection.Close();
//Recupera os valores de saída e insere numa HashTable
for (int i = 0; i < _params.Length; i++)
{
ht.Add(_params[i].ParameterName, _params[i].Value);
}
return ht;
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return ht;
}
/// <summary>
/// Executa uma procedure com parâmetros, retornando um System.Object, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inProcedure">Nome da StoredProcedure.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Object</returns>
public static object InstanceProcedureExecuteScalar(string _connectionKey, string _inProcedure, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
object scalar = null;
DbParameter parameter = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return scalar = null;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
parameter = dbInstance.Factory.CreateParameter();
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dbInstance.DbConnection.Open();
scalar = dbInstance.Command.ExecuteScalar();
dbInstance.DbConnection.Close();
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return scalar;
}
/// <summary>
/// Executa uma procedure com parâmetros, retornando um System.Object, utilizando a conexão do banco e transações.
/// </summary>
/// <param name="_inProcedure">Procedure de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_transManager">Objeto DBTransactionManager da transação.</param>
/// <param name="_transState">Estado de execução do bloco da transação. (Begin, End, ContinueExecution, StandAlone)</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Object</returns>
public static object InstanceProcedureExecuteScalar(string _inProcedure, out string _errorMessage, ref DBTransactionManager _transManager, DBTransactionManager.TransactionState _transState, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
_errorMessage = String.Empty;
object result = null;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return null;
}
try
{
//Executa uma transação isolada
if (_transState == DBTransactionManager.TransactionState.StandAlone)
{
//Abre a conexão
if (_transManager.DbConnection.State == ConnectionState.Closed)
_transManager.DbConnection.Open();
//Inicia a transação
_transManager.Transaction = _transManager.DbConnection.BeginTransaction();
ProcedureTransactionExecuteEscalar(_inProcedure, out result, _transManager, _params);
//Comita a transação
_transManager.Command.Transaction.Commit();
//Fecha a conexão da transação
_transManager.EndTransaction();
}
else
{
//Bloqueio de execução depois da detecção de um erro
if (!_transManager.TransHold)
{
//Inicia a transação.DBTransactionManager(true)
if (_transState == DBTransactionManager.TransactionState.Begin)
{
//Abre a conexão e inicia a transação
if (_transManager.DbConnection.State == ConnectionState.Closed)
_transManager.DbConnection.Open();
_transManager.Transaction = _transManager.DbConnection.BeginTransaction();
ProcedureTransactionExecuteEscalar(_inProcedure, out result, _transManager, _params);
}
//Permite a execução de + de três comandos em uma transação
else if (_transState == DBTransactionManager.TransactionState.ContinueExecution)
{
ProcedureTransactionExecuteEscalar(_inProcedure, out result, _transManager, _params);
}
//Finaliza a transação.DBTransactionManager(false)
else if (_transState == DBTransactionManager.TransactionState.End)
{
ProcedureTransactionExecuteEscalar(_inProcedure, out result, _transManager, _params);
//Comita a transação
_transManager.Command.Transaction.Commit();
//Fecha a conexão da transação
_transManager.EndTransaction();
}
}
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção de banco ocorreu durante a execução dos comandos. Nenhuma das operações foi executada. <BR>";
_errorMessage = _errorMessage + exp.Message;
if (_transManager.Command.Transaction != null)
_transManager.Command.Transaction.Rollback();
//Bloqueia a execução de qualquer chamada posterior dentro da transação em execução
_transManager.TransHold = true;
//Fecha a conexão da transação
_transManager.EndTransaction();
}
catch (Exception exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução dos comandos. Nenhuma das operações foi executada. <BR>";
_errorMessage = _errorMessage + exp.Message;
if (_transManager.Command.Transaction != null)
_transManager.Command.Transaction.Rollback();
//Bloqueia a execução de qualquer chamada posterior dentro da transação em execução
_transManager.TransHold = true;
//Fecha a conexão da transação
_transManager.EndTransaction();
}
return null;
}
/// <summary>
/// Executa uma procedure com parâmetros, retornando uma DataTable, utilizando a conexão do banco.
/// </summary>
/// <param name="_providerKey">Chave do Provider do AppSettings do web.config.</param>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inProcedure">Procedure de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Data.DataTable com as linhas da Query.</returns>
public static DataTable InstanceProcedureDataTable(string _providerKey, string _connectionKey, string _inProcedure, out string _errorMessage, Hashtable _params)
{
DBInstance dbInstance = new DBInstance();
DataTable dataTable = new DataTable();
DbDataReader dataReader = null;
DbParameter parameter = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataTable;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
foreach (DictionaryEntry de in _params)
{
parameter.ParameterName = de.Key.ToString();
parameter.Value = de.Value;
dbInstance.Command.Parameters.Add(parameter);
}
dbInstance.Command.Connection.Open();
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (dataReader.HasRows)
{
dataTable.Load(dataReader);
}
else
{
_errorMessage = "Nenhum registro encontrado. <BR>";
return null;
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataReader != null)
{
dataReader.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataTable);
}
/// <summary>
/// Executa uma procedure com parâmetros, retornando uma List, utilizando a conexão do banco.
/// </summary>
/// <param name="_providerKey">Chave do Provider do AppSettings do web.config.</param>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inProcedure">Procedure de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Collections.Generic.List(DataRow) com as linhas da Procedure.</returns>
public static List<DataRow> InstanceProcedureDataTableOutList(string _providerKey, string _connectionKey, string _inProcedure, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
DataTable dataTable = new DataTable();
DbDataReader dataReader = null;
DbParameter parameter = null;
List<DataRow> list = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return list;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_providerKey, _connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory(_providerKey);
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dbInstance.Command.Connection.Open();
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (dataReader.HasRows)
{
dataTable.Load(dataReader);
list = new List<DataRow>(dataTable.Select());
}
else
{
_errorMessage = "Nenhum registro encontrado. <BR>";
return null;
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataReader != null)
{
dataReader.Dispose();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (list);
}
/// <summary>
/// Executa uma query com parâmetros, retornando um DataSet, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inProcedure">Nome da StoredProcedure.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_hashTable">HashTable com os parâmetros de saída.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Data.DataSet com as linhas da StoredProcedure.</returns>
public static DataSet InstanceProcedureDataSet(string _connectionKey, string _inProcedure, out string _errorMessage, out Hashtable _hashTable, params DbParameter[] _params)
{
_hashTable = new Hashtable();
DBInstance dbInstance = new DBInstance();
DataSet dataSet = new DataSet();
DbParameter parameter = null;
DbDataAdapter dataAdapter = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataSet;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dataAdapter = dbInstance.Factory.CreateDataAdapter();
parameter = dbInstance.Factory.CreateParameter();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
//Adiciona os parâmetros de entrada
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dataAdapter.SelectCommand = dbInstance.Command;
dataAdapter.Fill(dataSet);
//Recupera os valores de saída e insere numa HashTable
for (int i = 0; i < _params.Length; i++)
{
_hashTable.Add(_params[i].ParameterName, _params[i].Value); //Valores de saída
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataSet);
}
/// <summary>
/// Executa uma query com parâmetros, retornando um DataSet, utilizando a conexão do banco.
/// </summary>
/// <param name="_inProcedure">Nome da StoredProcedure.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Hashtable com o parâmetro.</param>
/// <returns>System.Data.DataSet com as linhas da StoredProcedure.</returns>
public static DataSet InstanceProcedureDataSet(string _inProcedure, out string _errorMessage, Hashtable _params)
{
DBInstance dbInstance = new DBInstance();
DataSet dataSet = new DataSet();
DbParameter parameter = null;
DbDataAdapter dataAdapter = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataSet;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection();
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dataAdapter = dbInstance.Factory.CreateDataAdapter();
parameter = dbInstance.Factory.CreateParameter();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
foreach (DictionaryEntry de in _params)
{
parameter.ParameterName = de.Key.ToString();
parameter.Value = de.Value;
dbInstance.Command.Parameters.Add(parameter);
}
dataAdapter.SelectCommand = dbInstance.Command;
dataAdapter.Fill(dataSet);
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataSet);
}
/// <summary>
/// Executa uma procedure sem parâmetros, retornando uma DataReader, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inProcedure">Nome da StoredProcedure.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>System.Data.DataReader com as linhas da StoredProcedure.</returns>
public static DbDataReader InstanceProcedureDataReader(string _connectionKey, string _inProcedure, out string _errorMessage)
{
DBInstance dbInstance = new DBInstance();
DbDataReader dataReader = null;
_errorMessage = String.Empty;
if (_inProcedure.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataReader;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inProcedure;
dbInstance.Command.CommandType = CommandType.StoredProcedure;
dbInstance.Command.Connection = dbInstance.DbConnection;
dbInstance.Command.Connection.Open();
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (!dataReader.HasRows)
{
_errorMessage = "Nenhum registro encontrado. <BR>";
dbInstance.Command.Connection.Close();
return null;
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
}
return (dataReader);
}
/// <summary>
/// Executa uma query sem parâmetros, retornando um System.Object, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>System.Object</returns>
public static object InstanceExecuteScalar(string _connectionKey, string _inQuery, out string _errorMessage)
{
DBInstance dbInstance = new DBInstance();
object scalar = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return scalar;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
dbInstance.DbConnection.Open();
scalar = dbInstance.Command.ExecuteScalar();
dbInstance.DbConnection.Close();
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return scalar;
}
//Query InstanceDataTable
/// <summary>
/// Executa uma query sem parâmetros, retornando uma DataTable, utilizando a conexão do banco.
/// </summary>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>System.Data.DataTable com as linhas da Query.</returns>
public static DataTable InstanceDataTable(string _inQuery, out string _errorMessage)
{
DBInstance dbInstance = new DBInstance();
DataTable dataTable = new DataTable();
DbDataAdapter dataAdapter = null;
DbDataReader dataReader = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataTable;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection();
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dataAdapter = dbInstance.Factory.CreateDataAdapter();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
dbInstance.Command.Connection.Open();
dataReader = dbInstance.Command.ExecuteReader(CommandBehavior.CloseConnection);
if (dataReader.HasRows)
{
dataTable.Load(dataReader);
}
else
{
_errorMessage = "Nenhum registro encontrado. <BR>";
return null;
}
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (dataReader != null)
{
dataReader.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataTable);
}
/// <summary>
/// Executa uma query com parâmetros, retornando um DataSet, utilizando a conexão do banco.
/// </summary>
/// <param name="_connectionKey">Chave de Conexão do AppSettings do web.config.</param>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <param name="_params">Vetor de DbParameter com os parâmetros.</param>
/// <returns>System.Data.DataSet com as linhas da Query.</returns>
public static DataSet InstanceDataSet(string _connectionKey, string _inQuery, out string _errorMessage, params DbParameter[] _params)
{
DBInstance dbInstance = new DBInstance();
DataSet dataSet = new DataSet();
DbParameter parameter = null;
DbDataAdapter dataAdapter = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataSet;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection(_connectionKey);
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dataAdapter = dbInstance.Factory.CreateDataAdapter();
parameter = dbInstance.Factory.CreateParameter();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
//
for (int i = 0; i < _params.Length; i++)
{
parameter = _params[i];
dbInstance.Command.Parameters.Add(parameter);
}
dataAdapter.SelectCommand = dbInstance.Command;
dataAdapter.Fill(dataSet);
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (dbInstance.DbConnection.State == ConnectionState.Open)
{
dbInstance.DbConnection.Close();
}
if (dbInstance.DbConnection != null)
{
dbInstance.DbConnection.Dispose();
}
}
return (dataSet);
}
//Query InstanceDataSet
/// <summary>
/// Executa uma query sem parâmetros, retornando um DataSet, utilizando a conexão do banco.
/// </summary>
/// <param name="_inQuery">Query de Entrada.</param>
/// <param name="_errorMessage">Mensagem de Erro.</param>
/// <returns>System.Data.DataSet com as linhas da Query.</returns>
public static DataSet InstanceDataSet(string _inQuery, out string _errorMessage)
{
DBInstance dbInstance = new DBInstance();
DataSet dataSet = new DataSet();
DbDataAdapter dataAdapter = null;
_errorMessage = String.Empty;
if (_inQuery.Length == 0)
{
_errorMessage = "Nenhum comando SQL foi informado.";
return dataSet;
}
try
{
//Cria uma conexao na base padrão do AppSettings (Web.config)
dbInstance.DbConnectionProvider = new DBConnectionProvider();
dbInstance.DbConnection = dbInstance.DbConnectionProvider.GetConnection();
dbInstance.Factory = DBConnectionProvider.GetDbFactory();
dbInstance.Command = dbInstance.Factory.CreateCommand();
dataAdapter = dbInstance.Factory.CreateDataAdapter();
dbInstance.Command.CommandText = _inQuery;
dbInstance.Command.CommandType = CommandType.Text;
dbInstance.Command.Connection = dbInstance.DbConnection;
dataAdapter.SelectCommand = dbInstance.Command;
dataAdapter.Fill(dataSet);
}
catch (DbException exp)
{
_errorMessage = "Uma exceção ocorreu durante a execução do comando. <BR>";
_errorMessage = _errorMessage + exp.Message;
}
finally
{
if (dbInstance.Command != null)
{
dbInstance.Command.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
return (dataSet);
}