如何通过vb.net代码检查SQL Server表及其列 [英] how to check sql server table and their column through vb.net code

问题描述

如何通过vb.net代码检查SQL Server表及其列

how to check sql server table and their column through vb.net code

推荐答案

下面的代码将为您提供当前数据库的SQL Server表名.

您只需要传递必需的参数即可连接到数据库.

Below code will provide you the table name of the SQL server for the current database.

You just need to pass required parameter to connect to the database.

Protected Sub getTables()
       Dim dbconn As SqlConnection
       Dim sql As String
       Dim Command As SqlCommand
       dbconn = New SqlConnection("server=" & txtserver.Text.Trim & ";uid=" & txtuid.Text.Trim & ";pwd=" & txtpwd.Text.Trim & ";database=" & txtdatabase.Text.Trim & "")
       dbconn.Open()
       sql = "SELECT * FROM sys.tables where type='U'"
       Command = New SqlCommand(sql, dbconn)
       Dim dtAdapter As New SqlDataAdapter
       Dim dtTable As New DataTable
       dtAdapter.SelectCommand = Command
       dtAdapter.Fill(dtTable)
       chkTable.DataSource = dtTable
       chkTable.DataTextField = "name"
       chkTable.DataBind()
       Dispose()

   End Sub

因此，从上面的代码中，您将在DataTable对象chkTable中具有所有SQL表列表.

尝试以下代码以获取各个表的列名.

So from above code, you will have a all SQL table list in the DataTable object chkTable.

Try out below code for getting individual table column name.

For Each item As ListItem In chkTable.Items
                If item.Selected Then
                    dbconn.Open()
                    sql = "SELECT * FROM " & item.Text
                    Command = New SqlCommand(sql, dbconn)
                    Dim dtAdapter As New SqlDataAdapter
                    Dim dtTable As New DataTable
                    dtAdapter.SelectCommand = Command
                    dtAdapter.Fill(dtTable)
                    Dispose()
                    dbconn.Close()
                    'add your custom logic here for what you need to do with all the columns
                End If
            Next

希望对您有所帮助.

Hope it helps.

这篇关于如何通过vb.net代码检查SQL Server表及其列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！