使用Visual Studio宏来自动生成代码 [ Visual Studio | 宏 | 自动生成代码 ]

前言

宏的定义：是组合到一起形成一个命令以自动完成某项任务的一系列命令和指令。(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] = 'User'
              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