public int AddCatalogItem(String title, String uri)
{
FbConnection conn = GetConnection();
try {
using (FbCommand cmd = new FbCommand()) {
using (cmd.Transaction = conn.BeginTransaction(FbTransactionOptions.Autocommit|FbTransactionOptions.Concurrency)) {
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_AddCatalogItem";
cmd.Parameters.Add("@title", FbDbType.VarChar, 4000);
cmd.Parameters.Add("@uri", FbDbType.VarChar, 4000);
cmd.Parameters.Add("@catalog_item_id", FbDbType.Numeric);
cmd.Parameters[0].Value = title;
cmd.Parameters[1].Value = uri;
cmd.Parameters[2].Direction = ParameterDirection.ReturnValue;
//Capture the catalog item ID, and build the list
//of title characters
cmd.ExecuteNonQuery();
//int catId = (int)cmd.ExecuteScalar();
int catId = (int)cmd.Parameters[2].Value;
SetItemTitleChars(cmd.Transaction, catId, title);
cmd.Transaction.Commit();
return catId;
}
}
} finally {
conn.Close();
}
}
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 DisposeTest()
{
bool result = true;
try
{
FbCommand cmd = new FbCommand("select * from test", this.Connection);
cmd.Transaction = this.Connection.BeginTransaction(IsolationLevel.RepeatableRead);
FbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
}
r.Close();
cmd.Transaction.Rollback();
cmd.Transaction.Dispose();
result = false;
}
catch
{
}
finally
{
if (!result)
{
throw new Exception("Incorrect Dispose behavior");
}
}
}
public void AddTest()
{
FbCommand command = new FbCommand();
command.Parameters.Add(new FbParameter("@p292", 10000));
command.Parameters.Add("@p01", FbDbType.Integer);
command.Parameters.Add("@p02", 289273);
command.Parameters.Add("#p3", FbDbType.SmallInt, 2, "sourceColumn");
}
public void ClearCatalog()
{
FbConnection conn = GetConnection();
try {
using (FbCommand cmd = new FbCommand("delete from catalog_items", conn)) {
cmd.ExecuteNonQuery();
}
} finally {
conn.Close();
}
}
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 BinaryBlobTest()
{
int id_value = this.GetId();
string selectText = "SELECT blob_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, blob_field) values(@int_field, @blob_field)";
Console.WriteLine("\r\n\r\nBinary Blob Test");
Console.WriteLine("Generating an array of temp data");
// Generate an array of temp data
byte[] insert_values = new byte[100000*4];
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(insert_values);
Console.WriteLine("Executing insert command");
// Execute insert command
FbTransaction transaction = Connection.BeginTransaction();
FbCommand insert = new FbCommand(insertText, Connection, transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@blob_field", FbDbType.Binary).Value = insert_values;
insert.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(selectText, Connection);
byte[] select_values = (byte[])select.ExecuteScalar();
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");
}
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 SqlServerLikeTest00()
{
FbCommand command = new FbCommand("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();
// Print output value
Console.WriteLine("Output Parameters");
Console.WriteLine(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 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 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 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();
}
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 RemovePreparedCommand(FbCommand command)
{
this.PreparedCommands.Remove(command);
}
public void AddPreparedCommand(FbCommand command)
{
if (!this.PreparedCommands.Contains(command))
{
this.PreparedCommands.Add(command);
}
}
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 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 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 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);
}
private void RowUpdatingHandler(object sender, FbRowUpdatingEventArgs e)
{
if (e.Status != UpdateStatus.Continue)
{
return;
}
if (e.Command != null)
{
// Check that we can really build a new command.
// If the command passed in the FbRowUpdatingEventArgs
// is different than the one existent in the CommabdBuilder
// we can't build a new command
FbCommand command = null;
switch (e.StatementType)
{
case StatementType.Insert:
command = this.insertCommand;
break;
case StatementType.Delete:
command = this.deleteCommand;
break;
case StatementType.Update:
command = this.updateCommand;
break;
}
if (command != e.Command)
{
return;
}
}
try
{
switch (e.StatementType)
{
case StatementType.Insert:
e.Command = this.BuildInsertCommand(
e.Row,
e.TableMapping);
break;
case StatementType.Update:
e.Command = this.BuildUpdateCommand(
e.Row,
e.TableMapping);
break;
case StatementType.Delete:
e.Command = this.BuildDeleteCommand(
e.Row,
e.TableMapping);
break;
}
}
catch (Exception exception)
{
e.Errors = exception;
e.Status = UpdateStatus.ErrorsOccurred;
}
}
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);
}
public void DeriveParameters2()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("GETVARCHARFIELD", Connection, transaction);
command.CommandType = CommandType.StoredProcedure;
FbCommandBuilder.DeriveParameters(command);
Assert.AreEqual(2, command.Parameters.Count);
transaction.Commit();
}
public void DeriveParameters()
{
FbCommand command = new FbCommand("GETVARCHARFIELD", Connection);
command.CommandType = CommandType.StoredProcedure;
FbCommandBuilder.DeriveParameters(command);
Assert.AreEqual(2, command.Parameters.Count);
}
/// <include file='Doc/en_EN/FbCommandBuilder.xml' path='doc/class[@name="FbCommandBuilder"]/method[@name="DeriveParameters(FbCommand)"]/*'/>
public static void DeriveParameters(FbCommand command)
{
if (command.CommandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException("The command text is not a valid stored procedure name.");
}
string spName = command.CommandText.Trim();
string quotePrefix = "\"";
string quoteSuffix = "\"";
if (spName.StartsWith(quotePrefix) && spName.EndsWith(quoteSuffix))
{
spName = spName.Substring(1, spName.Length - 2);
}
else
{
spName = spName.ToUpper(CultureInfo.CurrentCulture);
}
command.Parameters.Clear();
DataView dataTypes = command.Connection.GetSchema("DataTypes").DefaultView;
DataTable spSchema = command.Connection.GetSchema(
"ProcedureParameters", new string[] { null, null, spName });
int count = 1;
foreach (DataRow row in spSchema.Rows)
{
dataTypes.RowFilter = String.Format(
CultureInfo.CurrentCulture,
"TypeName = '{0}'",
row["PARAMETER_DATA_TYPE"]);
FbParameter parameter = command.Parameters.Add(
"@" + row["PARAMETER_NAME"].ToString().Trim(),
FbDbType.VarChar);
parameter.FbDbType = (FbDbType)dataTypes[0]["ProviderDbType"];
parameter.Direction = (ParameterDirection)row["PARAMETER_DIRECTION"];
parameter.Size = Convert.ToInt32(row["PARAMETER_SIZE"], CultureInfo.InvariantCulture);
if (parameter.FbDbType == FbDbType.Decimal ||
parameter.FbDbType == FbDbType.Numeric)
{
if (row["NUMERIC_PRECISION"] != DBNull.Value)
{
parameter.Precision = Convert.ToByte(row["NUMERIC_PRECISION"], CultureInfo.InvariantCulture);
}
if (row["NUMERIC_SCALE"] != DBNull.Value)
{
parameter.Scale = Convert.ToByte(row["NUMERIC_SCALE"], CultureInfo.InvariantCulture);
}
}
count++;
}
}
/// <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;
}
override protected IDbCommand _Load(string conjuction)
{
bool hasColumn = false;
bool selectAll = true;
string query;
query = "SELECT ";
if( this._distinct) query += " DISTINCT ";
if( this._top >= 0) query += " FIRST " + this._top.ToString() + " ";
if(this._resultColumns.Length > 0)
{
query += this._resultColumns;
hasColumn = true;
selectAll = false;
}
if(this._countAll)
{
if(hasColumn)
{
query += ", ";
}
query += "COUNT(*)";
if(this._countAllAlias != string.Empty)
{
// Need DBMS string delimiter here
query += " AS " + D(this._countAllAlias);
}
hasColumn = true;
selectAll = false;
}
if(_aggregateParameters != null && _aggregateParameters.Count > 0)
{
bool isFirst = true;
if(hasColumn)
{
query += ", ";
}
AggregateParameter wItem;
foreach(object obj in _aggregateParameters)
{
wItem = obj as AggregateParameter;
if(wItem.IsDirty)
{
if(isFirst)
{
query += GetAggregate(wItem, true);
isFirst = false;
}
else
{
query += ", " + GetAggregate(wItem, true);
}
}
}
selectAll = false;
}
if(selectAll)
{
query += "*";
}
query += " FROM " + D(this._entity.QuerySource);
FbCommand cmd = new FbCommand();
if(_whereParameters != null && _whereParameters.Count > 0)
{
query += " WHERE ";
bool first = true;
bool requiresParam;
WhereParameter wItem;
bool skipConjuction = false;
string paramName;
string columnName;
foreach(object obj in _whereParameters)
{
// Maybe we injected text or a WhereParameter
if(obj.GetType().ToString() == "System.String")
{
string text = obj as string;
query += text;
if(text == "(")
{
skipConjuction = true;
}
}
else
{
wItem = obj as WhereParameter;
if(wItem.IsDirty)
{
if(!first && !skipConjuction)
{
if(wItem.Conjuction != WhereParameter.Conj.UseDefault)
{
if(wItem.Conjuction == WhereParameter.Conj.And)
query += " AND ";
else
query += " OR ";
}
else
{
query += " " + conjuction + " ";
}
}
requiresParam = true;
columnName = D(wItem.Column);
paramName = "@" + wItem.Column + (++inc).ToString();
wItem.Param.ParameterName = paramName;
switch(wItem.Operator)
{
case WhereParameter.Operand.Equal:
query += columnName + " = " + paramName + " ";
break;
case WhereParameter.Operand.NotEqual:
query += columnName + " <> " + paramName + " ";
break;
case WhereParameter.Operand.GreaterThan:
query += columnName + " > " + paramName + " ";
break;
case WhereParameter.Operand.LessThan:
query += columnName + " < " + paramName + " ";
break;
case WhereParameter.Operand.LessThanOrEqual:
query += columnName + " <= " + paramName + " ";
break;
case WhereParameter.Operand.GreaterThanOrEqual:
query += columnName + " >= " + paramName + " ";
break;
case WhereParameter.Operand.Like:
query += columnName + " LIKE " + paramName + " ";
break;
case WhereParameter.Operand.NotLike:
query += columnName + " NOT LIKE " + paramName + " ";
break;
case WhereParameter.Operand.IsNull:
query += columnName + " IS NULL ";
requiresParam = false;
break;
case WhereParameter.Operand.IsNotNull:
query += columnName + " IS NOT NULL ";
requiresParam = false;
break;
case WhereParameter.Operand.In:
query += columnName + " IN (" + wItem.Value + ") ";
requiresParam = false;
break;
case WhereParameter.Operand.NotIn:
query += columnName + " NOT IN (" + wItem.Value + ") ";
requiresParam = false;
break;
case WhereParameter.Operand.Between:
query += columnName + " BETWEEN " + paramName;
cmd.Parameters.Add(paramName, wItem.BetweenBeginValue);
paramName = "@" + wItem.Column + (++inc).ToString();
query += " AND " + paramName;
cmd.Parameters.Add(paramName, wItem.BetweenEndValue);
requiresParam = false;
break;
}
if(requiresParam)
{
IDbCommand dbCmd = cmd as IDbCommand;
dbCmd.Parameters.Add(wItem.Param);
wItem.Param.Value = wItem.Value;
}
first = false;
skipConjuction = false;
}
}
}
}
if(_groupBy.Length > 0)
{
query += " GROUP BY " + _groupBy;
if(this._withRollup)
{
query += " WITH ROLLUP";
}
}
if(_orderBy.Length > 0)
{
query += " ORDER BY " + _orderBy;
}
cmd.CommandText = query;
return cmd;
}
/// <include file='Doc/en_EN/FbDataReader.xml' path='doc/class[@name="FbDataReader"]/method[@name="Close"]/*'/>
public void Close()
{
bool closeConnection = false;
if (this.IsClosed)
{
return;
}
this.isClosed = true;
this.position = STARTPOS;
if (this.connection != null)
{
if ((this.behavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection)
{
closeConnection = true;
}
}
if (this.command != null && !this.command.IsDisposed)
{
if (this.command.CommandType == CommandType.StoredProcedure)
{
// Set values of output parameters
this.command.SetOutputParameters();
}
if (this.command.HasImplicitTransaction)
{
// Commit implicit transaction if needed
this.command.CommitImplicitTransaction();
}
// Set null the active reader of the command
this.command.ActiveReader = null;
}
if (closeConnection)
{
this.connection.Close();
}
this.command = null;
this.connection = null;
this.row = null;
this.schemaTable = null;
this.fields = null;
}
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);
}
/// <include file='Doc/en_EN/FbDataAdapter.xml' path='doc/class[@name="FbDataAdapter"]/constructor[@name="ctor(FbCommand)"]/*'/>
public FbDataAdapter(FbCommand selectCommand) : base()
{
this.SelectCommand = selectCommand;
}
internal FbDataReader(
FbCommand command, FbConnection connection, CommandBehavior behavior)
{
this.position = STARTPOS;
this.command = command;
this.behavior = behavior;
this.connection = connection;
this.fields = this.command.GetFieldsDescriptor();
this.UpdateRecordsAffected();
}