1、参考C#代码
using Help.DBAccessLayer.Business;
using Help.DBAccessLayer.Model.SqlGenerator;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using MSWord = Microsoft.Office.Interop.Word; namespace MainTest.TestModel
{
public class GenerateWordDBDesign
{
/// <summary>
/// 数据库链接字符串
/// </summary>
private string _DbConnectionString = ""; /// <summary>
/// 数据库名称
/// </summary>
private string dataBaseName = "master"; /// <summary>
/// 是否使用测试数据
/// </summary>
private readonly bool _isMock = true; /// <summary>
/// 由于使用的是COM库，因此有许多变量需要用Missing.Value代替
/// </summary>
private Object Nothing = Missing.Value; /// <summary>
/// 写入黑体文本
/// </summary>
private object unite = MSWord.WdUnits.wdStory; /// <summary>
/// 有长度的类型列表
/// </summary>
private readonly List<string> useLengthList = new List<string>(){
"varchar",
"nvarchar",
}; /// <summary>
/// 有小数的类型列表
/// </summary>
private readonly List<string> digitalList = new List<string>()
{
"decimal",
"numeric",
}; /// <summary>
/// 获取数据库中的所有表结构信息等，现在的问题是table的cell 的 高度控制不好，内容是上对齐的 20180316
/// </summary>
public void Generate()
{
MDataBaseDefine res = getData(); object path; //文件路径变量
MSWord.Application wordApp;
MSWord.Document wordDoc; path = Environment.CurrentDirectory + "\\" + DateTime.Now.ToString("yyyyMMdd") + "_" + this.dataBaseName + ".doc";
wordApp = new MSWord.Application(); wordApp.Visible = true;//使文档可见 //如果已存在，则删除
if (File.Exists((string)path))
{
File.Delete((string)path);
} //由于使用的是COM库，因此有许多变量需要用Missing.Value代替
wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing); //页面设置
wordDoc.PageSetup.PaperSize = MSWord.WdPaperSize.wdPaperA4;//设置纸张样式为A4纸
wordDoc.PageSetup.Orientation = MSWord.WdOrientation.wdOrientPortrait;//排列方式为垂直方向
wordDoc.PageSetup.TopMargin = 57.0f;
wordDoc.PageSetup.BottomMargin = 57.0f;
wordDoc.PageSetup.LeftMargin = 57.0f;
wordDoc.PageSetup.RightMargin = 57.0f;
wordDoc.PageSetup.HeaderDistance = 30.0f;//页眉位置 wordApp.Selection.ParagraphFormat.LineSpacing = 16f;//设置文档的行间距
wordApp.Selection.ParagraphFormat.FirstLineIndent = ;//首行缩进的长度 this.AddHeading1("物理模型设计", wordDoc);
this.DrawTableCountInfo(wordApp, wordDoc, res); wordDoc.Content.InsertAfter("\n");//这一句与下一句的顺序不能颠倒，原因还没搞透
wordApp.Selection.EndKey(ref unite, ref Nothing);//这一句不加，有时候好像也不出问题，不过还是加了安全 this.AddHeading2("2 表描述", wordDoc);
int tableIndex = ;
foreach (var item in res.TableList)
{
this.DrawTableDetailInfo(wordApp, wordDoc, tableIndex, item);
tableIndex++;
} wordDoc.Content.InsertAfter("\n"); // WdSaveFormat为Word 2003文档的保存格式
// office 2007就是wdFormatDocumentDefault
object format = MSWord.WdSaveFormat.wdFormatDocumentDefault;
//将wordDoc文档对象的内容保存为DOCX文档
wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing); wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
//关闭wordApp组件对象
wordApp.Quit(ref Nothing, ref Nothing, ref Nothing);
Console.WriteLine(path + " 创建完毕！");
Console.ReadKey(); } /// <summary>
/// 绘制表详细
/// </summary>
/// <param name="wordApp">Word App</param>
/// <param name="wordDoc">Word Doc</param>
/// <param name="tableIndex">表格序号</param>
/// <param name="tableDefine">表定义对象</param>
private void DrawTableDetailInfo(MSWord.Application wordApp, MSWord.Document wordDoc, int tableIndex, MTableDefine tableDefine)
{
// 这一句与下一句的顺序不能颠倒，原因还没搞透
// wordDoc.Content.InsertAfter("\n"); // 这一句不加，有时候好像也不出问题，不过还是加了安全
wordApp.Selection.EndKey(ref unite, ref Nothing); this.AddHeading4("2." + tableIndex + " " + tableDefine.TableName + "表的卡片", wordDoc); // 这一句与下一句的顺序不能颠倒，原因还没搞透
// wordDoc.Content.InsertAfter("\n");
// 将光标移动到文档末尾
wordApp.Selection.EndKey(ref unite, ref Nothing);
wordApp.Selection.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft; int tableRow = tableDefine.FieldList.Count + ;
int tableColumn = ; // 定义一个Word中的表格对象
MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range,
tableRow, tableColumn, ref Nothing, ref Nothing); // 默认创建的表格没有边框，这里修改其属性，使得创建的表格带有边框
// 这个值可以设置得很大，例如5、13等等
table.Borders.Enable = ; // 设置 每一列的 宽度
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ;
table.Columns[].Width = ; // 横向合并
table.Cell(, ).Merge(table.Cell(, ));
table.Cell(, ).Range.Text = tableDefine.TableName;
table.Cell(, ).Range.Shading.BackgroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorGray25; //表格的索引是从1开始的。
table.Cell(, ).Range.Text = "是否主键";
table.Cell(, ).Range.Text = "字段名";
table.Cell(, ).Range.Text = "字段描述";
table.Cell(, ).Range.Text = "数据类型";
table.Cell(, ).Range.Text = "长度";
table.Cell(, ).Range.Text = "可空";
table.Cell(, ).Range.Text = "约束";
table.Cell(, ).Range.Text = "缺省值";
table.Cell(, ).Range.Text = "备注"; for (int i = ; i <= tableRow; i++)
{
int row = i;
var field = tableDefine.FieldList[i - ]; // 是否主键
if (field.IsPrimaryKey)
{
table.Cell(row, ).Range.Text = "是";
}
else
{
table.Cell(row, ).Range.Text = "";
} // 字段名
table.Cell(row, ).Range.Text = field.FieldName;
if (string.IsNullOrEmpty(field.FieldNameCH) && field.FieldName == "ID")
{
// 字段描述
table.Cell(row, ).Range.Text = "主键ID";
}
else
{
// 字段描述
table.Cell(row, ).Range.Text = field.FieldNameCH;
} // 数据类型
table.Cell(row, ).Range.Text = field.DataType; if (this.digitalList.Contains(field.DataType))
{
table.Cell(row, ).Range.Text = field.Length.ToString() + "," + field.DigitalLength;
} else if (this.useLengthList.Contains(field.DataType))
{
// 长度
table.Cell(row, ).Range.Text = field.Length.ToString();
}
else
{
table.Cell(row, ).Range.Text = string.Empty;
} // 是否可空
if (field.IsNullable)
{
table.Cell(row, ).Range.Text = "是";
}
else
{
table.Cell(row, ).Range.Text = "否";
} // 约束
table.Cell(row, ).Range.Text = field.ValueConstraint; // 缺省值
table.Cell(row, ).Range.Text = field.DefaultValue; // 备注
table.Cell(row, ).Range.Text = field.ProjectSignificance;
} //设置table样式
//table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//高度规则是：行高有最低值下限？
//table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));// table.Range.Font.Size = 9.5F;
table.Range.Font.Bold = ;
table.Range.Font.Name = "新宋体"; table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft;//表格文本居中 table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalBottom;//文本垂直贴到底部 // 设置table边框样式
table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;//表格外框是双线
table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;//表格内框是单线 // 加粗
table.Rows[].Range.Font.Bold = ;
table.Rows[].Range.Font.Size = 9.5F; // 加粗
table.Rows[].Range.Font.Bold = ;
table.Rows[].Range.Font.Size = 9.5F;
} /// <summary>
/// 添加标题1
/// </summary>
/// <param name="s"></param>
/// <param name="wordDoc"></param>
private void AddHeading1(string s, MSWord.Document wordDoc)
{
//Word段落
Microsoft.Office.Interop.Word.Paragraph p; p = wordDoc.Content.Paragraphs.Add(ref Nothing); //设置段落中的内容文本
p.Range.Text = s; //设置为一号标题
object style = Microsoft.Office.Interop.Word.WdBuiltinStyle.wdStyleHeading1; p.set_Style(ref style); //添加到末尾
p.Range.InsertParagraphAfter(); //在应用 InsertParagraphAfter 方法之后，所选内容将扩展至包括新段落。
} /// <summary>
/// 添加标题2
/// </summary>
/// <param name="s"></param>
/// <param name="wordDoc"></param>
private void AddHeading2(string s, MSWord.Document wordDoc)
{
// Word段落
Microsoft.Office.Interop.Word.Paragraph p; p = wordDoc.Content.Paragraphs.Add(ref Nothing); // 设置段落中的内容文本
p.Range.Text = s; //设置为一号标题
object style = Microsoft.Office.Interop.Word.WdBuiltinStyle.wdStyleHeading2;
p.set_Style(ref style); // 添加到末尾
// 在应用 InsertParagraphAfter 方法之后，所选内容将扩展至包括新段落。
p.Range.InsertParagraphAfter();
} /// <summary>
/// 添加标题4
/// </summary>
/// <param name="s"></param>
/// <param name="wordDoc"></param>
private void AddHeading4(string s, MSWord.Document wordDoc)
{
//Word段落
Microsoft.Office.Interop.Word.Paragraph p; p = wordDoc.Content.Paragraphs.Add(ref Nothing);
//设置段落中的内容文本
p.Range.Text = s;
//设置为一号标题
object style = Microsoft.Office.Interop.Word.WdBuiltinStyle.wdStyleHeading4;
p.set_Style(ref style);
//添加到末尾
p.Range.InsertParagraphAfter(); //在应用 InsertParagraphAfter 方法之后，所选内容将扩展至包括新段落。
} /// <summary>
/// 绘制表清单
/// </summary>
/// <param name="wordApp"></param>
/// <param name="wordDoc"></param>
/// <param name="res"></param>
private void DrawTableCountInfo(MSWord.Application wordApp, MSWord.Document wordDoc, MDataBaseDefine res)
{
// 这一句与下一句的顺序不能颠倒，原因还没搞透
//wordDoc.Content.InsertAfter("\n"); // 这一句不加，有时候好像也不出问题，不过还是加了安全
wordApp.Selection.EndKey(ref unite, ref Nothing); this.AddHeading2("1 表清单", wordDoc); //将光标移动到文档末尾
wordApp.Selection.EndKey(ref unite, ref Nothing);
wordApp.Selection.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft; int tableRow = res.TableList.Count + ;
int tableColumn = ; // 定义一个Word中的表格对象
MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range,
tableRow, tableColumn, ref Nothing, ref Nothing); // 默认创建的表格没有边框，这里修改其属性，使得创建的表格带有边框
table.Borders.Enable = ;//这个值可以设置得很大，例如5、13等等 // 表格的索引是从1开始的。
table.Cell(, ).Range.Text = "序号";
table.Cell(, ).Range.Text = "代码";
table.Cell(, ).Range.Text = "描述"; for (int i = ; i < tableRow; i++)
{
int row = i + ;
var tableInfo = res.TableList[i - ];
table.Cell(row, ).Range.Text = i + ".";
table.Cell(row, ).Range.Text = tableInfo.TableName;
table.Cell(row, ).Range.Text = tableInfo.TableNameCH;
} // 设置table样式
// 高度规则是：行高有最低值下限？
table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;
//table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));// table.Range.Font.Size = 11F;
table.Range.Font.Bold = ;
table.Range.Font.Name = "新宋体"; // 表格文本居左
table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter; // 文本垂直贴到底部
table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalBottom; // 设置table边框样式
table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;//表格外框是双线
table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;//表格内框是单线 // 加粗
table.Rows[].Range.Font.Bold = ;
table.Rows[].Range.Font.Size = 11F; //将第 1列宽度设置为90
table.Columns[].Width = ; //将其他列的宽度都设置为75
for (int i = ; i <= tableColumn; i++)
{
table.Columns[i].Width = ;
}
} /// <summary>
/// 读取数据库及表结构信息
/// </summary>
/// <returns></returns>
private MDataBaseDefine getData()
{
if (_isMock)
{
return new MDataBaseDefine()
{
TableList = new List<MTableDefine>()
{
new MTableDefine(){
FieldList = new List<MFieldDefine>(){
new MFieldDefine(){
DataType = "varchar",
DefaultValue = string.Empty,
DigitalLength = ,
FieldFormat = string.Empty,
FieldName = "ID",
FieldNameCH = string.Empty,
ForeignRelation = string.Empty,
IsNullable = false,
Length = ,
IsAutoIncrement = false,
IsPrimaryKey = true, },
new MFieldDefine(){
DataType = "varchar",
DefaultValue = string.Empty,
DigitalLength = ,
FieldFormat = string.Empty,
FieldName = "UserName",
FieldNameCH = "用户名称",
ForeignRelation = string.Empty,
IsNullable = false,
Length = ,
IsAutoIncrement = false,
IsPrimaryKey = false, }
},
PrimaryKey = "ID",
TableDescrption = string.Empty,
TableName = "User",
TableNameCH = string.Empty
}
}
};
}
else
{
var res = new BGetSchema().GenerateDataBaseDefine(this._DbConnectionString); string json = JsonConvert.SerializeObject(res); // 存个表 string fileName = @"C:\Julius_J_Zhu\09DataLayer\MainTest\documents\" + this.dataBaseName + "_JSON_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt"; this.saveFileName(fileName, json); // 去除 含有 下划线的临时表
res.TableList = res.TableList.FindAll(sa => !sa.TableName.Contains("_")); return res;
}
} /// <summary>
/// 保存文件
/// </summary>
/// <param name="name">文件名</param>
/// <param name="str">文件内容</param>
private void saveFileName(string name, string str)
{
using (FileStream fs2 = new FileStream(name, FileMode.Create))
{
StreamWriter sw = new StreamWriter(fs2); //开始写入
sw.Write(str);
//清空缓冲区
sw.Flush();
//关闭流
sw.Close();
} }
}
}
2、所使用的Model类
2.1 数据库类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Model.SqlGenerator
{
/// <summary>
/// 数据库定义
/// </summary>
public class MDataBaseDefine
{
/// <summary>
/// 数据库定义
/// </summary>
public string DataBaseName
{
get;
set;
} /// <summary>
/// 数据库类型
/// </summary>
public MDataBaseType DataBaseType
{
get;
set;
} /// <summary>
/// 服务器地址
/// </summary>
public string ServerAddress
{
get;
set;
} /// <summary>
/// 读写账号
/// </summary>
public string ReadAccount
{
get;
set;
} /// <summary>
/// 写账号
/// </summary>
public string WriteAccount
{
get;
set;
} /// <summary>
/// 表列表
/// </summary>
public List<MTableDefine> TableList
{
get;
set;
}
}
}
MDataBaseDefine类
2.2 表结构类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Model.SqlGenerator
{
/// <summary>
/// 表定义
/// </summary>
public class MTableDefine
{
/// <summary>
/// 表名（英文）
/// </summary>
public string TableName
{
get;
set;
} /// <summary>
/// 表名（中文）
/// </summary>
public string TableNameCH
{
get;
set;
} /// <summary>
/// 表描述
/// </summary>
public string TableDescrption
{
get;
set;
} /// <summary>
/// 主键
/// </summary>
public string PrimaryKey
{
get;
set;
} /// <summary>
/// 字段定义
/// </summary>
public List<MFieldDefine> FieldList
{
get;
set;
}
}
}
MTableDefine类
2.3 字段类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Model.SqlGenerator
{
/// <summary>
/// 表字段定义
/// </summary>
public class MFieldDefine
{
/// <summary>
/// 字段格式
/// </summary>
public string FieldFormat; /// <summary>
/// 序号
/// </summary>
public int Index { get; set; } /// <summary>
/// 字段中文名
/// </summary>
public string FieldNameCH
{
get;
set;
} /// <summary>
/// 字段名
/// </summary>
public string FieldName
{
get;
set;
} /// <summary>
/// 数据类型
/// </summary>
public string DataType
{
get;
set;
} /// <summary>
/// 数据类型长度
/// </summary>
public int Length
{
get;
set;
} /// <summary>
/// 是否可空
/// </summary>
public bool IsNullable
{
get;
set;
} /// <summary>
/// 主键
/// </summary>
public int PrimaryKeyIndex
{
get;
set;
} /// <summary>
/// 外部关系
/// </summary>
public string ForeignRelation
{
get;
set;
} /// <summary>
/// 是否是唯一索引
/// </summary>
public bool IsUniqueIndex
{
get;
set;
} /// <summary>
/// 索引序号
/// </summary>
public int IndexNo
{
get;
set;
} /// <summary>
/// 是否是自增长类型
/// </summary>
public bool IsAutoIncrement
{
get;
set;
} /// <summary>
/// 默认值
/// </summary>
public string DefaultValue
{
get;
set;
} /// <summary>
/// 值约束
/// </summary>
public string ValueConstraint
{
get;
set;
} /// <summary>
/// 项目意义
/// </summary>
public string ProjectSignificance
{
get;
set;
} public int DigitalLength { get; set; } /// <summary>
/// 是否为主键，联合主键可能会有问题
/// </summary>
public bool IsPrimaryKey
{
get;
set;
}
}
}
MFieldDefine类
2.4 数据库类型枚举
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Model
{
public enum MDataBaseType
{
/// <summary>
/// 未知数据库
/// </summary>
UNKNOW = , /// <summary>
/// The mysql
/// </summary>
MYSQL = , /// <summary>
/// The sqlserver
/// </summary>
SQLSERVER = , /// <summary>
/// DB2
/// </summary>
DB2 =
}
}
MDataBaseType枚举
3、获取数据库结构相关
3.1 获取数据库结构业务层
using Help.DBAccessLayer.Factory;
using Help.DBAccessLayer.Model;
using Help.DBAccessLayer.Model.SqlGenerator;
using IBM.Data.DB2;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Business
{
public class BGetSchema
{
public List<MTableDesc> GetTableList(string creator)
{
string connstr = ConnectionFactory.TRSDbConnString; List<MTableDesc> ret = null; using (DB2Connection conn = new DB2Connection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE); ret = dao.GetTableList(conn, creator);
} return ret;
} public List<MColumn> GetColumnList(string tableName)
{
string connstr = ConnectionFactory.TRSDbConnString;
List<MColumn> ret = new List<MColumn>();
using (DB2Connection conn = new DB2Connection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE); ret = dao.GetColumnList(conn, tableName);
} return ret;
} public MDataBaseDefine GenerateDataBaseDefine(string connstr)
{
MDataBaseDefine ret = null;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.SQLSERVER, MDBAccessType.READONLY);
ret = dao.GenerateDataBaseDefine(conn);
} return ret;
}
}
}
BGetSchema类
3.2 获取数据库结构工厂
using Help.DBAccessLayer.DB2DAL;
using Help.DBAccessLayer.IDAL;
using Help.DBAccessLayer.Model;
using Help.DBAccessLayer.MySQLDAL; using SQLServer = Help.DBAccessLayer.SQLServer;
using Help.DBAccessLayer.OleDbDAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.Factory
{
public class DALFactory
{
public static IGetSchema GetSchemaDAO(MDataBaseType dbType, MDBAccessType accessType)
{
switch (dbType)
{
case MDataBaseType.DB2:
return new DGetSchema();
case MDataBaseType.SQLSERVER:
return new Help.DBAccessLayer.SQLServer.DGetSchema();
} throw new Exception(string.Format("未实现的IGetSchema接口:数据库类型:{0},数据库访问类型:{1}", dbType.ToString(), accessType.ToString()));
}
}
}
3.3 DGetSchema 获取数据库结构 SQL 实现
using Help.DBAccessLayer.Model;
using Help.DBAccessLayer.Model.SqlGenerator;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.IDAL
{
public interface IGetSchema
{
MDataBaseDefine GenerateDataBaseDefine(System.Data.IDbConnection conn);
}
}
using Help.DBAccessLayer.IDAL;
using Help.DBAccessLayer.Model.SqlGenerator;
using Help.DBAccessLayer.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Help.DBAccessLayer.SQLServer
{
public class DGetSchema : IGetSchema
{
public MDataBaseDefine GenerateDataBaseDefine(System.Data.IDbConnection conn)
{
MDataBaseDefine database = new MDataBaseDefine();
database.TableList = new List<MTableDefine>(); // 获取所有表名
List<string> tableNameList = this.GetTableNameList(conn); foreach (var tableName in tableNameList)
{
var table = this.GetTableDefine(conn, tableName);
database.TableList.Add(table);
} // 填充索引信息
List<MTableIndex> indexList = this.GetIndexInfo(conn); var group = from p in indexList
group p by new { p.TableName } into g
select new { g.Key };
foreach (var g in group)
{
var tableFind = database.TableList.Find(sa => sa.TableName == g.Key.TableName);
if (tableFind != null)
{
var columns = tableFind.FieldList;
var indexs = indexList.FindAll(sa => sa.TableName == g.Key.TableName); indexs.ForEach(sa =>
{
var column = columns.Find(p => p.FieldName == sa.ColumnName);
if (sa.IsUnique)
{
column.IsUniqueIndex = true;
}
else
{
column.IndexNo = columns.Max(q => q.IndexNo) + ;
}
});
}
} return database;
} private List<string> GetTableNameList(System.Data.IDbConnection conn)
{
string queryTableListSql = "SELECT name FROM SYSOBJECTS WHERE TYPE='U' and category=0 order by name;";
SqlCommand comm = new SqlCommand(queryTableListSql, (SqlConnection)conn);
SqlDataReader reader = comm.ExecuteReader(); List<string> tableNameList = new List<string>(); while (reader.Read())
{
string name = reader["name"] == DBNull.Value ? string.Empty : reader["name"].ToString();
tableNameList.Add(name);
} return tableNameList;
} private MTableDefine GetTableDefine(System.Data.IDbConnection conn, string tableName)
{
MTableDefine table = new MTableDefine();
table.TableName = tableName;
string sql = string.Format(@"
SELECT
TableName = case when a.colorder=1 then d.name else '' end,
TableNameCH = case when a.colorder=1 then isnull(f.value,'') else '' end,
FieldIndex = a.colorder,
FieldName = a.name,
IsIdentity = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end,
IsPrimaryKey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 'true' else 'false' end,
DataType = b.name,
DataTypeLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
DigitalLength = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
IsNullable = case when a.isnullable=1 then 'true'else 'false' end,
DefaultValue = isnull(e.text,''),
FieldNameCH = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where
d.name='{0}' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
order by
a.id,a.colorder;", tableName); SqlCommand comm = new SqlCommand(sql, (SqlConnection)conn);
SqlDataReader reader = comm.ExecuteReader();
List<MFieldDefine> fieldList = new List<MFieldDefine>();
while (reader.Read())
{
MFieldDefine field = new MFieldDefine();
table.TableNameCH = reader["TableNameCH"] == DBNull.Value ? string.Empty : reader["TableNameCH"].ToString();
field.Index = DBUtil.GetDBValueInt(reader, "FieldIndex", );
field.FieldName = DBUtil.GetDBValueStr(reader, "FieldName", string.Empty);
field.IsAutoIncrement = DBUtil.GetDBValueBool(reader, "IsIdentity", false); bool IsPrimaryKey = DBUtil.GetDBValueBool(reader, "IsPrimaryKey", false);
int maxPrimaryKeyIndex = fieldList != null && fieldList.Count > ? fieldList.Max(sa => sa.PrimaryKeyIndex) : ; if (IsPrimaryKey)
{
field.PrimaryKeyIndex = maxPrimaryKeyIndex + ;
} field.IsPrimaryKey = IsPrimaryKey; field.DataType = DBUtil.GetDBValueStr(reader, "DataType", string.Empty);
field.Length = DBUtil.GetDBValueInt(reader, "DataTypeLength", );
field.DigitalLength = DBUtil.GetDBValueInt(reader, "DigitalLength", );
field.IsNullable = DBUtil.GetDBValueBool(reader, "IsNullable", false);
field.DefaultValue = DBUtil.GetDBValueStr(reader, "DefaultValue", string.Empty); // 处理默认值
if (!string.IsNullOrEmpty(field.DefaultValue))
{
string[] twoBracketsSpecial = { "int", "tinyint" }; if (twoBracketsSpecial.Contains(field.DataType.ToLower()))
{
field.DefaultValue = field.DefaultValue.Replace("(", string.Empty).Replace(")", string.Empty);
}
else
{
// 只有一个括号
field.DefaultValue = field.DefaultValue.Substring(, field.DefaultValue.Length - );
}
} field.FieldNameCH = DBUtil.GetDBValueStr(reader, "FieldNameCH", string.Empty);
fieldList.Add(field);
} table.FieldList = fieldList; return table; } private List<MTableIndex> GetIndexInfo(System.Data.IDbConnection conn)
{
string sql = @"SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,''),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
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=IDX.is_primary_key,
IsUnique=IDX.is_unique,
Ignore_dup_key=IDX.ignore_dup_key,
Disabled=IDX.is_disabled,
Fill_factor=IDX.fill_factor,
Padded=IDX.is_padded
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 sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
WHERE IDX.is_primary_key<>1
AND O.Name NOT LIKE 'sys%';"; SqlCommand comm = new SqlCommand(sql, (SqlConnection)conn);
SqlDataReader reader = comm.ExecuteReader();
List<MTableIndex> list = new List<MTableIndex>();
while (reader.Read())
{
MTableIndex model = new MTableIndex();
model.TableName = DBUtil.GetDBValueStr(reader, "TableName");
model.IndexType = DBUtil.GetDBValueStr(reader, "IndexType");
model.IndexName = DBUtil.GetDBValueStr(reader, "IndexName");
model.ColumnName = DBUtil.GetDBValueStr(reader, "ColumnName");
model.IsUnique = DBUtil.GetDBValueBool(reader, "IsUnique");
model.Sort = DBUtil.GetDBValueStr(reader, "Sort"); list.Add(model);
} return list;
}
}
}
4 生成文档结果