private List <QuerySourceField> LoadFieldsFromTable(string connectionString, string schemaName = null, string tableName = null)
{
var result = new List <QuerySourceField>();
var dataTypeAdaptor = new DB2SupportDataType();
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
var builder = new SchemaRestrictionsBuilder(true);
if (!string.IsNullOrEmpty(schemaName))
{
builder = builder.TableSchema(schemaName);
}
if (!string.IsNullOrEmpty(tableName))
{
builder = builder.Table(tableName);
}
string[] restrictions = builder.Build();
var columns = conn.GetSchema(DB2MetaDataCollectionNames.Columns, restrictions);
//var columns = conn.GetSchema("Columns");
foreach (var row in columns.Rows.OfType <DataRow>())
{
var fieldDataType = string.Empty + row["DATA_TYPE_NAME"];
var izendaDataType = dataTypeAdaptor.GetIzendaDataType(fieldDataType);
if (string.IsNullOrEmpty(izendaDataType))
{
continue;
}
result.Add(
new QuerySourceField
{
Name = row["COLUMN_NAME"].ToString(),
DataType = fieldDataType,
IzendaDataType = izendaDataType,
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(fieldDataType),
//ExtendedProperties = string.IsNullOrEmpty("" + row["column_key"]) ? "" : new FieldExtendedProperty { PrimaryKey = true }.ToJson(),//TODO FieldExtendedProperty ?
Position = Convert.ToInt32(row["ORDINAL_POSITION"]),
QuerySourceName = row["TABLE_NAME"].ToString(),
CategoryName = row["TABLE_SCHEMA"].ToString()
});
}
}
return(result);
}
private IEnumerable <QuerySource> GetProcedures(DB2Connection conn, string schemaName)
{
string[] restrictions = new SchemaRestrictionsBuilder()
.TableSchema(schemaName)
.Build();
var tables = conn.GetSchema(DB2MetaDataCollectionNames.Procedures, restrictions);
return(GetQuerySources(tables, SQLQuerySourceType.Procedure, "PROCEDURE_NAME"));
//string sql = string.Format(@"SELECT PROCNAME FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA = '{0}';", schemaName);
//var result = conn.Query<dynamic>(sql)
// .Select(s => new QuerySource
// {
// Name = s.PROCNAME,
// Type = SQLQuerySourceType.Procedure
// }).ToList();
//return result;
}
private IEnumerable <QuerySource> GetViews(DB2Connection conn, string schemaName)
{
string[] restrictions = new SchemaRestrictionsBuilder()
.TableSchema(schemaName)
.TableType("View")
.Build();
var tables = conn.GetSchema(DB2MetaDataCollectionNames.Tables, restrictions);
return(GetQuerySources(tables, SQLQuerySourceType.View, "TABLE_NAME"));
//string sql = string.Format(@"SELECT NAME FROM SYSIBM.SYSTABLES WHERE TYPE = 'V' AND CREATOR = '{0}';", schemaName);
//var result = conn.Query<dynamic>(sql)
// .Select(s => new QuerySource
// {
// Name = s.NAME,
// Type = SQLQuerySourceType.View
// }).ToList();
//return result;
}
