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()); }