public static List <StoredProcedure> GetStoredProcedures(DbConnection dbConnection)
{
List <StoredProcedure> list = new List <StoredProcedure>();
using (DbCommand command = Transaction.CreateCommand(dbConnection, (Transaction)null))
{
command.CommandText = "SELECT SCHEMA_NAME(schema_id) AS schema_name, o.name AS object_name, p.parameter_id, p.name AS parameter_name,TYPE_NAME(p.user_type_id) AS parameter_type, p.is_output as is_output FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE type_desc = 'SQL_STORED_PROCEDURE' ORDER BY schema_name, object_name, p.parameter_id;";
command.Connection = dbConnection;
using (DbDataReader dbDataReader = command.ExecuteReader())
{
Dictionary <string, StoredProcedure> dictionary = new Dictionary <string, StoredProcedure>();
while (dbDataReader.Read())
{
string string1 = dbDataReader.GetString(0);
string string2 = dbDataReader.GetString(1);
string key = string1 + "*" + string2;
StoredProcedure storedProcedure;
if (!dictionary.ContainsKey(key))
{
storedProcedure = new StoredProcedure(string1, string2);
dictionary.Add(key, storedProcedure);
list.Add(storedProcedure);
}
else
{
storedProcedure = dictionary[key];
}
int int32 = dbDataReader.GetInt32(2);
string string3 = dbDataReader.GetString(3);
string string4 = dbDataReader.GetString(4);
bool boolean = dbDataReader.GetBoolean(5);
ParameterDirection pDirection = ParameterDirection.Input;
if (boolean)
{
pDirection = ParameterDirection.InputOutput;
}
storedProcedure.AddParameter(new SpParameter(int32, string3, MssqlSchemaProvider.GetDataType(string4), pDirection));
}
}
}
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);
}
