【Microsoft Visual Studio】基礎篇 - DB增刪查找 - 2010 Professional

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

＊　　　　　　　　　　電腦環境：Windows 7 64bit　　　　　　　　　　　　　　　　＊

＊　　　　　　　　　　Microsoft Visual Studio版本：2010 Professional　　　　　　　　 ＊

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

沿用上一個範例繼續http://bedingfield-tsots.blogspot.tw/2015/10/microsoft-visual-studio.html

從前一個範例的畫面來修改
將原本顯示名稱為SQLDataReader的按鈕改名為「查詢」
再加三個Button個別命名為「更新」、「新增」、「刪除」

在物件上點兩下就可切換到程式頁面

「查詢」功能不動

「新增」功能
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        '新增
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "INSERT INTO [Northwind].[dbo].[Employees] (LastName, FirstName, Title) VALUES ('C', 'Y', 'Sales')"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub

「更新」功能
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '更新 指定資料的特定欄位值
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "UPDATE [Northwind].[dbo].[Employees] SET FirstName = 'YY', Title = 'Engineer' where LastName='C'"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub

「刪除」功能
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        '刪除
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "DELETE FROM [Northwind].[dbo].[Employees] WHERE LastName = 'C'"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
＊　　　　　　　　　　完整的程式語法：Visual Basic　　　　　　　　　　　　　  　  ＊
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Imports System.Data.SqlClient

Public Class Form1

    Private resourceCulture As Global.System.Globalization.CultureInfo
    'Protected WithEvents Repeater1 As System.Web.UI.WebControls.Repeater

    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        '先將listBox清空
        listBox1.Items.Clear()
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "SELECT * FROM [Northwind].[dbo].[Employees]"

        'Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM [AC_Manager].[dbo].[OtherRankings_THE]", cs)

        '1.引用SqlConnection物件連接資料庫
        'Using cn As New SqlConnection(cs)
        Using cn As New SqlClient.SqlConnection(cs)
            '2.開啟資料庫
            cn.Open()
            Dim da As SqlDataAdapter = New SqlDataAdapter(qs, cs)
            Using command As New SqlCommand(qs, cn)

                Dim ds As DataSet = New DataSet()
                da.SelectCommand = command
                da.Fill(ds)
                DataGridView1.AutoGenerateColumns = True
                DataGridView1.DataSource = ds.Tables(0)

                'Using dr As SqlDataReader = command.ExecuteReader()
                'While (dr.Read())
                '5.判斷資料列是否為空
                'If Not dr(0).Equals(DBNull.Value) Then
                'listBox1.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
                'End If
                'End While
                'End Using
            End Using
            cn.Close()
            cn.Dispose()
        End Using
    End Sub

    Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button2.Click
        '先將listBox清空
        listBox1.Items.Clear()
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "select * from employees"
        '1.引用SqlConnection物件連接資料庫
        Using cn As New SqlConnection(cs)
            '2.開啟資料庫
            cn.Open()
            '3.引用SqlCommand物件
            Using command As New SqlCommand(qs, cn)
                '4.搭配SqlCommand物件使用SqlDataReader
                Using dr As SqlDataReader = command.ExecuteReader()
                    While (dr.Read())
                        Dim intID As Integer = dr.GetOrdinal("employeeid")
                        Dim intDesc As Integer = dr.GetOrdinal("FirstName")
                        listBox1.Items.Add((dr(intID).ToString() & " – ") + dr(intDesc).ToString())
                    End While
                End Using
            End Using
        End Using
    End Sub

    Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click
        '查詢
        '先將listBox清空
        listBox1.Items.Clear()
        listBox2.Items.Clear()
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "select * from employees;select * from products"

        '1.引用SqlConnection物件連接資料庫
        Using cn As New SqlConnection(cs)
            '2.開啟資料庫
            cn.Open()
            '3.引用SqlCommand物件
            Using command As New SqlCommand(qs, cn)
                '4.搭配SqlCommand物件使用SqlDataReader
                Using dr As SqlDataReader = command.ExecuteReader()
                    While (dr.Read())
                        '5.判斷資料列是否為空
                        If Not dr(0).Equals(DBNull.Value) Then
                            listBox1.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
                        End If
                    End While
                    '6.下一個查詢結果
                    dr.NextResult()
                    While (dr.Read())
                        If Not dr(0).Equals(DBNull.Value) Then
                            listBox2.Items.Add((((dr(0).ToString() & vbTab) + dr(1).ToString() & vbTab) + dr(2).ToString() & vbTab) + dr(3).ToString())
                        End If
                    End While
                End Using
            End Using
        End Using
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '更新 指定資料的特定欄位值
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "UPDATE [Northwind].[dbo].[Employees] SET FirstName = 'YY', Title = 'Engineer' where LastName='C'"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        '新增
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "INSERT INTO [Northwind].[dbo].[Employees] (LastName, FirstName, Title) VALUES ('C', 'Y', 'Sales')"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        '刪除
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "DELETE FROM [Northwind].[dbo].[Employees] WHERE LastName = 'C'"
        Using cn As New SqlClient.SqlConnection(cs)
            cn.Open()
            Using command As New SqlCommand(qs, cn)
                command.ExecuteNonQuery()
            End Using
            cn.Close()
        End Using
    End Sub
End Class