前几天公司在做CMMI 3级认证,需要提交一系列的Word文档,其中有一种文档要求添加公司几个系统的数据库中的表结构。我临时接到了这项任务,辅助相关人员完成这种文档。
本系列所有示例代码均在 Visual Studio 2010 Ultimate RTM + Office 2010 Professional Plus RTM x64 中测试通过
注:本次使用的数据库为 SQL Server 2008 R2 DataCenter RTM x64
世界杯到了,首先献上一张对阵表:
小组赛日期 | 时间 | 对阵 | 组别 | 比赛地 | 场次 |
6月11日 星期五 | 22:00 | 南非 VS 墨西哥 | A1-A2 | 约翰内斯堡(足) | 01 |
6月12日 星期六 | 02:30 | 乌拉圭 VS 法国 | A3-A4 | 开普敦 | 02 |
6月12日 星期六 | 19:30 | 韩国 VS 希腊 | B3-B4 | 伊丽莎白港 | 04 |
6月12日 星期六 | 22:00 | 阿根廷 VS 尼日利亚 | B1-B2 | 约翰内斯堡(埃) | 03 |
6月13日 星期日 | 02:30 | 英格兰 VS 美国 | C1-C2 | 鲁斯腾堡 | 05 |
6月13日 星期日 | 19:30 | 阿尔及利亚 VS 斯洛文尼亚 | C3-C4 | 保罗瓜尼 | 06 |
6月13日 星期日 | 22:00 | 塞尔维亚 VS 加纳 | D3-D4 | 比勒陀利亚 | 08 |
6月14日 星期一 | 02:30 | 德国 VS 澳大利亚 | D1-D2 | 德班 | 07 |
6月14日 星期一 | 19:30 | 荷兰 VS 丹麦 | E1-E2 | 约翰内斯堡(足) | 09 |
6月14日 星期一 | 22:00 | 日本 VS 喀麦隆 | E3-E4 | 布隆冯丹 | 10 |
6月15日 星期二 | 02:30 | 意大利 VS 巴拉圭 | F1-F2 | 开普敦 | 11 |
6月15日 星期二 | 19:30 | 新西兰 VS 斯洛伐克 | F3-F4 | 鲁斯腾堡 | 12 |
6月15日 星期二 | 22:00 | 科特迪瓦 VS 葡萄牙 | G3-G4 | 伊丽莎白港 | 13 |
6月16日 星期三 | 02:30 | 巴西 VS 朝鲜 | G1-G2 | 约翰内斯堡(埃) | 14 |
6月16日 星期三 | 19:30 | 洪都拉斯 VS 智利 | H3-H4 | 内斯普利特 | 15 |
6月16日 星期三 | 22:00 | 西班牙 VS 瑞士 | H1-H2 | 德班 | 16 |
6月17日 星期四 | 02:30 | 南非 VS 乌拉圭 | A1-A3 | 比勒陀利亚 | 17 |
6月17日 星期四 | 19:30 | 阿根廷 VS 韩国 | B1-B3 | 约翰内斯堡(足) | 20 |
6月17日 星期四 | 22:00 | 尼日利亚 VS 希腊 | B2-B4 | 布隆冯丹 | 19 |
6月18日 星期五 | 02:30 | 墨西哥 VS 法国 | A2-A4 | 保罗瓜尼 | 18 |
6月18日 星期五 | 19:30 | 德国 VS 塞尔维亚 | D1-D3 | 伊丽莎白港 | 21 |
6月18日 星期五 | 22:00 | 斯洛文尼亚 VS 美国 | C4-C2 | 约翰内斯堡(埃) | 22 |
6月19日 星期六 | 02:30 | 英格兰 VS 阿尔及利亚 | C1-C3 | 开普敦 | 23 |
6月19日 星期六 | 19:30 | 荷兰 VS 日本 | E1-E3 | 德班 | 25 |
6月19日 星期六 | 22:00 | 加纳 VS 澳大利亚 | D4-D2 | 鲁斯腾堡 | 24 |
6月20日 星期日 | 02:30 | 丹麦 VS 喀麦隆 | E2-E4 | 比勒陀利亚 | 26 |
6月20日 星期日 | 19:30 | 巴拉圭 VS 斯洛伐克 | F2-F4 | 布隆冯丹 | 27 |
6月20日 星期日 | 22:00 | 意大利 VS 新西兰 | F1-F3 | 内斯普利特 | 28 |
6月21日 星期一 | 02:30 | 巴西 VS 科特迪瓦 | G1-G3 | 约翰内斯堡(足) | 29 |
6月21日 星期一 | 19:30 | 朝鲜 VS 葡萄牙 | G2-G4 | 开普敦 | 30 |
6月21日 星期一 | 22:00 | 智利 VS 瑞士 | H4-H2 | 伊丽莎白港 | 31 |
6月22日 星期二 | 02:30 | 西班牙 VS 洪都拉斯 | H1-H3 | 约翰内斯堡(埃) | 32 |
6月22日 星期二 | 22:00 | 墨西哥 VS 乌拉圭 | A2-A3 | 鲁斯腾堡 | 33 |
6月22日 星期二 | 22:00 | 南非 VS 法国 | A1-A4 | 布隆冯丹 | 34 |
6月23日 星期三 | 02:30 | 尼日利亚 VS 韩国 | B2-B3 | 德班 | 35 |
6月23日 星期三 | 02:30 | 阿根廷 VS 希腊 | B1-B4 | 保罗瓜尼 | 36 |
6月23日 星期三 | 22:00 | 英格兰 VS 斯洛文尼亚 | C1-C4 | 伊丽莎白港 | 37 |
6月23日 星期三 | 22:00 | 美国 VS 阿尔及利亚 | C2-C3 | 比勒陀利亚 | 38 |
6月24日 星期四 | 02:30 | 德国 VS 加纳 | D1-D4 | 约翰内斯堡(足) | 39 |
6月24日 星期四 | 02:30 | 塞尔维亚 VS 澳大利亚 | D3-D2 | 内斯普利特 | 40 |
6月24日 星期四 | 22:00 | 意大利 VS 斯洛伐克 | F1-F4 | 约翰内斯堡(埃) | 41 |
6月24日 星期四 | 22:00 | 巴拉圭 VS 新西兰 | F2-F3 | 保罗瓜尼 | 42 |
6月25日 星期五 | 02:30 | 丹麦 VS 日本 | E2-E3 | 鲁斯腾堡 | 43 |
6月25日 星期五 | 02:30 | 荷兰 VS 喀麦隆 | E1-E4 | 开普敦 | 44 |
6月25日 星期五 | 22:00 | 巴西 VS 葡萄牙 | G1-G4 | 德班 | 45 |
6月25日 星期五 | 22:00 | 朝鲜 VS 科特迪瓦 | G2-G3 | 内斯普利特 | 46 |
6月26日 星期六 | 02:30 | 西班牙 VS 智利 | H1-H4 | 比勒陀利亚 | 47 |
6月26日 星期六 | 02:30 | 瑞士 VS 洪都拉斯 | H2-H3 | 布隆冯丹 | 48 |
CMMI文档中表结构要求格式如下:
XX表
字段名 | 名称 | 主键 | 数据类型(精度) | 空/非空 | 约束条件 | 默认值 | 备注 |
说明 | XX表 |
公司系统数据库非常庞大,表也很多,如果一个个手工处理,那将非常麻烦,于是考虑写一个程序,来自动处理。
既然是生成Word文档,我就想做一个VSTO Word插件,通过选择的数据库中的表,来生成表结构。
- 创建一个Word 2010 AddIn项目:
- 在Word的Ribbon中添加一个按钮,点击按钮后选择数据库、表:
测试数据库使用的SQL Server 2008 R2 Sample Databases,可以在CodePlex上下载。
这个添加连接窗体我是仿微软的做的,本来想找一个第三方组件,没有找到,就自己实现了一个,功能上有所裁剪(只支持SQL Server),够用就好,微软的如下:
首先选择或输入一个SQL Server服务器名,支持"."、"(local)"、IP地址、SQL Server实例等。本意设计的是点击下拉列表时获取局域网内所有的SQL Server实例,但是始终未能实现,若有朋友知道,请指点一下。
PS:我本机是一台域控制器,操作系统为Windows Server 2008 R2 DataCenter,数据库为SQL Server 2008 R2 DataCenter X64,获取局域网内所有的SQL Server实例的代码为:
DataTable __dtServers = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
foreach (DataRow __dr in __dtServers.Rows)
{
this .txt服务器名.Items.Add(__dr[ " InstanceName " ].ToString());
}
先简单说说常用系统数据库对象:
sys.all_columns
sys.tables
sys.objects
sys.key_constraints
sys.types
sys.indexes
sys.default_constraints
sys.all_objects
sys.schemas
注意SQL Server 2000、2005不受支持,因为使用了一些新特性,所以如果要测试代码,请使用SQL Server 2008 或更新版本。
从名字上就可以看出这些系统表中包含的内容,通过这些表之间的关联我们可以取得绝大多数数据库的元数据,在某些情况下是非常有用的。
另外常用系统视图:
sys.all_objects
sys.databases
sys.servers
常用系统存储过程:
Sp_help
Sp_helptext
Sp_PKeys
Sp_rename
获取所选择服务器上的所有的数据库:
附加数据库:
-- Attach database template
-- =====================================
IF NOT EXISTS (
SELECT *
FROM sys.databases
WHERE name = N ' <database_name, sysname, your_database_name> '
)
CREATE DATABASE < database_name, sysname, your_database_name >
ON PRIMARY (FILENAME = ' <database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\your_database_name.MDF> ' )
LOG ON (FILENAME = ' <database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\your_database_name.ldf> ' )
FOR ATTACH
GO
可以选择Windows或SQL Server验证,可以点击【测试连接】测试一下连接是否正常。选择或附加一个数据库后,点击确定,会获取指定数据库中的所有表,选择要生成表结构的表(默认全选),点击【确定】生成:
获取指定数据库中的所有表:
- 在Word中插入表格
数据库、表选择好了之后,剩下的工作就要在Word中处理了。【约束条件】、【默认值】、【备注】三列忽略,
在剩下的列中,【字段名】、【名称】(也取的字段名)、【数据类型】、【空、非空】比较好取得,只有一个【主键】颇费了一番功夫。
-- SELECT * FROM sys.all_columns
-- SELECT * FROM sys.tables
-- SELECT * FROM sys.objects
-- SELECT * FROM sys.key_constraints
-- SELECT * FROM sys.types
-- SELECT * FROM sys.indexes
-- SELECT * FROM sys.default_constraints
-- SELECT * FROM sys.all_objects
-- SELECT * FROM sys.schemas
-- 常用系统视图
-- SELECT * FROM sys.all_objects
-- SELECT * FROM sys.databases
-- SELECT * FROM sys.servers
-- 默认值测试
-- SELECT col.column_id,col.name,d.definition
-- FROM sys.columns AS col
-- INNER JOIN sys.default_constraints AS d ON col.column_id = d.parent_column_id
IF EXISTS ( SELECT * FROM sys.objects WHERE type = ' P ' and name = ' sp_生成数据结构 ' )
BEGIN
DROP PROCEDURE dbo.sp_生成数据结构
END
GO
CREATE PROCEDURE dbo.sp_生成数据结构
(
@TableNames SelectTables READONLY
)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
CREATE TABLE #TableNames
(
ID INT IDENTITY ( 1 , 1 ),
TableName VARCHAR ( 200 )
)
INSERT INTO #TableNames(TableName)
SELECT TableName
FROM @TableNames
DECLARE @table_owner sysname = null ,
@table_qualifier sysname = null ,
@table_id int ,
-- quotename() returns up to 258 chars
@full_table_name nvarchar ( 517 ) -- 258 + 1 + 258
DECLARE @i INT , @Count INT , @Name VARCHAR ( 200 )
SET @i = 1
SET @Count = ( SELECT COUNT ( * ) FROM #TableNames)
WHILE @i <= @Count
BEGIN
SET @Name = ( SELECT TableName FROM #TableNames WHERE ID = @i )
SET @table_owner =
(
SELECT s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
WHERE t.name = @Name
)
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename ( @Name )
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename ( @table_owner )
end
else
begin
select @full_table_name = quotename ( @table_owner ) + ' . ' + quotename ( @Name )
end
end
select @table_id = object_id ( @full_table_name )
SELECT col.name AS 字段名,
col.name AS 名称,
CASE WHEN col.name IN
(
select
COLUMN_NAME = c.name
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o
where
o. object_id = @table_id and
o. object_id = c. object_id and
o. object_id = i. object_id and
i.is_primary_key = 1 and
(c.name = index_col ( @full_table_name , i.index_id, 1 ) or
c.name = index_col ( @full_table_name , i.index_id, 2 ) or
c.name = index_col ( @full_table_name , i.index_id, 3 ) or
c.name = index_col ( @full_table_name , i.index_id, 4 ) or
c.name = index_col ( @full_table_name , i.index_id, 5 ) or
c.name = index_col ( @full_table_name , i.index_id, 6 ) or
c.name = index_col ( @full_table_name , i.index_id, 7 ) or
c.name = index_col ( @full_table_name , i.index_id, 8 ) or
c.name = index_col ( @full_table_name , i.index_id, 9 ) or
c.name = index_col ( @full_table_name , i.index_id, 10 ) or
c.name = index_col ( @full_table_name , i.index_id, 11 ) or
c.name = index_col ( @full_table_name , i.index_id, 12 ) or
c.name = index_col ( @full_table_name , i.index_id, 13 ) or
c.name = index_col ( @full_table_name , i.index_id, 14 ) or
c.name = index_col ( @full_table_name , i.index_id, 15 ) or
c.name = index_col ( @full_table_name , i.index_id, 16 ))
) THEN ' √ '
WHEN col.name NOT IN
(
select
COLUMN_NAME = convert (sysname,c.name)
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o
where
o. object_id = @table_id and
o. object_id = c. object_id and
o. object_id = i. object_id and
i.is_primary_key = 1 and
(c.name = index_col ( quotename ( @full_table_name ), i.index_id, 1 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 2 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 3 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 4 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 5 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 6 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 7 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 8 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 9 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 10 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 11 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 12 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 13 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 14 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 15 ) or
c.name = index_col ( quotename ( @full_table_name ), i.index_id, 16 ))
) THEN '' END AS 主键,
ty.name + ' ( ' + LTRIM ( RTRIM ( STR (col.max_length))) + ' ) ' AS [ 数据类型(精度) ] ,
CASE WHEN col.is_nullable = 1 THEN ''
WHEN col.is_nullable = 0 THEN ' NOT NULL ' END AS [ 空/非空 ] ,
'' AS 约束条件,
'' AS 默认值,
'' AS 备注,
@name AS 表名
FROM sys.all_columns AS col
INNER JOIN
(
SELECT t. object_id ,
t.name AS name
FROM sys.tables AS t
WHERE t.name = @Name
) AS t ON col. object_id = t. object_id
INNER JOIN sys.types AS ty ON col.user_type_id = ty.user_type_id
SET @i = @i + 1
END
DROP TABLE #TableNames
COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
这段代码的灵感来自于系统存储过程:sp_Pkeys,获取一个表中的主键字段。输入:sp_helptext 'sp_Pkeys'来查看源代码,我对其进修了修改,用在了我自己的存储过程中,目的就是判断一个字段是不是主键。
为了将用户选择的表传递给存储过程,创建一个用户自定义类型:SelectTables
BEGIN
CREATE TYPE SelectTables AS TABLE
(
Choice BIT NOT NULL ,
TableName VARCHAR ( 200 ) NOT NULL
)
END
GO
然后前台使用DataTable类型的参数:
{
this .__ds.Clear();
SqlConnection _cn = new SqlConnection(v_strConn);
SqlCommand _cmd = _cn.CreateCommand();
_cmd.CommandType = System.Data.CommandType.StoredProcedure;
_cmd.CommandText = " sp_生成数据结构 " ;
SqlParameter _p = _cmd.Parameters.AddWithValue( " @TableNames " , v_dt选择表);
SqlDataAdapter _da = new SqlDataAdapter(_cmd);
_da.Fill( this .__ds);
}
存储过程【sp_生成数据结构】返回的是一个数据集DataSet,DataSet中的每一个DataTable对应着一个物理表,下面就在Word中循环打印出来:
private void fn生成表格(System.Data.DataSet v_ds)
{
Word.Range r;
foreach (System.Data.DataTable dt in v_ds.Tables)
{
r = this .Application.ActiveDocument.Paragraphs.Last.Range;
r.MoveEnd();
Word.Table t = r.Tables.Add(r, dt.Rows.Count, dt.Columns.Count - 1 );
for ( int i = 1 ; i <= dt.Rows.Count; i ++ )
{
for ( int j = 1 ; j <= dt.Columns.Count - 1 ; j ++ )
{
t.Rows[i].Cells[j].Range.Text = dt.Rows[i - 1 ][j - 1 ].ToString();
}
}
t.Rows.Add();
t.Rows[t.Rows.Count].Cells[ 1 ].Range.Text = " 说明 " ;
t.Rows[t.Rows.Count].Cells[ 2 ].Range.Text = dt.Rows[ 0 ][dt.Columns.Count - 1 ].ToString() + " 表 " ;
object beforeRow = t.Rows[ 1 ];
t.Rows.Add( ref beforeRow);
t.Rows[ 1 ].Cells[ 1 ].Range.Text = " 字段名 " ;
t.Rows[ 1 ].Cells[ 2 ].Range.Text = " 名称 " ;
t.Rows[ 1 ].Cells[ 3 ].Range.Text = " 主键 " ;
t.Rows[ 1 ].Cells[ 4 ].Range.Text = " 数据类型(精度) " ;
t.Rows[ 1 ].Cells[ 5 ].Range.Text = " 空/非空 " ;
t.Rows[ 1 ].Cells[ 6 ].Range.Text = " 约束条件 " ;
t.Rows[ 1 ].Cells[ 7 ].Range.Text = " 默认值 " ;
t.Rows[ 1 ].Cells[ 8 ].Range.Text = " 备注 " ;
t.Columns[ 1 ].Width = 100 ;
t.Columns[ 2 ].Width = 100 ;
t.Columns[ 3 ].Width = 40 ;
t.Columns[ 4 ].Width = 100 ;
t.Columns[ 5 ].Width = 50 ;
t.Columns[ 6 ].Width = 20 ;
t.Columns[ 7 ].Width = 20 ;
t.Columns[ 8 ].Width = 20 ;
object title = t.Rows[ 1 ];
t.Rows.Add( ref title);
t.Rows[ 1 ].Cells[ 1 ].Range.Text = dt.Rows[ 0 ][dt.Columns.Count - 1 ].ToString();
t.Rows[ 1 ].Cells.Merge();
t.Rows[ 2 ].Select();
this .Application.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; // 垂直居中
this .Application.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter; // 水平居中
t.Rows[ 2 ].Range.Font.Bold = 2 ;
// 感谢 sunfny 的指点,用下面的方法设置表格单元格的背景颜色:
t.Rows[ 2 ].Shading.BackgroundPatternColor = Word.WdColor.wdColorSkyBlue;
t.Borders[Word.WdBorderType.wdBorderTop].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderLeft].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderRight].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderBottom].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderHorizontal].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderVertical].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Rows[ 1 ].Borders[Word.WdBorderType.wdBorderTop].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.Rows[ 1 ].Borders[Word.WdBorderType.wdBorderLeft].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.Rows[ 1 ].Borders[Word.WdBorderType.wdBorderRight].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.AllowAutoFit = true ;
this .Application.ActiveDocument.Paragraphs.Add();
}
}
Word中的Range对象比较特殊,感觉比Excel的Range难操作一些。
小结
本次在通过用户选择一个数据库,若干表的方式在Word中打印出来这些表的表结构,其中用到了很多系统表、系统存储过程,掌握这些系统对象还是很有必要的。其次我们练习了在Word中打印表格、控制显示样式的方法,也是很实用的。