使用GridView顯示Sql資料庫的Table Layout(Schema)

2008-05-28

35548
0
(.NET)ASP.NET
2008-05-31

小喵在開發系統過程中，經常會去查詢資料庫Table的Layout，一般來說會把他做成額外的文件，例如用Excel把Table的Layout打在上面，不過這個會有個問題，當資料庫Layout變動的時候，如果忘記去修改Excel文件中的Layout，那麼久而久之就會造成困擾(看到的文件與實際資料庫的不符合)

因此小喵特別設計一個

因此小喵特別設計一個ASP.NET的畫面用來查詢資料庫各Table的Layout，基本上符合以下幾個需求

自動列出指定資料庫(DB)的所有Table
可以設定Table的中文名稱，描述Table的用途
點選Table名稱，可以列出該Table的Layout
將Table各欄位的說明直接寫在資料表設計時的【描述】裡面
可以對欄位的描述加上註解

以下先來看看執行的畫面：

首先看列出資料庫中所有資料表的部分

接著看點選單一資料表名稱，顯示該資料表的Layout

(在畫面上，會將主索引的欄位使用不同顏色顯示)

在資料庫中，會使用【描述】這個欄位來存放【欄位中文名】與【備註】，使用;區隔

接著就來看看程式方面如何處理

小喵先重點的描述一些關鍵的部分

如何取得某資料庫中所有的資料表
如何取得某資料表的Schema
描述的部分：在資料庫中存在於另外的地方--Extended Property
取得主索引

◎資料庫中所有資料表：透過內定的Stored Procedure sp_tables


EXEC sp_tables  
	@table_name = '%', 
	@table_owner = 'dbo', 
	@table_qualifier = @DBName;

◎取得資料表Schema


sp_columns @TableName

◎取得欄位說明、備註(Extended Property)：這個是透過Sql內定的Function來處理


SELECT * 
FROM  ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default)

◎取得主索引

sp_pkeys @TableName

透過以上這些關鍵的技術，就能夠達到

最後小喵把相關的程式附上，特別說明一下，由於小喵是透過ObjectDataSouce去存取Complus的方式進行的，因此各位實際應用的時候，請您再擷取適當的部分程式碼。

畫面的部分：


    <asp:ScriptManager id="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
        DataSourceID="ObjectDataSource1" ForeColor="#333333" DataKeyNames="Tbl" BorderColor="Black" Font-Size="Small" PageSize="15">
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <Columns>
            <asp:CommandField ShowSelectButton="True" SelectText="Layout" ShowEditButton="True" HeaderText="控制" />
            <asp:BoundField DataField="Tbl" HeaderText="資料表" ReadOnly="True" />
            <asp:BoundField DataField="TblName" HeaderText="資料表名稱" />
            <asp:BoundField DataField="MEMO" HeaderText="備註" />
        </Columns>
        <RowStyle BackColor="#EFF3FB" BorderColor="DimGray" BorderStyle="Solid" />
        <EditRowStyle BackColor="#2461BF" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>
    <br />
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetTableList"
        TypeName="CCATTBLObj" UpdateMethod="ChgTblData">
        <UpdateParameters>
            <asp:Parameter Name="Tbl" Type="String" />
            <asp:Parameter Name="TblName" Type="String" />
            <asp:Parameter Name="MEMO" Type="String" />
            <asp:Parameter Name="User" Type="String" />
        </UpdateParameters>
    </asp:ObjectDataSource>
    <div id="divLayout" runat="server" visible="false">
        <asp:Button ID="btnSltTable" runat="server" Text="選取其他資料表" />
        <table border="1" class="tbl1">
            <tr class="dh">
                <th>
                    資料表</th>
                <th>
                    資料表名稱</th>
                <th style="width: 78px">
                    備註</th>
            </tr>
            <tr class="dl">
                <td>
                    <asp:Label ID="lblTbl" runat="server" Text=""></asp:Label>
                </td>
                <td>
                    <asp:Label ID="lblTblName" runat="server" Text=""></asp:Label>
                </td>
                <td style="width: 78px">
                    <asp:Label ID="lblMEMO" runat="server" Text=""></asp:Label>
                </td>
            </tr>
        </table>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BorderColor="Black"
            CellPadding="4" DataSourceID="ObjectDataSource2" Font-Size="Small" ForeColor="#333333">
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="SENC" HeaderText="順號" />
                <asp:BoundField DataField="PK" HeaderText="主索引" />
                <asp:BoundField DataField="Fld" HeaderText="欄位" />
                <asp:BoundField DataField="FldName" HeaderText="欄位中文名" />
                <asp:BoundField DataField="DataType" HeaderText="資料型態" />
                <asp:BoundField DataField="DataSize" HeaderText="資料大小" />
                <asp:BoundField DataField="DecimalSize" HeaderText="小數位數" />
                <asp:BoundField DataField="NULLAble" HeaderText="是否允許NULL" />
                <asp:BoundField DataField="DefaultValue" HeaderText="預設值" />
                <asp:BoundField DataField="MEMO" HeaderText="備註" />
            </Columns>
            <RowStyle BackColor="#EFF3FB" />
            <EditRowStyle BackColor="#2461BF" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" SelectMethod="GetTableLayout"
            TypeName="CCATTBLObj">
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="TableName" PropertyName="SelectedValue"
                    Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
    </div>
    <asp:Label ID="lblErrMsg" runat="server" ForeColor="Red"></asp:Label><br />

CodeFile


    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Dim Tbl As String = Me.GridView1.SelectedValue.ToString
        Me.lblTbl.Text = Tbl
        If Tbl <> "" Then

            Dim obj As Object = CreateObject("PCATTBL.CCATTBL1")
            Try
                Dim P As String = ""
                Dim R As String = ""
                Dim Ds As New DataSet
                Dim tmpTblName As String
                Dim tmpMEMO As String

                Dim rc As String = obj.SelectTBL(Tbl, Ds, P, R)

                If Ds.Tables(0).Rows.Count > 0 Then
                    Me.divLayout.Visible = True
                    Me.GridView1.Visible = False
                    tmpTblName = Ds.Tables(0).Rows(0).Item("TblName")
                    tmpMEMO = Ds.Tables(0).Rows(0).Item("MEMO")
                    'Me.lblTbl.Text = Tbl
                    Me.lblTblName.Text = tmpTblName
                    Me.lblMEMO.Text = tmpMEMO

                End If

            Catch ex As Exception
                Me.lblErrMsg.Text = ex.Message.ToString

            Finally
                obj.Dispose()
                obj = Nothing

            End Try
        End If

    End Sub

    Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView2.RowDataBound
        If e.Row.Cells(1).Text = "PK" Then
            e.Row.BackColor = Drawing.Color.Pink
        End If
    End Sub

    Protected Sub btnSltTable_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSltTable.Click
        Me.divLayout.Visible = False
        Me.GridView1.Visible = True
    End Sub

    Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.lblErrMsg.Text = ""
    End Sub

Class的部分


Imports Microsoft.VisualBasic
Imports System.Data


Public Class CCATTBLObj

    Dim m_Tbl As String
    Dim m_TblName As String
    Dim m_MEMO As String
    Public Property Tbl() As String
        Get
            Return m_Tbl
        End Get
        Set(ByVal value As String)
            m_Tbl = value
        End Set
    End Property
    Public Property TblName() As String
        Get
            Return m_TblName
        End Get
        Set(ByVal value As String)
            m_TblName = value
        End Set
    End Property
    Public Property MEMO() As String
        Get
            Return m_MEMO
        End Get
        Set(ByVal value As String)
            m_MEMO = value
        End Set
    End Property

    Public Function GetTableLayout(ByVal TableName As String) As DataTable

        Dim Dt As DataTable
        Using obj As Object = CreateObject("PCATTBL.CCATTBL1")
            Dim P As String = ""
            Dim R As String = ""
            Try
                Dt = obj.GetTableLayout(TableName, P, R)

            Catch ex As Exception
                Throw

            Finally

            End Try
        End Using
        Return Dt
    End Function

    Public Function GetTableList() As DataTable
        Dim Dt As DataTable
        Using obj As Object = CreateObject("PCATTBL.CCATTBL1")
            Dim P As String = ""
            Dim R As String = ""
            Try
                Dt = obj.GetTblList("", P, R)

            Catch ex As Exception
                Throw

            Finally

            End Try
        End Using
        Return Dt
    End Function

    Public Function ChgTblData(ByVal Tbl As String, ByVal TblName As String, ByVal MEMO As String, ByVal User As String) As String
        Using obj As Object = CreateObject("PCATTBL.CCATTBL2")
            Dim P As String = ""
            Dim R As String = ""
            If MEMO Is Nothing Then
                MEMO = ""
            End If
            Try
                Dim rc = obj.ChgTbl(Tbl, TblName, MEMO, P, R, User)
                Return rc

            Catch ex As Exception
                Throw

            Finally

            End Try
        End Using
    End Function
End Class

最後是COM+中的Class，以這個範例，

PCATTBL.CCATTBL1


Imports System.EnterpriseServices
Imports System.Runtime.InteropServices
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
<Guid("EF8D9AA8-11E5-46FD-B7F2-6FD0DB37168C"), _
EventTrackingEnabled(True)> _
Public Class CCATTBL1
    Inherits ServicedComponent
    Const DBName As String = "DCAT"
    Public Function GetConnStr(ByVal DBName As String) As String
        '*************************************************************************
        '**     撰寫者：小喵(topcat)     撰寫日期：20080507
        '**     用途：  1.讀取Connection String的方式
        '**     做法：
        '**             1.傳入參數DataBase Name DBName
        '**             2.透過Stream將文字檔讀出
        '**             3.將取得的資料傳回
        '**             4.關閉相關物件
        '**     注意事項：
        '**             1.
        '**             2.
        '**     維護記錄：
        '**         維護者：姓名(員工代號)     維護日期：日期
        '**         維護項目：
        '**                 1.
        '**                 2.
        '**         做法：  1.
        '**                 2.
        '**         注意事項：
        '**                 1.
        '*************************************************************************
        Dim StrmRd As New StreamReader("C:\DataLink\" + DBName + ".ini")
        Dim Line As String = ""
        Dim ConnStr As String = ""
        Try
            Do
                Line = StrmRd.ReadLine()
                If Line <> "" Then
                    ConnStr += Line
                End If
            Loop Until Line Is Nothing
            GetConnStr = ConnStr
        Catch ex As Exception
            Throw New Exception(ex.Message.ToString)
        Finally
            StrmRd.Close()
            StrmRd.Dispose()
            StrmRd = Nothing
        End Try
    End Function
    Public Function GetTblList(ByVal In1 As String, ByRef P As String, ByRef R As String) As DataTable
        '*************************************************************************
        '**     撰寫者：小喵(topcat)     撰寫日期：20080507
        '**     用途：  1.列出資料庫中所有資料表
        '**     做法：
        '**             1.列出本系統資料庫中所有資料表
        '**             2.比對TBL資料表，設定資料表名稱、備註
        '**             3.傳回DataTable
        '**             4.
        '**     注意事項：
        '**             1.
        '**             2.
        '**     維護記錄：
        '**         維護者：姓名(員工代號)     維護日期：日期
        '**         維護項目：
        '**                 1.
        '**                 2.
        '**         做法：  1.
        '**                 2.
        '**         注意事項：
        '**                 1.
        '*************************************************************************
        Dim ConnStr As String = GetConnStr(DBName)
        Dim Dt1 As New DataTable
        Dt1.Columns.Add("Tbl", Type.GetType("System.String"))
        Dt1.Columns.Add("TblName", Type.GetType("System.String"))
        Dt1.Columns.Add("MEMO", Type.GetType("System.String"))
        Using Conn As New SqlConnection(ConnStr)
            Conn.Open()
            Dim SqlTxt As String = ""
            Dim Cmmd As SqlCommand
            Dim Dr As SqlDataReader
            '**             1.列出本系統資料庫中所有資料表
            SqlTxt += " EXEC sp_tables  "
            SqlTxt += "    @table_name = '%', "
            SqlTxt += "    @table_owner = 'dbo', "
            SqlTxt += "    @table_qualifier = @DBName; "
            SqlTxt += "  "
            Cmmd = New SqlCommand(SqlTxt, Conn)
            Cmmd.Parameters.AddWithValue("@DBName", DBName)
            Dr = Cmmd.ExecuteReader
            Dim tmpTblName As String = ""
            Dim tmpRw As DataRow
            If Dr.HasRows Then
                While Dr.Read
                    tmpTblName = Dr.Item("TABLE_NAME")
                    tmpRw = Dt1.NewRow
                    tmpRw.Item("Tbl") = tmpTblName
                    Dt1.Rows.Add(tmpRw)
                End While
            End If
            Dr.Close()
            '**             2.比對TBL資料表，設定資料表名稱、備註
            SqlTxt = ""
            SqlTxt += " SELECT * "
            SqlTxt += " FROM TCATTBL WITH (NOLOCK) "
            SqlTxt += "  "
            Cmmd = New SqlCommand(SqlTxt, Conn)
            Dr = Cmmd.ExecuteReader
            Dim rws() As DataRow
            If Dr.HasRows Then
                While Dr.Read
                    tmpTblName = Dr.Item("Tbl")
                    rws = Dt1.Select("Tbl = '" + tmpTblName + "'")
                    rws(0).Item("TblName") = Dr.Item("TblName")
                    rws(0).Item("MEMO") = Dr.Item("MEMO")
                End While
            End If
            Dr.Close()
            '**             3.傳回DataTable
            Conn.Close()
        End Using
        Return Dt1
    End Function
    Public Function GetTableLayout(ByVal TableName As String, ByRef P As String, ByRef R As String) As DataTable
        '*************************************************************************
        '**     撰寫者：小喵(topcat)     撰寫日期：20080507
        '**     用途：  1.列出指定資料表的Layout
        '**     做法：
        '**             1.
        '**             2.
        '**             3.
        '**             4.
        '**     注意事項：
        '**             1.
        '**             2.
        '**     維護記錄：
        '**         維護者：姓名(員工代號)     維護日期：日期
        '**         維護項目：
        '**                 1.
        '**                 2.
        '**         做法：  1.
        '**                 2.
        '**         注意事項：
        '**                 1.
        '*************************************************************************
        '建立回傳的DataTable
        Dim Dt As New DataTable
        Dim y As Integer = 0
        '設定Dt欄位
        Dt.Columns.Add("SENC", Type.GetType("System.Decimal"))
        Dt.Columns.Add("PK", Type.GetType("System.String"))
        Dt.Columns.Add("Fld", Type.GetType("System.String"))
        Dt.Columns.Add("FldName", Type.GetType("System.String"))
        Dt.Columns.Add("DataType", Type.GetType("System.String"))
        Dt.Columns.Add("DataSize", Type.GetType("System.String"))
        Dt.Columns.Add("DecimalSize", Type.GetType("System.String"))
        Dt.Columns.Add("NULLAble", Type.GetType("System.String"))
        Dt.Columns.Add("DefaultValue", Type.GetType("System.String"))
        Dt.Columns.Add("MEMO", Type.GetType("System.String"))
        Dim ConnStr As String = GetConnStr(DBName)
        Dim Cmmd As SqlCommand
        Dim Dt2 As DataTable
        Dim Da As SqlDataAdapter
        Dim rw As DataRow
        Using Conn As New SqlConnection(ConnStr)
            'Conn.Open()
            '****處理欄位Layout資料
            Dim SqlTxt1 As String = ""
            SqlTxt1 += " sp_columns '" + TableName + "' "
            SqlTxt1 += "  "
            Cmmd = New SqlCommand(SqlTxt1, Conn)
            'Cmmd.Parameters.AddWithValue("@TableName", TableName)
            Da = New SqlDataAdapter(Cmmd)
            Dt2 = New DataTable
            Da.Fill(Dt2)
            If Dt2.Rows.Count > 0 Then
                For y = 0 To Dt2.Rows.Count - 1
                    rw = Dt.NewRow
                    rw.Item("SENC") = CInt(Dt2.Rows(y).Item("ORDINAL_POSITION"))
                    rw.Item("Fld") = Dt2.Rows(y).Item("COLUMN_NAME")
                    rw.Item("DataType") = Dt2.Rows(y).Item("TYPE_NAME")
                    rw.Item("DataSize") = Dt2.Rows(y).Item("PRECISION")
                    If IsDBNull(Dt2.Rows(y).Item("SCALE")) Then
                        rw.Item("DecimalSize") = ""
                    Else
                        rw.Item("DecimalSize") = Dt2.Rows(y).Item("SCALE")
                    End If
                    rw.Item("NULLAble") = Dt2.Rows(y).Item("NULLABLE")
                    rw.Item("DefaultValue") = Dt2.Rows(y).Item("COLUMN_DEF")
                    'rw.Item("MEMO") = Dt2.Rows(y).Item("")
                    Dt.Rows.Add(rw)
                Next
            End If
            '***處理欄位說明
            SqlTxt1 = ""
            SqlTxt1 += " SELECT * "
            SqlTxt1 += " FROM  ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default) "
            SqlTxt1 += "  "
            Cmmd = New SqlCommand(SqlTxt1, Conn)
            Cmmd.Parameters.AddWithValue("@TableName", TableName)
            Da = New SqlDataAdapter(Cmmd)
            Dt2 = New DataTable
            Da.Fill(Dt2)
            Dim tmpFld As String
            Dim tmpExt() As String
            Dim CurrentRw() As DataRow
            If Dt2.Rows.Count > 0 Then
                For y = 0 To Dt2.Rows.Count - 1
                    tmpFld = Dt2.Rows(y).Item("objname")
                    tmpExt = Split(Dt2.Rows(y).Item("value"), ";")
                    CurrentRw = Dt.Select("Fld = '" + tmpFld + "'")
                    CurrentRw(0).Item("FldName") = tmpExt(0)
                    If UBound(tmpExt) > 0 Then
                        CurrentRw(0).Item("MEMO") = tmpExt(1)
                    End If
                Next
            End If
            '取得Primery Key設定
            SqlTxt1 = ""
            SqlTxt1 += " sp_pkeys '" + TableName + "' "
            SqlTxt1 += "  "
            Cmmd = New SqlCommand(SqlTxt1, Conn)
            Da = New SqlDataAdapter(Cmmd)
            Dt2 = New DataTable
            Da.Fill(Dt2)
            If Dt2.Rows.Count > 0 Then
                For y = 0 To Dt2.Rows.Count - 1
                    tmpFld = Dt2.Rows(y).Item("COLUMN_NAME")
                    CurrentRw = Dt.Select("Fld = '" + tmpFld + "'")
                    CurrentRw(0).Item("PK") = "PK"
                Next
            End If
            Conn.Close()
        End Using
        Return Dt
    End Function
End Class

另外一個是維護Table名稱與說明的PCATTBL.CCATTBL2


Imports System.EnterpriseServices
Imports System.Runtime.InteropServices
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
<Guid("4F0698AD-7060-44A2-B4E7-21440F48E4F6") _
, Transaction(TransactionOption.Required) _
, Synchronization(SynchronizationOption.Required) _
, JustInTimeActivation(True) _
, EventTrackingEnabled(True)> _
Public Class CCATTBL2
    Inherits ServicedComponent
    Const DBName As String = "DCAT"
    <AutoComplete()> _
    Public Function GetConnStr(ByVal DBName As String) As String
        '*************************************************************************
        '**     撰寫者：小喵(topcat)     撰寫日期：20080507
        '**     用途：  1.讀取Connection String的方式
        '**     做法：
        '**             1.傳入參數DataBase Name DBName
        '**             2.透過Stream將文字檔讀出
        '**             3.將取得的資料傳回
        '**             4.關閉相關物件
        '**     注意事項：
        '**             1.
        '**             2.
        '**     維護記錄：
        '**         維護者：姓名(員工代號)     維護日期：日期
        '**         維護項目：
        '**                 1.
        '**                 2.
        '**         做法：  1.
        '**                 2.
        '**         注意事項：
        '**                 1.
        '*************************************************************************
        Dim StrmRd As New StreamReader("C:\DataLink\" + DBName + ".ini")
        Dim Line As String = ""
        Dim ConnStr As String = ""
        Try
            Do
                Line = StrmRd.ReadLine()
                If Line <> "" Then
                    ConnStr += Line
                End If
            Loop Until Line Is Nothing
            GetConnStr = ConnStr
        Catch ex As Exception
            Throw New Exception(ex.Message.ToString)
        Finally
            StrmRd.Close()
            StrmRd.Dispose()
            StrmRd = Nothing
        End Try
    End Function
    <AutoComplete()> _
    Public Function ChgTbl(ByVal Tbl As String, ByVal TblName As String, ByVal MEMO As String, ByRef P As String, ByRef R As String, ByVal User As String) As String
        '*************************************************************************
        '**     撰寫者：小喵(topcat)     撰寫日期：20080507
        '**     用途：  1.維護Table設定
        '**     做法：
        '**             1.取得傳入的參數
        '**             2.判斷是否有該筆設定
        '**                 2a.有→設定更新SQL
        '**                 2b.沒有→設定新增SQL
        '**             3.執行更新
        '**             4.回報結果
        '**             5.
        '**     注意事項：
        '**             1.
        '**             2.
        '**     維護記錄：
        '**         維護者：姓名(員工代號)     維護日期：日期
        '**         維護項目：
        '**                 1.
        '**                 2.
        '**         做法：  1.
        '**                 2.
        '**         注意事項：
        '**                 1.
        '*************************************************************************
        Dim ConnStr As String = GetConnStr(DBName)
        Using Conn As New SqlConnection(ConnStr)
            Dim Cmmd As SqlCommand
            Dim SqlTxt As String = ""
            Dim SqlTxt2 As String = ""
            Try
                '**             1.取得傳入的參數
                '**             2.判斷是否有該筆設定
                '**                 2a.有→設定更新SQL
                '**                 2b.沒有→設定新增SQL
                '**             3.執行更新
                '**             4.回報結果
                Conn.Open()
                SqlTxt = ""
                SqlTxt += " SELECT * "
                SqlTxt += " FROM TCATTBL WITH (NOLOCK) "
                SqlTxt += " WHERE (Tbl = @Tbl) "
                SqlTxt += "  "
                Cmmd = New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@Tbl", Tbl)
                Dim Dr As SqlDataReader
                Dr = Cmmd.ExecuteReader
                SqlTxt2 = ""
                If Dr.HasRows Then
                    SqlTxt2 += " UPDATE TCATTBL "
                    SqlTxt2 += " SET    TblName = @TblName, "
                    SqlTxt2 += "        MEMO = @MEMO "
                    SqlTxt2 += " WHERE (Tbl = @Tbl) "
                    SqlTxt2 += "  "
                Else
                    SqlTxt2 += " INSERT INTO TCATTBL "
                    SqlTxt2 += " (Tbl,TblName,MEMO) "
                    SqlTxt2 += " VALUES (@Tbl,@TblName,@MEMO) "
                    SqlTxt2 += "  "
                End If
                Dr.Close()
                Cmmd = New SqlCommand(SqlTxt2, Conn)
                Cmmd.Parameters.AddWithValue("@Tbl", Tbl)
                Cmmd.Parameters.AddWithValue("@TblName", TblName)
                Cmmd.Parameters.AddWithValue("@MEMO", MEMO)
                Cmmd.ExecuteNonQuery()
                Conn.Close()
                ChgTbl = "Success"
            Catch ex As Exception
                ChgTbl = "False"
                Throw New Exception(ex.Message)
            Finally
                Conn.Dispose()
            End Try
        End Using
    End Function
End Class

以上方式，提供大家參考

以下是簽名：

歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考

Microsoft MVP
Visual Studio and Development Technologies
(2005~2019/6)

topcat
Blog:http://www.dotblogs.com.tw/topcat

回首頁

topcat 姍舞之間的極度凝聚

世界上有兩樣東西分享給別人後，不但不會變少，還會變更多：一個是快樂，另一個是知識~分享...是知識累積的開始...

使用GridView顯示Sql資料庫的Table Layout(Schema)

Microsoft MVP

SkillTree

系列文章

標籤雲

最新留言