private DataTable GetNonUniqueIndexes(string tableName)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
FbCommand select = new FbCommand(this.connection);
ISQLCursor cursor = null;
DataTable schema = BdpMetaDataHelper.GetIndices();
short resultCols = 0;
sql.Append(
@"SELECT " +
"idx.rdb$relation_name AS TableName, " +
"idx.rdb$index_name AS IndexName, " +
"seg.rdb$field_name AS ColumnName, " +
"seg.rdb$field_position AS FieldPosition, " +
"idx.rdb$index_type AS IndexType, " +
"idx.rdb$index_inactive AS InactiveIndex, " +
"idx.rdb$unique_flag AS IsUnique, " +
"idx.rdb$description AS Description " +
"FROM " +
"rdb$indices idx " +
"left join rdb$index_segments seg ON idx.rdb$index_name = seg.rdb$index_name ");
if (tableName != null && tableName.Length > 0)
{
where.AppendFormat("rdb$relation_name = '{0}' AND idx.rdb$unique_flag IS NULL", tableName);
}
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where.ToString());
}
sql.Append(" ORDER BY idx.rdb$relation_name, idx.rdb$index_name, seg.rdb$field_position");
/*
RecNo
CatalogName
SchemaName
TableName
IndexName
ColumnName
ColumnPosition
PKeyName
IndexType
SortOrder
Filter
*/
// Prepare and execute the command
select.Prepare(sql.ToString(), 0);
select.Execute(out cursor, ref resultCols);
int recno = 0;
while (cursor.Next() != -1)
{
DataRow row = schema.NewRow();
row["Recno"] = recno++;
row["CatalogName"] = null;
row["SchemaName"] = null;
row["TableName"] = ((FbCursor)cursor).GetValue(0).ToString().Trim();
row["IndexName"] = ((FbCursor)cursor).GetValue(1).ToString().Trim();
row["ColumnName"] = ((FbCursor)cursor).GetValue(2).ToString().Trim();
row["ColumnPosition"] = ((FbCursor)cursor).GetValue(3);
row["PKeyName"] = String.Empty;
row["IndexType"] = IndexType.NonUnique;
row["SortOrder"] = String.Empty;
row["Filter"] = String.Empty;
schema.Rows.Add(row);
}
cursor.Release();
select.Release();
return schema;
}
public FbCursor(FbCommand command)
{
this.command = command;
this.fields = this.command.GetFieldsDescriptor();
}
public DataTable GetProcedureParams(string spName, string paramName)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
FbCommand select = new FbCommand(this.connection);
ISQLCursor cursor = null;
short resultCols = 0;
DataTable schema = BdpMetaDataHelper.GetProcedureParams();
sql.Append(
@"SELECT " +
"pp.rdb$procedure_name AS ProcName, " +
"pp.rdb$parameter_name AS ParamName, " +
"pp.rdb$parameter_type AS ParamType, " +
"pp.rdb$parameter_number AS ParamPosition, " +
"fld.rdb$field_type AS ParamDataType, " +
"fld.rdb$field_sub_type AS ParamSubType, " +
"fld.rdb$field_length AS ParamSize, " +
"fld.rdb$field_precision AS ParamPrecision, " +
"fld.rdb$field_scale AS ParamScale " +
"FROM " +
"rdb$procedure_parameters pp " +
"left join rdb$fields fld ON pp.rdb$field_source = fld.rdb$field_name " +
"left join rdb$character_sets cs ON cs.rdb$character_set_id = fld.rdb$character_set_id " +
"left join rdb$collations coll ON (coll.rdb$collation_id = fld.rdb$collation_id AND coll.rdb$character_set_id = fld.rdb$character_set_id) ");
if (spName != null && spName.Length > 0)
{
where.AppendFormat("pp.rdb$procedure_name = '{0}'", spName);
}
if (paramName != null && paramName.Length > 0)
{
if (where.Length > 0)
{
where.Append(" AND ");
}
where.AppendFormat("pp.rdb$parameter_name = '{0}'", paramName);
}
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where.ToString());
}
// Prepare and execute the command
select.Prepare(sql.ToString(), 0);
select.Execute(out cursor, ref resultCols);
int recno = 0;
while (cursor.Next() != -1)
{
DataRow row = schema.NewRow();
int blrType = 0;
int subType = 0;
int length = 0;
int precision = 0;
int scale = 0;
int direction = 0;
if (((FbCursor)cursor).GetValue(4) != null &&
((FbCursor)cursor).GetValue(4) != DBNull.Value)
{
blrType = Convert.ToInt32(((FbCursor)cursor).GetValue(4));
}
if (((FbCursor)cursor).GetValue(5) != null &&
((FbCursor)cursor).GetValue(5) != DBNull.Value)
{
subType = Convert.ToInt32(((FbCursor)cursor).GetValue(5));
}
if (((FbCursor)cursor).GetValue(6) != null &&
((FbCursor)cursor).GetValue(6) != DBNull.Value)
{
length = Convert.ToInt32(((FbCursor)cursor).GetValue(6));
}
if (((FbCursor)cursor).GetValue(7) != null &&
((FbCursor)cursor).GetValue(7) != DBNull.Value)
{
precision = Convert.ToInt32(((FbCursor)cursor).GetValue(7));
}
if (((FbCursor)cursor).GetValue(8) != null &&
((FbCursor)cursor).GetValue(8) != DBNull.Value)
{
scale = Convert.ToInt32(((FbCursor)cursor).GetValue(8));
}
if (((FbCursor)cursor).GetValue(2) != null &&
((FbCursor)cursor).GetValue(2) != DBNull.Value)
{
direction = Convert.ToInt16(((FbCursor)cursor).GetValue(2));
}
DbDataType paramDbType = TypeHelper.GetDbDataType(blrType, subType, scale);
row["Recno"] = recno++;
row["CatalogName"] = null;
row["SchemaName"] = null;
row["ProcName"] = ((FbCursor)cursor).GetValue(0).ToString().Trim();
row["ParamName"] = ((FbCursor)cursor).GetValue(1).ToString().Trim();
if (direction == 0)
{
row["ParamType"] = ParameterDirection.Input;
}
else if (direction == 1)
{
row["ParamType"] = ParameterDirection.Output;
}
row["ParamPosition"] = Convert.ToInt16(((FbCursor)cursor).GetValue(3));
row["ParamDataType"] = BdpTypeHelper.GetBdpType(paramDbType);
row["ParamSubType"] = BdpTypeHelper.GetBdpSubType(paramDbType);
row["ParamTypeName"] = TypeHelper.GetDataTypeName(paramDbType);
row["ParamLength"] = length;
row["ParamPrecision"] = precision;
row["ParamScale"] = Convert.ToInt16(scale);
row["ParamNullable"] = true;
schema.Rows.Add(row);
}
cursor.Release();
select.Release();
return schema;
}
public DataTable GetProcedures(string spName, ProcedureType procType)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
FbCommand select = new FbCommand(this.connection);
ISQLCursor cursor = null;
DataTable schema = BdpMetaDataHelper.GetProcedures();
short resultCols = 0;
sql.Append(
@"SELECT " +
"rdb$procedure_name AS ProcName, " +
"rdb$procedure_inputs AS Inputs, " +
"rdb$procedure_outputs AS Outputs " +
"FROM " +
"rdb$procedures");
if (spName != null && spName.Length > 0)
{
where.AppendFormat("rdb$procedure_name = '{0}'", spName);
}
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where.ToString());
}
sql.Append(" ORDER BY rdb$procedure_name");
// Prepare and execute the command
select.Prepare(sql.ToString(), 0);
select.Execute(out cursor, ref resultCols);
int recno = 0;
while (cursor.Next() != -1)
{
DataRow row = schema.NewRow();
row["Recno"] = recno++;
row["CatalogName"] = null;
row["SchemaName"] = null;
row["ProcName"] = ((FbCursor)cursor).GetValue(0).ToString().Trim();
row["ProcType"] = procType;
row["InParams"] = ((FbCursor)cursor).GetValue(1);
row["OutParams"] = ((FbCursor)cursor).GetValue(2);
schema.Rows.Add(row);
}
cursor.Release();
select.Release();
return schema;
}
public DataTable GetColumns(
string tableName,
string columnName,
ColumnType columnType)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
FbCommand select = new FbCommand(this.connection);
ISQLCursor cursor = null;
DataTable schema = BdpMetaDataHelper.GetColumns();
short resultCols = 0;
sql.Append(
@"SELECT " +
"rfr.rdb$relation_name AS TableName, " +
"rfr.rdb$field_name AS ColumName, " +
"rfr.rdb$field_position AS ColumnPosition, " +
"fld.rdb$field_type AS ColumnDataType, " +
"fld.rdb$field_sub_type AS ColumnSubType, " +
"fld.rdb$field_length AS ColumnSize, " +
"fld.rdb$field_precision AS ColumnPrecision, " +
"fld.rdb$field_scale AS ColumnScale, " +
"coalesce(fld.rdb$null_flag, rfr.rdb$null_flag) AS ColumnNullable " +
"FROM " +
"rdb$relation_fields rfr " +
"left join rdb$fields fld ON rfr.rdb$field_source = fld.rdb$field_name ");
if (tableName != null && tableName.Length > 0)
{
where.AppendFormat("rfr.rdb$relation_name = '{0}'", tableName);
}
if (columnName != null && columnName.Length > 0)
{
if (where.Length > 0)
{
where.Append(" AND ");
}
where.AppendFormat("rfr.rdb$field_name = '{0}'", columnName);
}
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where.ToString());
}
sql.Append(" ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position");
// Prepare and execute the command
select.Prepare(sql.ToString(), 0);
select.Execute(out cursor, ref resultCols);
int recno = 0;
while (cursor.Next() != -1)
{
DataRow row = schema.NewRow();
row["Recno"] = recno++;
row["CatalogName"] = null;
row["SchemaName"] = null;
row["TableName"] = ((FbCursor)cursor).GetValue(0).ToString().Trim();
row["ColumnName"] = ((FbCursor)cursor).GetValue(1).ToString().Trim();
row["ColumnPosition"] = ((FbCursor)cursor).GetValue(2);
row["ColumnType"] = columnType;
row["ColumnDataType"] = ((FbCursor)cursor).GetValue(3);
row["ColumnTypeName"] = String.Empty;
row["ColumnSubtype"] = ((FbCursor)cursor).GetValue(4);
row["ColumnLength"] = ((FbCursor)cursor).GetValue(5);
row["ColumnPrecision"] = ((FbCursor)cursor).GetValue(6);
row["ColumnScale"] = ((FbCursor)cursor).GetValue(7);
if (((FbCursor)cursor).GetValue(8) == DBNull.Value)
{
row["ColumnNullable"] = true;
}
else
{
row["ColumnNullable"] = false;
}
schema.Rows.Add(row);
}
cursor.Release();
select.Release();
return schema;
}
public DataTable GetTables(string tableName, TableType tableType)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
FbCommand select = new FbCommand(this.connection);
ISQLCursor cursor = null;
DataTable schema = BdpMetaDataHelper.GetTables();
short resultCols = 0;
sql.Append(
@"SELECT " +
"rdb$relation_name AS TableName " +
"FROM " +
"rdb$relations");
if (tableName != null && tableName.Length > 0)
{
where.AppendFormat("rdb$relation_name = '{0}'", tableName);
}
if (where.Length > 0)
{
where.Append(" AND ");
}
switch (tableType)
{
case TableType.SystemTable:
where.Append("rdb$view_source IS null AND rdb$system_flag = 1");
break;
case TableType.View:
where.Append("rdb$view_source IS NOT null AND rdb$system_flag = 0");
break;
case TableType.Table:
default:
where.Append("rdb$view_source IS NULL AND rdb$system_flag = 0");
break;
}
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where.ToString());
}
sql.Append(" ORDER BY rdb$system_flag, rdb$owner_name, rdb$relation_name");
// Prepare and execute the command
select.Prepare(sql.ToString(), 0);
select.Execute(out cursor, ref resultCols);
int recno = 0;
while (cursor.Next() != -1)
{
DataRow row = schema.NewRow();
row["Recno"] = recno++;
row["CatalogName"] = null;
row["SchemaName"] = null;
row["TableName"] = ((FbCursor)cursor).GetValue(0).ToString().Trim();
row["TableType"] = tableType;
schema.Rows.Add(row);
}
cursor.Release();
select.Release();
return schema;
}
private void ExecuteDDL(string sql)
{
FbCommand command = new FbCommand(this.connection);
ISQLCursor cursor = null;
short numCols = 0;
command.Prepare(sql, 0);
command.Execute(out cursor, ref numCols);
command.Release();
}
