public List<Indicador> Listar(string empresa, string filial)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "SELECT EMPRESA, FILIAL, CODIGO, DESCRICAO FROM INDICADOR "+
"WHERE ((INDICADOR.EMPRESA = @EMPRESA) OR (INDICADOR.EMPRESA = '**')) "+
"AND ((INDICADOR.FILIAL = @FILIAL) OR (INDICADOR.FILIAL = '**'))";
sqlCommand.Parameters.AddWithValue("@EMPRESA", empresa);
sqlCommand.Parameters.AddWithValue("@FILIAL", filial);
FbDataAdapter sqlAdapter = new FbDataAdapter();
sqlAdapter.SelectCommand = sqlCommand;
DataTable dtIndicador = new DataTable();
sqlAdapter.Fill(dtIndicador);
return this.ConverteDataTableEmList(dtIndicador).ToList();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public static bool ContratoTemItens(string contrato)
{
bool retorno = false;
conn = AcessoDados.AcessoDados.getConn();
FbCommand comando = new FbCommand("select COUNT(*) as contador from sci_licitacao_contrato_itens lci where lci.ctrcod = @CONTRATO",conn);
FbParameter IDCONTRATO = new FbParameter("@CONTRATO", FbDbType.Integer);
IDCONTRATO.Value = contrato;
comando.Parameters.Add(IDCONTRATO);
conn.Open();
FbDataReader dr = comando.ExecuteReader();
while (dr.Read()) {
if (Convert.ToInt16(dr["contador"]) > 0)
{
retorno = true;
}
else {
retorno = false;
}
}
conn.Close();
comando.Dispose();
return retorno;
}
public void Cadastrar(Agendamento obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO AGENDAMENTO (EMPRESA, FILIAL, FUNCIONARIO, CLIENTE,STATUS,DATAPREVISTO, " +
"INICIOPREVISTO,FIMPREVISTO,TRASLADOPREVISTO, RESUMOAGENDAMENTO) " +
"VALUES (@EMPRESA,@FILIAL,@FUNCIONARIO,@CLIENTE, @STATUS, @DATAPREVISTO, @INICIOPREVISTO, " +
"@FIMPREVISTO,@TRASLADOPREVISTO, @RESUMOAGENDAMENTO)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void InsertGuidTest()
{
FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection);
createTable.ExecuteNonQuery();
createTable.Dispose();
Guid newGuid = Guid.Empty;
Guid guidValue = Guid.NewGuid();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection);
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection);
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
newGuid = r.GetGuid(0);
}
}
Assert.AreEqual(guidValue, newGuid);
}
public void SavePointTest()
{
FbCommand command = new FbCommand();
Console.WriteLine("Iniciada nueva transaccion");
Transaction = Connection.BeginTransaction("InitialSavePoint");
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = "insert into TEST (INT_FIELD) values (200) ";
command.ExecuteNonQuery();
Transaction.Save("FirstSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (201) ";
command.ExecuteNonQuery();
Transaction.Save("SecondSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (202) ";
command.ExecuteNonQuery();
Transaction.Rollback("InitialSavePoint");
Transaction.Commit();
command.Dispose();
}
public void Cadastrar(Projeto obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO PROJETOS (CLIENTE, EMPRESA, FILIAL, HORASGERENTE, HORASCONSULTOR, HORASCOORDENADOR, DESCRICAO, META) " +
"VALUES (@CLIENTE, @EMPRESA, @FILIAL, @HORAGERENTE, @HORACONSULTOR, @HORACOORDENADOR, @DESCRICAO, @META)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void AlterarSenha(Usuario obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "UPDATE SYS_USERS SET USUARIOWEB = @USUARIO, SENHAWEB = @SENHA WHERE CODIGO = @CODIGO";
sqlCommand.Parameters.AddWithValue("@USUARIO", obj.NomeUsuario);
sqlCommand.Parameters.AddWithValue("@SENHA", obj.Senha);
sqlCommand.Parameters.AddWithValue("@CODIGO", obj.Codigo);
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void InsertNullGuidTest()
{
FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (INT_FIELD INTEGER, GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection);
createTable.ExecuteNonQuery();
createTable.Dispose();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection);
insert.Parameters.Add("@IntField", FbDbType.Integer).Value = this.GetId();
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = DBNull.Value;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection);
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
if (!r.IsDBNull(1))
{
throw new Exception();
}
}
}
}
public void Cadastrar(OrdemServico obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO ORDEM_SERVICO (EMPRESA, FILIAL, CLIENTE, FUNCIONARIO, DATA, INICIO, FIM, TRANSLADO, ATIVIDADE, "+
"FATURADO, STATUS, OBSERVACAO, PROJETO, TIPOHORA) " +
"VALUES (@EMPRESA, @FILIAL, @CLIENTE, @FUNCIONARIO, @DATA, @INICIO, @FIM, @TRANSLADO, @ATIVIDADE, "+
"@FATURADO, @SITUACAO, @OBSERVACAO, @PROJETO, @TIPOHORA)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void Cadastrar(Meta obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO META (EMPRESA, FILIAL, DESCRICAO, DATACADASTRO, INDICADOR, FUNCIONARIO) " +
"VALUES (@EMPRESA, @FILIAL, @DESCRICAO, @DATACADASTRO, @INDICADOR, @FUNCIONARIO)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void AbortTransaction()
{
FbTransaction transaction = null;
FbCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new FbCommand("ALTER TABLE \"TEST\" drop \"INT_FIELD\"", this.Connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction = null;
}
catch (Exception)
{
transaction.Rollback();
transaction = null;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void InsertNullGuidTest()
{
// Insert the Guid
var id = GetId();
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection);
insert.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = DBNull.Value;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
using (FbCommand select = new FbCommand("SELECT * FROM GUID_TEST WHERE INT_FIELD = @IntField", Connection))
{
select.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
if (!r.IsDBNull(1))
{
throw new Exception();
}
}
}
}
}
public static bool check_func(FbConnectionStringBuilder fc)
{
bool res_ = false;
using (FbConnection fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_func,fb,ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
res_ = true;
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch { }
finally { fb.Close(); }
fb.Dispose();
}
return res_;
}
public void Cadastrar(Periodo obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO PERIODO (EMPRESA, FILIAL, ANO, MES, REALIZADO, ESPERADO, META, FUNCIONARIO) VALUES "+
"(@EMPRESA, @FILIAL, @ANO, @MES, @REALIZADO, @ESPERADO, @META, @FUNCIONARIO)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void BigArrayTest()
{
int id_value = GetId();
int elements = short.MaxValue;
string selectText = "SELECT big_array FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, big_array) values(@int_field, @array_field)";
Console.WriteLine("\r\n\r\nBigArrayTest");
Console.WriteLine("Generating an array of temp data");
// Generate an array of temp data
byte[] bytes = new byte[elements * 4];
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(bytes);
int[] insert_values = new int[elements];
Buffer.BlockCopy(bytes, 0, insert_values, 0, bytes.Length);
Console.WriteLine("Executing insert command");
// Execute insert command
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
int[] select_values = new int[insert_values.Length];
System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length);
for (int i = 0; i < insert_values.Length; i++)
{
if (insert_values[i] != select_values[i])
{
throw new Exception("differences at index " + i.ToString());
}
}
}
}
Console.WriteLine("Finishing test");
reader.Close();
select.Dispose();
// Start a new Transaction
Transaction = Connection.BeginTransaction();
}
public static void CreateDataBase()
{
//First run
if (IsServer && !IsDbAlreadyCreated)
{
if (File.Exists(DbPath))
FbConnection.DropDatabase(GetConnectionString());
FbConnection.CreateDatabase(GetConnectionString());
FbConnection connection = GetConnection();
Open();
FbTransaction mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand("create table MethodLogs (Token varchar(50), MethodName varchar(200), Status int, " +
"TimeTaken bigint, Datetime timestamp, EndDatetime timestamp, Error BLOB SUB_TYPE TEXT)", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
Close();
IsDbAlreadyCreated = !IsDbAlreadyCreated;
}
//subsequent runs
//do not recreate db, only refresh the database (drop and recreate MethodLogs table)
else if (IsDbAlreadyCreated)
{
Close();
FbConnection connection = GetConnection();
Open();
FbTransaction mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand("drop table MethodLogs", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
command = new FbCommand("create table MethodLogs (Token varchar(50), MethodName varchar(200), Status int, " +
"TimeTaken bigint, Datetime timestamp, EndDatetime timestamp, Error BLOB SUB_TYPE TEXT)", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
Close();
}
}
public void IntegerArrayTest()
{
int id_value = this.GetId();
Console.WriteLine("\r\n");
Console.WriteLine("Integer Array Test");
string selectText = "SELECT iarray_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, iarray_field) values(@int_field, @array_field)";
// Insert new Record
int[] insert_values = new int[4];
insert_values[0] = 10;
insert_values[1] = 20;
insert_values[2] = 30;
insert_values[3] = 40;
Console.WriteLine("Executing insert command");
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
int[] select_values = new int[insert_values.Length];
System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length);
for (int i = 0; i < insert_values.Length; i++)
{
if (insert_values[i] != select_values[i])
{
throw new Exception("differences at index " + i.ToString());
}
}
}
}
reader.Close();
select.Dispose();
}
public void ReadClobTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
reader.GetValue(reader.GetOrdinal("clob_field"));
reader.GetValue(reader.GetOrdinal("clob_field"));
}
reader.Close();
command.Dispose();
transaction.Rollback();
}
public void BigIntGetStringTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.Write(reader.GetString(reader.GetOrdinal("bigint_field")) + "\t");
Console.WriteLine();
}
reader.Close();
command.Dispose();
transaction.Rollback();
}
public static bool getParametroGradeDIDComplementar()
{
bool retorno = false;
FbConnection conn = AcessoDados.AcessoDados.getConn();
FbCommand comando = new FbCommand("Select DIDCOMPLEMENTAR_UNIDORC FROM WEBCONFIGURACOES ", conn);
conn.Open();
FbDataReader dr = comando.ExecuteReader();
while (dr.Read()) {
if (!Convert.IsDBNull(dr["DIDCOMPLEMENTAR_UNIDORC"])) {
retorno = true;
}
}
conn.Close();
comando.Dispose();
return retorno;
}
public DataTable fnGetFields()
{
// Initializate
cm = new FbCommand();
dt = new DataTable();
// Command attributes
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "spFields_SEL";
cm.Connection = (FbConnection)objMainBE.oConnection;
// Stored Procedure Atributtes
if (ObjMainBE.sItem == "")
{
cm.Parameters.AddWithValue("@w_sItem", DBNull.Value);
}
else
{
cm.Parameters.Add("@w_sItem", ObjMainBE.sItem);
}
try
{
// Execute
da = new FbDataAdapter(cm);
da.Fill(dt);
da.Dispose();
return dt;
}
finally
{
cm.Dispose();
dt.Dispose();
}
}
public DataTable fnSearchBank()
{
// Initializate
cm = new FbCommand();
dt = new DataTable();
// Command attributes
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "spBank_SEL_ALL";
cm.Connection = (FbConnection)objBankBE.oConnection;
// Stored Procedure Atributtes
if (ObjBankBE.nBank == 0)
{
cm.Parameters.AddWithValue("@sCommand", DBNull.Value);
}
else
{
cm.Parameters.Add("@sCommand", FbDbType.Char).Value = objBankBE.sSearchCommand;
}
try
{
// Execute
da = new FbDataAdapter(cm);
da.Fill(dt);
da.Dispose();
return dt;
}
finally
{
cm.Dispose();
dt.Dispose();
}
}
public void FillTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
protected void ExecutarComando(string query, List<FbParameter> parametros)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._connection;
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddRange(parametros);
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void FirebirdLikeTest00()
{
FbCommand command = new FbCommand("EXECUTE PROCEDURE GETVARCHARFIELD(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
// Check the value
Assert.AreEqual("IRow Number 1", command.Parameters[1].Value);
// Dispose command - this will do a transaction commit
command.Dispose();
}
public void FirebirdLikeTest01()
{
FbCommand command = new FbCommand("SELECT * FROM GETVARCHARFIELD(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters[0].Value = 1;
// This will fill output parameters values
FbDataReader reader = command.ExecuteReader();
reader.Read();
// Print output value
Console.WriteLine("Output Parameters - Result of SELECT command");
Console.WriteLine(reader[0]);
reader.Close();
// Dispose command - this will do a transaction commit
command.Dispose();
}
public void DataAdapterFillTest()
{
FbCommand command = new FbCommand("select * from TEST where DATE_FIELD = ?", Connection);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", FbDbType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
/// <summary>
/// Atualiza uma linha da tabela especificada
/// </summary>
/// <param name="tabela">Tabela para atualizar os dados, NÃO PODE SER NULL</param>
/// <param name="colunas">Colunas da tabela a serem usadas no comando UPDATE, NÃO PODE SER NULL</param>
/// <param name="onde">Clausula WHERE sem o WHERE propriamente dito. Deve conter os parâmetros, tendo os nomes da coluna precedida por @, NÃO PODE SER NULL</param>
/// <param name="dados">FbCommand com os parâmetros contendo os dados a serem atualizados na tabela, NÃO PODE SER NULL</param>
/// <returns>inteiro com chave primária do registro atualizado</returns>
public int atualizar(string tabela, string[] colunas, string onde, FbParameterCollection dados, string colunaRetorno)
{
if ((tabela == null || tabela.Length == 0) || (colunas == null || colunas.Length == 0) || (onde == null || onde.Length == 0) ||(dados == null || dados.Count == 0)) {
return -1;
}
StringBuilder sql = new StringBuilder();
int tamanho = colunas.Length;
sql.Append("UPDATE ");
sql.Append(tabela);
sql.Append(" SET ");
for (int i = 0; i < tamanho; i++) {
sql.Append(i != (tamanho - 1) ? colunas[i] + " = @" + colunas[i] + ", " : colunas[i] + " = @" + colunas[i]);
}
sql.Append(" WHERE ");
sql.Append(onde);
sql.Append(" RETURNING ");
sql.Append(colunaRetorno + ";");
FbCommand comando = new FbCommand(sql.ToString(), conexao, conexao.BeginTransaction());
foreach (FbParameter parametro in dados) {
comando.Parameters.Add(parametro);
}
int codigoRetorno = -1;
try {
codigoRetorno = Convert.ToInt32(comando.ExecuteScalar());
comando.Transaction.Commit();
} catch (Exception) {
comando.Transaction.Rollback();
return -1;
} finally {
comando.Dispose();
}
return codigoRetorno;
}
public void InsertGuidTest()
{
Guid newGuid = Guid.Empty;
Guid guidValue = Guid.NewGuid();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection);
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
using (FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection))
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
newGuid = r.GetGuid(1);
}
}
Assert.AreEqual(guidValue, newGuid);
}
private async Task <DataTable> GetSchemaTableImpl(AsyncWrappingCommonArgs async)
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
var tableCount = 0;
var currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction);
try
{
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
await schemaCmd.PrepareImpl(async).ConfigureAwait(false);
_schemaTable.BeginLoadData();
for (var i = 0; i < _fields.Count; i++)
{
var isKeyColumn = false;
var isUnique = false;
var isReadOnly = false;
var precision = 0;
var isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
var reader = await schemaCmd.ExecuteReaderImpl(CommandBehavior.Default, async).ConfigureAwait(false);
try
{
if (await reader.ReadImpl(async).ConfigureAwait(false))
{
isReadOnly = (IsReadOnly(reader) || IsExpression(reader)) ? true : false;
isKeyColumn = (reader.GetInt32(2) == 1) ? true : false;
isUnique = (reader.GetInt32(3) == 1) ? true : false;
precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4);
isExpression = IsExpression(reader);
}
}
finally
{
#if NET48 || NETSTANDARD2_0
reader.Dispose();
#else
await async.AsyncSyncCallNoCancellation(reader.DisposeAsync, reader.Dispose).ConfigureAwait(false);
#endif
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
await schemaCmd.Close(async).ConfigureAwait(false);
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
}
finally
{
#if NET48 || NETSTANDARD2_0
schemaCmd.Dispose();
#else
await async.AsyncSyncCallNoCancellation(schemaCmd.DisposeAsync, schemaCmd.Dispose).ConfigureAwait(false);
#endif
}
return(_schemaTable);
}
public override DataTable GetSchemaTable()
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
var tableCount = 0;
var currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
var schemaCmd = new FbCommand(
GetSchemaCommandText(),
_command.Connection,
_command.Connection.InnerConnection.ActiveTransaction);
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
_schemaTable.BeginLoadData();
for (var i = 0; i < _fields.Count; i++)
{
var isKeyColumn = false;
var isUnique = false;
var isReadOnly = false;
var precision = 0;
var isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
using (var r = schemaCmd.ExecuteReader())
{
if (r.Read())
{
isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false;
isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
isUnique = (r.GetInt32(3) == 1) ? true : false;
precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
isExpression = IsExpression(r);
}
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
/* Close statement */
schemaCmd.Close();
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
/* Dispose command */
schemaCmd.Dispose();
return(_schemaTable);
}
public override DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return(this.schemaTable);
}
#region Variables
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string.Empty;
this.schemaTable = GetSchemaTableStructure();
const Int16 batchLimit = 90; //Could be adjusted as needed.
Int16 paramCounter = 0; //Counter for the whole batch process
Int16 batchRounds = 0; //counter for each batch (limited by batchlimit)
Hashtable relationList = new Hashtable(); //HashTable to store the query's unique Field Tables Names.
List <RDBTableInfo> fieldList = new List <RDBTableInfo>(this.fields.Count + 1); //List to store the whole statement Schema Field Values.
const Int16 metadataColSize = 31; //Firebird MAX Column Size.
Int16 batchID = 0; //Batch marker. When batchlimit reaches its limit it increases by one the value.
StringBuilder sb = new StringBuilder(); //Stores dynamic generated schema query.
#endregion
// Prepare statement for schema fields information
//Asign current active schema command connection and transaccion
FbCommand schemaCmd = new FbCommand();
schemaCmd.Connection = this.command.Connection;
schemaCmd.Transaction = this.command.Connection.InnerConnection.ActiveTransaction;
for (paramCounter = 0; paramCounter < this.FieldCount; paramCounter++)
{
if (batchRounds >= batchLimit) //Process field params until batch limit is reached.
{
batchID++;
batchRounds = 0;
}
RDBTableInfo rdbinfo = new RDBTableInfo();
rdbinfo.Ordinal = paramCounter;
rdbinfo.FieldName = this.fields[paramCounter].Name;
rdbinfo.RelationName = this.fields[paramCounter].Relation;
rdbinfo.BatchID = batchID;
fieldList.Add(rdbinfo);
batchRounds++;
}
//Process batch schema query
for (Int16 i = 0; i <= batchID; i++)
{
sb.Length = 0;
relationList.Clear();
List <RDBTableInfo> rdblBatch = new List <RDBTableInfo>(this.fields.Count + 1);
//Find all RDBTableInfo elements according to batchID
rdblBatch = fieldList.FindAll(rdbti => rdbti.BatchID == i);
//Just add the needed tables according to the fieldnames on the current batch.
for (Int16 j = 0; j < rdblBatch.Count; j++)
{
//Keep a list of unique relation names (tables) from all the fieldlist.
if (!relationList.ContainsValue(rdblBatch[j].RelationName))
{
relationList.Add(relationList.Count, rdblBatch[j].RelationName);
}
}
if (schemaCmd.Parameters.Count > 0) //Clear previous command parameters.
{
schemaCmd.Parameters.Clear();
}
//Get the Base Squema query to start generating Dynamic Schema query
sb.Append(GetSchemaCommandTextBase());
//Perform batch field query against table schema
//Add relation (table names) to schemaCmd
for (int j = 0; j < relationList.Count; j++)
{
if (j > 0) //More than one table in query statement
{
sb.Append(" OR ");
}
List <RDBTableInfo> tmpList = rdblBatch.FindAll(rdbti => rdbti.RelationName.Equals(relationList[j]));
sb.AppendFormat(" (rfr.rdb$field_name in {0} AND rfr.rdb$relation_name='{1}') ", GetParamExpression(tmpList.Count), relationList[j]);
for (int k = 0; k < tmpList.Count; k++)
{
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, metadataColSize).Value = tmpList[k].FieldName;
}
tmpList = null;
}
//set order to schema query
sb.Append(" ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position");
schemaCmd.CommandText = sb.ToString();
schemaCmd.Prepare();
schemaTable.BeginLoadData();
//Reset Column Values
int Ordinal = 0;
int batchCount = 0;
//perform batch query
using (FbDataReader r = schemaCmd.ExecuteReader())
{
batchCount = 0;//reset batch counter
while (r.Read())
{
rdblBatch[batchCount].isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false;
rdblBatch[batchCount].isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
rdblBatch[batchCount].isUnique = (r.GetInt32(3) == 1) ? true : false;
rdblBatch[batchCount].precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
rdblBatch[batchCount].isExpression = IsExpression(r);
batchCount++;
}
}
for (int j = 0; j < rdblBatch.Count; j++)
{
Ordinal = rdblBatch[j].Ordinal;
// Create new row for the Schema Table
schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = this.GetName(Ordinal);
schemaRow["ColumnOrdinal"] = Ordinal;
schemaRow["ColumnSize"] = this.fields[Ordinal].GetSize();
if (fields[Ordinal].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (rdblBatch[j].precision > 0)
{
schemaRow["NumericPrecision"] = rdblBatch[j].precision;
}
schemaRow["NumericScale"] = this.fields[Ordinal].NumericScale * (-1);
}
schemaRow["DataType"] = this.GetFieldType(Ordinal);
schemaRow["ProviderType"] = this.GetProviderType(Ordinal);
schemaRow["IsLong"] = this.fields[Ordinal].IsLong();
schemaRow["AllowDBNull"] = this.fields[Ordinal].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = rdblBatch[j].isReadOnly;
schemaRow["IsKey"] = rdblBatch[j].isKeyColumn;
schemaRow["IsUnique"] = rdblBatch[j].isUnique;
schemaRow["IsAliased"] = this.fields[Ordinal].IsAliased();
schemaRow["IsExpression"] = rdblBatch[j].isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = this.fields[Ordinal].Relation;
schemaRow["BaseColumnName"] = this.fields[Ordinal].Name;
schemaTable.Rows.Add(schemaRow);
if (!String.IsNullOrEmpty(this.fields[Ordinal].Relation) && currentTable != this.fields[Ordinal].Relation)
{
tableCount++;
currentTable = this.fields[Ordinal].Relation;
}
}
schemaTable.EndLoadData();
rdblBatch = null;
}//Finish Batch Round Iteration
schemaCmd.Close();
if (tableCount > 1)
{
foreach (DataRow row in schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
//Dispose command
schemaCmd.Dispose();
relationList = null;
fieldList = null;
return(schemaTable);
}
