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.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 IntergerArrayTest()
{
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 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 FillTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
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 BigIntGetStringTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
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 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();
}
public void ReadTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
reader.Close();
command.Dispose();
transaction.Rollback();
}
public void GetValuesTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < values.Length; i++)
{
Console.Write(values[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void InvalidParameterFormat()
{
string sql = "update test set timestamp_field = @timestamp where int_field = @integer";
FbTransaction transaction = this.Connection.BeginTransaction();
try
{
FbCommand command = new FbCommand(sql, this.Connection, transaction);
command.Parameters.Add("@timestamp", FbDbType.TimeStamp).Value = 1;
command.Parameters.Add("@integer", FbDbType.Integer).Value = 1;
command.ExecuteNonQuery();
command.Dispose();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
public void GetSchemaTableWithExpressionFieldTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction);
FbDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema = reader.GetSchemaTable();
Console.WriteLine();
Console.WriteLine("DataReader - GetSchemaTable Method- Test");
DataRow[] currRows = schema.Select(null, null, DataViewRowState.CurrentRows);
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myCol.ColumnName);
}
Console.WriteLine();
foreach (DataRow myRow in currRows)
{
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myRow[myCol]);
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void NextResultTest()
{
string querys = "select * from TEST order by INT_FIELD asc;" +
"select * from TEST order by INT_FIELD desc;";
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand(querys, Connection, transaction);
FbDataReader reader = command.ExecuteReader();
Console.WriteLine();
Console.WriteLine("DataReader - NextResult Method - Test ( First Result )");
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
if(reader.NextResult())
{
Console.WriteLine("DataReader - NextResult Method - Test ( Second Result )");
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void DoubleArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("Double Array Test");
Console.WriteLine("------ ----- ----");
string updateText = "update TEST set barray_field = @array_field " +
"WHERE int_field = 1";
double[] new_values = new double[2];
new_values[0] = 1700.10;
new_values[1] = 1800.20;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void ExecuteReaderWithBehaviorTest()
{
FbCommand command = new FbCommand("select * from TEST", Connection);
FbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
command.Dispose();
}
public void PartialUpdatesTest()
{
int id_value = this.GetId();
int elements = 16384;
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\nPartialUpdatesTest");
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 void UnicodeTest()
{
string createTable = "CREATE TABLE VARCHARTEST (VARCHAR_FIELD VARCHAR(10));";
FbCommand ct = new FbCommand(createTable, this.Connection);
ct.ExecuteNonQuery();
ct.Dispose();
ArrayList l = new ArrayList();
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('11');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('111');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1111');");
foreach (string statement in l)
{
FbCommand insert = new FbCommand(statement, this.Connection);
insert.ExecuteNonQuery();
insert.Dispose();
}
string sql = "select * from varchartest";
FbCommand cmd = new FbCommand(sql, this.Connection);
FbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
Console.WriteLine("{0} :: {1}", r[0], r[0].ToString().Length);
}
r.Close();
}
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.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 TimeStampArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("TimeStamp Array Test");
Console.WriteLine("--------- ----- ----");
string updateText = "update TEST set tsarray_field = @array_field " +
"WHERE int_field = 1";
DateTime[] new_values = new DateTime[2];
new_values[0] = DateTime.Now.AddSeconds(100);
new_values[1] = DateTime.Now.AddSeconds(200);
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void SimplifiedChineseTest()
{
string createTable = "CREATE TABLE TABLE1 (FIELD1 varchar(20))";
FbCommand create = new FbCommand(createTable, this.Connection);
create.ExecuteNonQuery();
create.Dispose();
// insert using parametrized SQL
string sql = "INSERT INTO Table1 VALUES (@value)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Parameters.Add("@value", FbDbType.VarChar).Value = "中文";
command.ExecuteNonQuery();
command.Dispose();
sql = "SELECT * FROM TABLE1";
FbCommand select = new FbCommand(sql, this.Connection);
string result = select.ExecuteScalar().ToString();
select.Dispose();
Assert.AreEqual("中文", result, "Incorrect results in parametrized insert");
sql = "DELETE FROM TABLE1";
FbCommand delete = new FbCommand(sql, this.Connection);
delete.ExecuteNonQuery();
delete.Dispose();
// insert using plain SQL
sql = "INSERT INTO Table1 VALUES ('中文')";
FbCommand plainCommand = new FbCommand(sql, this.Connection);
plainCommand.ExecuteNonQuery();
plainCommand.Dispose();
sql = "SELECT * FROM TABLE1";
select = new FbCommand(sql, this.Connection);
result = select.ExecuteScalar().ToString();
select.Dispose();
Assert.AreEqual("中文", result, "Incorrect results in plain insert");
}
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.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);
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 DateTime(100, 10, 10, 10, 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 void ExecuteNonQueryWithOutputParameters()
{
FbCommand command = new FbCommand("EXECUTE PROCEDURE GETASCIIBLOB(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@CLOB_FIELD", FbDbType.Text).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
// Check that the output parameter has a correct value
Assert.AreEqual("IRow Number 1", command.Parameters[1].Value, "Output parameter value is not valid");
// Dispose command - this will do a transaction commit
command.Dispose();
}
/// <include file='Doc/en_EN/FbTransaction.xml' path='doc/class[@name="FbTransaction"]/method[@name="Rollback(System.String)"]/*'/>
public void Rollback(string savePointName)
{
lock (this)
{
if (savePointName == null)
{
throw new ArgumentException("No transaction name was be specified.");
}
else
{
if (savePointName.Length == 0)
{
throw new ArgumentException("No transaction name was be specified.");
}
}
if (this.isUpdated)
{
throw new InvalidOperationException("This Transaction has completed; it is no longer usable.");
}
try
{
FbCommand command = new FbCommand(
"ROLLBACK WORK TO SAVEPOINT " + savePointName,
this.connection,
this);
command.ExecuteNonQuery();
command.Dispose();
}
catch (IscException ex)
{
throw new FbException(ex.Message, ex);
}
}
}
public void BigIntArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("BigInt Array Test");
Console.WriteLine("------ ----- ----");
string updateText = "update TEST set larray_field = @array_field " +
"WHERE int_field = 1";
long[] new_values = new long[4];
new_values[0] = 900;
new_values[1] = 1000;
new_values[2] = 1100;
new_values[3] = 1200;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
/// <include file='Doc/en_EN/FbDataReader.xml' path='doc/class[@name="FbDataReader"]/method[@name="GetSchemaTable"]/*'/>
public DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return(this.schemaTable);
}
DataRow schemaRow;
this.schemaTable = this.GetSchemaTableStructure();
/* Prepare statement for schema fields information */
FbCommand schemaCmd = new FbCommand(
this.GetSchemaCommandText(),
this.command.Connection,
this.command.ActiveTransaction);
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
schemaTable.BeginLoadData();
for (int i = 0; i < this.fields.Count; i++)
{
bool isKeyColumn = false;
bool isUnique = false;
bool isReadOnly = false;
int precision = 0;
if (!this.fields[i].IsExpression())
{
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = this.fields[i].Relation;
schemaCmd.Parameters[1].Value = this.fields[i].Name;
FbDataReader r = schemaCmd.ExecuteReader();
if (r.Read())
{
isReadOnly = (this.IsReadOnly(r) || this.fields[i].IsExpression()) ? true : false;
isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
isUnique = (r.GetInt32(3) == 1) ? true : false;
precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
}
/* Close the Reader */
r.Close();
}
/* Create new row for the Schema Table */
schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = this.GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = this.fields[i].GetSize();
if (fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = this.fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = this.GetFieldType(i);
schemaRow["ProviderType"] = this.GetProviderType(i);
schemaRow["IsLong"] = this.fields[i].IsLong();
schemaRow["AllowDBNull"] = this.fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = this.fields[i].IsAliased();
schemaRow["IsExpression"] = this.fields[i].IsExpression();
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = this.fields[i].Relation;
schemaRow["BaseColumnName"] = this.fields[i].Name;
schemaTable.Rows.Add(schemaRow);
/* Close statement */
schemaCmd.Close();
}
schemaTable.EndLoadData();
/* Dispose command */
schemaCmd.Dispose();
return(schemaTable);
}
public void NumericArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("Numeric/Decimal Array Test");
Console.WriteLine("--------------- ----- ----");
string updateText = "update TEST set narray_field = @array_field " +
"WHERE int_field = 1";
decimal[] new_values = new decimal[2];
new_values[0] = 2100.10M;
new_values[1] = 2200.20M;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void ParameterDescribeTest()
{
string sql = "insert into TEST (int_field) values (@value)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Prepare();
command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;
command.ExecuteNonQuery();
command.Dispose();
}
public void VarCharArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("VarChar Array Test");
Console.WriteLine("------- ----- ----");
string updateText = "update TEST set varray_field = @array_field " +
"WHERE int_field = 1";
string[] new_values = new string[2];
new_values[0] = "abc";
new_values[1] = "abcdef";
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
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.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();
}
/// <include file='Doc/en_EN/FbDataReader.xml' path='doc/class[@name="FbDataReader"]/method[@name="GetSchemaTable"]/*'/>
public DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return this.schemaTable;
}
DataRow schemaRow;
this.schemaTable = this.GetSchemaTableStructure();
/* Prepare statement for schema fields information */
FbCommand schemaCmd = new FbCommand(
this.GetSchemaCommandText(),
this.command.Connection,
this.command.ActiveTransaction);
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
schemaTable.BeginLoadData();
for (int i = 0; i < this.fields.Count; i++)
{
bool isKeyColumn = false;
bool isUnique = false;
bool isReadOnly = false;
int precision = 0;
if (!this.fields[i].IsExpression())
{
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = this.fields[i].Relation;
schemaCmd.Parameters[1].Value = this.fields[i].Name;
FbDataReader r = schemaCmd.ExecuteReader();
if (r.Read())
{
isReadOnly = (this.IsReadOnly(r) || this.fields[i].IsExpression()) ? true : false;
isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
isUnique = (r.GetInt32(3) == 1) ? true : false;
precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
}
/* Close the Reader */
r.Close();
}
/* Create new row for the Schema Table */
schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = this.GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = this.fields[i].GetSize();
if (fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = this.fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = this.GetFieldType(i);
schemaRow["ProviderType"] = this.GetProviderType(i);
schemaRow["IsLong"] = this.fields[i].IsLong();
schemaRow["AllowDBNull"] = this.fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = this.fields[i].IsAliased();
schemaRow["IsExpression"] = this.fields[i].IsExpression();
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = this.fields[i].Relation;
schemaRow["BaseColumnName"] = this.fields[i].Name;
schemaTable.Rows.Add(schemaRow);
/* Close statement */
schemaCmd.Close();
}
schemaTable.EndLoadData();
/* Dispose command */
schemaCmd.Dispose();
return schemaTable;
}
public void FillMultipleTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
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();
}