Пример #1
0
        /// <summary>
        /// 获取某个表的表结构
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Table GetTable(Connection conn, string tableName)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT A.TABLE_NAME,");
            builder.Append("       A.COLUMN_NAME,");
            builder.Append("       A.DATA_TYPE,");
            builder.Append("       A.DATA_LENGTH,");
            builder.Append("       A.DATA_PRECISION,");
            builder.Append("       A.NULLABLE,");
            builder.Append("       A.DATA_DEFAULT,");
            builder.Append("       A.CHAR_LENGTH,");
            builder.Append("       A.DATA_SCALE,");
            builder.Append("       B.COMMENTS,");
            builder.Append("       C.COMMENTS TABLECOMMENTS");
            builder.Append("  FROM USER_TAB_COLUMNS A");
            builder.Append("  LEFT OUTER JOIN user_col_comments B");
            builder.Append("    ON A.table_name = B.table_name");
            builder.Append("   and A.column_name = B.column_name");
            builder.Append("  LEFT OUTER JOIN user_tab_comments C");
            builder.Append("    ON A.table_name = C.table_name");
            builder.Append(" WHERE A.TABLE_NAME = '");
            builder.Append(tableName);
            builder.Append("'");
            builder.Append(" ORDER BY A.TABLE_NAME, A.COLUMN_ID");
            DataTable dataTable = new DataTable();

            using (OracleConnection connection = new OracleConnection(conn.ToString()))
            {
                try
                {
                    connection.Open();
                    new OracleDataAdapter(builder.ToString(), connection).Fill(dataTable);
                }
                catch (Exception exception)
                {
                    connection.Close();
                    connection.Dispose();
                    throw exception;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            if (dataTable.Rows.Count == 0)
            {
                return(null);
            }
            Table table2 = new Table();

            table2.Name     = dataTable.Rows[0]["TABLE_NAME"].ToString();
            table2.Comments = dataTable.Rows[0]["TABLECOMMENTS"].ToString();
            table2.Database = conn.Database;
            string str  = string.Empty;
            string str2 = string.Empty;

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ColumnSchema item = new ColumnSchema();
                item.Name     = dataTable.Rows[i]["COLUMN_NAME"].ToString();
                item.Property = dataTable.Rows[i]["COLUMN_NAME"].ToString().Replace("_", "");
                item.DataType = dataTable.Rows[i]["DATA_TYPE"].ToString();
                if (item.DataType.StartsWith("NUMBER") || item.DataType.StartsWith("TIMESTAMP"))
                {
                    str  = (dataTable.Rows[i]["DATA_PRECISION"].ToString() == "0") ? "" : dataTable.Rows[i]["DATA_PRECISION"].ToString();
                    str2 = (dataTable.Rows[i]["DATA_SCALE"].ToString() == "0") ? "" : dataTable.Rows[i]["DATA_SCALE"].ToString();
                    if (string.IsNullOrEmpty(str) && string.IsNullOrEmpty(str2))
                    {
                        item.Length = 0;
                    }
                    else
                    {
                        item.Length = Convert.ToInt32(str + str2);
                    }
                }
                else
                {
                    item.Length = Convert.ToInt32(dataTable.Rows[i]["CHAR_LENGTH"].ToString());
                }
                item.IsNullAble   = dataTable.Rows[i]["NULLABLE"].ToString() == "Y";
                item.DefaultValue = dataTable.Rows[i]["DATA_DEFAULT"].ToString();
                item.Comments     = dataTable.Rows[i]["COMMENTS"].ToString();
                table2.Columns.Add(item);
            }
            return(table2);
        }
Пример #2
0
        public Table GetTable(Connection conn, string tableName)
        {
            StringBuilder commandText = new StringBuilder();

            commandText.Append("SELECT ");
            commandText.Append("     TableName      = case when a.colorder=1 then d.name else '' end, ");
            commandText.Append("     TableDescription    = case when a.colorder=1 then isnull(f.value,'') else '' end, ");
            commandText.Append("     [Index] = a.colorder, ");
            commandText.Append("    [Name]    = a.name, ");
            commandText.Append("    IsIdentity      = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ");
            commandText.Append("    IsPrimaryKey      = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ");
            commandText.Append("                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, ");
            commandText.Append("    DataType      = b.name, ");
            commandText.Append("    Length= a.length, ");
            commandText.Append("    [Size]      = COLUMNPROPERTY(a.id,a.name,'PRECISION'), ");
            commandText.Append("    DecimalDigits = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), ");
            commandText.Append("    IsNullAble    = case when a.isnullable=1 then '√'else '' end, ");
            commandText.Append("    DefaultValue    = isnull(e.text,''), ");
            commandText.Append("    Description = isnull(g.[value],'') ");
            commandText.Append("FROM ");
            commandText.Append("    syscolumns a ");
            commandText.Append("left join ");
            commandText.Append("    systypes b ");
            commandText.Append("on ");
            commandText.Append("    a.xusertype=b.xusertype ");
            commandText.Append("inner join ");
            commandText.Append("    sysobjects d ");
            commandText.Append("on ");
            commandText.Append("    a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
            commandText.Append("left join ");
            commandText.Append("    syscomments e ");
            commandText.Append("on ");
            commandText.Append("    a.cdefault=e.id ");
            commandText.Append("left join ");
            commandText.Append("sys.extended_properties   g ");
            commandText.Append("on ");
            commandText.Append("    a.id=G.major_id and a.colid=g.minor_id  ");
            commandText.Append("left join ");
            commandText.Append("sys.extended_properties f ");
            commandText.Append("on ");
            commandText.Append("    d.id=f.major_id and f.minor_id=0 ");
            commandText.Append("where ");
            commandText.Append(" d.name='").Append(tableName).Append("'");//只查询指定表
            commandText.Append("order by ");
            commandText.Append("    a.id,a.colorder");

            DataTable dataTable = new DataTable();

            using (SqlConnection connection = new SqlConnection(conn.ToString()))
            {
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(commandText.ToString(), connection);
                    command.Fill(dataTable);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connection.Close();
                    connection.Dispose();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            if (dataTable.Rows.Count == 0)
            {
                return(null);
            }
            Table table = new Table();

            table.Name     = dataTable.Rows[0]["TableName"].ToString();
            table.Comments = dataTable.Rows[0]["TableDescription"].ToString();
            table.Database = conn.Database;
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ColumnSchema column = new ColumnSchema();
                column.IsIdentity    = dataTable.Rows[i]["IsIdentity"].ToString() == "√" ? true : false;
                column.IsPrimaryKey  = dataTable.Rows[i]["IsPrimaryKey"].ToString() == "√" ? true : false;
                column.Name          = dataTable.Rows[i]["Name"].ToString();
                column.Property      = dataTable.Rows[i]["Name"].ToString().Replace("_", "");
                column.DataType      = dataTable.Rows[i]["DataType"].ToString();
                column.Length        = Convert.ToInt64(dataTable.Rows[i]["Length"].ToString());
                column.Size          = Convert.ToInt32(dataTable.Rows[i]["Size"].ToString());
                column.DecimalDigits = Convert.ToInt32(dataTable.Rows[i]["DecimalDigits"].ToString());
                column.IsNullAble    = dataTable.Rows[i]["IsNullAble"].ToString() == "√" ? true : false;
                column.DefaultValue  = dataTable.Rows[i]["DefaultValue"].ToString();
                column.Comments      = dataTable.Rows[i]["Description"].ToString();
                table.Columns.Add(column);
            }

            return(table);
        }
Пример #3
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Table GetTable(Connection conn, string tableName)
        {
            StringBuilder sb = new StringBuilder(524);

            sb.AppendLine(@"SELECT C.TABLE_NAME,");
            sb.AppendLine(@"       C.COLUMN_NAME,");
            sb.AppendLine(@"       C.DATA_TYPE,");
            sb.AppendLine(@"       C.CHARACTER_MAXIMUM_LENGTH DATA_LENGTH,");
            sb.AppendLine(@"       C.NUMERIC_PRECISION        DATA_PRECISION,");
            sb.AppendLine(@"       C.IS_NULLABLE              NULLABLE,");
            sb.AppendLine(@"       C.COLUMN_DEFAULT           DATA_DEFAULT,");
            sb.AppendLine(@"       C.COLUMN_TYPE,");
            sb.AppendLine(@"       C.COLUMN_COMMENT,");
            sb.AppendLine(@"       T.TABLE_COMMENT");
            sb.AppendLine(@"  FROM INFORMATION_SCHEMA.COLUMNS C");
            sb.AppendLine(@"  LEFT JOIN INFORMATION_SCHEMA.TABLES T");
            sb.AppendLine(@"    ON C.TABLE_NAME = T.TABLE_NAME");
            sb.AppendLine(@" WHERE C.TABLE_NAME = ");
            sb.AppendFormat("'{0}'", tableName);
            sb.AppendLine(string.Format(@" AND C.TABLE_SCHEMA = '{0}' ", conn.Database));
            sb.AppendLine(string.Format(@" AND T.TABLE_SCHEMA =  '{0}'", conn.Database));
            sb.AppendLine(@" ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION");

            DataTable dataTable = new DataTable();

            using (MySqlConnection connection = new MySqlConnection(conn.ToString()))
            {
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
                    command.Fill(dataTable);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connection.Close();
                    connection.Dispose();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            if (dataTable.Rows.Count == 0)
            {
                return(null);
            }
            Table table = new Table();

            table.Name     = dataTable.Rows[0]["TABLE_NAME"].ToString();
            table.Comments = dataTable.Rows[0]["TABLE_COMMENT"].ToString();
            table.Database = conn.Database;
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ColumnSchema column = new ColumnSchema();
                column.Name     = dataTable.Rows[i]["COLUMN_NAME"].ToString();
                column.Property = dataTable.Rows[i]["COLUMN_NAME"].ToString().Replace("_", "");
                column.DataType = dataTable.Rows[i]["DATA_TYPE"].ToString();

                if (column.DataType.Equals("varchar") || column.DataType.Equals("char"))
                {
                    column.DataLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
                    column.CharLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
                }
                else if (column.DataType.Equals("int"))
                {
                    string columnType  = dataTable.Rows[i]["COLUMN_TYPE"].ToString();
                    int    leftBracket = columnType.IndexOf("(");
                    if (leftBracket > 0)
                    {
                        string columnLeft = columnType.Substring(leftBracket);
                        columnLeft = columnLeft.TrimStart(new char[] { '(', ')' }).TrimEnd(new char[] { '(', ')' });
                        int iColumnLen = int.Parse(columnLeft);
                        if (iColumnLen > 8)
                        {
                            column.DataType = "bigint";
                        }
                        column.DataLength = iColumnLen;
                    }
                }

                //column.DataLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
                //column.CharLength = Convert.ToInt32(dataTable.Rows[i]["CHAR_LENGTH"].ToString());
                column.IsNullAble   = dataTable.Rows[i]["NULLABLE"].ToString() == "YES" ? true : false;
                column.DefaultValue = dataTable.Rows[i]["DATA_DEFAULT"].ToString() == "NULL" ? "" : dataTable.Rows[i]["DATA_DEFAULT"].ToString();
                column.Comments     = dataTable.Rows[i]["COLUMN_COMMENT"].ToString();
                table.Columns.Add(column);
            }

            return(table);
        }