前言
宏的定义：是组合到一起形成一个命令以自动完成某项任务的一系列命令和指令。(MSDN)
在使用Visual Studio宏实现JS折叠功能的时候就想过用它来实现代码自动生成，有了前面的基础，实现起来就不那么困难了，本文将实现根据表名自动生成相关字段属性的Model代码。
正文
一、预备
1.1 关于宏的新建以及简单用法，请参见这里。
1.2 环境 Microsoft Visual Studio 2008、Microsoft SQL Server 2000
二、目的
根据类名(类名和表名须一致)自动生成列名、字段说明、属性(Properties)、构造函数。
三、实现步骤
3.1 准备测试用表结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[User]
GO
CREATE TABLE [dbo].[User] (
[UniqueID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[UserPermission_Id] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User] ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UniqueID]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'姓名', N'user', N'dbo', N'table', N'User', N'column', N'Name'
GO
exec sp_addextendedproperty N'MS_Description', N'密码', N'user', N'dbo', N'table', N'User', N'column', N'Password'
GO
exec sp_addextendedproperty N'MS_Description', N'用户名', N'user', N'dbo', N'table', N'User', N'column', N'Username'
GO
exec sp_addextendedproperty N'MS_Description', N'用户权限', N'user', N'dbo', N'table', N'User', N'column', N'UserPermission_Id'
GO
3.2 准备获取表结构帮助类(C#)
3.2.1 新建项目 -> 类库，项目名称：SqlSchemaProvider
2.2.2 数据传输类ColumnInfo.cs
using System;
using System.Collections.Generic;
using System.Text;
/// <summary>
/// 字段信息
/// </summary>
public sealed class ColumnInfo
{
#region Member Variable
private string name;
private string desc;
private string type;
#endregion
#region Constructor
public ColumnInfo(string name, string type, string desc)
{
this.name = name;
this.desc = desc;
this.type = type;
}
#endregion
#region Properties
/// <summary>
/// 列名
/// </summary>
public string Name
{
get { return name; }
set { name = value; }
}
/// <summary>
/// 列说明
/// </summary>
public string Description
{
get { return desc; }
set { desc = value; }
}
/// <summary>
/// 数据类型(已经转换为C#)
/// </summary>
public string Type
{
get { return type; }
set { type = value; }
}
#endregion
}
2.2.3 元数据获取帮助类SqlSchemaProvider.cs ，注意这里使用了SqlHelper.cs！
//==============================================================================
//
// 作 者：农民伯伯
// 邮 箱：over140@gmail.com
// 博 客：http://over140.cnblogs.com/
// 时 间：2009-6-24
// 描 述：获取SQL SERVER 元数据
//
//==============================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
public sealed class SqlSchemaProvider
{
#region GetTableColumns
public ColumnInfo[] GetTableColumns(string connectstring, string tableName)
{
List<ColumnInfo> result = new List<ColumnInfo>();
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(connectstring);
using (SqlDataReader reader = SqlHelper.ExecuteReader(scsb.ConnectionString, CommandType.Text, SQL2000_GetTableColumns,
new SqlParameter("@DatabaseName", scsb.InitialCatalog),
new SqlParameter("@SchemaName", "dbo"),
new SqlParameter("@TableName", tableName)))
{
while (reader.Read())
{
result.Add(new ColumnInfo(reader.GetString(0), GetCSharpType(reader.GetString(1)), reader.GetString(17)));
}
}
return result.ToArray();
}
#region Type Maps
private string GetCSharpType(string type)
{
if (string.IsNullOrEmpty(type))
return "string";
string reval = string.Empty;
switch (type.ToLower())
{
case "varchar":
case "nchar":
case "ntext":
case "text":
case "char":
case "nvarchar":
reval = "string";
break;
case "int":
reval = "int";
break;
case "smallint":
reval = "Int16";
break;
case "bigint":
reval = "Int64";
break;
case "float":
reval = "double";
break;
case "bit":
reval = "bool";
break;
case "decimal":
case "smallmoney":
case "money":
case "numeric":
reval = "decimal";
break;
case "binary":
reval = "System.Byte[]";
break;
case "real":
reval = "System.Single";
break;
case "datetime":
case "smalldatetime":
case "timestamp":
reval = "System.DateTime";
break;
case "tinyint":
reval = "System.Byte";
break;
case "uniqueidentifier":
reval = "System.Guid";
break;
case "image":
case "varbinary":
reval = "System.Byte[]";
break;
case "Variant":
reval = "Object";
break;
default:
reval = "string";
break;
}
return reval;
}
#endregion
#endregion
#region SQL Templates
#region GetTableColumns
private const string SQL2000_GetTableColumns = @"
SELECT
clmns.[name] AS [Name],
usrt.[name] AS [DataType],
ISNULL(baset.[name], N'') AS [SystemType],
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT) AS [Length],
CAST(clmns.xprec AS TINYINT) AS [NumericPrecision],
CAST(clmns.xscale AS INT) AS [NumericScale],
CASE CAST(clmns.isnullable AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
defaults.text AS [DefaultValue],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS INT) AS [Identity],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS INT) AS IsRowGuid,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS INT) AS IsComputed,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS INT) AS IsDeterministic,
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentitySeed],
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentityIncrement],
cdef.[text] AS ComputedDefinition,
clmns.[collation] AS Collation,
CAST(clmns.colid AS int) AS ObjectId,
isnull(prop.value, '') AS ColumnDesc
FROM
dbo.sysobjects AS tbl
INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
LEFT OUTER JOIN sysproperties prop ON clmns.id = prop.id AND clmns.colid = prop.smallid
WHERE
(tbl.[type] = 'U' OR tbl.[type] = 'S')
AND stbl.[name] = 'dbo'
AND tbl.[name] = @TableName
ORDER BY
clmns.colorder";
#endregion
#endregion
}
3.3 宏中引用帮助类
3.3.1 由于不能直接用绝对路径添加dll，所以需要将SqlSchemaProvider.dll拷贝到<安装目录>\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies
3.3.2 引用System.Data.dll和SqlSchemaProvider.dll
3.4 编写宏代码
Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics
Imports System.Collections.Generic
'============================================================================
'
' 作 者：农民伯伯
' 邮 箱：over140@gmail.com
' 博 客：http://over140.cnblogs.com/
' 时 间：2009-6-24
' 描 述：自动生成Model宏
'
'============================================================================
Public Module AutoModel
Dim selection As EnvDTE.TextSelection
Sub GenerateModel()
Dim schemaProvide As SqlSchemaProvider = New SqlSchemaProvider()
Const ConnStr As String = "Data Source=.;Initial Catalog=DBName;User ID=sa;Password=sa;"
Dim tableName As String
Dim columns() As ColumnInfo '用于存放字段信息
Dim line As Integer
selection = DTE.ActiveDocument.Selection
tableName = selection.Text
'------------------------------------------------------验证
If String.IsNullOrEmpty(tableName) Then
MsgBox("请选择要表名！", MsgBoxStyle.OkOnly)
Return
End If
'取得所有字段
columns = schemaProvide.GetTableColumns(ConnStr, tableName)
If columns.Length = 0 Then
MsgBox("表不存在或该表没有字段！", MsgBoxStyle.OkOnly)
Return
End If
'移动当前行位置，添加占位行
line = selection.ActivePoint.Line + 2
selection.GotoLine(line)
selection.NewLine(columns.Length * 2)
selection.GotoLine(line)
'------------------------------------------------------成员变量
NewLineInsert("#region Member Variable")
selection.NewLine(2)
For Each column As ColumnInfo In columns
InsertNewLine(String.Format("private {0} {1};", column.Type, column.Name.ToLower()))
Next
NewLineInsert("#endregion")
selection.NewLine()
'------------------------------------------------------构造函数
NewLineInsert("#region Constructor")
selection.NewLine()
NewLineInsert(String.Format("public {0}()", tableName))
NewLineInsert("{")
NewLineInsert("}")
NewLineInsert("#endregion")
selection.NewLine()
'------------------------------------------------------字段
NewLineInsert("#region 字段名称")
selection.NewLine(2)
For Each column As ColumnInfo In columns
InsertNewLine("/// <summary>")
If (String.IsNullOrEmpty(column.Description)) Then
InsertNewLine("没有字段说明")
Else
InsertNewLine(column.Description)
End If
InsertNewLine("</summary>")
selection.GotoLine(selection.AnchorPoint.Line)
selection.SelectLine()
InsertNewLine(String.Format("public const string CN_{0} = ""{0}"";", column.Name))
selection.NewLine()
Next
InsertNewLine("#endregion")
'------------------------------------------------------属性(Properties)
NewLineInsert("#region Properties")
selection.NewLine(2)
For Each column As ColumnInfo In columns
InsertNewLine("/// <summary>")
If (String.IsNullOrEmpty(column.Description)) Then
InsertNewLine("没有字段说明")
Else
InsertNewLine(column.Description)
End If
InsertNewLine("</summary>")
selection.GotoLine(selection.AnchorPoint.Line)
selection.SelectLine()
InsertNewLine(String.Format("public {0} {1}", column.Type, System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(column.Name)))
InsertNewLine("{")
InsertNewLine(String.Concat("get { return ", column.Name.ToLower(), "; }"))
InsertNewLine(String.Concat("set { ", column.Name.ToLower(), " = value; }"))
InsertNewLine("}")
selection.NewLine()
Next
NewLineInsert("#endregion")
selection.NewLine()
'格式化文档
'DTE.ExecuteCommand("Edit.FormatDocument")
DTE.ExecuteCommand("编辑.设置文档的格式")
'折叠代码
DTE.ExecuteCommand("编辑.折叠到定义")
End Sub
Sub NewLineInsert(ByVal code As String)
'相当于在编辑器内按Enter键
selection.NewLine()
selection.Insert(code)
End Sub
Sub InsertNewLine(ByVal code As String)
selection.Insert(code)
selection.NewLine()
End Sub
End Module
代码说明：
a). 如果不熟悉VBA编程建议尽量将逻辑代码、访问数据库代码用C#写成类库再引用进来调用。
b). 注意连接数据库字符串ConnStr需要替换成自己的数据库连接字符串！
c). 输出代码的排版并不全是简单的一行输出一个字串就行了，需要模拟你在编辑器中输入代码的同时VS自动生成的代码，比如在一个属性(Property)上行输入"///"他就会自动给你生成注释，如果你在后面的行还输出"/// 说明"那就会出错了，格式也乱了，所以需要特别注意！
d). 宏最后有"DTE.ExecuteCommand("编辑.设置文档的格式")"这样的代码，这个用于直接调用工具栏里面功能，这里写英文的行，写中文的也行:)
3.5 设置运行宏
3.5.1 设置宏快捷键(参照前文)，这里设置为Ctrl+G、Ctrl+M
3.5.2 新建类User，注意类名和表名须一致
class User
{
}
3.5.3 选中类名User，快捷键Ctrl+G、Ctrl+M运行宏，生成如下:
展开部分代码
结束语
曾做过一次对日外包的项目，印象最深的是他们Excel用得非常好，用来写式样书、写测试报告，用Excel中的宏来生成数据库、生成测试数据等复杂的操作，非常强悍，至此也算小小的满足了一下羡慕的心理: )
转载：http://www.cnblogs.com/over140/archive/2009/06/25/1510162.html