Ejemplo n.º 1
0
        public IDbCommand BuildCommand(string cmdText, DbParameterCollection parameters, CommandType cmdType, int cmdTimeout = 30)
        {
            IDbCommand cmd = new MySqlCommand();
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = cmdTimeout;
            foreach (DbParameter par in parameters)
                cmd.Parameters.Add(ConvertToNativeParameter(par));

            return cmd;
        }
Ejemplo n.º 2
0
        private DataTable FetchParentRelations(string tableName)
        {
            //SELECT fk.CONSTRAINT_NAME, fk.COLUMN_NAME AS CHILD_COLUMN, fk.REFERENCED_TABLE_NAME AS PARENT_TABLE, fk.REFERENCED_COLUMN_NAME AS PARENT_COLUMN
            //FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as fk
            //WHERE fk.REFERENCED_TABLE_NAME is not null
            //  and fk.TABLE_NAME = 'employeeterritories'
            //  AND fk.TABLE_SCHEMA = 'northwind'

            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT fk.CONSTRAINT_NAME, fk.COLUMN_NAME AS CHILD_COLUMN, fk.REFERENCED_TABLE_NAME AS PARENT_TABLE, fk.REFERENCED_COLUMN_NAME AS PARENT_COLUMN ");
            select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as fk  ");
            select.AppendLine("WHERE fk.REFERENCED_TABLE_NAME is not null ");
            select.AppendLine("  AND fk.TABLE_NAME = @tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            if (!string.IsNullOrEmpty(this.Catalog))
            {
                select.AppendLine("  AND fk.TABLE_SCHEMA = @schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            else
            {
                select.AppendLine("AND fk.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
            }

            DataTable parentData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            RemoveExcludedFieldsData(tableName, "COLUMN_NAME", parentData);
            return parentData;
        }
Ejemplo n.º 3
0
        private DataTable FetchConstraintData(string tableName)
        {
            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, CONSTRAINT_TYPE ");
            select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ");
            select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON  ");
            select.AppendLine("	INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME ");
            select.AppendLine(" AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ");
            select.AppendLine("WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            if (!string.IsNullOrEmpty(this.Catalog))
            {
                select.AppendLine("AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA = @schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            else
            {
                select.AppendLine("AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
            }

            DataTable constraintData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            return constraintData;
        }
Ejemplo n.º 4
0
        private DataTable FetchColumnData(string tableName)
        {
            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT INFORMATION_SCHEMA.COLUMNS.*, 0 AS IsComputed, CHARACTER_MAXIMUM_LENGTH AS ColumnLength, ");
            select.AppendLine("case extra when 'auto_increment' then 1 else 0 end AS IsIdentity, ");
            select.AppendLine("0 AS IsRowGuidColumn, 0 AS IsPrimaryKey, 0 AS IsForeignKey, 0 AS HasUniqueConstraint, ");
            select.AppendLine("null as SequenceName ");
            select.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS ");
            select.AppendLine("WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            if (!string.IsNullOrEmpty(this.Catalog))
            {
                select.AppendLine("AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = @schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            else
            {
                select.AppendLine("AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
            }
            select.AppendLine("ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ");

            DataTable fieldsData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            RemoveExcludedFieldsData(tableName, "COLUMN_NAME", fieldsData);
            return fieldsData;
        }
Ejemplo n.º 5
0
        /// <summary>Fetches list with basic table and view info.</summary>
        public IEnumerable<SqlObjectHeader> FetchTablesAndViews()
        {
            DbParameterCollection parameters = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT TABLE_NAME as ObjectName, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 0 WHEN 'VIEW' THEN 1 END AS ObjectType ");
            select.AppendLine("FROM INFORMATION_SCHEMA.TABLES ");
            if (!string.IsNullOrEmpty(this.Catalog))
            {
                select.AppendLine("WHERE TABLE_SCHEMA = @schema ");
                parameters.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            else
            {
                select.AppendLine("WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
            }
            select.AppendLine("ORDER BY TABLE_TYPE ASC, TABLE_NAME ASC ");

            DataTable tablesAndViews = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), parameters, CommandType.Text, null, 30);
            List<SqlObjectHeader> list = new List<SqlObjectHeader>();
            foreach (DataRow row in tablesAndViews.Rows)
                list.Add(new SqlObjectHeader((string)row["ObjectName"], (SqlObjectType)Convert.ToInt32(row["ObjectType"])));

            return list;
        }