ASP.NET(VB)如何根据下拉列表选择切换对应数据库连接字符串

1. 修改web.config配置，添加所有需要的数据库连接字符串 #

<connectionStrings>
  <add name="constr1" connectionString="Data Source=SERVERNAME;Initial Catalog=DatabaseName1;Integrated Security=True" />
  <add name="constr2" connectionString="Data Source=SERVERNAME;Initial Catalog=DatabaseName2;Integrated Security=True" />
  <!-- 可继续添加其他数据库的连接字符串，name字段可自定义，后续逻辑会自动读取 -->
</connectionStrings>

2. 前端aspx页面添加下拉列表控件 #

在原页面GridView控件上方新增DropDownList控件，开启自动回发并绑定选中变更事件：

<asp:DropDownList ID="ddlDatabases" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlDatabases_SelectedIndexChanged">
</asp:DropDownList>
<br /><br />
<!-- 原有的GridView2控件保持不变 -->
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" OnPageIndexChanging="OnPageIndexChanging">
</asp:GridView>

3. 修改后台VB代码 #

完整修改后的代码如下：

' 若提示ConfigurationManager不存在，在文件顶部添加导入：Imports System.Configuration
Imports System.Data.SqlClient

Public Class Main_Dashboard
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            ' 首次加载时绑定下拉列表选项
            BindDatabaseDropDown()
            ' 默认加载第一个数据库的查询结果
            Me.BindGrid(ddlDatabases.SelectedValue)
        End If
    End Sub

    ' 自动读取web.config中的连接字符串，绑定下拉列表选项
    Private Sub BindDatabaseDropDown()
        For Each connStr As ConnectionStringSettings In ConfigurationManager.ConnectionStrings
            ' 跳过系统默认的连接字符串，只加载自定义配置
            If connStr.Name <> "LocalSqlServer" Then
                ' 提取连接字符串中的数据库名作为显示文本，连接字符串的name作为选中值
                Dim dbName As String = New SqlConnectionStringBuilder(connStr.ConnectionString).InitialCatalog
                ddlDatabases.Items.Add(New ListItem(dbName, connStr.Name))
            End If
        Next
    End Sub
 
    ' 修改BindGrid方法，接收连接字符串名称作为参数
    Private Sub BindGrid(ByVal connStrName As String)
        Dim constr As String = ConfigurationManager.ConnectionStrings(connStrName).ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("DECLARE @QueryString NVARCHAR(MAX) ;
                                         SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                                              + 'SELECT '
                                              + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                                              + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                                              , COUNT(*) AS [RowCount] FROM '
                                              + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                                              + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
                                         FROM sys.objects AS sOBJ
                                         WHERE
                                              sOBJ.type = 'U'
                                              AND sOBJ.is_ms_shipped = 0x0
                                         ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
                                         EXEC sp_executesql @QueryString;
                                          
 
                                         SELECT
                                              QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
                                              , SUM(sdmvPTNS.row_count) AS [RowCount]
                                         FROM
                                              sys.objects AS sOBJ
                                              INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
                                                    ON sOBJ.object_id = sdmvPTNS.object_id
                                         WHERE
                                              sOBJ.type = 'U'
                                              AND sOBJ.is_ms_shipped = 0x0
                                              AND sdmvPTNS.index_id < 2
                                         GROUP BY
                                              sOBJ.schema_id
                                              , sOBJ.name
                                         ORDER BY [TableName];")
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        GridView2.DataSource = dt
                        GridView2.DataBind()
                    End Using
                End Using
            End Using
        End Using
    End Sub
 
    ' 下拉列表选中变更事件
    Protected Sub ddlDatabases_SelectedIndexChanged(sender As Object, e As EventArgs)
        ' 选中新数据库后重新加载表格数据
        Me.BindGrid(ddlDatabases.SelectedValue)
    End Sub
 
    Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        GridView2.PageIndex = e.NewPageIndex
        ' 分页时保持使用当前选中的数据库
        Me.BindGrid(ddlDatabases.SelectedValue)
    End Sub
End Class

说明 #

• 无需手动硬编码下拉列表选项，后续新增数据库只需在web.config中添加对应连接字符串即可，无需修改其他逻辑
• 需确保站点运行身份对所有配置的数据库都有查询权限，否则会触发权限报错

内容的提问来源于stack exchange，提问作者Trini DBA