Ejemplo n.º 1
0
        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;
            }
        }
Ejemplo n.º 2
0
 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));
     }
 }
Ejemplo n.º 3
0
 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)));
     }
 }
Ejemplo n.º 4
0
 public void Add(string pNumeParametru, object pValoare)
 {
     if (pValoare == null)
     {
         this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
     }
     else
     {
         this.Add(CInterfataSQLServer.getNewDataParameterForStoredProcedure(pNumeParametru, pValoare));
     }
 }
Ejemplo n.º 5
0
 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));
     }
 }
Ejemplo n.º 6
0
 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));
         }
     }
 }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
 public void AddEmpty(string pNumeParametru)
 {
     this.Add(CInterfataSQLServer.getParametruEmpty(pNumeParametru));
 }
Ejemplo n.º 9
0
 /// <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));
 }
Ejemplo n.º 10
0
        //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;
            }
        }
Ejemplo n.º 11
0
        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;
            }
        }
Ejemplo n.º 12
0
        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;
            }
        }
Ejemplo n.º 13
0
        /// <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;
            }
        }
Ejemplo n.º 14
0
 internal static string getConnectionString()
 {
     return(CInterfataSQLServer.GetConnectionString());
 }