使用Visual Studio宏來自動生成代碼 [ Visual Studio | 宏

一、預備

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]

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]

ALTER TABLE [dbo].[User] ADD

CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

(

[UniqueID]

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'姓名', N'user', N'dbo', N'table', N'User', N'column', N'Name'

exec sp_addextendedproperty N'MS_Description', N'密碼', N'user', N'dbo', N'table', N'User', N'column', N'Password'

exec sp_addextendedproperty N'MS_Description', N'使用者名', N'user', N'dbo', N'table', N'User', N'column', N'Username'

exec sp_addextendedproperty N'MS_Description', N'使用者權限', N'user', N'dbo', N'table', N'User', N'column', N'UserPermission_Id'

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;

}

#region Properties

/// <summary>

/// 列名

/// </summary>

public string Name

get { return name; }

set { name = value; }

/// 列說明

public string Description

get { return desc; }

set { desc = value; }

/// 資料類型(已經轉換為C#)

public string Type

get { return type; }

set { type = value; }

}

2.2.3 中繼資料擷取幫助類SqlSchemaProvider.cs ，注意這裡使用了SqlHelper.cs！

//==============================================================================

// 作者：農民伯伯

// 郵箱：[email protected]

// 博客：http://over140.cnblogs.com/

// 時間：2009-6-24

// 描述：擷取SQL SERVER 中繼資料

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";

case "smallint":

reval = "Int16";

case "bigint":

reval = "Int64";

case "float":

reval = "double";

case "bit":

reval = "bool";

case "decimal":

case "smallmoney":

case "money":

case "numeric":

reval = "decimal";

case "binary":

reval = "System.Byte[]";

case "real":

reval = "System.Single";

case "datetime":

case "smalldatetime":

case "timestamp":

reval = "System.DateTime";

case "tinyint":

reval = "System.Byte";

case "uniqueidentifier":

reval = "System.Guid";

case "image":

case "varbinary":

case "Variant":

reval = "Object";

default:

return reval;

}

#region SQL Templates

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";

3.3 宏中引用幫助類

3.3.1 由于不能直接用絕對路徑添加dll，是以需要将SqlSchemaProvider.dll拷貝到<安裝目錄>\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies

3.3.2 引用System.Data.dll和SqlSchemaProvider.dll

使用Visual Studio宏來自動生成代碼 [ Visual Studio | 宏 | 自動生成代碼 ]

3.4 編寫宏代碼

Imports System

Imports EnvDTE

Imports EnvDTE80

Imports EnvDTE90

Imports System.Diagnostics

Imports System.Collections.Generic

'============================================================================

' 作者：農民伯伯

' 郵箱：[email protected]

' 博客：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)

'移動目前行位置，添加占位行

line = selection.ActivePoint.Line + 2

selection.GotoLine(line)

selection.NewLine(columns.Length * 2)

'------------------------------------------------------成員變量

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()))

NewLineInsert("#endregion")

selection.NewLine()

'------------------------------------------------------構造函數

NewLineInsert("#region Constructor")

NewLineInsert(String.Format("public {0}()", tableName))

NewLineInsert("{")

NewLineInsert("}")

'------------------------------------------------------字段

NewLineInsert("#region 字段名稱")

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()

InsertNewLine("#endregion")

'------------------------------------------------------屬性(Properties)

NewLineInsert("#region Properties")

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("}")

'格式化文檔

'DTE.ExecuteCommand("Edit.FormatDocument")

DTE.ExecuteCommand("編輯.設定文檔的格式")

'折疊代碼

DTE.ExecuteCommand("編輯.折疊到定義")

End Sub

Sub NewLineInsert(ByVal code As String)

'相當于在編輯器内按Enter鍵

selection.Insert(code)

Sub InsertNewLine(ByVal code As String)

End Module

代碼說明：

a). 如果不熟悉VBA程式設計建議盡量将邏輯代碼、通路資料庫代碼用C#寫成類庫再引用進來調用。

b). 注意連接配接資料庫字元串ConnStr需要替換成自己的資料庫連接配接字元串！

c). 輸出代碼的排版并不全是簡單的一行輸出一個字串就行了，需要模拟你在編輯器中輸入代碼的同時VS自動生成的代碼，比如在一個屬性(Property)上行輸入"///"他就會自動給你生成注釋，如果你在後面的行還輸出"/// 說明"那就會出錯了，格式也亂了，是以需要特别注意！

d). 宏最後有"DTE.ExecuteCommand("編輯.設定文檔的格式")"這樣的代碼，這個用于直接調用工具欄裡面功能，這裡寫英文的行，寫中文的也行:)

3.5 設定運作宏

3.5.2 建立類User，注意類名和表名須一緻

class User

3.5.3 選中類名User，快捷鍵Ctrl+G、Ctrl+M運作宏，生成如下:

　　　　　　　　展開部分代碼

使用Visual Studio宏來自動生成代碼 [ Visual Studio | 宏 | 自動生成代碼 ]

繼續閱讀

Unity3D中背景音樂和相關音效的添加，背景音樂暫停、停止和播放的設定

Unity遊戲開發背景知識

vs編譯完提示不支援嘗試的執行操作

【C# MySQL】第一次使用C#對MySQL進行操作的心得

C#基礎——與C#的第一次邂逅聲明：IDE：第一個C#果實：Console淺析：總結：

英文月日年日期格式的實作

C# 位元組數組 byte[] 快速比較算法

C# 位元組數組、字元串轉化位元組數組轉字元串字元串轉位元組數組位元組數組轉整數整數轉位元組數組字元串轉位元組字元串轉數值數值轉字元串

C#用Zlib壓縮或解壓縮位元組數組

C# 比較兩個位元組數組是否相等

對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1

《C#進階程式設計》讀書筆記 -索引

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

C#多線程——前台線程和背景線程

C#+ArcEngine代碼檢測ArcEngine Runtime是否安裝以及是否授權

七牛雲-C#SDK-上傳-前期準備