private static IList <DbTable> GetDbTables(string tableName, DbConnection dbConnection, string schema = "public")
{
IList <DbTable> list;
using (DbCommand command = Transaction.CreateCommand(dbConnection, null))
{
string str1 = " where tables.table_schema = '" + schema + "'" + (!string.IsNullOrEmpty(tableName) ? " and tables.table_name ILIKE '" + tableName + "' " : string.Empty);
command.CommandText = @"select tables.table_name, tables.table_schema, columns.column_name, columns.is_nullable, columns.udt_name, columns.column_default,
key_usage.ordinal_position from information_schema.tables as tables join information_schema.columns as columns
on tables.table_catalog = columns.table_catalog and tables.table_schema = columns.table_schema and
tables.table_name = columns.table_name left join information_schema.key_column_usage as key_usage on
tables.table_catalog = key_usage.table_catalog and tables.table_schema = key_usage.table_schema and
tables.table_name = key_usage.table_name and columns.column_name = key_usage.column_name "
+ str1 + "order by table_schema, table_name, columns.ordinal_position";
command.Connection = dbConnection;
DbDataReader dbDataReader = command.ExecuteReader();
list = (IList <DbTable>) new List <DbTable>();
Dictionary <string, DbTable> dictionary1 = new Dictionary <string, DbTable>();
Dictionary <string, DbColumn> dictionary2 = new Dictionary <string, DbColumn>();
while (dbDataReader.Read())
{
string string1 = dbDataReader.GetString(0);
string string2 = dbDataReader.GetString(1);
string string3 = dbDataReader.GetString(2);
string string4 = dbDataReader.GetString(3);
string string5 = dbDataReader.GetString(4);
string str2 = !dbDataReader.IsDBNull(5) ? dbDataReader.GetString(5) : string.Empty;
bool isPrimaryKey = !dbDataReader.IsDBNull(6);
DbTable dbTable = null;
if (!dictionary1.ContainsKey(string1))
{
dbTable = new DbTable(string1, string2, (IList <DbColumn>) new List <DbColumn>());
dictionary1.Add(string1, dbTable);
list.Add(dbTable);
}
else
{
dbTable = dictionary1[string1];
}
string key = string1 + "*" + string3;
if (!dictionary2.ContainsKey(key))
{
bool pIsAutoGenerated = str2.StartsWith("nextval");
IList <DbColumn> columns = dictionary1[string1].Columns;
DbColumn dbColumn = new DbColumn(string3, PostgresSchemaProvider.GetDataType(string5), string4 == "YES", isPrimaryKey, pIsAutoGenerated, false);
dbColumn.ColumnTypeName = DbColumnFactory.GetColumnTypeName(dbColumn.DbType, dbTable.Name, dbColumn.IsPrimaryKey, dbColumn.Nullable);
dbColumn.ReturnType = DbColumnFactory.GetReturnType(dbColumn.DbType, dbColumn.Nullable);
columns.Add(dbColumn);
dictionary2.Add(key, dbColumn);
}
else if (isPrimaryKey)
{
dictionary2[key].IsPrimaryKey = true;
}
}
}
dbConnection.Close();
return(list);
}
private IList <DbTable> GetDbTables(string tableName, DbConnection dbConnection)
{
Dictionary <string, DbTable> mapDbTables = new Dictionary <string, DbTable>();
IList <DbTable> dbTables = (IList <DbTable>) new List <DbTable>();
using (DbCommand command = Transaction.CreateCommand(dbConnection, (Transaction)null))
{
string str1 = !string.IsNullOrEmpty(tableName) ? "WHERE tables.TABLE_NAME = '" + tableName + "' " : string.Empty;
command.CommandText = @"SELECT tables.TABLE_NAME, tables.TABLE_SCHEMA, columns.COLUMN_NAME, IS_NULLABLE,DATA_TYPE,
COLUMN_DEFAULT, tc.CONSTRAINT_TYPE , columnproperty(object_id(quotename(columns.TABLE_SCHEMA) + '.' + quotename(columns.TABLE_NAME)),
columns.COLUMN_NAME, 'IsIdentity') FROM INFORMATION_SCHEMA.TABLES AS tables
JOIN INFORMATION_SCHEMA.COLUMNS AS columns ON tables.TABLE_CATALOG = columns.TABLE_CATALOG AND
tables.TABLE_SCHEMA = columns.TABLE_SCHEMA AND tables.TABLE_NAME = columns.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON cu.Table_Name = columns.Table_Name
AND cu.Column_Name = columns.COLUMN_NAME LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND CONSTRAINT_TYPE = 'PRIMARY KEY' " + str1 + "ORDER BY tables.TABLE_SCHEMA, tables.TABLE_NAME, columns.ORDINAL_POSITION";
command.Connection = dbConnection;
Dictionary <string, DbColumn> dictionary2 = new Dictionary <string, DbColumn>();
using (DbDataReader dbDataReader = command.ExecuteReader())
{
while (dbDataReader.Read())
{
string schemaTableName = dbDataReader.GetString(0);
string schemaName = dbDataReader.GetString(1);
string string3 = dbDataReader.GetString(2);
string string4 = dbDataReader.GetString(3);
string string5 = dbDataReader.GetString(4);
string str2 = !dbDataReader.IsDBNull(5) ? dbDataReader.GetString(5) : string.Empty;
bool isPrimaryKey = !dbDataReader.IsDBNull(6) && !string.IsNullOrEmpty(dbDataReader.GetString(6));
bool pIsAutoGenerated = dbDataReader.GetInt32(7) == 1;
DbTable dbTable = null;
if (!mapDbTables.ContainsKey(schemaTableName))
{
dbTable = new DbTable(schemaTableName, schemaName, (IList <DbColumn>) new List <DbColumn>());
mapDbTables.Add(schemaTableName, dbTable);
dbTables.Add(dbTable);
}
else
{
dbTable = mapDbTables[schemaTableName];
}
IList <DbColumn> columns = mapDbTables[schemaTableName].Columns;
string key = schemaTableName + schemaName + string3;
if (!dictionary2.ContainsKey(key))
{
DbColumn dbColumn = new DbColumn(string3, MssqlSchemaProvider.GetDataType(string5),
string4 == "YES", isPrimaryKey, pIsAutoGenerated, false);
dbColumn.ColumnTypeName = DbColumnFactory.GetColumnTypeName(dbColumn.DbType, dbTable.Name, dbColumn.IsPrimaryKey, dbColumn.Nullable);
dbColumn.ReturnType = DbColumnFactory.GetReturnType(dbColumn.DbType, dbColumn.Nullable);
columns.Add(dbColumn);
dictionary2.Add(key, dbColumn);
}
else
{
DbColumn dbColumn = dictionary2[key];
if (isPrimaryKey)
{
dbColumn.IsPrimaryKey = isPrimaryKey;
}
if (pIsAutoGenerated)
{
dbColumn.IsAutoGenerated = pIsAutoGenerated;
}
}
}
}
}
LoadForeignKeys(tableName, ref dbTables, dbConnection);
using (DbCommand command = Transaction.CreateCommand(dbConnection, (Transaction)null))
{
command.CommandText = "SELECT VIEWS.TABLE_NAME, VIEWS.TABLE_SCHEMA, VIEW_COLUMN_USAGE.COLUMN_NAME, IS_NULLABLE,DATA_TYPE FROM INFORMATION_SCHEMA.VIEWS AS VIEWS JOIN INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS VIEW_COLUMN_USAGE ON VIEWS.TABLE_CATALOG = VIEW_COLUMN_USAGE.VIEW_CATALOG AND VIEWS.TABLE_SCHEMA = VIEW_COLUMN_USAGE.VIEW_SCHEMA AND VIEWS.TABLE_NAME = VIEW_COLUMN_USAGE.VIEW_NAME JOIN INFORMATION_SCHEMA.COLUMNS AS COLUMNS ON VIEW_COLUMN_USAGE.TABLE_CATALOG = COLUMNS.TABLE_CATALOG AND VIEW_COLUMN_USAGE.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND VIEW_COLUMN_USAGE.TABLE_NAME = COLUMNS.TABLE_NAME AND VIEW_COLUMN_USAGE.COLUMN_NAME = COLUMNS.COLUMN_NAME " + (!string.IsNullOrEmpty(tableName) ? "WHERE VIEWS.TABLE_NAME = '" + tableName + "' " : string.Empty);
command.Connection = dbConnection;
using (DbDataReader dbDataReader = command.ExecuteReader())
{
mapDbTables.Clear();
while (dbDataReader.Read())
{
string string1 = dbDataReader.GetString(0);
string string2 = dbDataReader.GetString(1);
string string3 = dbDataReader.GetString(2);
string string4 = dbDataReader.GetString(3);
string string5 = dbDataReader.GetString(4);
if (!mapDbTables.ContainsKey(string1))
{
DbTable dbTable = new DbTable(string1, string2, (IList <DbColumn>) new List <DbColumn>());
mapDbTables.Add(string1, dbTable);
dbTables.Add(dbTable);
}
mapDbTables[string1].Columns.Add(new DbColumn(string3, MssqlSchemaProvider.GetDataType(string5), string4 == "YES", false, false, true));
}
}
}
dbConnection.Close();
return(dbTables);
}
public static string GenerateStoredProcedureCode(StoredProcedure pProc, string columnPrefix, bool pIncludeSchema)
{
string newLine = Environment.NewLine;
string str = "\t";
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("using System;").Append(newLine);
stringBuilder.Append("using System.Data;");
stringBuilder.Append("using System.Data.SqlClient;");
stringBuilder.Append("namespace Tables.").Append(pProc.Name).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append("public sealed class Proc : ").Append(typeof(StoredProcBase).ToString()).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public static readonly Proc Instance = new Proc();").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Proc() : base(DATABASE, \"").Append(!pIncludeSchema || string.IsNullOrEmpty(pProc.Schema) ? string.Empty : pProc.Schema + ".").Append(pProc.Name).Append("\", typeof(Row)) {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("//AddColumns(");
stringBuilder.Append(");").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Sql.IResult Execute(");
int index1 = 0;
while (index1 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index1];
if (index1 > 0)
{
stringBuilder.Append(", ");
}
if (spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue)
{
stringBuilder.Append("out ");
}
stringBuilder.Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(" ").Append(spParameter.Name);
checked { ++index1; }
}
if (pProc.Parameters.Count > 0)
{
stringBuilder.Append(", ");
}
stringBuilder.Append("Sql.Transaction transaction){").Append(newLine).Append(newLine);
int index2 = 0;
while (index2 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index2];
stringBuilder.Append(str).Append(str).Append(str).Append("SqlParameter p").Append(index2.ToString()).Append(" = new SqlParameter(\"").Append(spParameter.Name).Append("\", SqlDbType.").Append(DbColumnFactory.GetSqlType(spParameter.ParamType).ToString()).Append(");").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Direction = ParameterDirection.").Append(spParameter.Direction.ToString()).Append(";").Append(newLine);
if (spParameter.Direction == ParameterDirection.Input || spParameter.Direction == ParameterDirection.InputOutput)
{
stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Value = ").Append(spParameter.Name).Append(";").Append(newLine);
}
stringBuilder.Append(newLine);
checked { ++index2; }
}
stringBuilder.Append(str).Append(str).Append(str).Append("Sql.IResult result = ExecuteProcedure(transaction");
int num = 0;
while (num < pProc.Parameters.Count)
{
stringBuilder.Append(", p").Append(num.ToString());
checked { ++num; }
}
stringBuilder.Append(");").Append(newLine).Append(newLine);
int index3 = 0;
while (index3 < pProc.Parameters.Count)
{
SpParameter spParameter = pProc.Parameters[index3];
if (spParameter.Direction == ParameterDirection.InputOutput || spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue)
{
stringBuilder.Append(str).Append(str).Append(str).Append(spParameter.Name).Append(" = (").Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(")").Append("p").Append(index3.ToString()).Append(".Value;").Append(newLine);
}
checked { ++index3; }
}
stringBuilder.Append(str).Append(str).Append(str).Append("return result;").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine).Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Row this[int pIndex, Sql.IResult pResult]{").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Row)pResult.GetRow(this, pIndex); }").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append("public sealed class Row : ").Append(typeof(Record).ToString()).Append(" {").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("private new Proc Tbl {").Append(newLine);
stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Proc)base.Tbl; }").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(newLine);
stringBuilder.Append(str).Append(str).Append("public Row() : base(Proc.Instance) {").Append(newLine);
stringBuilder.Append(str).Append(str).Append("}").Append(newLine);
stringBuilder.Append(str).Append("}").Append(newLine);
stringBuilder.Append("}");
return(stringBuilder.ToString());
}
