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 DataTable PesquisaGridView(string sCampos, string sWhere)
{
try
{
DataTable dt = new DataTable();
string sQuery = "Select "
+ sCampos
+ " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
+ " Where " + sWhere;
FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
dt.Clear();
cx.Open_Conexao();
da.Fill(dt);
da.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cx.Close_Conexao();
}
}
public DataTable BuscaDadosNF()
{
DataTable dt = new DataTable();
try
{
sSQL = "SELECT " + this.sCampos + " FROM " + this.sTabela;
if (!(this.sInner.Equals(String.Empty)))
sSQL += this.sInner.ToString();
if (!(this.sWhere.Equals(String.Empty)))
sSQL += " WHERE " + this.sWhere + " ";
if (!(this.sOrder.Equals(String.Empty)))
sSQL += " ORDER BY " + this.sOrder;
FbDataAdapter Da = new FbDataAdapter(sSQL, cx.get_Conexao());
cx.Open_Conexao();
dt.Clear();
Da.Fill(dt);
Da.Dispose();
}
catch (Exception)
{
throw;
}
finally { cx.Close_Conexao(); }
return dt;
}
public DataTable PesquisaGridViewContingencia(string sCampos)
{
try
{
DataTable dt = new DataTable();
string sQuery = "Select "
+ sCampos
+ " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
+ " where conhecim.st_contingencia ='S' and (conhecim.st_cte='N' or conhecim.st_cte is null)";
FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
dt.Clear();
cx.Open_Conexao();
da.Fill(dt);
da.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cx.Close_Conexao();
}
}
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 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 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();
}
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();
}
public DataTable GetSchema(FbConnection connection, string collectionName, string[] restrictions)
{
DataTable dataTable = new DataTable(collectionName);
FbCommand command = this.BuildCommand(connection, collectionName, this.ParseRestrictions(restrictions));
FbDataAdapter adapter = new FbDataAdapter(command);
try
{
adapter.Fill(dataTable);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataTable);
return this.ProcessResult(dataTable);
}
public DataTable BuscaDadosNF()
{
Conn.Open();
sSQL = "SELECT " + this.sCampos + " FROM " + this.sTabela;
if (!(this.sInner.Equals(String.Empty)))
sSQL += this.sInner.ToString();
if (!(this.sWhere.Equals(String.Empty)))
sSQL += " WHERE " + this.sWhere + " ";
if (!(this.sOrder.Equals(String.Empty)))
sSQL += " ORDER BY " + this.sOrder;
/*
SelCmd = new FbCommand(sSQL, Conn);
,SelCmd.CommandType = CommandType.Text;
*/
DataTable dt = new DataTable();
FbDataAdapter Da = new FbDataAdapter(sSQL, Conn);
dt.Clear();
Da.Fill(dt);
Da.Dispose();
Conn.Close();
return dt;
}
/// ------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// Führt das übegenen SQL Kommando aus
/// </summary>
/// <param name="sql">SQL Befehl der ausgeführt werden soll</param>
/// <returns>Die Ergebnismenge des Befehls</returns>
public static DataTable ExecuteQuery(string sql)
{
DataTable dt = new DataTable();
FbTransaction fbTransaction = fbConnection.BeginTransaction();
FbCommand fbCommand = new FbCommand();
fbCommand.CommandText = sql;
fbCommand.Connection = fbConnection;
fbCommand.Transaction = fbTransaction;
FbDataAdapter fbAdapter = new FbDataAdapter(fbCommand);
fbAdapter.Fill(dt);
fbTransaction.Commit();
fbTransaction.Dispose();
fbCommand.Dispose();
fbAdapter.Dispose();
return dt;
}
public void UpdateTimeStampTest()
{
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 = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
public void FillMultipleTest()
{
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 ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
/*
public static DataSet GetDataSet(string sql, CommandType commandType, int timeout)
{
FbDataAdapter adap = new FbDataAdapter(GetCommand(sql, commandType));
DataSet dataSet = new DataSet();
adap.SelectCommand.CommandTimeout = timeout;
adap.Fill(dataSet);
//definir tratamento e log de erros
adap.SelectCommand.Connection.Close();
adap.SelectCommand.Connection.Dispose();
adap.Dispose();
return dataSet;
}
*/
public DataTable GetDataTable(string sql, CommandType commandType)
{
FbDataAdapter adap = new FbDataAdapter(GetCommand(sql, commandType));
DataTable dataTable = new DataTable();
adap.Fill(dataTable);
//definir tratamento e log de erros
adap.SelectCommand.Connection.Close();
adap.SelectCommand.Connection.Dispose();
adap.Dispose();
return dataTable;
}
public void UpdateDecimalTest()
{
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 = 1;
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]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has not correct value");
}
public void UpdateVarCharTest()
{
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 = 1;
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]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
string val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
public void InsertTest()
{
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");
DataRow newRow = ds.Tables["TEST"].NewRow();
newRow["int_field"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGint_field"] = 100000;
newRow["SMALLint_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new TimeSpan(10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public static DataTable GetDataTable(FbCommand command)
{
FbDataAdapter adap = new FbDataAdapter(command);
DataTable dataTable = new DataTable();
adap.Fill(dataTable);
//definir tratamento e log de erros
adap.SelectCommand.Connection.Close();
adap.SelectCommand.Connection.Dispose();
adap.Dispose();
return dataTable;
}
private string BuscaNumNotas(string scd_nrlanc)
{
belConnection cx = new belConnection();
DataTable dt = new DataTable();
scd_nrlanc = Convert.ToInt32(scd_nrlanc).ToString().PadLeft(7, '0');
string sQuery = string.Format("select cd_nf from nfconhec where cd_empresa = '{0}' and nr_lancconhecim = '{1}'", belStatic.CodEmpresaCte, scd_nrlanc);
FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
dt.Clear();
cx.Open_Conexao();
da.Fill(dt);
da.Dispose();
string sretorno = "";
foreach (DataRow item in dt.Rows)
{
sretorno += "- Nota nº" + item["cd_nf"].ToString();
}
return sretorno;
}
public Int32 fnSaveBank()
{
// Initializate
cm = new FbCommand();
dt = new DataTable();
// Command attributes
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "SPBANK_IU";
cm.Connection = (FbConnection)objBankBE.oConnection;
// Stored Procedure Atributtes
if (ObjBankBE.nBank == 0)
{
cm.Parameters.AddWithValue("@W_NBANK", DBNull.Value);
}
else
{
cm.Parameters.Add("@W_NBANK", FbDbType.Integer).Value = objBankBE.nBank;
}
if (objBankBE.sBank == null)
{
cm.Parameters.AddWithValue("@W_SBANK", DBNull.Value);
}
else
{
cm.Parameters.Add("@W_SBANK", FbDbType.VarChar, 50).Value = objBankBE.sBank;
}
cm.Parameters.Add("@W_NERROR", FbDbType.Integer).Direction = ParameterDirection.Output;
try
{
// Execute
da = new FbDataAdapter(cm);
da.Fill(dt);
da.Dispose();
return Int32.Parse(((DataRow)(dt.Rows[0]))[0].ToString());
}
catch (Exception ex)
{
return 3;
}
finally
{
cm.Dispose();
dt.Dispose();
}
}