SqlConnection 寫法

 

SqlConnection 寫法有兩種方式，以 VB.NET WinForm 來示範

 

一、直接字串填入

Imports System.Data.SqlClient

Public Class Form1
    Dim sqlCn As New SqlConnection("data source = .\SQLExpress;" & "attachDbFilename = |DataDirectory|Northwind.mdf;" & "Integrated Security = True;" & "user instance = true;")
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            ShowConnection()
            Button1.Text = "關閉"
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Sub ShowConnection()
        Try
            RichTextBox1.Text = "連接字串:" & sqlCn.ConnectionString & vbCrLf & _
                "逾時秒數:" & sqlCn.ConnectionTimeout & vbCrLf & _
                "資料庫:" & sqlCn.Database & vbCrLf & _
                "資料來源:" & sqlCn.DataSource & vbCrLf
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            If sqlCn.State = ConnectionState.Closed Then
                sqlCn.Open()
                ShowConnection()
                Button1.Text = "開啟"
            Else
                sqlCn.Close()
                ShowConnection()
                Button1.Text = "關閉"
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

 

二、引用組態檔(App.config)

點擊兩下 My Project，到 Settings 頁籤，新增如下內容

而於 App.config 將會產生類似下面內容

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
            <section name="學習組態檔.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        </sectionGroup>
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <applicationSettings>
        <學習組態檔.My.MySettings>
            <setting name="connstring" serializeAs="String">
                <value>data source = .\SQLExpress; attachDbFilename = |DataDirectory|ch15DB.mdf; Integrated Security = True;user instance = true;</value>
            </setting>
        </學習組態檔.My.MySettings>
    </applicationSettings>
</configuration>

 

於程式裡引用組態檔替代字串

Imports System.Data.SqlClient

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            Using cn As New SqlConnection(My.Settings.connstring)
                cn.Open()
                MsgBox("連接資料庫成功" & vbCrLf & cn.Database)
            End Using
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

 

範例下載

 

參考資料：

SqlConnection.ConnectionString Property

Settings page, Project Designer

SqlCredential Class