コード例 #1
0
        /// <summary>取得数据库名称</summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            GenericSqlCommand command = new GenericSqlCommand(connectionString, "SQLite");

            string commandText = string.Format("SHOW FULL FIELDS FROM {1} FROM {0}", databaseName, tableName);

            var table = command.ExecuteQueryForDataTable(commandText);

            foreach (DataRow row in table.Rows)
            {
                DataColumnSchema item = new DataColumnSchema();

                // 名称
                item.Name = row["Field"].ToString();
                // 数据类型
                item.Type = SetDataType(row["Type"].ToString());
                // 原生数据类型
                item.NativeType = row["Type"].ToString();
                // 是否允许为空
                item.Nullable = (row["Null"].ToString() == "NO") ? false : true;
                // 默认值
                item.DefaultValue = row["Default"].ToString();
                // 注释
                item.Description = row["Comment"].ToString();

                list.Add(item);
            }

            return(list);
        }
コード例 #2
0
        /// <summary>
        /// 取得数据库名称
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetPrimaryKeyColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            GenericSqlCommand command = new GenericSqlCommand(connectionString, "OracleClient");

            string commandText = string.Format(@"
select cu.* 
  from user_cons_columns cu, user_constraints au 
 where cu.constraint_name = au.constraint_name 
   and au.constraint_type = 'P' 
   and au.table_name = '{0}' ", tableName);

            var table = command.ExecuteQueryForDataTable(commandText);

            foreach (DataRow row in table.Rows)
            {
                DataColumnSchema item = new DataColumnSchema();

                item.Name = row["column_name"].ToString();

                list.Add(item);
            }

            return(list);
        }
コード例 #3
0
        /// <summary>
        /// 取得数据库名称
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetPrimaryKeyColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection  = conn;
                cmd.CommandText = @"
SELECT  
	cols.COLUMN_NAME AS Name
FROM  
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE cols  
WHERE  
	cols.TABLE_CATALOG = @DatabaseName  
	AND cols.TABLE_SCHEMA = @OwnerName  
	AND cols.TABLE_NAME = @TableName  
ORDER BY  
	cols.ORDINAL_POSITION;
";
                SqlParameter param;

                param       = new SqlParameter("@DatabaseName", SqlDbType.NVarChar);
                param.Value = databaseName;
                cmd.Parameters.Add(param);

                param       = new SqlParameter("@OwnerName", SqlDbType.NVarChar);
                param.Value = ownerName;
                cmd.Parameters.Add(param);

                param       = new SqlParameter("@TableName", SqlDbType.NVarChar);
                param.Value = tableName;
                cmd.Parameters.Add(param);

                conn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        DataColumnSchema item = new DataColumnSchema();

                        item.Name = (dr["Name"] == DBNull.Value) ? string.Empty : (string)dr["Name"];

                        list.Add(item);
                    }
                }
                conn.Close();
            }

            return(list);
        }
コード例 #4
0
        /// <summary>
        /// 取得数据库名称
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetNoPrimaryKeyColumns(DataTableSchema table)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            for (int i = 0; i < table.Columns.Count; i++)
            {
                if (!table.Columns[i].PrimaryKey)
                {
                    list.Add(table.Columns[i]);
                }
            }
            return(list);
        }
コード例 #5
0
        /// <summary>
        /// 取得数据库名称
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            GenericSqlCommand command = new GenericSqlCommand(connectionString, "OracleClient");

            string commandText = string.Format(@"
select column_id, 
       column_name, 
       data_type, 
       data_length, 
       data_precision, 
       data_scale, 
       nullable,
       data_default 
  from user_tab_columns 
 where table_name = '{0}' 
 order by column_id", tableName);

            var table = command.ExecuteQueryForDataTable(commandText);

            foreach (DataRow row in table.Rows)
            {
                DataColumnSchema item = new DataColumnSchema();

                item.Name = row["column_name"].ToString().ToLower();

                item.Type = SetDataType(row["data_type"].ToString());

                item.Nullable = (row["nullable"].ToString() == "N") ? false : true;

                switch (item.Type)
                {
                case DbType.String:
                    item.Length = (row["data_length"] == DBNull.Value) ? 0 : Convert.ToInt32(row["data_length"].ToString());
                    break;

                case DbType.Decimal:
                    // item.Precision = (dr["Precision"] == DBNull.Value) ? (byte)0 : (byte)dr["Precision"];
                    // item.Scale = (dr["Scale"] == DBNull.Value) ? 0 : (int)dr["Scale"];
                    break;

                default:
                    break;
                }

                list.Add(item);
            }

            return(list);
        }
コード例 #6
0
        /// <summary>查询数据库中表的外键字段信息</summary>
        /// <param name="databaseName">数据库</param>
        /// <param name="ownerName">所有者</param>
        /// <param name="tableName">表名</param>
        /// <returns>外键字段信息集合</returns>
        public DataColumnSchemaCollection GetForeignKeyColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            GenericSqlCommand command = new GenericSqlCommand(connectionString, "SQLite");

            string commandText = string.Format("SHOW FULL FIELDS FROM {1} FROM {0} WHERE `Key`='MUL'", databaseName, tableName);

            var table = command.ExecuteQueryForDataTable(commandText);

            foreach (DataRow row in table.Rows)
            {
                DataColumnSchema item = new DataColumnSchema();

                item.Name = row["Field"].ToString();

                list.Add(item);
            }

            return(list);
        }
コード例 #7
0
        /// <summary>
        /// 取得数据库名称
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <returns>数据库名称</returns>
        public DataColumnSchemaCollection GetColumns(string databaseName, string ownerName, string tableName)
        {
            DataColumnSchemaCollection list = new DataColumnSchemaCollection();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection  = conn;
                cmd.CommandText = @"
SELECT  
	cols.COLUMN_NAME AS Name,  
    CASE  
        WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME  
        ELSE cols.DATA_TYPE  
    END AS Type,
    cols.DATA_TYPE AS DataType,
    CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int) AS Length,  
    cols.NUMERIC_PRECISION AS Precision,  
    cols.NUMERIC_SCALE AS Scale,  
    cols.IS_NULLABLE AS IsNull,  
    cols.COLUMN_DEFAULT,  
    COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsIdentity') AS IS_IDENTITY, 
    COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsRowGuidCol') AS IS_ROW_GUID_COL,  
    COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,  
    COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC,  
    CASE 
        WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) 
        then CONVERT(nvarchar(40), ident_seed(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']')) 
        else null end AS IDENTITY_SEED,  
    CASE 
        WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) 
        then CONVERT(nvarchar(40), ident_incr(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']')) else null 
    end AS IDENTITY_INCREMENT,
    NULL AS COMPUTED_DEFINITION,
    NULL AS [collation]  
FROM  
    INFORMATION_SCHEMA.COLUMNS cols  
WHERE  
	cols.TABLE_CATALOG = @DatabaseName  
	AND cols.TABLE_SCHEMA = @OwnerName  
	AND cols.TABLE_NAME = @TableName  
ORDER BY  
	cols.ORDINAL_POSITION;
";
                SqlParameter param;

                param       = new SqlParameter("@DatabaseName", SqlDbType.NVarChar);
                param.Value = databaseName;
                cmd.Parameters.Add(param);

                param       = new SqlParameter("@OwnerName", SqlDbType.NVarChar);
                param.Value = ownerName;
                cmd.Parameters.Add(param);

                param       = new SqlParameter("@TableName", SqlDbType.NVarChar);
                param.Value = tableName;
                cmd.Parameters.Add(param);

                conn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        DataColumnSchema item = new DataColumnSchema();

                        item.Name = (dr["Name"] == DBNull.Value) ? string.Empty : (string)dr["Name"];

                        item.Type = SetDataType((string)dr["Type"]);

                        item.Nullable = ((string)dr["IsNull"] == "NO") ? false : true;

                        switch (item.Type)
                        {
                        case DbType.String:
                            item.Length = (dr["Length"] == DBNull.Value) ? 0 : (int)dr["Length"];
                            break;

                        case DbType.Decimal:
                            item.Precision = (dr["Precision"] == DBNull.Value) ? (byte)0 : (byte)dr["Precision"];
                            item.Scale     = (dr["Scale"] == DBNull.Value) ? 0 : (int)dr["Scale"];
                            break;

                        default:
                            break;
                        }

                        list.Add(item);
                    }
                }

                conn.Close();
            }

            return(list);
        }