Example #1
0
        // Run_Transaction_Non_Query using string Cmd
        public bool Run_Transaction_Non_Query(string Cmd_String)
        {
            if (Is_Connected())
            {
                Open_Con();
                SqlTransaction Sqltransaction = SqlCon.BeginTransaction();
                try
                {
                    using (SqlCommand SqlCmd = new SqlCommand(Cmd_String, SqlCon, Sqltransaction))
                    {
                        SqlCmd.ExecuteNonQuery();
                        Sqltransaction.Commit();
                        return(true);
                    };
                }
                catch
                {
                    Sqltransaction.Rollback();
                    return(false);
                }
                finally
                {
                    Sqltransaction.Dispose();
                    Close_Con();
                }
            }

            return(false);
        }
        /// <summary>
        /// Constructs an instance of an atom transaction scope.
        /// </summary>
        /// <param name="kind">Type of transaction scope.</param>
        /// <param name="conn">Connection to use for the transaction scope.</param>
        protected internal SqlStoreTransactionScope(StoreTransactionScopeKind kind, SqlConnection conn)
        {
            this.Kind = kind;
            _conn = conn;

            switch (this.Kind)
            {
                case StoreTransactionScopeKind.ReadOnly:
                    SqlUtils.WithSqlExceptionHandling(() =>
                    {
                        _tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                    });
                    break;
                case StoreTransactionScopeKind.ReadWrite:
                    SqlUtils.WithSqlExceptionHandling(() =>
                    {
                        _tran = conn.BeginTransaction(IsolationLevel.RepeatableRead);
                    });
                    break;
                default:
                    // Do not start any transaction.
                    Debug.Assert(this.Kind == StoreTransactionScopeKind.NonTransactional);
                    break;
            }
        }
Example #3
0
        public int NonQuery(string sqlstr)
        {
            int    result = 0;
            string msg    = "";

            if (CONN.State != ConnectionState.Open)
            {
                CONN.Open();
            }
            CMD.Connection  = CONN;
            CMD.CommandText = sqlstr;
            CMD.CommandType = CommandType.Text;
            SqlTransaction trans = CONN.BeginTransaction();

            CMD.Transaction = trans;
            try
            {
                result = CMD.ExecuteNonQuery();
                trans.Commit();
                CONN.Close();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                result = 0;
                msg    = ex.Message;
            }
            finally
            {
                if (CONN.State == ConnectionState.Open)
                {
                    // trans.Commit();
                    CONN.Close();
                }
                trans.Dispose();
            }

            try  //log用另外的try防止檔案被咬
            {
                if (msg != "")
                {
                    WriteCMD();
                    WriteLog("[" + DateTime.Now.ToLongTimeString() + "][Main.NonQuery.Error]:" + msg + sqlstr);
                }
                else
                {
                    if (LogStat >= 1)
                    {
                        WriteCMD();
                    }
                    WriteLog(sqlstr);
                }
            }
            catch { }

            return(result);
        }
Example #4
0
        static void Main(string[] args)
        {
            using (var sqlConnection = new SqlConnection("connectionString"))
            {
                sqlConnection.Open();

                // Create the Service Broker Service and Queue if they don't exist.
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    ServiceBrokerWrapper.CreateServiceAndQueue(sqlTransaction, @"[\\Example\Service]", "ExampleServiceQueue");
                    sqlTransaction.Commit();
                }

                // Send a single message to a Service endpoint and immediately
                // end this side of the conversation
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    var messageData = new byte[1000];
                    var conversationHandle =
                        ServiceBrokerWrapper.SendOne(
                        sqlTransaction
                        , @"[\\Example\Service2]"
                        , @"[\\Example\Service]"
                        , "MessageContractName"
                        , "MessageType"
                        , messageData);
                    sqlTransaction.Commit();
                }

                // Wait for a message to be available on the queue. We will
                // wait for some number of milliseconds. If the timeout expires
                // then the method returns "null". Otherwise it will contain
                // the message received from the queue.
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    Message message = null;
                    while (message == null)
                    {
                        message = ServiceBrokerWrapper.WaitAndReceive(sqlTransaction, "ExampleServiceQueue", 60 * 60 * 1000);
                    }
                    // ...handle message...

                    // If we rollback the transaction then the message will
                    // return to the queue to be handled again.
                    // If we commit the transaction then we're done.
                    sqlTransaction.Commit();
                }
            }
        }
Example #5
0
        public void updateProduct(int productID, Products product)
        {
            SqlConnection con     = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
            SqlCommand    command = con.CreateCommand();

            con.Open();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = con.BeginTransaction();

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection  = con;
            command.Transaction = transaction;
            command.CommandText =
                "UPDATE productTable SET name = @name, price = @price, inStock = @inStock, min = @min, max = @max WHERE  productID = @productID;";
            command.Parameters.AddWithValue("@productID", productID);
            command.Parameters.AddWithValue("@name", product.getName());
            command.Parameters.AddWithValue("@price", product.getPrice());
            command.Parameters.AddWithValue("@inStock", product.getInStock());
            command.Parameters.AddWithValue("@min", product.getProductMin());
            command.Parameters.AddWithValue("@max", product.getMax());
            var affected = command.ExecuteNonQuery();

            transaction.Commit();
            con.Close();
        }
Example #6
0
        public void addProduct(Products product)
        {
            SqlConnection con     = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
            SqlCommand    command = con.CreateCommand();

            con.Open();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = con.BeginTransaction();

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection  = con;
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO [dbo].[productTable] ([productID], [name], [price], [inStock], [min], [max]) VALUES (@productID, @name, @price, @inStock, @min, @max); SELECT productID, name, price, inStock, min, max FROM productTable WHERE(productID = @productID)";
            command.Parameters.AddWithValue("@productID", product.getProductID());
            command.Parameters.AddWithValue("@name", product.getName());
            command.Parameters.AddWithValue("@price", product.getPrice());
            command.Parameters.AddWithValue("@inStock", product.getInStock());
            command.Parameters.AddWithValue("@min", product.getProductMin());
            command.Parameters.AddWithValue("@max", product.getMax());
            command.ExecuteNonQuery();
            transaction.Commit();
            con.Close();
        }
Example #7
0
 public void GuardarOmniPack()
 {
     System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
     System.Data.SqlClient.SqlTransaction transaccion;
     conexion.ConnectionString = Config.ConnectionString;
     conexion.Open();
     transaccion = conexion.BeginTransaction();
     try
     {
         if (ChoferID == 0)
         {
             Config.Conexion.EjecutarSinResultados(transaccion, "ChoferOMNIINS", Utiles.BaseDatos.StrToSql(ChoferEmpresaSITT), Utiles.BaseDatos.StrToSql(LegajoSITT),
                                                   Utiles.BaseDatos.StrToSql(Nombre), Utiles.BaseDatos.IntToSql(UnidadNegocioID));
         }
         else
         {
             Config.Conexion.EjecutarSinResultados(transaccion, "ChoferOMNIUPD", Utiles.BaseDatos.IntToSql(ChoferID), Utiles.BaseDatos.StrToSql(ChoferEmpresaSITT),
                                                   Utiles.BaseDatos.StrToSql(LegajoSITT), Utiles.BaseDatos.StrToSql(Nombre), Utiles.BaseDatos.IntToSql(UnidadNegocioID));
         }
         transaccion.Commit();
     }
     catch (Exception ex)
     {
         transaccion.Rollback();
         throw ex;
     }
 }
Example #8
0
        private bool disposed = false;                        //Disposeを何度も呼び出させない


        public MyModel()
        {
            // 接続文字列を生成する

            string connectString =
                "Data Source = " + Settings.DatabaseServer
                + ";Initial Catalog = " + Settings.DatabaseName
                + ";User ID = " + Settings.DatabaseUser
                + ";Password = "******"データベース接続エラー " + ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);

                sqlConn.Close();

                Application.Exit();
            }
        }
Example #9
0
 public void ExecuteSqlTran(Hashtable SQLStringList)
 {
     using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
     {
         sqlConnection.Open();
         using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
         {
             System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
             try
             {
                 foreach (DictionaryEntry dictionaryEntry in SQLStringList)
                 {
                     string cmdText = dictionaryEntry.Key.ToString();
                     System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value;
                     DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, cmdParms);
                     int num = sqlCommand.ExecuteNonQuery();
                     sqlCommand.Parameters.Clear();
                 }
                 sqlTransaction.Commit();
             }
             catch
             {
                 sqlTransaction.Rollback();
                 throw;
             }
         }
     }
 }
Example #10
0
        public int ExecuteSqlTran(List <string> SQLStringList)
        {
            int result;

            using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
            {
                sqlConnection.Open();
                System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
                sqlCommand.Connection = sqlConnection;
                System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                sqlCommand.Transaction = sqlTransaction;
                try
                {
                    int num = 0;
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        string text = SQLStringList[i];
                        if (text.Trim().Length > 1)
                        {
                            sqlCommand.CommandText = text;
                            num += sqlCommand.ExecuteNonQuery();
                        }
                    }
                    sqlTransaction.Commit();
                    result = num;
                }
                catch
                {
                    sqlTransaction.Rollback();
                    result = 0;
                }
            }
            return(result);
        }
Example #11
0
        public static int InsertBySQL(List <string> SQL)
        {
            System.Data.SqlClient.SqlConnection oracleConnection = new System.Data.SqlClient.SqlConnection(constr);
            System.Data.SqlClient.SqlCommand    oracleCommand    = new System.Data.SqlClient.SqlCommand();
            oracleCommand.Connection = oracleConnection;
            int rowsAffected = 0;

            oracleConnection.Open();
            System.Data.SqlClient.SqlTransaction tran = oracleConnection.BeginTransaction();
            try
            {
                foreach (string s in SQL)
                {
                    oracleCommand.Transaction = tran;
                    oracleCommand.CommandText = s;
                    rowsAffected += oracleCommand.ExecuteNonQuery();
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw new NotSupportedException(ex.Message);
            }
            finally
            {
                oracleConnection.Close();
            }
            return(rowsAffected);
        }
        public void GuardarPlanilla(DsPlanillaRedespacho ds)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                SqlParameter pAgenciaID = new SqlParameter("@AgenciaID", Utiles.BaseDatos.IntToSql(AgenciaID));
                SqlParameter pUsuarioID = new SqlParameter("@UsuarioID", Utiles.BaseDatos.IntToSql(UsuarioID));
                PlanillaRedespachoID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico("PlanillaRedespachoCabeceraINS", AgenciaID, UsuarioID));

                if (PlanillaRedespachoID > 0)
                {
                    GuardarDetallePlanilla(transaccion, ds);
                    transaccion.Commit();
                }
                else
                {
                    transaccion.Rollback();
                    Exception ex = new Exception("No se pudo guardar la planilla");
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
        }
        public bool GuardarCliente(int guia)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                // actualizo el estado de la Solicitud
                //     Config.Conexion.EjecutarResultadoUnico(transaccion, "ClienteEventualGuiaINS",guia);

                transaccion.Commit();
                return(true);
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
            finally
            {
                conexion.Close();
            }
        }
Example #14
0
        public bool Guardar(int usuarioID)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                this.CotizacionClienteUVentaDistribucionID =
                    Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "CotizacionClienteUVentaDistribucionINS", Utiles.BaseDatos.IntToSql(CotizacionClienteUVentaID), PagaSeguro, Utiles.BaseDatos.DoubleToSql(SeguroTantoPorMil),
                                                                           Utiles.BaseDatos.DoubleToSql(ValorDeclaradoMin), Utiles.BaseDatos.DoubleToSql(ValorDeclaradoMax), usuarioID, Utiles.BaseDatos.DoubleToSql(ValorConversionM3),
                                                                           FechaVencimientoSeguro));


                transaccion.Commit();
                return(true);
            }

            catch (Exception ex)
            {
                transaccion.Rollback();
                throw new Exception(ex.Message, ex);
            }

            finally
            {
                conexion.Close();
            }
        }
Example #15
0
        public bool deletePart(Part part)
        {
            SqlConnection con     = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
            SqlCommand    command = con.CreateCommand();

            con.Open();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = con.BeginTransaction("SampleTransaction");

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection  = con;
            command.Transaction = transaction;
            command.CommandText =
                "DELETE FROM[dbo].[partTable] WHERE(([partID] = @partID) AND([price] = @price) AND ([inStock] = @inStock) AND([min] = @min) AND([max] = @max))";
            command.Parameters.AddWithValue("@partID", part.getParttID());
            command.Parameters.AddWithValue("@price", part.getPrice());
            command.Parameters.AddWithValue("@inStock", part.getInStock());
            command.Parameters.AddWithValue("@min", part.getMin());
            command.Parameters.AddWithValue("@max", part.getMax());
            command.ExecuteNonQuery();
            transaction.Commit();
            con.Close();

            return(true);
        }
Example #16
0
        protected void ButInput_Click(object sender, System.EventArgs e)
        {
            //���浽���ݿ�
            int i=0;
            string strConn=ConfigurationSettings.AppSettings["strConn"];
            SqlConnection ObjConn =new SqlConnection(strConn);
            ObjConn.Open();
            SqlTransaction ObjTran=ObjConn.BeginTransaction();
            SqlCommand ObjCmd=new SqlCommand();
            ObjCmd.Transaction=ObjTran;
            ObjCmd.Connection=ObjConn;
            try
            {
                ObjCmd.CommandText="Update UserInfo set DeptID=0 where DeptID="+intDeptID+"";
                ObjCmd.ExecuteNonQuery();

                for(i=0;i<LBSelected.Items.Count;i++)
                {
                    ObjCmd.CommandText="Update UserInfo set DeptID="+intDeptID+" where UserID="+LBSelected.Items[i].Value+"";
                    ObjCmd.ExecuteNonQuery();
                }

                ObjTran.Commit();
            }
            catch
            {
                ObjTran.Rollback();
            }
            finally
            {
                ObjConn.Close();
                ObjConn.Dispose();
            }
            this.RegisterStartupScript("newWindow","<script language='javascript'>window.close();</script>");
        }
Example #17
0
 public void AddMemberInfo(List<MemberInfo> list)
 {
     //创建连接对象
     using (SqlConnection con = new SqlConnection(SqlHelper.str))
     {
         con.Open();
         SqlTransaction sqlTran = con.BeginTransaction();//开启
         try
         {
             for (int i = 0; i < list.Count; i++)
             {
                 MemberInfo member = list[i];
                 string sql = "insert into MemberInfo( MemName, MemMobilePhone, DelFlag)values( @MemName, @MemMobilePhone, @DelFlag)";
                 SqlParameter[] ps = {
                             new SqlParameter("@MemName",member.MemName),
                             new SqlParameter("@MemMobilePhone",member.MemMobilePhone),
                             new SqlParameter("@DelFlag",member.DelFlag)
                                 };
                 SqlHelper.ExecuteNonQuery(con, sqlTran, sql, ps);
             }
             sqlTran.Commit();//提交
         }
         catch (Exception ex)
         {
             sqlTran.Rollback();//回滚
             throw;
         }
     }
 }
Example #18
0
 public static void BulkCopy(DataTable table, string connectionString)
 {
     using (var connection = new SqlConnection(connectionString))
     {
         SqlTransaction transaction = null;
         connection.Open();
         try
         {
             transaction = connection.BeginTransaction();
             using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
             {
                 sqlBulkCopy.BatchSize = table.Rows.Count;
                 sqlBulkCopy.DestinationTableName = table.TableName;
                 sqlBulkCopy.MapColumns(table);
                 sqlBulkCopy.WriteToServer(table);
             }
             transaction.Commit();
         }
         catch
         {
             transaction?.Rollback();
             throw;
         }
     }
 }
Example #19
0
 public int Guardar()
 {
     System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection();
     System.Data.SqlClient.SqlTransaction transaccion;
     conexion.ConnectionString = Config.ConnectionString;
     conexion.Open();
     transaccion = conexion.BeginTransaction();
     try
     {
         SqlParameter pObservacionID = new SqlParameter("@ObservacionID", Utiles.BaseDatos.IntToSql(ObservacionID));
         SqlParameter pCodigo = new SqlParameter("@Codigo", Utiles.BaseDatos.IntToSql(Codigo));
         SqlParameter pObsDescrip = new SqlParameter("@ObsDescrip", Utiles.BaseDatos.StrToSql(ObsDescrip));
         SqlParameter pTipoObservacionID = new SqlParameter("@TipoObservacionID", Utiles.BaseDatos.IntToSql(TipoObservacionID));
         SqlParameter pHoras = new SqlParameter("@Horas", Utiles.BaseDatos.DoubleToSql(Horas));
         SqlParameter pImpactoID = new SqlParameter("@ImpactoID", Utiles.BaseDatos.IntToSql(ImpactoID));
         SqlParameter pNoTomarFallo = new SqlParameter("@NoTomarFallo", NoTomarFallo);
         if (ObservacionID > 0)
         {
             Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionUPD", pObservacionID, pCodigo,
                 pObsDescrip, pTipoObservacionID, pHoras, pImpactoID, pNoTomarFallo);
         }
         else
         {
             ObservacionID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(System.Data.CommandType.StoredProcedure, "ObservacionINS", pCodigo, pObsDescrip,
                 pTipoObservacionID, pHoras, pImpactoID, pNoTomarFallo));
         }
         transaccion.Commit();
         return ObservacionID;
     }
     catch (Exception ex)
     {
         transaccion.Rollback();
         throw ex;
     }
 }
Example #20
0
 /// <summary>
 /// Creates new ability in database.
 /// </summary>
 /// <param name="abilityname">Name of ability.</param>
 /// <returns>ID of new database record.</returns>
 public override int CreateAbility(string abilityname)
 {
     int id = -1;
     using (SqlConnection connection = new SqlConnection(this.ConnectionString))
     {
         connection.Open();
         SqlTransaction transaction = connection.BeginTransaction();
         SqlCommand command = connection.CreateCommand();
         command.Transaction = transaction;
         command.CommandText =
             string.Format(
                 "INSERT INTO {0} (Name) VALUES  (@Ability) SELECT @@IDENTITY",
                 DBAbilityTableName);
         command.Parameters.Add("@Ability", SqlDbType.NVarChar).Value =
             abilityname;
         try
         {
             id = Convert.ToInt32(ExecuteScalar(command));
             transaction.Commit();
         }
         catch
         { transaction.Rollback(); }
     }
     return id;
 }
 public static void SetUp()
 {
     Console.WriteLine("Assembly Setup beginning...");
     if (ConfigurationManager.AppSettings["connectionStringName"] == "subtextExpress")
     {
         //For use with SQL Express. If you use "subtextData", we assume you already have the database created.
         DatabaseHelper.CreateAndInstallDatabase(Config.ConnectionString, Config.ConnectionString.Database,
                                                 "App_Data");
     }
     else
     {
         using (var connection = new SqlConnection(Config.ConnectionString))
         {
             connection.Open();
             using (SqlTransaction transaction = connection.BeginTransaction())
             {
                 try
                 {
                     ScriptHelper.ExecuteScript("StoredProcedures.sql", transaction);
                     transaction.Commit();
                 }
                 catch (Exception)
                 {
                     transaction.Rollback();
                 }
             }
         }
     }
 }
        public void Write(IEnumerable<Row> rows) {
            var sql = _context.SqlUpdateCalculatedFields(_original);
            var temp = new List<Field> { _context.Entity.Fields.First(f => f.Name == Constants.TflKey) };
            temp.AddRange(_context.Entity.CalculatedFields.Where(f => f.Output && f.Name != Constants.TflHashCode));
            var fields = temp.ToArray();

            var count = 0;
            using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) {
                cn.Open();
                foreach (var batch in rows.Partition(_context.Entity.UpdateSize)) {
                    var trans = cn.BeginTransaction();
                    var batchCount = cn.Execute(
                        sql,
                        batch.Select(r => r.ToExpandoObject(fields)),
                        trans,
                        _context.Connection.Timeout,
                        CommandType.Text
                    );
                    trans.Commit();
                    count += batchCount;
                    _context.Increment(batchCount);
                }
                _context.Info("{0} to {1}", count, _context.Connection.Name);
            }
            _context.Entity.Updates += count;
        }
        private void saveData(DataTable dt)
        {
            using (SqlConnection c = new SqlConnection(csRemoto))
            {
                c.Open();

                SqlTransaction t = c.BeginTransaction(IsolationLevel.RepeatableRead);

                SqlCommand cmdEliminar = new SqlCommand("delete from " + tableName, c, t);
                cmdEliminar.ExecuteNonQuery();

                SqlCommand cmdInsertar = new SqlCommand(sqlRemoto, c, t);
                SqlParameter[] pConsulta = new SqlParameter[pRemoto.Length];

                for (int i = 0; i < pRemoto.Length; i++)
                {
                    pConsulta[i] = cmdInsertar.Parameters.Add(pRemoto[i].ParameterName, pRemoto[i].SqlDbType);
                }

                foreach (DataRow r in dt.Rows)
                {
                    foreach (SqlParameter p in pConsulta)
                    {
                        p.Value = r[p.ParameterName];
                    }
                    cmdInsertar.ExecuteNonQuery();
                }

                t.Commit();
                c.Close();
            }
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlTransaction myTransaction = null;
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb;
                                                            User ID=ajjpsqlserverdb; Password= Devry2010;");

                conn.Open();
                SqlCommand command = conn.CreateCommand();
                string strSQL;
                string txtBoxText = TextBox1.Text;
                txtBoxText = txtBoxText.Replace("'", "''");

                myTransaction = conn.BeginTransaction();
                command.Transaction = myTransaction;

                strSQL = "UPDATE aspnet_Membership SET Password = '******' WHERE UserID = '" + DropDownList1.SelectedValue + "'";

                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = strSQL;
                command.ExecuteNonQuery();

                myTransaction.Commit();

                command.Connection.Close();
                Response.Redirect("~/User/Main.aspx");
            }
            catch (Exception ex)
            {
                lblErr.Text = ex.Message;
            }
        }
    }
Example #25
0
 public static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] pars)
 {
     SqlConnection conn = new SqlConnection(connStr);
     SqlTransaction tran = null;
     try
     {
         using (SqlCommand cmd = new SqlCommand(cmdText, conn, tran))
         {
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);
             conn.Open();
             tran = conn.BeginTransaction();
             tran.Commit();
             return cmd.ExecuteNonQuery();
         }
     }
     catch (Exception)
     {
         tran.Rollback();
         throw;
     }
     finally
     {
         conn.Close();
     }
 }
Example #26
0
        public void Import(string connectionString, StatbookModel statbook, bool assumeATeams)
        {
            _connection = new SqlConnection(connectionString);
            try
            {
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                // insert leagues
                LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
                var leagues = leagueGateway.GetAllLeagues();
                League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
                League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
                int maxID = leagues.Select(l => l.ID).Max();
                if(homeLeague == null)
                {
                    homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false);
                    maxID++;
                }
                if(awayLeague == null)
                {
                    awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false);
                    maxID++;
                }

                // insert teams
                TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
                Team homeTeam, awayTeam;
                if (assumeATeams)
                {
                    homeTeam = teamGateway.GetATeam(homeLeague.ID);
                    awayTeam = teamGateway.GetATeam(awayLeague.ID);

                }
                else
                {
                    homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false);
                    awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false);
                }

                // insert bout
                BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
                if(!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
                {
                    Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
                    BoutDataImport(statbook, bout, homeTeam, awayTeam);
                }
                else
                {
                    // bout already exists
                    Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
                }

                _transaction.Commit();
            }
            finally
            {
                _connection.Close();
            }
        }
Example #27
0
        public static bool AddTaskReplyAttachments(string taskid, string replyid, List<Attachment> attachments,string userid,string clientid)
        {
            SqlConnection conn = new SqlConnection(TaskDAL.ConnectionString);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();


            foreach (var attachment in attachments)
            {
                if (!TaskDAL.BaseProvider.AddTaskReplyAttachment(taskid,replyid,attachment.Type,
                    attachment.ServerUrl,attachment.FilePath,attachment.FileName,attachment.OriginalName,attachment.ThumbnailName,attachment.Size,
                    userid,clientid, tran))
                {
                    tran.Rollback();
                    conn.Dispose();

                    return false;
                }
            }

            tran.Commit();
            conn.Dispose();

            return true;
        }
Example #28
0
        public bool removeAssociatedPart(int removeID)
        {
            SqlConnection con     = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
            SqlCommand    command = con.CreateCommand();

            con.Open();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = con.BeginTransaction("SampleTransaction");

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection  = con;
            command.Transaction = transaction;
            command.CommandText =
                "DELETE FROM[dbo].[associatedParts] WHERE productID = @productID and partID = @partID";
            command.Parameters.AddWithValue("@productID", getProductID());
            command.Parameters.AddWithValue("@partID", removeID);
            command.ExecuteNonQuery();
            transaction.Commit();
            con.Close();

            return(true);
        }
Example #29
0
        public bool Guardar(IConvenioRedespachoDetalle detalle)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                this.ConvenioReDespachoID = Convert.ToInt32(
                    Config.Conexion.EjecutarResultadoUnico(transaccion, "ConvenioRedespachoINS",
                                                           this.EmpresaProveedoraID));

                detalle.ConvenioReDespachoID = this.ConvenioReDespachoID;
                detalle.Guardar(transaccion);
                transaccion.Commit();
                return(true);
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
            finally
            {
                conexion.Close();
            }
        }
Example #30
0
        public Transaction BeginTransaction()
        {
            System.Data.SqlClient.SqlTransaction tr = Conn.BeginTransaction();
            Transaction result = new Transaction(tr);

            return(result);
        }
Example #31
0
        public void ActualizarOrdenMasivo(int PedidoID, int OrdenID)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                // actualizo el estado de la Solicitud
                Config.Conexion.EjecutarResultadoUnico(transaccion, "UPDATEOrdenCliente", OrdenID, PedidoID);

                transaccion.Commit();
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
            finally
            {
                conexion.Close();
            }
        }
Example #32
0
        public void CambiarEstadoOrdenHRR()
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                // actualizo el estado de la Solicitud
                Config.Conexion.EjecutarResultadoUnico(transaccion, "CambiarEstadoOrdenHRR", this.OrdenRetiroID);

                transaccion.Commit();
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
            finally
            {
                conexion.Close();
            }
        }
 private void BtnUpdate_Click(object sender, EventArgs e)
 {
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand("", Con);
         SqlTransaction trn = null;
         try
         {
             Con.Open();
             trn = Con.BeginTransaction();
             Cmd.Transaction = trn;
             Cmd.CommandText = string.Format("Delete From UserRoles Where UserId = {0}", LUEItems.EditValue);
             Cmd.ExecuteNonQuery();
             foreach (DataRow row in RolesTbl.Rows)
             {
                 if (row["Active"].ToString() == "1")
                 {
                     Cmd.CommandText = String.Format("INSERT INTO UserRoles (UserId, RoleId) VALUES ({0}, {1})", LUEItems.EditValue, row["RoleID"].ToString());
                     Cmd.ExecuteNonQuery();
                 }
             }
             trn.Commit();
             LoadRoles(LUEItems.EditValue.ToString());
             Program.ShowMsg("تم التعديل ..", false, this);
             Program.Logger.LogThis("تم التعديل ..", Text, FXFW.Logger.OpType.success, null, null, this);
         }
         catch (Exception ex)
         {
             trn.Rollback();
             MessageBox.Show(ex.Message);
             Program.Logger.LogThis(null, Text, FXFW.Logger.OpType.fail, ex, null, this);
         }
     }
 }
        private void InsertWikiPages(DataTable table)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("WikiDumpDB")))
            {
                connection.Open();

                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "WikiPage";
                            bulkCopy.WriteToServer(table);
                            transaction.Commit();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("Commit Exception Type: {0}", e.GetType());
                            Console.WriteLine("  Message: {0}", e.Message);
                            try
                            {
                                transaction.Rollback();
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("Rollback Exception Type", ex.ToString());
                                Console.WriteLine("  Message: {0}", ex.Message);
                            }
                        }
                    }
                }
            }
        }
Example #35
0
 /// <summary>
 /// 运行SQL语句
 /// </summary>
 /// <param name="sql">SQL语句</param>
 /// <returns></returns>
 public string RunSql(string sql)
 {
     if (!string.IsNullOrWhiteSpace(sql))
     {
         SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString);
         conn.Open();
         using (SqlTransaction trans = conn.BeginTransaction())
         {
             string[] sqlList = StringHelper.SplitString(sql, "-sqlseparator-");
             foreach (string item in sqlList)
             {
                 if (!string.IsNullOrWhiteSpace(item))
                 {
                     try
                     {
                         RDBSHelper.ExecuteNonQuery(CommandType.Text, item);
                         trans.Commit();
                     }
                     catch (Exception ex)
                     {
                         trans.Rollback();
                         return ex.Message;
                     }
                 }
             }
         }
         conn.Close();
     }
     return string.Empty;
 }
        public void TestInitializer()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = @".\SQLEXPRESS";
            builder.IntegratedSecurity = true;
            builder.InitialCatalog = "DBBlogs";
            SqlConnection connection1 = new SqlConnection(builder.ConnectionString);
            connection1.Open();
            _currentTransaction1 = connection1.BeginTransaction();
            SqlConnection connection2 = new SqlConnection(builder.ConnectionString);
            connection2.Open();
            _currentTransaction2 = connection2.BeginTransaction();

            MetaDataStore metaDataStore = new MetaDataStore();
            metaDataStore.BuildTableInfoFor<Blog>();

            var identityMap1 = new IdentityMap();
            var identityMap2 = new IdentityMap();

            _blogMapper1 = new BlogMapper(connection1,
                _currentTransaction1,
                metaDataStore,
                new EntityHydrater(metaDataStore, identityMap1),
                identityMap1);
            _blogMapper2 = new BlogMapper(connection2,
                _currentTransaction2,
                metaDataStore,
                new EntityHydrater(metaDataStore, identityMap2),
                identityMap2);
        }
Example #37
0
		/// <summary>
		/// 执行多条SQL语句,实现数据库事务。
		/// </summary>
		/// <param name="SQLStringList">多条SQL语句</param>		
		public static void ExecuteSqlTran(ArrayList SQLStringList)
		{
			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				conn.Open();
				SqlCommand cmd = new SqlCommand();
				cmd.Connection=conn;				
				SqlTransaction tx=conn.BeginTransaction();			
				cmd.Transaction=tx;				
				try
				{   		
					for(int n=0;n<SQLStringList.Count;n++)
					{
						string strsql=SQLStringList[n].ToString();
						if (strsql.Trim().Length>1)
						{
							cmd.CommandText=strsql;
							cmd.ExecuteNonQuery();
						}
					}										
					tx.Commit();					
				}
				catch(System.Data.SqlClient.SqlException E)
				{		
					tx.Rollback();
					throw new Exception(E.Message);
				}
			}
		}
Example #38
0
        private void OpenConnection()
        {
            connection = new SqlConnection(AppSettings.AdminConnectionString);
            connection.Open();

            transaction = connection.BeginTransaction();
        }
Example #39
0
        public void GuardarDatosConf(System.Collections.Hashtable hCotizacionCliente, bool habilita)
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            try
            {
                // Recorro los seleccionados y los marco como que sí requieren actualización
                foreach (int cotizacionClienteID in hCotizacionCliente.Keys)
                {
                    // Seteo a todas las unidades de venta del cliente como que no requieren datos de conformación
                    Config.Conexion.EjecutarSinResultados("CotizacionClienteDatosConfDEL", cotizacionClienteID);

                    if (habilita)
                    {
                        Config.Conexion.EjecutarSinResultados("CotizacionClienteDatosConfUPD", cotizacionClienteID);
                    }
                }
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conexion.Close();
            }
        }
Example #40
0
        public void GuardarObservacionFT(int observacionFTID, string observacionFTCodigo, string observacionFTDescrip, int observacionID, int estadoGuiaID, int tipoEstadoGuiaID)
        {
            System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();
            try
            {
                SqlParameter pObservacionFTID = new SqlParameter("@ObservacionFTID", Utiles.BaseDatos.IntToSql(observacionFTID));
                SqlParameter pObservacionFTCodigo = new SqlParameter("@ObservacionFTCodigo", Utiles.BaseDatos.StrToSql(observacionFTCodigo));
                SqlParameter pObservacionFTDescrip = new SqlParameter("@ObservacionFTDescrip", Utiles.BaseDatos.StrToSql(observacionFTDescrip));
                SqlParameter pObservacionID = new SqlParameter("@ObservacionID", Utiles.BaseDatos.IntToSql(observacionID));
                SqlParameter pEstadoGuiaID = new SqlParameter("@EstadoGuiaID", Utiles.BaseDatos.IntToSql(estadoGuiaID));
                SqlParameter pTipoEstadoGuiaID = new SqlParameter("@TipoEstadoGuiaID", Utiles.BaseDatos.IntToSql(tipoEstadoGuiaID));

                if (observacionFTID > 0)
                {
                    Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionFTUPD", pObservacionFTID, pObservacionFTCodigo, pObservacionFTDescrip, pObservacionID, pEstadoGuiaID, pTipoEstadoGuiaID);
                }
                else
                {
                    Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionFTINS", pObservacionFTCodigo, pObservacionFTDescrip, pObservacionID, pEstadoGuiaID, pTipoEstadoGuiaID);
                }
                transaccion.Commit();
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
        }
 public SqlServerProcessor(SqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
     : base(generator, announcer, options)
 {
     Connection = connection;
     connection.Open();
     Transaction = connection.BeginTransaction();
 }
Example #42
0
 private void BtnDelete_Click(object sender, EventArgs e)
 {
     if (MessageBox.Show("هل ترغب في الاستمرار؟", "تأكــــيد", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == System.Windows.Forms.DialogResult.No)
     {
         return;
     }
     SqlTransaction Trn = null;
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand(String.Format("DELETE FROM CustomersSuppliers WHERE (PERSONID = {0})", LUEItems.EditValue), Con);
         try
         {
             Con.Open();
             Trn = Con.BeginTransaction();
             Cmd.Transaction = Trn;
             Cmd.ExecuteNonQuery();
             if (CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString() != string.Empty)
             {
                 Cmd.CommandText = "DELETE FROM TBL_Accountes Where AccountId = " + CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString();
                 Cmd.ExecuteNonQuery();
             }
             Trn.Commit();
             LoadData();
             GetNewAccountID();
             LUEItems.ItemIndex = -1;
             MessageBox.Show("تم الحـــذف ", "حـــــذف", MessageBoxButtons.OK, MessageBoxIcon.Information);
         }
         catch (Exception ex)
         {
             Trn.Rollback();
             MessageBox.Show(ex.Message);
         }
     }
 }
Example #43
0
 public AssignmentUnitOfWork(IAssignmentRepository assignmentRepository)
 {
     _AssignmentRepository = assignmentRepository;
     connection = new SqlConnection(DBHelper.ConnectionString);
     connection.Open();
     _AssignmentRepository.DbContext = new SqlDbContext() { Transaction = connection.BeginTransaction() };
 }
Example #44
0
        private IList<Project> getUserProjects(int userID, String condition)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                SqlCommand command = connection.CreateCommand();
                command.Transaction = transaction;

                String subQuery = String.IsNullOrEmpty(condition)
                                      ? String.Empty
                                      : String.Format(" AND {0}", condition);

                StringBuilder query = new StringBuilder();
                query.AppendFormat(" SELECT {0}.ID, {0}.Name, {0}.Description", DBProjectsTableName);
                query.AppendFormat(" FROM {0} INNER JOIN {1} ON {0}.ID = {1}.ProjectID", DBProjectsTableName, DBProjectsUserTableName);
                query.AppendFormat(" WHERE {0}.UserID = @userID {1}",
                                   DBProjectsUserTableName,
                                   subQuery);
                query.AppendFormat(" ORDER BY {0}.Name", DBProjectsTableName);

                command.CommandText = query.ToString();
                command.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
                command.CommandText = query.ToString();

                using (IDataReader reader = ExecuteReader(command))
                {
                    return getAllProjectsDataFromReader(reader);
                }
            }
        }
Example #45
0
        public Boolean fnDelT_Material_Empaque(String psCodigo_Material_Empaque)
        {
            Boolean lbResultado=true;
            //conexion
            SqlTransaction loTx ;
            SqlConnection loCn = new SqlConnection();
            loCn.ConnectionString = ClassCadena.fnConexion();
            //parametros
            SqlParameter[] par = new SqlParameter[1];
            //cambiar aca:
            par[0] = new SqlParameter("@Codigo_Material_Empaque",System.Data.SqlDbType.VarChar,10);
            par[0].Value= psCodigo_Material_Empaque;

            loCn.Open();
            loTx = loCn.BeginTransaction();
            try
            {
                SqlHelper.ExecuteNonQuery(loTx, CommandType.StoredProcedure, "usp_DelT_Material_Empaque", par);
                loTx.Commit();
            }
            catch (Exception e)
            {
                lbResultado=false;
                loTx.Rollback();
                throw e;
            }
            finally
            {
                loCn.Close();
                loCn.Dispose();
            }

            return lbResultado;
        }
        protected void btnGuardar_Click(object sender, EventArgs e)
        {
            if (!currentUser.ValidarPrivilegio("Insertar Faltas"))
                return;

            bool exitoooooooo = true;
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                guardarAlumno(con,trans);
                guargarFalta(con,trans);
                guargarEvidencias(con, trans);
                trans.Commit();
                limpiar();
            }
            catch (Exception ex)
            {
                exitoooooooo = false;
                trans.Rollback();
                Session["error"] = ex.StackTrace;

            }
            con.Close();

            if(!exitoooooooo)
                Response.Redirect("~/Seguridad/Error.aspx");
            else
                Response.Redirect("ListaFaltas.aspx");
        }
        public void CalculateDurationEstimates()
        {
            SqlConnection connection = new SqlConnection(_connectionString);
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();
            if (_jams == null)
            {
                _jams = new JamGateway(connection, transaction).GetAllJams();
            }
            if (_penaltyGroups == null)
            {
                _penaltyGroups = new PenaltyGroupGateway(connection, transaction).GetAllPenaltyGroups();
            }

            var jamBoutMap = _jams.ToDictionary(j => j.ID, j => j.BoutID);
            var penaltyGroupMap = _penaltyGroups.GroupBy(pg => jamBoutMap[pg.BoxTimes.First().JamID]).ToDictionary(gp => gp.Key);
            var boutJams = _jams.GroupBy(j => j.BoutID);

            var jamEstimateMap = CalculateJamDurationLimits(connection, transaction);

            foreach (IGrouping<int, Jam> boutJamSet in boutJams)
            {
                ProcessBout(boutJamSet, penaltyGroupMap[boutJamSet.Key], jamEstimateMap);
            }
            var boxTimeEstimates = CalculateBoxTimeEstimates(jamEstimateMap);
            new JamTimeLimitGateway(connection, transaction).InsertJamTimeEstimates(jamEstimateMap.Values);
            new BoxTimeEstimateGateway(connection, transaction).InsertBoxTimeEstimates(boxTimeEstimates);
            transaction.Commit();
            connection.Close();
        }
        public async Task<IReadOnlyCollection<Entry>> Query()
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new SqlCommand(@"
SELECT [Owner], [Type], [Value] FROM [Data] WHERE [Owner] <> @Owner
", connection, transaction))
                    {
                        command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString;

                        var results = new List<Entry>();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (reader.Read())
                            {
                                results.Add(new Entry((string)reader[0], (string)reader[1], (string)reader[2]));
                            }
                        }
                        return results;
                    }
                }
            }
        }
        private static void InsertValues(string tableName, string columnName, string[] values, SqlConnection connection)
        {
            const string insertStatementPattern = "INSERT INTO [{0}] ({1}) VALUES ('{2}'); ";

              string query = string.Empty;

              foreach (var version in values)
              {
            query += string.Format(insertStatementPattern, tableName, columnName, version);
              }

              if (string.IsNullOrEmpty(query)) return;

              using (var transaction = connection.BeginTransaction())
              {
            using (var command = connection.CreateCommand())
            {
              command.CommandText = query;
              command.Transaction = transaction;
              command.ExecuteNonQuery();
            }

            transaction.Commit();
              }
        }
Example #50
0
 public bool AddProduct(ProductsDto productsDto)
 {
     var cn = new SqlConnection(GetConnection());
     SqlTransaction trx = null;
     var isInsert = false;
     try
     {
         cn.Open();
         trx = cn.BeginTransaction();
         string cmdText = " insert into Products(ProductName,ProductCategory,MemberAnaylst,OrganizationName,TenureId,StyleResearchId,StrategyId,FrequencyCall,FrequencyCallType,Logo,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) " +
                          "values('" + productsDto.ProductName + "'," + productsDto.ProductCategory + "," + productsDto.MemberAnaylst + ",'" + productsDto.OrganizationName + "'," + productsDto.TenureId + "," + productsDto.StyleResearchId + "," + productsDto.StrategyId + "," + productsDto.FrequencyCall + ",'" + productsDto.FrequencyCallType + "','" + productsDto.Logo + "'," + productsDto.CreateUserId + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + productsDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();";
         var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
         var productId = Convert.ToInt32(cmd.ExecuteScalar());
         foreach (var documents in productsDto.ProductDocumentDtos)
             (new SqlCommand("insert into ProductDocument(ProductId,FileName,DocumentName) values( " + productId + ",'" + documents.FileName + "','" + documents.DocumentName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
         trx.Commit();
         isInsert = true;
         cn.Close();
     }
     catch (Exception)
     {
         if (trx != null) trx.Rollback();
         cn.Close();
     }
     return isInsert;
 }
Example #51
0
        /// <summary>
        /// Importars the datos.
        /// </summary>
        private void ImportarDatos()
        {
            //La transacción va a este nivel para poder hacer el rollback en el cath (si se produce error)
            SqlTransaction transaccion = null;
            try
            {
                //Al utillizar el "using" me aseguro que los recursos se liberen cuando termina el bloque
                SqlConnection conexion = new SqlConnection() { ConnectionString = ConnectionString };
                {
                    //objeto que voy a utilizar para obtener los datos (BD Transaccional)
                    objDAObtenerDatos = new DAObtenerDatos(objConfiguracion.valor);

                    //abre la conexión a la bd
                    conexion.Open();
                    //le indica al objeto transaccion que va a iniciar una transacción
                    transaccion = conexion.BeginTransaction();

                    objDAImportarDatos.GrabarTipoAsistencia(objDAObtenerDatos.obtenerTipoAsistenciaBDTransaccional(objConfiguracion), transaccion);

                    objDAImportarDatos.GrabarAsistencia(objDAObtenerDatos.obtenerAsistenciaBDTransaccional(objConfiguracion), transaccion);
                }
                //si la importación de los objetos fue exitosa, entonces confirmo las modificaciones.
                transaccion.Commit();
                conexion.Close();
            }
            catch (Exception ex)
            {
                //Valido que la transacción no sea nula, sino daría error al intentar el rollback
                if (transaccion != null)
                    transaccion.Rollback();
                //mando la excepción para arriba
                throw ex;
            }
        }
Example #52
0
        /// <summary>
        /// 删除记录(string类型id)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(string id)
        {
            bool result = true;
            StringBuilder sql = new StringBuilder();
            SqlConnection conn = new SqlConnection(SQLHelper.ConnString);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            string[] idArr = id.Split(new char[] { ',' });
            try
            {
                for (int i = 0; i < idArr.Length; i++)
                {
                    sql.Append("delete from BigDog_Admin where Id=@Id");
                    SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter("@Id",SqlDbType.NVarChar,50)
                        };
                    parms[0].Value = idArr[i];
                    SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql.ToString(), parms);
                    sql.Clear();
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                result = false;
                throw new ApplicationException(e.Message);

            }
            finally
            {
                conn.Close();
            }
            return result;
        }
Example #53
0
 public SqlTransaction GetTrans()
 {
     if (myTrans == null)
     {
         OpenCon();
         myTrans = mySqlCon.BeginTransaction();
     }
     return(myTrans);
 }
Example #54
0
        public async Task BatchExcuteAsync(BulkCopyModel bulkCopyModel, params SqlCommand[] commands)   //IContextInfo contextInfo,
        {
            if (commands?.Length < 1)
            {
                return;
            }

            using (var connection = new sql.SqlConnection(_connectionString))
            {
                //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) =>
                //{
                //    if (e.CurrentState == System.Data.ConnectionState.Open)
                //        setContextInfo(connection, contextInfo);

                //});

                connection.TryOpen();
                using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        foreach (var command in commands)
                        {
                            command.Connection  = connection;
                            command.Transaction = tran;
                            await command.ExecuteNonQueryAsync();
                        }

                        using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
                        {
                            bulkCopy.BatchSize            = bulkCopyModel.BatchSize; // ~ 100
                            bulkCopy.DestinationTableName = bulkCopyModel.TableName;
                            if (bulkCopyModel.ColumnMappings != null)
                            {
                                bulkCopyModel.ColumnMappings.ForEach(c => bulkCopy.ColumnMappings.Add(c));
                            }
                            var dataTable = bulkCopyModel.DataTable;
                            bulkCopy.BulkCopyTimeout = bulkCopyModel.Timeout;
                            await bulkCopy.WriteToServerAsync(dataTable);
                        }

                        tran.Commit();
                    }
                    catch (Exception e)
                    {
                        tran.Rollback();
                        throw e;
                    }
                    finally
                    {
                        //clearContextInfo(connection, contextInfo);
                        connection.TryClose();
                    }
                }
            }
        }
Example #55
0
 public returnval merge(returnval val)
 {
     if (val.rows == null && val.childs == null)
     {
         SD.DataTable DataTable;
         using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
             DataTable = new SD.DataTable();
             (new SDSc.SqlDataAdapter(new SDSc.SqlCommand()
             {
                 CommandText = string.Format(
                     "SELECT {0}{1}{2};"
                     , selectcols.Replace("@currentuser", "1")
                     , dbname == null ? "" : string.Format(" FROM {0}", dbname)
                     , whereparam == null ? "" : string.Format(" WHERE {0}", whereparam
                                                               .Replace("@currentuser", "1")
                                                               .Replace("@currentlogin", "1"))
                     ),
                 Connection = SqlConnection
             })).Fill(DataTable);
         };
         val.rows = new List <object[]>();
         foreach (SD.DataRow DataRow in DataTable.Rows)
         {
             val.rows.Add(DataRow.ItemArray);
         }
     }
     else
     {
         using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) {
             SqlConnection.Open();
             SDSc.SqlTransaction SqlTransaction = SqlConnection.BeginTransaction();
             try {
                 merge(val, SqlTransaction);
                 if (val.childs != null)
                 {
                     foreach (returnval subtable in val.childs)
                     {
                         thistables.table(subtable.name).merge(subtable, SqlTransaction);
                         if (subtable.childs != null)
                         {
                             foreach (returnval subtable2 in subtable.childs)
                             {
                                 thistables.table(subtable2.name).merge(subtable2, SqlTransaction);
                             }
                         }
                     }
                 }
                 SqlTransaction.Commit();
             } catch (Exception e) {
                 SqlTransaction.Rollback();
                 throw e;
             }
         }
     }
     return(val);
 }
Example #56
0
        public bool Guardar()
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();
            try
            {
                if (this.PromocionServicioTransporteID == 0)
                {
                    // guardo la promocion
                    this.PromocionServicioTransporteID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion
                                                                                                                , "PromocionServicioTransporteINS",
                                                                                                                Utiles.BaseDatos.IntToSql(this.AgenciaOrigenID),
                                                                                                                Utiles.BaseDatos.IntToSql(this.AgenciaDestinoID),
                                                                                                                Utiles.BaseDatos.IntToSql(this.UnidadVentaID), this.Nombre,
                                                                                                                this.PorcentajeBonificacion, this.FechaInicioVigencia, this.FechaFinVigencia,
                                                                                                                this.ServicioTransporteID));
                }
                else
                {
                    this.PromocionServicioTransporteID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion
                                                                                                                , "PromocionServicioTransporteUPD",
                                                                                                                this.PromocionServicioTransporteID,
                                                                                                                Utiles.BaseDatos.IntToSql(this.AgenciaOrigenID),
                                                                                                                Utiles.BaseDatos.IntToSql(this.AgenciaDestinoID),
                                                                                                                Utiles.BaseDatos.IntToSql(this.UnidadVentaID), this.Nombre,
                                                                                                                this.PorcentajeBonificacion, this.FechaInicioVigencia, this.FechaFinVigencia,
                                                                                                                this.ServicioTransporteID));
                }

                // elimino las agencias asociadas anteriormente
                Config.Conexion.EjecutarSinResultados(transaccion, "PromoAgenciaUVentaOrigenDestinoDEL", this.PromocionServicioTransporteID);

                // guardo las agencias a las que se aplica
                for (int i = 0; i <= this.Agencias.Count - 1; i++)
                {
                    Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "PromoAgenciaUVentaOrigenDestinoINS",
                                                                           this.PromocionServicioTransporteID, this.Agencias[i]));
                }

                transaccion.Commit();
                return(true);
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw ex;
            }
            finally
            {
                conexion.Close();
            }
        }
Example #57
0
        /// <summary>執行 Transaction</summary>
        /// <param name="listSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param>
        /// <param name="sqlConn">連線物件</param>
        /// <returns>Transaction是否成功</returns>
        /// <remarks></remarks>
        public static bool raiseTransaction(ArrayList listSQL, System.Data.SqlClient.SqlConnection sqlConn)
        {
            if (listSQL == null)
            {
                return(true);
            }
            if (listSQL.Count == 0)
            {
                return(true);
            }
            if (sqlConn == null)
            {
                sqlConn = createConnection();
            }
            System.Data.SqlClient.SqlTransaction sqlTrans = null;
            if (!(sqlConn.State == ConnectionState.Open))
            {
                sqlConn.Open();
            }
            System.Data.SqlClient.SqlCommand cmd = sqlConn.CreateCommand();
            StringBuilder strSQL = new StringBuilder("");

            sqlTrans = sqlConn.BeginTransaction();
            try
            {
                cmd.Transaction = sqlTrans;
                for (int i = 0; i <= listSQL.Count - 1; i++)
                {
                    if (!string.IsNullOrEmpty(listSQL[i].ToString()))
                    {
                        strSQL.AppendLine(listSQL[i].ToString());
                    }
                }
                cmd.CommandText = strSQL.ToString();
                cmd.ExecuteNonQuery();
                sqlTrans.Commit();
                return(true);
            }
            catch (Exception Ex)
            {
                if ((sqlTrans != null))
                {
                    sqlTrans.Rollback();
                }
                //Message.alertMessage("C0002", null, Ex.Message.ToString(), null);
                return(false);
            }
            finally
            {
                if (!(sqlConn.State == ConnectionState.Closed))
                {
                    sqlConn.Close();
                }
            }
        }
Example #58
0
        public void addAssociatedPart(Part part)
        {
            int associatedPartID;
            //first get assoicatedPartID
            SqlConnection con2 = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");

            con2.Open();

            SqlCommand cmd2 = new SqlCommand();

            cmd2.Connection  = con2;
            cmd2.CommandType = CommandType.Text;
            cmd2.CommandText = "SELECT MAX(associatedPartID) FROM associatedParts;";

            try
            {
                associatedPartID = (int)cmd2.ExecuteScalar();
                associatedPartID = associatedPartID + 1;
            }
            catch
            {
                associatedPartID = 0;
            }
            con2.Close();



            SqlConnection con     = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
            SqlCommand    command = con.CreateCommand();

            con.Open();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = con.BeginTransaction();

            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection  = con;
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO [dbo].[associatedParts] ([productID], [partID], [name], [price], [inStock], [associatedPartID]) VALUES (@productID, @partID, @name, @price, @inStock , @associatedPartID);";
            command.Parameters.AddWithValue("@productID", getProductID());
            Console.WriteLine("Inserting Part " + part.getParttID() + " " + "for productID " + getProductID());
            command.Parameters.AddWithValue("@partID", part.getParttID());
            command.Parameters.AddWithValue("@name", part.getName());
            command.Parameters.AddWithValue("@price", part.getPrice());
            command.Parameters.AddWithValue("@inStock", part.getInStock());
            command.Parameters.AddWithValue("@associatedPartID", associatedPartID);
            Console.WriteLine("associatePartID " + associatedPartID);
            command.ExecuteNonQuery();
            transaction.Commit();
            con.Close();
        }
Example #59
0
        public bool BeginTransaction()
        {
            if (SqlConn != null && SqlConn.State == ConnectionState.Closed && Transaction == null)
            {
                Open();
                Transaction = SqlConn.BeginTransaction();
                return(true);
            }

            return(false);
        }
Example #60
0
        public bool AsignarRangoCodigoBarrasAgencia()
        {
            System.Data.SqlClient.SqlConnection  conexion = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlTransaction transaccion;
            conexion.ConnectionString = Config.ConnectionString;
            conexion.Open();
            transaccion = conexion.BeginTransaction();

            CodigoBarras codigoBarras = new CodigoBarras();

            try
            {
                //reviso que la agencia a la que se le quiere asignar el rango de codigos de barras,
                //sea emisora
                IAgencia agencia = new Agencia();
                agencia.AgenciaID = this.AgenciaID;
                agencia.Consultar();
                if (agencia.EsEmisora)
                {
                    rangoCodigoBarrasID = this.Guardar(transaccion);

                    codigoBarras = new CodigoBarras();
                    codigoBarras.UnidadVentaID         = this.UnidadVentaID;
                    codigoBarras.ServicioID            = this.ServicioID;
                    codigoBarras.ModalidadEntregaID    = this.ModalidadEntregaID;
                    codigoBarras.AgenciaID             = this.AgenciaID;
                    codigoBarras.VendedorID            = this.VendedorID;
                    codigoBarras.ClienteID             = this.ClienteID;
                    codigoBarras.EstadoCodigoBarrasID  = this.EstadoCodigoBarrasID;
                    codigoBarras.FechaAlta             = DateTime.Now;
                    codigoBarras.TarifaPreVentaID      = this.TarifaPreVentaID;
                    codigoBarras.TarifarioFleteID      = TarifarioFleteID;
                    codigoBarras.ConceptoFacturacionID = ConceptoFacturacionID;
                    codigoBarras.ActualizarCodigoBarras(transaccion, this.CodigoBarrasInicial, this.CodigoBarrasFinal);

                    transaccion.Commit();
                    return(true);
                }
                else
                {
                    throw new Exception("Errores.Invalidos.AgenciaNoVendedora");
                }
            }
            catch (Exception ex)
            {
                transaccion.Rollback();
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conexion.Close();
            }
        }