private DataTable Schema_ViewColumns(string strCatalog, string strView, string strColumn)
{
DataTable tbl = new DataTable("ViewColumns");
DataRow row;
string strSql;
int n;
DataRow schemaRow;
DataRow viewRow;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("VIEW_CATALOG", typeof(string));
tbl.Columns.Add("VIEW_SCHEMA", typeof(string));
tbl.Columns.Add("VIEW_NAME", typeof(string));
tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String));
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("COLUMN_NAME", typeof(string));
tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool));
tbl.Columns.Add("COLUMN_DEFAULT", typeof(string));
tbl.Columns.Add("COLUMN_FLAGS", typeof(long));
tbl.Columns.Add("IS_NULLABLE", typeof(bool));
tbl.Columns.Add("DATA_TYPE", typeof(string));
tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
tbl.Columns.Add("NUMERIC_PRECISION", typeof(int));
tbl.Columns.Add("NUMERIC_SCALE", typeof(int));
tbl.Columns.Add("DATETIME_PRECISION", typeof(long));
tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string));
tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string));
tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string));
tbl.Columns.Add("COLLATION_CATALOG", typeof(string));
tbl.Columns.Add("COLLATION_SCHEMA", typeof(string));
tbl.Columns.Add("COLLATION_NAME", typeof(string));
tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
tbl.Columns.Add("EDM_TYPE", typeof(string));
tbl.Columns.Add("AUTOINCREMENT", typeof(bool));
tbl.Columns.Add("UNIQUE", typeof(bool));
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
tbl.BeginLoadData();
using (SqliteCommand cmdViews = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this))
using (SqliteDataReader rdViews = cmdViews.ExecuteReader())
{
while (rdViews.Read())
{
if (String.IsNullOrEmpty(strView) || String.Compare(strView, rdViews.GetString(2), true, CultureInfo.InvariantCulture) == 0)
{
using (SqliteCommand cmdViewSelect = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdViews.GetString(2)), this))
{
strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase);
if (n < 0)
continue;
strSql = strSql.Substring(n + 4);
using (SqliteCommand cmd = new SqliteCommand(strSql, this))
using (SqliteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly))
using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable(false, false))
using (DataTable tblSchema = rd.GetSchemaTable(false, false))
{
for (n = 0; n < tblSchema.Rows.Count; n++)
{
viewRow = tblSchemaView.Rows[n];
schemaRow = tblSchema.Rows[n];
if (String.Compare(viewRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0
|| strColumn == null)
{
row = tbl.NewRow();
row["VIEW_CATALOG"] = strCatalog;
row["VIEW_NAME"] = rdViews.GetString(2);
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName];
row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName];
row["COLUMN_HASDEFAULT"] = (viewRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value);
row["COLUMN_DEFAULT"] = viewRow[SchemaTableOptionalColumn.DefaultValue];
row["ORDINAL_POSITION"] = viewRow[SchemaTableColumn.ColumnOrdinal];
row["IS_NULLABLE"] = viewRow[SchemaTableColumn.AllowDBNull];
row["DATA_TYPE"] = viewRow["DataTypeName"]; // SqliteConvert.DbTypeToType((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString();
row["EDM_TYPE"] = SqliteConvert.DbTypeToTypeName((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString().ToLower(CultureInfo.InvariantCulture);
row["CHARACTER_MAXIMUM_LENGTH"] = viewRow[SchemaTableColumn.ColumnSize];
row["TABLE_SCHEMA"] = viewRow[SchemaTableColumn.BaseSchemaName];
row["PRIMARY_KEY"] = viewRow[SchemaTableColumn.IsKey];
row["AUTOINCREMENT"] = viewRow[SchemaTableOptionalColumn.IsAutoIncrement];
row["COLLATION_NAME"] = viewRow["CollationType"];
row["UNIQUE"] = viewRow[SchemaTableColumn.IsUnique];
tbl.Rows.Add(row);
}
}
}
}
}
}
}
tbl.EndLoadData();
tbl.AcceptChanges();
return tbl;
}