예제 #1
0
        public ColumnDetails GetTableDetails(string selectedTableName)
        {
            var columnDetails = new ColumnDetails();
            var conn = new SqlConnection(connectionStr);
            conn.Open();
            using (conn)
            {
                using (var tableDetailsCommand = conn.CreateCommand())
                {
                    tableDetailsCommand.CommandText = "select column_name, data_type, character_maximum_length, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, IS_NULLABLE from information_schema.columns where table_name = '" + selectedTableName + "'";
                    using (var sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
                    {
                        if (sqlDataReader != null)
                        {
                            while (sqlDataReader.Read())
                            {
                                var columnName = sqlDataReader.GetString(0);
                                var dataType = sqlDataReader.GetString(1);
                                int dataLength = 0;
                                int dataPrecision = 0;
                                int dataScale = 0;
                                bool isNullable = false;
                                try
                                {
                                    dataLength = sqlDataReader.GetInt32(2);
                                    dataPrecision = sqlDataReader.GetInt32(3);
                                    dataScale = sqlDataReader.GetInt32(4);
                                    isNullable = sqlDataReader.GetBoolean(5);
                                }catch (Exception)
                                {

                                }

                                columnDetails.Add(new ColumnDetail(columnName, dataType, dataLength, dataPrecision, dataScale, isNullable));
                            }
                        }
                    }
                }

                using (var constraintCommand = conn.CreateCommand())
                {
                    constraintCommand.CommandText = "select constraint_name from information_schema.TABLE_CONSTRAINTS where table_name = '" + selectedTableName + "' and constraint_type = 'PRIMARY KEY'";
                    var value = constraintCommand.ExecuteScalar();
                    if (value != null)
                    {
                        var constraintName = (string)value;
                        using (var pkColumnCommand = conn.CreateCommand())
                        {
                            pkColumnCommand.CommandText = "select column_name from information_schema.CONSTRAINT_COLUMN_USAGE where table_name = '" + selectedTableName + "' and constraint_name = '" + constraintName + "'";
                            var colName = pkColumnCommand.ExecuteScalar();
                            if (colName != null)
                            {
                                var pkColumnName = (string)colName;
                                var columnDetail = columnDetails.Find(detail => detail.ColumnName.Equals(pkColumnName));
                                columnDetail.IsPrimaryKey = true;
                            }
                        }
                    }
                }
            }
            columnDetails.Sort((x, y) => x.ColumnName.CompareTo(y.ColumnName));
            return columnDetails;
        }
예제 #2
0
        public ColumnDetails GetTableDetails(string selectedTableName)
        {
            var columnDetails = new ColumnDetails();
            var conn = new OracleConnection(connectionStr);
            conn.Open();
            using (conn)
            {
                using (var tableCommand = conn.CreateCommand())
                {
                    tableCommand.CommandText = "select column_name, data_type, data_length, data_precision, data_scale, nullable from user_tab_cols where table_name = '" + selectedTableName + "'";
                    using (var oracleDataReader = tableCommand.ExecuteReader(CommandBehavior.Default))
                    {
                        while (oracleDataReader.Read())
                        {
                            var columnName = oracleDataReader.GetString(0);
                            var dataType = oracleDataReader.GetString(1);
                            int dataLength = 0;
                            int dataPrecision = 0;
                            int dataScale = 0;
                            string isNullableStr = "N";
                            try
                            {
                                dataLength = oracleDataReader.GetInt32(2);
                                dataPrecision = oracleDataReader.GetInt32(3);
                                dataScale = oracleDataReader.GetInt32(4);
                                isNullableStr = oracleDataReader.GetString(5);
                            }
                            catch (InvalidOperationException)
                            {

                            }
                            bool isNullable = false;
                            if (isNullableStr.Equals("Y", StringComparison.CurrentCultureIgnoreCase))
                            {
                                isNullable = true;
                            }
                            columnDetails.Add(new ColumnDetail(columnName, dataType, dataLength, dataPrecision, dataScale, isNullable));
                        }
                    }
                }
                using(var constraintCommand = conn.CreateCommand())
                {
                    constraintCommand.CommandText = "select constraint_name from user_constraints where table_name = '" + selectedTableName + "' and constraint_type = 'P'";
                    var value = constraintCommand.ExecuteOracleScalar();
                    if (value != null)
                    {
                        var constraintName = (OracleString) value;
                        using(var pkColumnCommand = conn.CreateCommand())
                        {
                            pkColumnCommand.CommandText = "select column_name from user_cons_columns where table_name = '" + selectedTableName+ "' and constraint_name = '"+ constraintName.Value +"'";
                            var colNames = pkColumnCommand.ExecuteReader();
                            //var colName = pkColumnCommand.ExecuteOracleScalar();

                            if (colNames.HasRows)
                            {

                                while (colNames.Read())
                                {
                                    var pkColumnName = (OracleString)colNames.GetOracleValue(0);
                                    var columnDetail = columnDetails.Find(detail => detail.ColumnName.Equals(pkColumnName.Value));
                                    columnDetail.IsPrimaryKey = true;
                                }
                            }
                        }
                    }
                }
            }
            columnDetails.Sort((x, y) => x.ColumnName.CompareTo(y.ColumnName));
            return columnDetails;
        }