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)
}
}
public void EjecutarSp(System.Data.SqlClient.SqlCommand cb)
{
int result;
conexion.Open();
cb.CommandType = CommandType.StoredProcedure;
cb.Connection = conexion;
result = cb.ExecuteNonQuery();
conexion.Close();
}
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);
}
internal void ExecuteNonReader(System.Data.OleDb.OleDbCommand cmd)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
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;
}
}
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();
}
public int ExecuteNonQuery(System.Data.Common.DbCommand command)
{
int rowNumber = 0;
try
{
command.Prepare();
rowNumber = command.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
return rowNumber;
}
/*
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);
}
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;
");
}
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;
}
}
}
}
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);
");
}
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();
}
public int ExecuteNonQuery(System.Data.SqlClient.SqlCommand cm)
{
Functions.RFConnection RF = GetAvailableConnection();
cm.Connection = RF.Connection;
int numrecs = cm.ExecuteNonQuery();
RF.Release();
return numrecs;
}
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;
}
}
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!");
}
/// <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();
}