Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports EnvDTE90a
Imports EnvDTE100
Imports System.Diagnostics
Imports System.Data
Imports System.Data.SqlClient

'' 把数据表生成实体类 (暂只支持SqlServer 2005以上)
'' 需要放置一个 Studio.ini 的文件到需要生成实体类的项目的根目录。 内容为 DbConnection = "数据库的链接字符串"
'' Author : S.P    开源社区: studio.80y.cn     交流QQ群:1809084   转载请保留此段信息
Public Module SqlToEntity

    Private Const title As String = "SP.Studio 实体类生成工具"
    Private Const dbKey As String = "DbConnection = "

    Public Sub CreateEntity()

        Dim path As String = DTE.ActiveDocument.FullName
        Dim dbConnection As String

        Do While (path.Contains("\"))
            path = path.Substring(0, path.LastIndexOf("\"))
            If System.IO.File.Exists(path + "\Studio.ini") Then
                path += "\Studio.ini"
                Exit Do
            End If
        Loop
        If Not path.Contains("\") Then
            MsgBox("未找到配置文件。请将配置文件放入项目的根目录中。", MsgBoxStyle.Critical, title)
            Exit Sub
        End If

        Dim config As String = System.Text.Encoding.UTF8.GetString(System.IO.File.ReadAllBytes(path))
        If config.Contains(dbKey) Then
            config = config.Substring(config.IndexOf(dbKey) + dbKey.Length())
            If config.Contains(Chr(13)) Then
                config = config.Substring(0, config.IndexOf(Chr(13)))
            End If
            dbConnection = config.Substring(1, config.Length - 2)
        Else
            MsgBox("配置文件中没有配置 """ + dbKey + """的的相关内容", MsgBoxStyle.Critical, title)
            Exit Sub
        End If

        Dim table As String = InputBox("请输入表名", title)
        If String.IsNullOrEmpty(table) Then
            MsgBox("没有输入表名", MsgBoxStyle.Critical, title)
            Exit Sub
        End If

        Dim code As String = System.Text.Encoding.UTF8.GetString(System.IO.File.ReadAllBytes(DTE.ActiveDocument.FullName))
        Dim line As Integer = DTE.ActiveDocument.Selection.CurrentLine
        Dim column As Integer = DTE.ActiveDocument.Selection.CurrentColumn

        DTE.ActiveDocument.Selection.MoveToLineAndOffset(1, 1)
        If Not DTE.ActiveDocument.Selection.FindText("using System.Data.Linq.Mapping;") Then
            DTE.ActiveDocument.Selection.FindText(Chr(10) + "namespace ")
            DTE.ActiveDocument.Selection.StartOfLine()
            DTE.ActiveDocument.Selection.LineUp()
            DTE.ActiveDocument.Selection.Text = "using System.Data.Linq.Mapping;"
            DTE.ActiveDocument.Selection.NewLine()
            line = line + 1
        End If

        If Not DTE.ActiveDocument.Selection.FindText("[Table(Name") Then
            If DTE.ActiveDocument.Selection.FindText("class") Then
                DTE.ActiveDocument.Selection.MoveTo(DTE.ActiveDocument.Selection.CurrentLine, DTE.ActiveDocument.Selection.CurrentColumn - "class".Length)
                DTE.ActiveDocument.Selection.Insert("public ")
                DTE.ActiveDocument.Selection.LineUp()
                DTE.ActiveDocument.Selection.NewLine()
                DTE.ActiveDocument.Selection.Insert("[Table(Name = """ + table + """)]")
                line = line + 1
            End If
        End If

        If DTE.ActiveDocument.Selection.FindText("class") Then
            DTE.ActiveDocument.Selection.FindText("{")
            DTE.ActiveDocument.Selection.EndOfLine()
        End If


        Dim conn As SqlConnection = New SqlConnection(dbConnection)
        Dim comm As SqlCommand = New SqlCommand()
        comm.Connection = conn
        comm.CommandType = CommandType.StoredProcedure
        comm.CommandText = "sp_GetTableColumn"
        comm.Parameters.Add(New SqlParameter("@TableName", table))
        Dim ds As DataSet = New DataSet()
        Dim ada As SqlDataAdapter = New SqlDataAdapter()
        ada.SelectCommand = comm
        ada.Fill(ds)
        conn.Close()
        conn.Dispose()

        If ds.Tables().Item(0).Rows.Count() = 0 Then
            MsgBox("表 " + table + " 不存在", MsgBoxStyle.Critical, title)
            Exit Sub
        End If


        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()


        For Each dr As DataRow In ds.Tables().Item(0).Rows
            Dim desc As String = dr.Item("字段说明").ToString()
            Dim field As String = CType(dr.Item("字段名"), String)
            If Not code.Contains("Column(Name = """ + field + """") Then
                DTE.ActiveDocument.Selection.NewLine()
                If Not String.IsNullOrEmpty(desc) Then
                    desc = Replace(desc, Chr(10), " ")
                    desc = Replace(desc, Chr(13), " ")
                    DTE.ActiveDocument.Selection.Text = "/// <summary>"
                    DTE.ActiveDocument.Selection.NewLine()
                    DTE.ActiveDocument.Selection.Text = desc
                    DTE.ActiveDocument.Selection.NewLine()
                    DTE.ActiveDocument.Selection.Text = "</summary>"
                    DTE.ActiveDocument.Selection.NewLine()
                    DTE.ActiveDocument.Selection.DeleteLeft(4)
                End If
                DTE.ActiveDocument.Selection.Text = String.Format("[Column(Name = ""{0}""{1}{2})]", field, IIf(CType(dr.Item("主键"), Boolean), ", IsPrimaryKey = true", ""), IIf(CType(dr.Item("标识"), Boolean), ", IsDbGenerated = true", ""))
                DTE.ActiveDocument.Selection.NewLine()
                DTE.ActiveDocument.Selection.Text = String.Format("public {0} {1} ", GetFieldType(dr.Item("类型").ToString()), field) + "{ get; set; }"
                DTE.ActiveDocument.Selection.NewLine()
            End If
        Next

    End Sub

    Function GetFieldType(ByVal type As String) As String
        Dim t As String = "string"
        Select Case type
            Case "int"
                t = "int"
            Case "bigint"
                t = "long"
            Case "tinyint"
                t = "byte"
            Case "smalldatetime"
            Case "datetime"
                t = "DateTime"
        End Select
        Return t
    End Function

End Module