Type IProvider.GetColumnType(DataColumnSchema dataColumnSchema) { var dbType = (DbType)dataColumnSchema.ProviderType; Type type; switch (dbType) { case DbType.DateTime: type = typeof(DateTime); break; case DbType.String: type = typeof(string); break; case DbType.Int32: type = typeof(int); break; default: type = typeof(object); break; } return(type); }
/// <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); }
/// <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); }
public string GetColumnResult(DataColumnSchema column) { string param; param = "(dr[\"" + column.Name + "\"] == DBNull.Value) ? " + this.GetDefaultValue(column.Type) + ":" + "(" + this.ConvertType(column.Type) + ")dr[\"" + column.Name + "\"]"; return(param); }
/// <summary> /// Constructs the column information with the details /// </summary> /// <param name="table">The table schema</param> /// <param name="column">The column schema</param> public QueryColumnInfo ( DataTableSchema table, DataColumnSchema column ) { Validate.IsNotNull(table); Validate.IsNotNull(column); this.Table = table; this.Column = column; }
/// <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); }
public override void Init(string taskName, CodeBuilderConfiguration configuration) { provider = (X3Platform.CodeBuilder.Data.IDbSchemaProvider)Assembly.Load(configuration.DatabaseProvider.Assembly).CreateInstance(configuration.DatabaseProvider.ClassName, false, BindingFlags.Default, null, new object[] { configuration.DatabaseProvider.ConnectionString }, null, null); database = new X3Platform.CodeBuilder.Data.DatabaseSchema(); database.Name = provider.GetDatabaseName(); database.OwnerName = configuration.DatabaseProvider.OwnerName; string dataTables = configuration.Tasks[taskName].Properties["DataTables"].Value; string[] tableNames = dataTables.Split(','); this.tables = new List <DataTableSchema>(); // List<DataTableSchema> foreach (string tableName in tableNames) { DataTableSchema table = provider.GetTable(database.Name, database.OwnerName, tableName); this.tables.Add(table); } // 补全 for (int i = 0; i < tables.Count; i++) { DataTableSchema table = this.tables[i]; Dictionary <string, int> dict = GetDisplayLength(table); for (int j = 0; j < table.Columns.Count; j++) { DataColumnSchema column = table.Columns[j]; column.Name = Padding(column.Name, dict["Name"]); column.NativeType = Padding(column.NativeType, dict["NativeType"]); column.Description = Padding(column.Description, dict["Description"]); } } //设置 输出文件 if (configuration.Tasks[taskName].Properties["File"] == null) { configuration.Tasks[taskName].Properties.Add(new TaskProperty("File", taskName + ".md")); } else if (string.IsNullOrEmpty(configuration.Tasks[taskName].Properties["File"].Value)) { configuration.Tasks[taskName].Properties["File"].Value = taskName + ".md"; } }
/// <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); }
/// <summary> /// Constructs the query cell with the data /// </summary> /// <param name="column">The column schema</param> /// <param name="value">The cell value</param> public QueryCell ( DataColumnSchema column, object value ) { Validate.IsNotNull(column); this.Column = column; this.Value = value; this.FormattingType = column.ValueType.GetFormattingType ( value ); }
/// <summary> /// Resolves the queries columns from the output type /// </summary> private void ResolveColumns() { _columns = new List <QueryColumnInfo>(); var connectionString = GetConnectionString(); var procedureName = this.StoredProcedureName; using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand(procedureName, connection)) { command.CommandType = CommandType.StoredProcedure; var reader = command.ExecuteReader(); var schema = reader.GetSchemaTable(); foreach (DataColumn column in schema.Columns) { var columnName = column.ColumnName; var valueType = column.DataType; var tableSchema = this.DataSource.GetSchemaTable ( column.Table.TableName ); var columnSchema = new DataColumnSchema ( columnName, valueType ); _columns.Add ( new QueryColumnInfo ( tableSchema, columnSchema ) ); } } } }
/// <summary> /// Maps an entities mapping properties to column schema's /// </summary> /// <param name="map">The entity map</param> /// <returns>An array of column schema's</returns> private DataColumnSchema[] MapTableProperties ( IEntityMap map ) { var columnSchemas = new List <DataColumnSchema>(); foreach (var property in map.Properties) { var column = new DataColumnSchema ( property.ColumnName, property.Type ); columnSchemas.Add(column); } return(columnSchemas.ToArray()); }
/// <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); }
/// <summary> /// Defines a single query column /// </summary> /// <param name="tableName">The table name</param> /// <param name="columnSchema">The column schema</param> protected void DefineColumn ( string tableName, DataColumnSchema columnSchema ) { Validate.IsNotEmpty(tableName); Validate.IsNotNull(columnSchema); var tableSchema = this.DataSource.GetSchemaTable ( tableName ); var columnName = columnSchema.Name; var isDefined = _columns.Any ( info => info.Column.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase) ); if (isDefined) { throw new InvalidOperationException ( $"A column named '{columnName}' has already been defined." ); } _columns.Add ( new QueryColumnInfo ( tableSchema, columnSchema ) ); }
/// <summary> /// Defines a single query column /// </summary> /// <param name="tableName">The table name</param> /// <param name="columnName">The column name</param> /// <param name="valueType">The column value type</param> protected void DefineColumn ( string tableName, string columnName, Type valueType ) { Validate.IsNotEmpty(tableName); Validate.IsNotEmpty(columnName); Validate.IsNotNull(valueType); var columnSchema = new DataColumnSchema ( columnName, valueType ); DefineColumn ( tableName, columnSchema ); }
private Dictionary <string, int> GetDisplayLength(DataTableSchema table) { Dictionary <string, int> dict = new Dictionary <string, int>() { { "Name", 0 }, { "NativeType", 0 }, { "Description", 0 } }; int maxlength = 0; for (int j = 0; j < table.Columns.Count; j++) { DataColumnSchema column = table.Columns[j]; maxlength = GetTextLength(column.Name); if (dict["Name"] < maxlength) { dict["Name"] = maxlength; } maxlength = GetTextLength(column.NativeType); if (dict["NativeType"] < maxlength) { dict["NativeType"] = maxlength; } maxlength = GetTextLength(column.Description); if (dict["Description"] < maxlength) { dict["Description"] = maxlength; } } return(dict); }
/// <summary> /// Populates the table schema using a database context /// </summary> /// <param name="context">The database context</param> private void PopulateSchema ( DbContext context ) { try { var entityMaps = context.Db(); var tableSchemas = new List <DataTableSchema>(); foreach (var map in entityMaps) { var columnSchemas = MapTableProperties(map); var table = new DataTableSchema ( map.TableName, columnSchemas ); // Set the primary key details if (map.Pks != null) { var pkColumns = new List <DataColumnSchema>(); foreach (var property in map.Pks) { var column = new DataColumnSchema ( property.ColumnName, property.Type ); pkColumns.Add(column); } table = table.WithPrimaryKey ( pkColumns.ToArray() ); } // Set the foreign key details if (map.Fks != null) { var foreignKeys = new List <DataForeignKey>(); foreach (var property in map.Fks) { var referencedProperty = property.FkTargetColumn; var fkMap = referencedProperty.EntityMap; var fkTableColumns = MapTableProperties(map); var key = new DataForeignKey ( new DataColumnSchema[] { new DataColumnSchema ( property.ColumnName, property.Type ) }, new DataTableSchema ( fkMap.TableName, fkTableColumns ), new DataColumnSchema[] { new DataColumnSchema ( referencedProperty.ColumnName, referencedProperty.Type ) } ); foreignKeys.Add(key); } table = table.WithForeignKeys ( foreignKeys.ToArray() ); } tableSchemas.Add(table); } _schema = tableSchemas.ToArray(); this.DateSchemaResolved = DateTime.UtcNow; } catch (Exception ex) { _schema = new DataTableSchema[] { }; MarkSchemaAsUnresolvable ( ex.Message ); } }
/// <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); }