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 Add(string pNumeParametru, int pValoare, bool pTestNull)
{
if (pTestNull && pValoare <= 0)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
else
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, pValoare));
}
}
public void Add(string pNumeParametru, CDefinitiiComune.EnumTipMoneda pValoare, bool pTestNull)
{
if (pValoare == CDefinitiiComune.EnumTipMoneda.Nedefinit && pTestNull)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
else
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, Convert.ToInt32(pValoare)));
}
}
public void Add(string pNumeParametru, object pValoare)
{
if (pValoare == null)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
else
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, pValoare));
}
}
public void Add(string pNumeParametru, DateTime pValoare, bool pTestNull)
{
if (pTestNull && pValoare == CConstante.DataNula)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
else
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, pValoare));
}
}
public void Add(string pNumeParametru, CDefinitiiComune.EnumRaspuns pValoare)
{
if (pValoare == CDefinitiiComune.EnumRaspuns.NuStiu || pValoare == null)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
else
{
if (pValoare == CDefinitiiComune.EnumRaspuns.Da)
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, true));
}
else
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, false));
}
}
}
internal static System.Data.SqlClient.SqlConnection getConexiuneNoua()
{
System.Data.SqlClient.SqlConnection conexiune = null;
if (suntConectatPeAlternativ)
{
conexiune = new System.Data.SqlClient.SqlConnection(CInterfataSQLServer.GetConnectionStringAlternativ());
}
else
{
conexiune = new System.Data.SqlClient.SqlConnection(CInterfataSQLServer.GetConnectionString());
}
try
{
conexiune.Open();
}
catch (System.Data.SqlClient.SqlException)
{
conexiune.Dispose();
conexiune = null;
if (!suntConectatPeAlternativ)
{
conexiune = new System.Data.SqlClient.SqlConnection(CInterfataSQLServer.GetConnectionStringAlternativ());
}
else
{
conexiune = new System.Data.SqlClient.SqlConnection(CInterfataSQLServer.GetConnectionString());
}
try
{
conexiune.Open();
suntConectatPeAlternativ = !suntConectatPeAlternativ;
}
catch (System.Data.SqlClient.SqlException)
{
}
}
return(conexiune);
}
public void AddEmpty(string pNumeParametru)
{
this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
}
/// <summary>
/// Constructor util pentru metodele GetById
/// </summary>
/// <param name="pNumeParametru">Numele parametrului ID</param>
/// <param name="pValoare">valoarea parametrului ID</param>
public BColectieParametriSQL(string pNumeParametru, int pValoare)
{
this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, pValoare));
}
//private static void startSQLService(IDbConnection pConexiune)
//{
// ServiceController[] controllers = ServiceController.GetServices();
// foreach (var item in controllers)
// {
// if (item.ServiceName.Contains("MSSQL$") && item.Status != ServiceControllerStatus.Running)
// {
// var sc = new System.ServiceProcess.ServiceController(item.ServiceName, ((System.Data.SqlClient.SqlConnection)(pConexiune)).WorkstationId);
// sc.Start();
// sc.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(10));
// System.Threading.Thread.Sleep(5000);
// //item.Start();
// //item.WaitForStatus(ServiceControllerStatus.Running, TimeSpan.FromSeconds(20));
// //break;
// }
// }
// controllers = null;
//}
/// <summary>
/// Metoda ce permite obtinerea unei conexiuni deschise cu o transactie
/// </summary>
/// <param name="sConnexionString"></param>
/// <returns>Conexiunea cu tranzactia in parametru</returns>
public static IDbTransaction GetTransactionOnConnection(string sConnexionString)
{
string sqlConnect;
if (!String.IsNullOrEmpty(sConnexionString))
{
sqlConnect = sConnexionString;
}
else
{
if (suntConectatPeAlternativ)
{
sqlConnect = CInterfataSQLServer.GetConnectionStringAlternativ();
}
else
{
sqlConnect = CInterfataSQLServer.GetConnectionString();
}
}
IDbConnection cn = CInterfataSQLServer.getNewDataConnection(sqlConnect);
try
{
cn.Open();
}
catch (System.Data.SqlClient.SqlException)
{
if (string.IsNullOrEmpty(sConnexionString))
{
cn.Dispose();
cn = null;
//Daca nu merge pe normal incercam pe alternativ
if (suntConectatPeAlternativ)
{
sqlConnect = CInterfataSQLServer.GetConnectionString();
}
else
{
sqlConnect = CInterfataSQLServer.GetConnectionStringAlternativ();
}
cn = CInterfataSQLServer.getNewDataConnection(sqlConnect);
try
{
cn.Open();
//Pentru a sti care merge
suntConectatPeAlternativ = !suntConectatPeAlternativ;
}
catch (System.Data.SqlClient.SqlException)
{
if (!suntConectatPeAlternativ)
{
startSQLService(cn);
cn.Open();
}
}
}
else
{
startSQLService(cn);
cn.Open();
}
}
try
{
IDbTransaction Transac;
Transac = cn.BeginTransaction();
return(Transac);
}
catch (Exception ex)
{
cn.Close();
throw ex;
}
}
public static object GetScalarByComandaDirecta(string pComandaSQL, List <IDataParameter> pListaParametri, IDbTransaction pTranzactie)
{
IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand();
try
{
//Configure l'objet SqlCommand
cmdSqlCommand.CommandType = CommandType.Text; //Tipul comenzii = text
cmdSqlCommand.CommandText = pComandaSQL; //Comanda SQL
//Specificam conexiunea la BDD
if (pTranzactie == null)
{
//Atasam conexiunea la comanda SQL
cmdSqlCommand.Connection = getConexiuneNoua(); //ajunge aici?
}
else
{
//Atasam conexiunea tranzactiei si tranzactia la comanda SQL
cmdSqlCommand.Connection = pTranzactie.Connection;
cmdSqlCommand.Transaction = pTranzactie;
}
//Stergem lista de parametri existenti
cmdSqlCommand.Parameters.Clear();
//Adaugam lista de parametri transmiri in parametru
if (pListaParametri != null)
{
foreach (IDataParameter sqlParam in pListaParametri)
{
cmdSqlCommand.Parameters.Add(sqlParam);
}
}
//Daca avem o tranzactie inseamna ca o conexiune a fost deschisa
if (pTranzactie == null)
{
if (cmdSqlCommand.Connection.State != ConnectionState.Open)
{
cmdSqlCommand.Connection.Open();
}
}
//Obiectul de returnat
Object returnValue = cmdSqlCommand.ExecuteScalar();
//In cazul in care nu a fost transmisa o tranzactie, inchidem conexiunea
if (pTranzactie == null)
{
cmdSqlCommand.Connection.Close();
}
return(returnValue);
}
catch (Exception exc)
{
if (cmdSqlCommand.Connection.State == ConnectionState.Open)
{
cmdSqlCommand.Connection.Close();
}
throw exc;
}
finally
{
cmdSqlCommand.Dispose();
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;
}
}
/// <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;
}
}
internal static string getConnectionString()
{
return(CInterfataSQLServer.GetConnectionString());
}