コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }