public static int ExecuteByComandaDirecta(string pComanda, BColectieParametriSQL pListaParametri, IDbTransaction pTranzactie)
{
IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand();
try
{
//Configuram comanda (SqlCommand)
cmdSqlCommand.CommandType = CommandType.Text; //Fixam tipul comenzii la text
cmdSqlCommand.CommandText = pComanda; //Numele procedurii stocate
//Detaliem conexiunea la baza de date
//Atasam conexiunea comenzii SQL
if (pTranzactie == null)
{
cmdSqlCommand.Connection = getConexiuneNoua();
}
else
{
//Atasam conexiunea tranzactiei, si tranzactia, comenzii SQL
cmdSqlCommand.Connection = pTranzactie.Connection;
cmdSqlCommand.Transaction = (System.Data.SqlClient.SqlTransaction)(pTranzactie);
}
//Stergem parametrii comenzii (SqlCommand)
cmdSqlCommand.Parameters.Clear();
//Adaugam parametrii trimisi pentru a-i adauga comenzii (SqlCommand)
if (pListaParametri != null)
{
pListaParametri.AdaugaParametriiLaComanda(cmdSqlCommand);
}
//Daca exista tranzactie, inseamna ca o conexiune a fost deschisa
if (pTranzactie == null)
{
if (cmdSqlCommand.Connection.State != ConnectionState.Open)
{
cmdSqlCommand.Connection.Open();
}
}
//Executa procedura stocata, returnand numarul de linii afectate
//A se utiliza pentru INSERT, UPDATE, DELETE
return(cmdSqlCommand.ExecuteNonQuery());
}
catch (Exception exc)
{
if (cmdSqlCommand.Connection.State == ConnectionState.Open)
{
cmdSqlCommand.Connection.Close();
}
throw exc;
}
finally
{
cmdSqlCommand = null;
}
}
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;
}
}