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 void AppendForUpdate(BColectieParametriSQL pListaParametri, StringBuilder pSet)
{
foreach (KeyValuePair <string, object> corespondenta in this)
{
pSet.AppendFormat(" {0} = @{0} , ", corespondenta.Key);
pListaParametri.Add(corespondenta.Key, corespondenta.Value);
}
}
public void AppendForInsert(BColectieParametriSQL pListaParametri, StringBuilder pColoane, StringBuilder pValori)
{
foreach (KeyValuePair <string, object> corespondenta in this)
{
pColoane.Append(string.Format("{0} , ", corespondenta.Key));
pValori.Append(string.Format("@{0} , ", corespondenta.Key));
pListaParametri.Add(string.Format("@{0}", corespondenta.Key), corespondenta.Value);
}
}
public void AppendForConditie(BColectieParametriSQL pListaParametri, StringBuilder pConditie)
{
foreach (KeyValuePair <string, object> coloanaId in this)
{
if (coloanaId.Value == DBNull.Value)
{
continue;
}
pConditie.AppendFormat(" {0} = @{0} AND ", coloanaId.Key);
pListaParametri.Add(coloanaId.Key, coloanaId.Value);
}
}
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;
}
}
public static DataSet GetDataSetByComandaDirecta(string sComandaDirecta, BColectieParametriSQL pListaParametri, IDbTransaction pTranzactieSQL)
{
return(GetDataSetByComandaDirecta(sComandaDirecta, pListaParametri, pTranzactieSQL, string.Empty));
}
