private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field", FbDbType.Integer);
command.Parameters.Add("@char_field", FbDbType.Char);
command.Parameters.Add("@varchar_field", FbDbType.VarChar);
command.Parameters.Add("@bigint_field", FbDbType.BigInt);
command.Parameters.Add("@smallint_field", FbDbType.SmallInt);
command.Parameters.Add("@float_field", FbDbType.Double);
command.Parameters.Add("@double_field", FbDbType.Double);
command.Parameters.Add("@numeric_field", FbDbType.Numeric);
command.Parameters.Add("@date_field", FbDbType.Date);
command.Parameters.Add("@time_Field", FbDbType.Time);
command.Parameters.Add("@timestamp_field", FbDbType.TimeStamp);
command.Parameters.Add("@clob_field", FbDbType.Text);
command.Parameters.Add("@blob_field", FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
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 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 void PrepareTest()
{
// Create a new test table
FbCommand create = new FbCommand("create table PrepareTest(test_field varchar(20));", Connection);
create.ExecuteNonQuery();
create.Dispose();
// Insert data using a prepared statement
FbCommand command = new FbCommand(
"insert into PrepareTest(test_field) values(@test_field);",
Connection);
command.Parameters.Add("@test_field", FbDbType.VarChar).Value = DBNull.Value;
command.Prepare();
for (int i = 0; i < 5; i++)
{
if (i < 1)
{
command.Parameters[0].Value = DBNull.Value;
}
else
{
command.Parameters[0].Value = i.ToString();
}
command.ExecuteNonQuery();
}
command.Dispose();
try
{
// Check that data is correct
FbCommand select = new FbCommand("select * from PrepareTest", Connection);
FbDataReader reader = select.ExecuteReader();
int count = 0;
while (reader.Read())
{
if (count == 0)
{
Assert.AreEqual(DBNull.Value, reader[0], "Invalid value.");
}
else
{
Assert.AreEqual(count, reader.GetInt32(0), "Invalid value.");
}
count++;
}
reader.Close();
select.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
// Drop table
FbCommand drop = new FbCommand("drop table PrepareTest", Connection);
drop.ExecuteNonQuery();
drop.Dispose();
}
}
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);
}
/// <summary>
/// Clears the quadruples of the store
/// </summary>
public override RDFStore ClearQuadruples() {
//Create command
var command = new FbCommand("DELETE FROM Quadruples", this.Connection);
try {
//Open connection
this.Connection.Open();
//Prepare command
command.Prepare();
//Open transaction
command.Transaction = this.Connection.BeginTransaction();
//Execute command
command.ExecuteNonQuery();
//Close transaction
command.Transaction.Commit();
//Close connection
this.Connection.Close();
}
catch (Exception ex) {
//Rollback transaction
command.Transaction.Rollback();
//Close connection
this.Connection.Close();
//Propagate exception
throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);
}
return this;
}
/// <summary>
/// Removes the quadruples with the given literal as object
/// </summary>
public override RDFStore RemoveQuadruplesByLiteral(RDFLiteral literalObject) {
if (literalObject != null) {
//Create command
var command = new FbCommand("DELETE FROM Quadruples WHERE ObjectID = @OBJID AND TripleFlavor = @TFV", this.Connection);
command.Parameters.Add(new FbParameter("OBJID", FbDbType.BigInt));
command.Parameters.Add(new FbParameter("TFV", FbDbType.Integer));
//Valorize parameters
command.Parameters["OBJID"].Value = literalObject.PatternMemberID;
command.Parameters["TFV"].Value = RDFModelEnums.RDFTripleFlavors.SPL;
try {
//Open connection
this.Connection.Open();
//Prepare command
command.Prepare();
//Open transaction
command.Transaction = this.Connection.BeginTransaction();
//Execute command
command.ExecuteNonQuery();
//Close transaction
command.Transaction.Commit();
//Close connection
this.Connection.Close();
}
catch (Exception ex) {
//Rollback transaction
command.Transaction.Rollback();
//Close connection
this.Connection.Close();
//Propagate exception
throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);
}
}
return this;
}
/// <summary>
/// Removes the quadruples with the given predicate
/// </summary>
public override RDFStore RemoveQuadruplesByPredicate(RDFResource predicateResource) {
if (predicateResource != null) {
//Create command
var command = new FbCommand("DELETE FROM Quadruples WHERE PredicateID = @PREDID", this.Connection);
command.Parameters.Add(new FbParameter("PREDID", FbDbType.BigInt));
//Valorize parameters
command.Parameters["PREDID"].Value = predicateResource.PatternMemberID;
try {
//Open connection
this.Connection.Open();
//Prepare command
command.Prepare();
//Open transaction
command.Transaction = this.Connection.BeginTransaction();
//Execute command
command.ExecuteNonQuery();
//Close transaction
command.Transaction.Commit();
//Close connection
this.Connection.Close();
}
catch (Exception ex) {
//Rollback transaction
command.Transaction.Rollback();
//Close connection
this.Connection.Close();
//Propagate exception
throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);
}
}
return this;
}
/// <summary>
/// Adds the given quadruple to the store, avoiding duplicate insertions
/// </summary>
public override RDFStore AddQuadruple(RDFQuadruple quadruple) {
if (quadruple != null) {
//Create command
var command = new FbCommand("UPDATE OR INSERT INTO Quadruples (QuadrupleID, TripleFlavor, Context, ContextID, Subject, SubjectID, Predicate, PredicateID, Object, ObjectID) VALUES (@QID, @TFV, @CTX, @CTXID, @SUBJ, @SUBJID, @PRED, @PREDID, @OBJ, @OBJID) MATCHING (QuadrupleID)", this.Connection);
command.Parameters.Add(new FbParameter("QID", FbDbType.BigInt));
command.Parameters.Add(new FbParameter("TFV", FbDbType.Integer));
command.Parameters.Add(new FbParameter("CTX", FbDbType.VarChar, 1000));
command.Parameters.Add(new FbParameter("CTXID", FbDbType.BigInt));
command.Parameters.Add(new FbParameter("SUBJ", FbDbType.VarChar, 1000));
command.Parameters.Add(new FbParameter("SUBJID", FbDbType.BigInt));
command.Parameters.Add(new FbParameter("PRED", FbDbType.VarChar, 1000));
command.Parameters.Add(new FbParameter("PREDID", FbDbType.BigInt));
command.Parameters.Add(new FbParameter("OBJ", FbDbType.VarChar, 5000));
command.Parameters.Add(new FbParameter("OBJID", FbDbType.BigInt));
//Valorize parameters
command.Parameters["QID"].Value = quadruple.QuadrupleID;
command.Parameters["TFV"].Value = quadruple.TripleFlavor;
command.Parameters["CTX"].Value = quadruple.Context.ToString();
command.Parameters["CTXID"].Value = quadruple.Context.PatternMemberID;
command.Parameters["SUBJ"].Value = quadruple.Subject.ToString();
command.Parameters["SUBJID"].Value = quadruple.Subject.PatternMemberID;
command.Parameters["PRED"].Value = quadruple.Predicate.ToString();
command.Parameters["PREDID"].Value = quadruple.Predicate.PatternMemberID;
command.Parameters["OBJ"].Value = quadruple.Object.ToString();
command.Parameters["OBJID"].Value = quadruple.Object.PatternMemberID;
try {
//Open connection
this.Connection.Open();
//Prepare command
command.Prepare();
//Open transaction
command.Transaction = this.Connection.BeginTransaction();
//Execute command
command.ExecuteNonQuery();
//Close transaction
command.Transaction.Commit();
//Close connection
this.Connection.Close();
}
catch (Exception ex) {
//Rollback transaction
command.Transaction.Rollback();
//Close connection
this.Connection.Close();
//Propagate exception
throw new RDFStoreException("Cannot insert data into Firebird store because: " + ex.Message, ex);
}
}
return this;
}
