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);
}
public override DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return(this.schemaTable);
}
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string.Empty;
this.schemaTable = this.GetSchemaTableStructure();
/* Prepare statement for schema fields information */
FbCommand schemaCmd = new FbCommand(
this.GetSchemaCommandText(),
this.command.Connection,
this.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 (int i = 0; i < this.fields.Count; i++)
{
bool isKeyColumn = false;
bool isUnique = false;
bool isReadOnly = false;
int precision = 0;
bool isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = this.fields[i].Relation;
schemaCmd.Parameters[1].Value = this.fields[i].Name;
using (FbDataReader r = schemaCmd.ExecuteReader())
{
if (r.Read())
{
isReadOnly = (this.IsReadOnly(r) || this.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 = this.IsExpression(r);
}
}
/* 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"] = 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);
if (!String.IsNullOrEmpty(this.fields[i].Relation) && currentTable != this.fields[i].Relation)
{
tableCount++;
currentTable = this.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);
}