public static DataSet GetDataSetByComandaDirecta(string sComandaDirecta, BColectieParametriSQL pListaParametri, IDbTransaction pTranzactieSQL, string pConnexionString)
{
IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand();
IDbDataAdapter adpSqlDataAdapter = CInterfataSQLServer.getNewDataAdaptater();
DataSet dsDataSet = new DataSet();
try
{
//Configuram comanda
using (cmdSqlCommand)
{
cmdSqlCommand.CommandType = CommandType.Text; //precizam ca executam o comanda directa
cmdSqlCommand.CommandText = sComandaDirecta; //textul comenzii directe
//Conexiunea la BDD pe care o vom utiliza
if (pTranzactieSQL == null)
{
IDbTransaction myTrans = GetTransactionOnConnection(pConnexionString);
cmdSqlCommand.Connection = myTrans.Connection;
cmdSqlCommand.Transaction = myTrans;
}
else
{ //Atasam conexiunea si tranzactia comenzii
cmdSqlCommand.Connection = pTranzactieSQL.Connection;
cmdSqlCommand.Transaction = pTranzactieSQL;
}
//Adaugam noii parametri la comanda
if (pListaParametri != null)
{
pListaParametri.AdaugaParametriiLaComanda(cmdSqlCommand);
}
}
//Configuram un SqlDataAdapter pentru a folosi SqlCommand si a incarca DataSet-ul
adpSqlDataAdapter.SelectCommand = cmdSqlCommand;
adpSqlDataAdapter.Fill(dsDataSet);
//Daca nu am pasat o tranzactie atunci inchidem si facem comit tranzactiei create si utilizate
if (pTranzactieSQL == null)
{
CloseTransactionOnConnection(cmdSqlCommand.Transaction, true);
cmdSqlCommand.Connection.Close();
cmdSqlCommand.Connection.Dispose();
}
return(dsDataSet);
}
catch (Exception ex)
{
if (cmdSqlCommand.Connection != null && cmdSqlCommand.Connection.State == ConnectionState.Open)
{
//daca nu am transmis tranzactie
if (pTranzactieSQL == null)
{
//Daca exista o tranzactie creata in interiorul acestei metode
if (cmdSqlCommand.Transaction != null)
{
CloseTransactionOnConnection(cmdSqlCommand.Transaction, false);
}
}
cmdSqlCommand.Connection.Close();
cmdSqlCommand.Connection.Dispose();
}
throw ex;
}
finally
{
cmdSqlCommand.Dispose();
cmdSqlCommand = null;
adpSqlDataAdapter = null;
dsDataSet = null;
}
}
/// <summary>
/// Executa o PS de selectie si returneaza intr-un DataSet inregistrarile ce corespund acestei executii
/// </summary>
/// <param name="sNumeProcedura">numele procedurii stocate pe care dorim sa o executam</param>
/// <param name="lstParametri">lista de parametri pasati PS</param>
/// <param name="xTranzactieSQL">Tranzactia in cadrul careia executam PS</param>
/// <param name="sConnexionString">poate fi utilizat doar daca nu transmitem tranzactie in parametru deoarece acest connection string este atasat noii tranzactii</param>
/// <returns>Un DataSet ce contine rezultatul executiei procedurii stocate</returns>
public static DataSet GetDataSetByStoredProc(string sNumeProcedura, List <IDataParameter> lstParametri, IDbTransaction xTranzactieSQL, string sConnexionString)
{
IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand();
IDbDataAdapter adpSqlDataAdapter = CInterfataSQLServer.getNewDataAdaptater();
DataSet dsDataSet = new DataSet();
try
{
//Configuram comanda
using (cmdSqlCommand)
{
cmdSqlCommand.CommandType = CommandType.StoredProcedure; //precizam ca executam o procedura stocata
cmdSqlCommand.CommandText = sNumeProcedura; //numele procedurii stocate
//Conexiunea la BDD pe care o vom utiliza
if (xTranzactieSQL == null)
{
IDbTransaction myTrans = GetTransactionOnConnection(sConnexionString);
cmdSqlCommand.Connection = myTrans.Connection;
cmdSqlCommand.Transaction = myTrans;
}
else
{ //Atasam conexiunea si tranzactia comenzii
cmdSqlCommand.Connection = xTranzactieSQL.Connection;
cmdSqlCommand.Transaction = xTranzactieSQL;
}
//Stergem parametrii precedenti ai comenzii
if (cmdSqlCommand.Parameters != null)
{
cmdSqlCommand.Parameters.Clear();
}
//Adaugam noii parametri la comanda
if (lstParametri != null)
{
foreach (IDataParameter sqlParam in lstParametri)
{
cmdSqlCommand.Parameters.Add(sqlParam);
}
}
}
//Configuram un SqlDataAdapter pentru a folosi SqlCommand si a incarca DataSet-ul
adpSqlDataAdapter.SelectCommand = cmdSqlCommand;
try
{
adpSqlDataAdapter.Fill(dsDataSet);
}
catch (Exception)
{
//in caz de eroare mai incercam o data
adpSqlDataAdapter.Fill(dsDataSet);
}
//Daca nu am pasat o tranzactie atunci inchidem si facem comit tranzactiei create si utilizate
if (xTranzactieSQL == null)
{
CloseTransactionOnConnection(cmdSqlCommand.Transaction, true);
cmdSqlCommand.Connection.Close();
cmdSqlCommand.Connection.Dispose();
}
return(dsDataSet);
}
catch (Exception ex)
{
if (cmdSqlCommand.Connection != null && cmdSqlCommand.Connection.State == ConnectionState.Open)
{
//daca nu am transmis tranzactie
if (xTranzactieSQL == null)
{
//Daca exista o tranzactie creata in interiorul acestei metode
if (cmdSqlCommand.Transaction != null)
{
CloseTransactionOnConnection(cmdSqlCommand.Transaction, false);
}
}
cmdSqlCommand.Connection.Close();
cmdSqlCommand.Connection.Dispose();
}
throw ex;
}
finally
{
cmdSqlCommand.Dispose();
cmdSqlCommand = null;
adpSqlDataAdapter = null;
dsDataSet = null;
}
}