S.P

导航

宏生成实体类升级版

Posted on 2012-02-01 16:48  S.P  阅读(475)  评论(0)    收藏  举报

1、新增加了在数据库备注里面设定字段名的功能

格式： [Name=ID]  用于数据库字段或者表名与实体类类名和属性名不一样的情况。

2、自定义类型功能。

格式：[Type=UserStatus] 用于枚举的字段或者其他类型

关于宏的使用请查看：http://www.cnblogs.com/sp-studio/archive/2011/05/20/VS2010_Macros.html

 

查看代码 
  1 Imports System
  2 Imports EnvDTE
  3 Imports EnvDTE80
  4 Imports EnvDTE90
  5 Imports EnvDTE90a
  6 Imports EnvDTE100
  7 Imports System.Diagnostics
  8 Imports System.Data
  9 Imports System.Data.SqlClient
 10 Imports System.Text
 11 Imports System.Text.RegularExpressions
 12 
 13 
 14 '' 把数据表生成实体类 (暂只支持SqlServer 2005以上)
 15 '' 需要放置一个 Studio.ini 的文件到需要生成实体类的项目的根目录。 内容为 DbConnection = "数据库的链接字符串"
 16 '' Author : S.P    开源社区: studio.80y.cn     交流QQ群:1809084   转载请保留此段信息
 17 Public Module SqlToEntity
 18 
 19     Private Const title As String = "SP.Studio 实体类生成工具"
 20     Private Const dbKey As String = "DbConnection = "
 21 
 22     Public Sub CreateEntity()
 23 
 24         Dim path As String = DTE.ActiveDocument.FullName
 25         Dim dbConnection As String
 26 
 27         Do While (path.Contains("\"))
 28             path = path.Substring(0, path.LastIndexOf("\"))
 29             If System.IO.File.Exists(path + "\Studio.ini") Then
 30                 path += "\Studio.ini"
 31                 Exit Do
 32             End If
 33         Loop
 34         If Not path.Contains("\") Then
 35             MsgBox("未找到配置文件。请将配置文件放入项目的根目录中。", MsgBoxStyle.Critical, title)
 36             Exit Sub
 37         End If
 38 
 39         Dim config As String = System.Text.Encoding.UTF8.GetString(System.IO.File.ReadAllBytes(path))
 40         If config.Contains(dbKey) Then
 41             config = config.Substring(config.IndexOf(dbKey) + dbKey.Length())
 42             If config.Contains(Chr(13)) Then
 43                 config = config.Substring(0, config.IndexOf(Chr(13)))
 44             End If
 45             dbConnection = config.Substring(1, config.Length - 2)
 46         Else
 47             MsgBox("配置文件中没有配置 """ + dbKey + """的的相关内容", MsgBoxStyle.Critical, title)
 48             Exit Sub
 49         End If
 50 
 51         Dim table As String = InputBox("请输入表名", title)
 52         If String.IsNullOrEmpty(table) Then
 53             MsgBox("没有输入表名", MsgBoxStyle.Critical, title)
 54             Exit Sub
 55         End If
 56 
 57         Dim code As String = System.Text.Encoding.UTF8.GetString(System.IO.File.ReadAllBytes(DTE.ActiveDocument.FullName))
 58         Dim line As Integer = DTE.ActiveDocument.Selection.CurrentLine
 59         Dim column As Integer = DTE.ActiveDocument.Selection.CurrentColumn
 60         Dim selection As EnvDTE.TextSelection = DTE.ActiveDocument.Selection
 61 
 62         DTE.ActiveDocument.Selection.MoveToLineAndOffset(1, 1)
 63         DTE.ActiveDocument.Selection.StartOfLine()
 64         DTE.ActiveDocument.Selection.LineUp()
 65 
 66         Dim names As String
 67         If Not DTE.ActiveDocument.Selection.FindText("namespace ") Then
 68             MsgBox("没有找到命名空间", MsgBoxStyle.Critical, title)
 69             Return
 70         Else
 71             selection.SelectLine()
 72             names = selection.Text()    ' 取得命名空间
 73         End If
 74 
 75         selection.SelectAll()
 76         selection.Delete()
 77 
 78         selection.Text = "using System;"
 79         selection.NewLine()
 80         line = line + 1
 81 
 82         selection.Text = "using System.Data.Linq.Mapping;"
 83         selection.NewLine()
 84         selection.NewLine()
 85         line = line + 2
 86 
 87         selection.Text = names    '命名空间
 88         selection.Text = "{"
 89         selection.NewLine()
 90         line = line + 1
 91 
 92 
 93 
 94 
 95         Dim conn As SqlConnection = New SqlConnection(dbConnection)
 96         Dim comm As SqlCommand = New SqlCommand()
 97         comm.Connection = conn
 98         comm.CommandType = CommandType.Text
 99         comm.CommandText = "SELECT 表名 = case when a.colorder=1 then d.name else '' end, 表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号 = a.colorder,  字段名 = a.name, 标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end, 主键 = 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, 类型 = b.name, 占用字节数 = a.length, 长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空     = case when a.isnullable=1 then 'true'else 'false' end, 默认值     = isnull(e.text,''), 字段说明   = 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= @TableName order by   a.id,a.colorder"
100         comm.Parameters.Add(New SqlParameter("@TableName", table))
101         Dim ds As DataSet = New DataSet()
102         Dim ada As SqlDataAdapter = New SqlDataAdapter()
103         ada.SelectCommand = comm
104         ada.Fill(ds)
105         conn.Close()
106         conn.Dispose()
107 
108         If ds.Tables().Item(0).Rows.Count() = 0 Then
109             MsgBox("表 " + table + " 不存在", MsgBoxStyle.Critical, title)
110             Exit Sub
111         End If
112 
113         Dim desc As String = ds.Tables(0).Rows(0).Item("表说明")
114 
115         Dim className As String = GetName(table, desc)
116         If Not String.IsNullOrEmpty(desc) Then
117             selection.Insert("/// <summary> " + Chr(13) + "/// " + desc + Chr(13) + "/// </summary>")
118             selection.NewLine()
119             selection.DeleteLeft(4)
120         End If
121         selection.Insert(" [Table(Name = """ + table + """)]")
122         selection.NewLine()
123         selection.Text = "public partial class " + className + " {"
124         selection.NewLine()
125 
126         Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
127 
128         For Each dr As DataRow In ds.Tables().Item(0).Rows
129             desc = dr.Item("字段说明").ToString()
130             Dim description As String = desc
131             Dim field As String = CType(dr.Item("字段名"), String)  ' 数据库字段名
132             Dim fieldset As String = field  ' 实体类字段名
133 
134             selection.NewLine()
135             If Not String.IsNullOrEmpty(desc) Then
136                 fieldset = GetName(field, desc)
137                 If Not String.IsNullOrEmpty(desc) Then
138                     desc = Replace(desc, Chr(10), " ")
139                     desc = Replace(desc, Chr(13), " ")
140                     selection.Insert("/// <summary> " + Chr(13) + "/// " + desc + Chr(13) + "/// </summary>")
141                     selection.NewLine()
142                     selection.DeleteLeft(4)
143                 End If
144             End If
145             selection.Text = String.Format("[Column(Name = ""{0}""{1}{2})]", field, IIf(CType(dr.Item("主键"), Boolean), ", IsPrimaryKey = true", ""), IIf(CType(dr.Item("标识"), Boolean), ", IsDbGenerated = true", ""))
146             selection.NewLine()
147             selection.Insert(String.Format("public {0} {1} ", GetFieldType(dr.Item("类型").ToString(), description), fieldset) + "{ get; set; }")
148             selection.NewLine()
149         Next
150 
151         selection.EndOfLine()
152         selection.Text = "}"    '类结束
153         selection.Text = "}"    '命名空间结束
154 
155     End Sub
156 
157     Public Sub CreateLinqTable()
158         DTE.ActiveDocument.Selection().SelectLine()
159         Dim tableName As String = Trim(DTE.ActiveDocument.Selection().Text)
160         Dim className As String = tableName
161         If className.Contains(".") Then
162             className = className.Substring(className.LastIndexOf(".") + 1)
163         End If
164         DTE.ActiveDocument.Selection().Text = String.Format("public Table<{0}> {1}", tableName, className)
165         DTE.ActiveDocument.Selection().NewLine()
166         DTE.ActiveDocument.Selection().Text = "{"
167         DTE.ActiveDocument.Selection().NewLine()
168         DTE.ActiveDocument.Selection().Text = "get"
169         DTE.ActiveDocument.Selection().NewLine()
170         DTE.ActiveDocument.Selection().Text = "{"
171         DTE.ActiveDocument.Selection().NewLine()
172         DTE.ActiveDocument.Selection().Text = String.Format("return this.GetTable<{0}>();", tableName)
173         DTE.ActiveDocument.Selection().NewLine()
174         DTE.ActiveDocument.Selection().Text = "}"
175         DTE.ActiveDocument.Selection().NewLine()
176         DTE.ActiveDocument.Selection().Text = "}"
177         DTE.ActiveDocument.Selection().NewLine()
178     End Sub
179 
180     Function GetFieldType(ByVal type As String, Optional ByRef desc As String = "") As String
181 
182         Dim t As String = "string"
183         Dim regex As Regex = New Regex("\[Type=(?<Type>\w+)\]", RegexOptions.IgnoreCase)
184         If regex.IsMatch(desc) Then
185             desc = desc + " "
186             t = regex.Match(desc).Groups.Item("Type").Value
187             desc = desc.Substring(desc.LastIndexOf("]") + 1).Trim()
188             Return t
189         End If
190 
191         Select Case type
192             Case "int"
193                 t = "int"
194             Case "bigint"
195                 t = "long"
196             Case "tinyint"
197                 t = "byte"
198             Case "smallint"
199                 t = "short"
200             Case "smalldatetime"
201                 t = "DateTime"
202             Case "datetime"
203                 t = "DateTime"
204         End Select
205         Return t
206     End Function
207 
208     ' 从备注中获取名字
209     Function GetName(ByVal name As String, ByRef desc As String) As String
210         Dim regex As Regex = New Regex("\[Name=(?<Field>\w+)\]", RegexOptions.IgnoreCase)
211         If regex.IsMatch(desc) Then
212             name = regex.Match(desc).Groups.Item("Field").Value
213         End If
214         desc = desc + " "
215         desc = desc.Substring(desc.LastIndexOf("]") + 1).Trim()
216         Return name
217     End Function
218 
219 
220 
221 End Module