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);
}
