public int deductfromaccount(string account, string amount, System.Data.SqlClient.SqlCommand comm)
    {
        //actually remove the amount from the account, check the balance, return the balance
        string sql = "update huber_bankaccounts set balance = balance - @amount where accountnumber = @accountnumber";
        comm.CommandText = sql;
        comm.Parameters.Clear();
        comm.Parameters.AddWithValue("@amount", amount);
        comm.Parameters.AddWithValue("@accountnumber", account);

        comm.ExecuteNonQuery();

        sql = "select balance from huber_bankaccounts where accountnumber = @accountnumber ";

        comm.CommandText = sql;
        comm.Parameters.Clear();
        comm.Parameters.AddWithValue("@accountnumber", account);

        var result = comm.ExecuteScalar();

        if (result != null)
        {
            return int.Parse(result.ToString());
        }
        else
        {
            return -1; // (really an error code)
        }
    }
Пример #2
0
 public void EjecutarSp(System.Data.SqlClient.SqlCommand cb)
 {
     int result;
     conexion.Open();
     cb.CommandType = CommandType.StoredProcedure;
     cb.Connection = conexion;
     result = cb.ExecuteNonQuery();
     conexion.Close();
     
 }
Пример #3
0
		protected override void Cadastrar(System.Data.IDbCommand cmd)
		{
			cmd.CommandText = "INSERT INTO `problemafoto` (`referencia`, `descricao`, `data`, `usuario`) "
				+ " VALUES ("
				+ DbTransformar(referencia) + ", "
				+ DbTransformar(descricao) + ", "
				+ " NOW(), " // Data do BD
				+ DbTransformar(Usuários.UsuárioAtual.Nome) + ")";

			if (cmd.ExecuteNonQuery() == 0)
				throw new Exception("O sistema conseguiu registrar o problema da foto. Nenhuma query foi alterada. \n\n Consulta SQL: " + cmd.CommandText);
		}
Пример #4
0
        internal void ExecuteNonReader(System.Data.OleDb.OleDbCommand cmd)
        {
            try
            {
                cmd.Connection = conn;

                conn.Open();

                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
Пример #5
0
 protected int doAddUpdateDelete(System.Data.SqlClient.SqlCommand sql)
 {
     try
     {
         sql.Connection = con;
         sql.CommandType = System.Data.CommandType.StoredProcedure;
         int i = sql.ExecuteNonQuery();
         conClose();
         return i;
     }
     catch (System.Exception ex)
     {
         conClose();
         return 0;
     }
 }
Пример #6
0
        public static int executeCommand(System.Data.Common.DbCommand com)
        {
            try
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                com.Connection = new SQLiteConnection(conStr);
                com.Connection.Open();
                return com.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Logging.logMessage(ex.Message);
                throw ex;
                return -1;
            }
            finally
            {
                com.Connection.Close();
                com.Connection.Dispose();
            }
        }
		protected internal override void Descadastrar(System.Data.IDbCommand cmd)
		{
            DbManipulação[] relacionamentos = infoManipulação.ObterRelacionamentosInvertidos(this);

            foreach (DbManipulação relacionamento in relacionamentos)
                if (relacionamento != null)
                    DescadastrarEntidade(cmd, relacionamento);

            if (Transacionando || cmd.Transaction != null)
            {
                InfoManipulaçãoConexão info;
                IDbCommand molde;

                info  = ObterInfoManipulação(cmd.Connection);
                molde = info.PrepararDescadastramento(this);

                CopiarMoldeComando(molde, cmd);
            }

            cmd.ExecuteNonQuery();
		}
Пример #8
0
        public int ExecuteNonQuery(System.Data.Common.DbCommand command)
        {
            int rowNumber = 0;
            try
            {
                command.Prepare();
                rowNumber = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }

            return rowNumber;
        }
Пример #9
0
        /*
        internal void ParseAndInsertParameters(ref System.Data.SqlClient.SqlCommand cm, string sParams)
        {
            string[] Parameters = sParams.Split('*');

            foreach(string Param in Parameters)
            {
                string[] Data = Param.Split('~');
                cm.Parameters.Add(Data[0],Data[1]);
            }

        }

        */
        /// <summary>
        /// "DOESN'T WORK!!! Executes a Command using ExecuteNonQuery and returns the affected number of records
        /// </summary>
        /// <param name="CM"></param>
        /// <returns> Int value of the affected number of records</returns>
        public int ExecuteCommand(System.Data.SqlClient.SqlCommand CM)
        {
            Logger.WriteLog("ExecuteCommand**");
            RemoteFunctions.RFConnection RFC = GetAvailableConnection();
            System.Data.SqlClient.SqlConnection SC = RFC.Connection;
            CM.Connection = SC;
            int NumRecs = CM.ExecuteNonQuery();
            CM.Dispose();
            RFC.Release();
            return NumRecs;
        }
		protected internal override void Cadastrar(System.Data.IDbCommand cmd)
		{
            if (Transacionando || cmd.Transaction != null)
            {
                InfoManipulaçãoConexão info;
                IDbCommand molde;

                info  = ObterInfoManipulação(cmd.Connection);
                molde = info.PrepararCadastramento(this);

                CopiarMoldeComando(molde, cmd);
            }
#if DEBUG
			try 
			{
#endif
				cmd.ExecuteNonQuery();
#if DEBUG
			} 
			catch (Exception e)
			{
				throw new Exception("Comando SQL: " + cmd.CommandText + "\n" + e.ToString());
			}
#endif

			FieldInfo [] vetorAutoIncremento = infoManipulação.AutoIncremento;

			if (vetorAutoIncremento.Length == 1)
				vetorAutoIncremento[0].SetValue(this, Convert.ChangeType(ObterÚltimoCódigoInserido(cmd.Connection), vetorAutoIncremento[0].FieldType));
			
			else if (vetorAutoIncremento.Length > 1)
				throw new NotSupportedException("Existe mais de um valor auto-incrementado. Não é possível cadastrar automaticamente e atribuir valores auto-incrementados ao objeto.");

            DbManipulação[] relacionamentos = infoManipulação.ObterRelacionamentosInvertidos(this);

            foreach (DbManipulação relacionamento in relacionamentos)
                if (relacionamento != null)
                    CadastrarEntidade(cmd, relacionamento);
		}
Пример #11
0
 public static void DropIndexes (System.Data.IDbConnection conn)
 {
     conn.ExecuteNonQuery(@"DROP INDEX IF EXISTS Protein_Accession;
                            DROP INDEX IF EXISTS PeptideInstance_PeptideProtein;
                            DROP INDEX IF EXISTS PeptideInstance_ProteinOffsetLength;
                            DROP INDEX IF EXISTS SpectrumSourceGroupLink_SourceGroup;
                            DROP INDEX IF EXISTS Spectrum_SourceIndex;
                            DROP INDEX IF EXISTS Spectrum_SourceNativeID;
                            DROP INDEX IF EXISTS PeptideSpectrumMatch_PeptideSpectrumAnalysis;
                            DROP INDEX IF EXISTS PeptideSpectrumMatch_SpectrumAnalysisPeptide;
                            DROP INDEX IF EXISTS PeptideSpectrumMatch_QValue;
                            DROP INDEX IF EXISTS PeptideSpectrumMatch_Rank;
                            DROP INDEX IF EXISTS PeptideModification_PeptideSpectrumMatchModification;
                            DROP INDEX IF EXISTS PeptideModification_ModificationPeptideSpectrumMatch;
                           ");
 }
Пример #12
0
                public int ExecuteNonQuery(System.Data.IDbCommand command)
                {
                        if (this.ReadOnly)
                                throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");

                        if (this.IsOpen() == false)
                                this.Open();

                        if (Lfx.Workspace.Master.TraceMode)
                                Lfx.Workspace.Master.DebugLog(this.Handle, command.CommandText);

                        int Intentos = 3;
                        while (true) {
                                try {
                                        if (command.Connection == null)
                                                command.Connection = this.DbConnection;

                                        this.ResetKeepAliveTimer();
                                        int Res = command.ExecuteNonQuery();
                                        return Res;
                                } catch (Exception ex) {
                                        if (this.TryToRecover(ex) || Intentos-- <= 0) {
                                                LogError("----------------------------------------------------------------------------");
                                                LogError(ex.Message);
                                                LogError(command.CommandText);
                                                ex.Data.Add("Command", command.CommandText);
                                                throw ex;
                                        }
                                }
                        }
                }
Пример #13
0
 public static void CreateIndexes(System.Data.IDbConnection conn)
 {
     conn.ExecuteNonQuery(@"CREATE UNIQUE INDEX Protein_Accession ON Protein (Accession);
                            CREATE INDEX PeptideInstance_PeptideProtein ON PeptideInstance (Peptide, Protein);
                            CREATE UNIQUE INDEX PeptideInstance_ProteinOffsetLength ON PeptideInstance (Protein, Offset, Length);
                            CREATE UNIQUE INDEX SpectrumSourceGroupLink_SourceGroup ON SpectrumSourceGroupLink (Source, Group_);
                            CREATE INDEX Spectrum_SourceIndex ON Spectrum (Source, Index_);
                            CREATE UNIQUE INDEX Spectrum_SourceNativeID ON Spectrum (Source, NativeID);
                            CREATE INDEX PeptideSpectrumMatch_PeptideSpectrumAnalysis ON PeptideSpectrumMatch (Peptide, Spectrum, Analysis);
                            CREATE INDEX PeptideSpectrumMatch_SpectrumAnalysisPeptide ON PeptideSpectrumMatch (Spectrum, Analysis, Peptide);
                            CREATE INDEX PeptideSpectrumMatch_QValue ON PeptideSpectrumMatch (QValue);
                            CREATE INDEX PeptideSpectrumMatch_Rank ON PeptideSpectrumMatch (Rank);
                            CREATE INDEX PeptideModification_PeptideSpectrumMatchModification ON PeptideModification (PeptideSpectrumMatch, Modification);
                            CREATE INDEX PeptideModification_ModificationPeptideSpectrumMatch ON PeptideModification (Modification, PeptideSpectrumMatch);
                           ");
 }
Пример #14
0
        public static int ExecuteNonQuery(System.Data.IDbCommand cmd)
        {
            int iAffected = -1;
            using (System.Data.IDbConnection idbConn = GetConnection())
            {

                lock (idbConn)
                {

                    lock (cmd)
                    {
                        cmd.Connection = idbConn;

                        if (cmd.Connection.State != System.Data.ConnectionState.Open)
                            cmd.Connection.Open();

                        using (System.Data.IDbTransaction idbtTrans = idbConn.BeginTransaction())
                        {

                            try
                            {
                                cmd.Transaction = idbtTrans;

                                iAffected = cmd.ExecuteNonQuery();
                                idbtTrans.Commit();
                            } // End Try
                            catch (System.Data.Common.DbException ex)
                            {
                                if (idbtTrans != null)
                                    idbtTrans.Rollback();

                                iAffected = -2;

                                if (Log(ex))
                                    throw;
                            } // End catch
                            finally
                            {
                                if (cmd.Connection.State != System.Data.ConnectionState.Closed)
                                    cmd.Connection.Close();
                            } // End Finally

                        } // End Using idbtTrans

                    } // End lock cmd

                } // End lock idbConn

            } // End Using idbConn

            return iAffected;
        }
        private static void InsertUserWithAddress(System.Data.IDbCommand cmd)
        {
            var addressId = RandomGuid();
            var line1 = "line1 " + RandomString().Substring(0, 5);
            var line2 = "line2 " + RandomString().Substring(0, 8);
            var postcode = "PO" + Random.Next(1, 99) + " " + Random.Next(1, 99) + "CD";
            var sql = string.Format("INSERT INTO Addresses(AddressId, Line1, Line2, Postcode) VALUES('{0}', '{1}', '{2}', '{3}')",
                                addressId, line1, line2, postcode);
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            var userId = RandomGuid();
            var firstName = "F" + RandomString().Substring(0, 10);
            var lastName = "L " + RandomString().Substring(0, 8);
            sql = string.Format("INSERT INTO Users(UserId, FirstName, LastName, AddressId) VALUES('{0}', '{1}', '{2}', '{3}')",
                                userId, firstName, lastName, addressId);
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
Пример #16
0
 public int ExecuteNonQuery(System.Data.SqlClient.SqlCommand cm)
 {
     Functions.RFConnection RF = GetAvailableConnection();
     cm.Connection = RF.Connection;
     int numrecs = cm.ExecuteNonQuery();
     RF.Release();
     return numrecs;
 }
Пример #17
0
        private static int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction, out int out__rowid, bool findoutLastInsertId)
        {
            bool retry = false;
            bool trans_started = false;
            System.Data.SqlServerCe.SqlCeTransaction trans = null;

            try
            {
                if (useTransaction)
                {
                    trans = command.Connection.BeginTransaction();
                    trans_started = true;
                    command.Transaction = trans;
                }
                int rows_aff = command.ExecuteNonQuery();
                int row_id = -1;
                if (findoutLastInsertId)
                {
                    if (useTransaction)
                        row_id = GetLastInsertId(trans);
                    else
                        row_id = GetLastInsertId();
                }
                if (useTransaction)
                {
                    trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
                    trans_started = false;
                    command.Transaction = null;
                }

                out__rowid = row_id;
                return rows_aff;
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                ////////MessageBox.Show(ex.Message + "\n" + "HRES = " + ex.HResult + "\n" + "ERRNO = " + ex.NativeError);

                if (useTransaction && trans_started)
                {
                    SafeTransRollback(trans);
                    trans.Dispose();
                    trans = null;
                    command.Transaction = null;
                    trans_started = false;
                }

                if (ex.NativeError == 0)
                {
                    retry = true;
                }
                else
                {
                    throw;
                }
            }

            if (retry)
            {
                command.Connection.Close();
                command.Connection.Open();

                if (useTransaction)
                {
                    trans = command.Connection.BeginTransaction();
                    trans_started = true;
                    command.Transaction = trans;
                }
                int rows_aff = command.ExecuteNonQuery();
                int row_id = -1;
                if (findoutLastInsertId)
                {
                    if (useTransaction)
                        row_id = GetLastInsertId(trans);
                    else
                        row_id = GetLastInsertId();
                }
                if (useTransaction)
                {
                    trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
                    trans_started = false;
                    command.Transaction = null;
                }

                out__rowid = row_id;
                return rows_aff;
            }
            else
            {
                out__rowid = -1;
                return 0;
            }
        }
Пример #18
0
		protected override void Cadastrar(System.Data.IDbCommand cmd)
		{
			// Verificar tipo de registro
			cmd.CommandText =
				"INSERT INTO telefonemanomenome " +
				"(quando, telefone, origem, destino, cidade, tipoOrigem, tipoDestino) " +
				"VALUES (" + DbTransformar(Quando) + ", " +
				DbTransformar(Telefone) + ", " +
				DbTransformar(Origem) + ", " +
				DbTransformar(Destino) + ", " +
				DbTransformar(Cidade) + ", " +
				"'" + ((int) TOrigem).ToString() + "', " +
				"'" + ((int) TDestino).ToString() + "')";

			if (cmd.ExecuteNonQuery() != 1)
				throw new Exception("Não foi possível inserir os dados do telefonema!");
		}
Пример #19
0
			/// <summary>
			/// This method is for such prepared statements verify if the Conection is autoCommit for assing the transaction to the command.
			/// </summary>
			/// <param name="command">The command to be tested.</param>
			/// <returns>The number of rows afected.</returns>
			public int ExecuteUpdate(System.Data.OleDb.OleDbCommand command)
			{
				if (!(((ConnectionProperties)this[command.Connection]).AutoCommit))
				{
					command.Transaction = ((ConnectionProperties)this[command.Connection]).Transaction;
					return command.ExecuteNonQuery();
				}
				else
					return command.ExecuteNonQuery();
			}