public interface IDbSchema
{
IDbTableSchema[] GetSchema();
}
public interface IDbTableSchema
{
string Name { get; }
IDbColumnSchema[] Columns { get; }
}
public interface IDbColumnSchema
{
int Ordinal { get; }
string Name { get; }
bool IsNullable { get; }
bool IsPrimaryKey { get; }
Type FieldType { get; }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Rocky.Data;
namespace Rocky.CodeBuilder
{
public class DbSchema : IDbSchema
{
#region Properties
protected DbFactory factory;
public DbFactory Factory { get { return factory; } }
#endregion
#region Methods
public DbSchema(DbFactory factory)
{
this.factory = factory;
}
#region old
[Obsolete("Old", true)]
public DataSet GetSchema(string Comments)
{
var connstr = "Provider=SQLOLEDB;" + factory.ConnectionString;
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connstr))
using (OleDbDataAdapter da = new OleDbDataAdapter(conn.CreateCommand()))
{
conn.Open();
DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
for (int i = 0; i < tables.Rows.Count; i++)
{
string sql = "SELECT * FROM [" + tables.Rows[i]["TABLE_NAME"].ToString() + "] WHERE 1=-1";
da.SelectCommand.CommandText = sql;
DataTable dt = new DataTable(tables.Rows[i]["TABLE_NAME"].ToString());
//自定义属性 ClassName ClassComments
dt.ExtendedProperties["ClassName"] = dt.TableName + "Entity";
dt.ExtendedProperties["Comments"] = Comments;
dt.ExtendedProperties["IsSaveToMemory"] = false;
da.FillSchema(dt, SchemaType.Source);
ds.Tables.Add(dt);
//标识主键
DataColumn[] columns = dt.PrimaryKey;
for (int j = 0; j < columns.Length; j++)
{
columns[j].ExtendedProperties.Add("Key", "primary");
}
}
}
return ds;
}
#endregion
public virtual DbTableSchema[] GetSchema()
{
var conn = factory.CreateConnection();
conn.Open();
DataTable dt = conn.GetSchema("tables");
DataTable dc = conn.GetSchema("columns");
conn.Close();
List<DbTableSchema> tables = new List<DbTableSchema>();
Database db = new Database(factory);
dt.DefaultView.Sort = "TABLE_NAME ASC";
foreach (DataRowView tRow in dt.DefaultView)
{
if (tRow["TABLE_TYPE"].ToString() != "TABLE")
{
continue;
}
DbTableSchema table = new DbTableSchema();
table.Name = tRow["TABLE_NAME"].ToString();
List<DbColumnSchema> columns = new List<DbColumnSchema>();
using (var dr = db.ExecuteStoredProcReader("SELECT TOP 1 * FROM [" + table.Name + "]"))
{
for (int i = 0; i < dr.FieldCount; i++)
{
DbColumnSchema column = new DbColumnSchema(i);
column.Name = dr.GetName(i);
column.FieldType = dr.GetFieldType(i);
foreach (DataRow cRow in dc.Rows)
{
if (String.Compare(cRow["COLUMN_NAME"].ToString(), column.Name, true) == 0)
{
column.IsNullable = (bool)cRow["IS_NULLABLE"];
}
}
if (i == 0)
{
column.IsPrimaryKey = column.FieldType == typeof(int);
}
columns.Add(column);
}
}
table.Columns = columns.ToArray();
tables.Add(table);
}
return tables.ToArray();
}
#endregion
#region IDbSchema 成员
IDbTableSchema[] IDbSchema.GetSchema()
{
return GetSchema();
}
#endregion
}
#region DbTableSchema
public class DbTableSchema : IDbTableSchema
{
private string name;
protected Array columns;
public string Name
{
get { return name; }
set { name = CoreHelper.ToTitleUpperCase(value); }
}
public DbColumnSchema[] Columns
{
get { return this.DbColumns; }
set { this.DbColumns = value; }
}
protected virtual DbColumnSchema[] DbColumns
{
get { return (DbColumnSchema[])columns; }
set { columns = value ?? new DbColumnSchema[0]; }
}
IDbColumnSchema[] IDbTableSchema.Columns
{
get { return this.Columns; }
}
}
public class DbColumnSchema : IDbColumnSchema
{
private string name, prefixName, lowerName;
public int Ordinal { get; private set; }
public string Name
{
get { return name; }
set
{
name = CoreHelper.ToTitleUpperCase(value);
prefixName = "_" + name;
lowerName = name.ToLower();
}
}
public string PrefixName
{
get { return prefixName; }
}
public string LowerName
{
get { return lowerName; }
}
public bool IsNullable { get; set; }
public bool IsPrimaryKey { get; set; }
public Type FieldType { get; set; }
public DbColumnSchema(int ordinal)
{
this.Ordinal = ordinal;
this.IsNullable = true;
}
}
#endregion
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;
using System.Windows.Forms;
using System.IO;
using Rocky.Data;
namespace Rocky.CodeBuilder
{
public class SqlSchema : DbSchema
{
#region SqlToCShap
private static Type SqlToCShap(string sqlType)
{
Type type;
switch (sqlType.ToLower())
{
case "int":
type = typeof(int);
break;
case "bigint":
type = typeof(long);
break;
case "binary":
case "image":
case "varbinary":
type = typeof(byte[]);
break;
case "bit":
type = typeof(bool);
break;
case "char":
case "nchar":
type = typeof(char);
break;
case "datetime":
case "smalldatetime":
case "timestamp":
type = typeof(DateTime);
break;
case "decimal":
case "money":
case "numeric":
case "smallmoney":
type = typeof(decimal);
break;
case "float":
type = typeof(double);
break;
case "real":
type = typeof(float);
break;
case "smallint":
type = typeof(short);
break;
case "tinyint":
type = typeof(byte);
break;
case "uniqueidentifier":
type = typeof(Guid);
break;
case "Variant":
type = typeof(object);
break;
case "varchar":
case "text":
case "nvarchar":
case "ntext":
default:
type = typeof(string);
break;
}
return type;
}
#endregion
#region Fields
#region sqlGetTables
private static string sqlGetTables = @"
SELECT
TableId = O.object_id,
TableName = O.name,
TableDesc = PTB.[value]
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
AND PTB.[name] = 'MS_Description'
WHERE C.column_id=1
ORDER BY TableName
";
#endregion
#region sqlGetColumns
private static string sqlGetColumns = @"
SELECT
TableName = CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc = ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id = C.column_id,
ColumnName = C.name,
ColumnDesc = ISNULL(PFD.[value],N''),
[Type] = T.name,
Length = C.max_length,
[Precision] = C.precision,
Scale = C.scale,
Nullable = CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default] = ISNULL(D.definition,N''),
[IDENTITY] = CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed = CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
PrimaryKey = ISNULL(IDX.PrimaryKey,N''),
IndexName = ISNULL(IDX.IndexName,N''),
IndexSort = ISNULL(IDX.Sort,N''),
Create_Date = O.Create_Date,
Modify_Date = O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
AND PFD.name='MS_Description' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
AND PTB.name='MS_Description' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
WHERE O.name=N'{0}' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id
";
#endregion
#endregion
#region Methods
public SqlSchema(DbFactory factory)
: base(factory)
{
}
protected Database GetDatabase()
{
return new Database(this.factory);
}
public override DbTableSchema[] GetSchema()
{
return GetSqlSchema();
//return base.GetSchema();
}
public SqlTableSchema[] GetSqlSchema()
{
var db = GetDatabase();
List<SqlTableSchema> tables = new List<SqlTableSchema>();
Func<SqlTableSchema, SqlColumnSchema[]> func = table =>
{
using (var reader = db.ExecuteStoredProcReader(string.Format(sqlGetColumns, table.Name)))
{
List<SqlColumnSchema> columns = new List<SqlColumnSchema>();
while (reader.Read())
{
SqlColumnSchema column = new SqlColumnSchema(columns.Count);
column.ColumnId = Convert.ToInt32(reader["Column_id"]);
column.Name = reader["ColumnName"].ToString();
column.SqlType = reader["Type"].ToString();
column.FieldType = SqlToCShap(column.SqlType);
column.Length = Convert.ToInt32(reader["Length"]);
column.Description = reader["ColumnDesc"].ToString();
column.IsNullable = reader["Nullable"].ToString() == "√";
column.IsIdentity = reader["Identity"].ToString() == "√";
column.IsPrimaryKey = reader["PrimaryKey"].ToString() == "√";
column.IndexName = reader["IndexName"].ToString().Trim();
column.IndexSort = reader["IndexSort"].ToString();
columns.Add(column);
}
return columns.ToArray();
}
};
using (var reader = db.ExecuteStoredProcReader(sqlGetTables))
{
while (reader.Read())
{
SqlTableSchema table = new SqlTableSchema();
table.TableId = Convert.ToInt32(reader["TableId"]);
table.Name = reader["TableName"].ToString();
table.Description = reader["TableDesc"].ToString();
table.Columns = func(table);
tables.Add(table);
}
}
return tables.ToArray();
}
public void Draw(SqlTableSchema[] tables)
{
var conn = factory.CreateConnection();
string database = conn.Database;
for (int i = 0; i < tables.Length; i++)
{
Image.DrawPhoto(database, tables[i], SystemConfig.Instance.SavePath + @"\" + SystemConfig.Instance.FolderName + @"\DbImg\", i + 1);
}
}
#endregion
}
#region SqlTableSchema
public class SqlTableSchema : DbTableSchema
{
public int TableId { get; set; }
public string Description { get; set; }
public new SqlColumnSchema[] Columns
{
get { return (SqlColumnSchema[])base.columns; }
set { base.columns = value ?? new SqlColumnSchema[0]; }
}
protected override DbColumnSchema[] DbColumns
{
get { return this.Columns; }
set { this.Columns = (SqlColumnSchema[])value; }
}
}
public class SqlColumnSchema : DbColumnSchema
{
public int ColumnId { get; set; }
public string SqlType { get; set; }
public int Length { get; set; }
public string Description { get; set; }
public bool IsIdentity { get; set; }
public string IndexName { get; set; }
public string IndexSort { get; set; }
public SqlColumnSchema(int ordinal)
: base(ordinal)
{
}
}
#endregion
#region Image
public static class Image
{
public static void DrawPhoto(string database, SqlTableSchema table, string dir, int counter)
{
string fontFamily = "SimSun";
float fontSize = 1.5F;
int vFontSize = 9;
int imageWidth = 0;
int imageHeight = 0;
int rowHeight = 20;
int borderWidth = 4;
int nameWidth = 0;
int typeWidth = 0;
int descWidth = 0;
Font titleFont = new Font(fontFamily, fontSize, FontStyle.Bold);
Font vTitleFont = new Font(fontFamily, vFontSize, FontStyle.Bold);
string number = string.Format("[{0}]", counter.ToString());
int numberWidth = TextRenderer.MeasureText(number, vTitleFont).Width;
string title = string.Format("{0} ({1})", table.Name, table.Description);
string fullTitle = number + title;
int titleWidth = TextRenderer.MeasureText(fullTitle, vTitleFont).Width;
// 计算图高
imageHeight = rowHeight + rowHeight * table.Columns.Length + borderWidth * 2;
// 计算列宽、图宽
foreach (SqlColumnSchema column in table.Columns)
{
Font font = new Font(fontFamily, vFontSize);
int tmpNameWidth = TextRenderer.MeasureText(column.Name, font).Width + borderWidth;
int tmpTypeWidth = TextRenderer.MeasureText(column.SqlType + "(" + column.Length + ")", font).Width + borderWidth;
int tmpDescWidth = TextRenderer.MeasureText(column.Description, font).Width + borderWidth;
if (tmpNameWidth > nameWidth)
nameWidth = tmpNameWidth;
if (tmpTypeWidth > typeWidth)
typeWidth = tmpTypeWidth;
if (tmpDescWidth > descWidth)
descWidth = tmpDescWidth;
}
imageWidth = nameWidth + typeWidth + descWidth + borderWidth * 2;
// 标题宽度较大
if (titleWidth > imageWidth)
imageWidth = titleWidth;
Bitmap bitmap = new Bitmap(imageWidth, imageHeight);
bitmap.SetResolution(600, 600);
Graphics graphics = Graphics.FromImage(bitmap);
// 填充背景
graphics.FillRectangle(new SolidBrush(Color.White), 0, 0, imageWidth, imageHeight);
// 画竖线
graphics.DrawLine(new Pen(Color.LightGray, 1), new Point(nameWidth, rowHeight + borderWidth), new Point(nameWidth, imageHeight));
graphics.DrawLine(new Pen(Color.LightGray, 1), new Point(nameWidth + typeWidth, rowHeight + borderWidth), new Point(nameWidth + typeWidth, imageHeight));
// 画横线
for (int i = 0; i < table.Columns.Length; i++)
graphics.DrawLine(new Pen(Color.LightGray, 1), new Point(0, rowHeight + borderWidth + rowHeight * i), new Point(imageWidth, rowHeight + borderWidth + rowHeight * i));
// 画外边框
graphics.DrawLine(new Pen(Color.Gray, borderWidth), new Point(0, 0), new Point(imageWidth, 0));
graphics.DrawLine(new Pen(Color.Gray, borderWidth), new Point(0, imageHeight), new Point(imageWidth, imageHeight));
graphics.DrawLine(new Pen(Color.Gray, borderWidth), new Point(0, 0), new Point(0, imageHeight));
graphics.DrawLine(new Pen(Color.Gray, borderWidth), new Point(imageWidth, 0), new Point(imageWidth, imageHeight));
// 画表名
graphics.DrawString(number, titleFont, new SolidBrush(Color.Green), borderWidth, borderWidth);
graphics.DrawString(title, titleFont, new SolidBrush(Color.Black), borderWidth + numberWidth, borderWidth);
// 画字段
for (int i = 0; i < table.Columns.Length; i++)
{
SolidBrush brush = new SolidBrush(Color.Black);
Font font = new Font(fontFamily, fontSize);
// 存在索引就将文字变成蓝色
if (table.Columns[i].IndexName != "")
brush = new SolidBrush(Color.Blue);
// 是标识就将文字加下划线
if (table.Columns[i].IsIdentity)
font = new Font(fontFamily, fontSize, FontStyle.Underline);
// 是主键就将文字颜色改为红色
if (table.Columns[i].IsPrimaryKey)
brush = new SolidBrush(Color.Red);
graphics.DrawString(table.Columns[i].Name, font, brush, borderWidth, rowHeight + borderWidth * 2 + rowHeight * i);
graphics.DrawString(table.Columns[i].SqlType + "(" + table.Columns[i].Length + ")", font, brush, nameWidth + borderWidth, rowHeight + borderWidth * 2 + rowHeight * i);
graphics.DrawString(table.Columns[i].Description, font, brush, nameWidth + typeWidth + borderWidth, rowHeight + borderWidth * 2 + rowHeight * i);
}
string path = string.Format("{0}\\{1}", dir, database);
string fullPath = string.Format("{0}\\{1}.png", path, table.Name);
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
bitmap.Save(fullPath);
}
}
#endregion
}