学习记录：VB.NET操作SQL SERVER数据库

今天学习在VB.NET上操作SQL SERVER数据库，为防止将来忘记，记录于此。

一、清空某张表中的全部数据

Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
        cn.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "delete from tbMergeIns"
        cmd.ExecuteNonQuery()

        cmd.Dispose()                                                                                         '释放资源
        cn.Close()
        cn.Dispose()                                                                                            '释放资源 感觉很像VB6里的 set xx=nothing
        MessageBox.Show("全部删除完成")

二 向某张表插入数据

        Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
        cn.Open()

        Dim cmd As New SqlCommand()
        cmd.Connection = cn

        Dim Id As String
        Dim SendCompany As String
        Dim TotalWeightLock As Integer

        id = "201906140001"
        sendcompany = "a"
        totalweightlock = 1
        cmd.CommandText = "insert into tbMergeIns (id,sendcompany,totalweightlock) values(" & Chr(39) & Id & Chr(39) & "," & Chr(39) & SendCompany & Chr(39) & "," & TotalWeightLock & ")"
        cmd.ExecuteNonQuery()

        Id = "201906140002"
        SendCompany = "aa"
        TotalWeightLock = 1
        cmd.CommandText = "insert into tbMergeIns (id,sendcompany,totalweightlock) values(" & Chr(39) & Id & Chr(39) & "," & Chr(39) & SendCompany & Chr(39) & "," & TotalWeightLock & ")"
        cmd.ExecuteNonQuery()

        Id = "201906140003"
        SendCompany = "aaa"
        TotalWeightLock = 1
        cmd.CommandText = "insert into tbMergeIns (id,sendcompany,totalweightlock) values(" & Chr(39) & Id & Chr(39) & "," & Chr(39) & SendCompany & Chr(39) & "," & TotalWeightLock & ")"
        cmd.ExecuteNonQuery()

        cmd.Dispose()
        cn.Close()
        cn.Dispose()
        MessageBox.Show("插入3行测试完成")
 

三 修改

        Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
        cn.Open()

        Dim cmd As New SqlCommand()
        cmd.Connection = cn

        Dim strNewSendCompany As String
        strNewSendCompany = "aaaaaaa"
        Dim strCondition As String
        strCondition = "a"
        cmd.CommandText = "update tbMergeIns set sendcompany=" & Chr(39) & strNewSendCompany & Chr(39) & " where sendcompany=" & Chr(39) & strCondition & Chr(39)
        cmd.ExecuteNonQuery()

        cmd.Dispose()
        cn.Close()
        cn.Dispose()
        MessageBox.Show("修改测试完成")
 

四 删除

        Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
        cn.Open()
        Dim cmd As New SqlCommand()
        cmd.Connection = cn
        Dim strCondition As String
        strCondition = "aaaaaaa"
        cmd.CommandText = "delete from tbMergeIns where sendcompany=" & Chr(39) & strCondition & Chr(39)
        cmd.ExecuteNonQuery()

        cmd.Dispose()
        cn.Close()
        cn.Dispose()
        MessageBox.Show("删除测试完成")

五 查询

Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
        cn.Open()

        Dim strIDCondition As String
        strIDCondition = "201906140002"
        Dim iTotalWeightLockCondition As Integer
        iTotalWeightLockCondition = 1

        Dim cmd As New SqlCommand()
        cmd.Connection = cn
        cmd.CommandText = "select * from tbMergeIns where id=" & Chr(39) & strIDCondition & Chr(39) & " and TotalWeightLock=" & iTotalWeightLockCondition

        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()

        If dr.HasRows() = True Then
            Do While dr.Read() = True                                   '如果有记录，就循环打印符合条件的记录的address字段中内容
                Debug.Print(dr.Item("id") & " " & dr.Item("sendcompany") & " " & dr.Item("totalweightlock"))
            Loop
        Else
            MessageBox.Show("搜索失败")
        End If
        dr.Close()
        '结果为
        '201906140002  aa 1

        '查询全部
        cmd.CommandText = "select * from tbMergeIns order by id"
        dr = cmd.ExecuteReader()
        If dr.HasRows() = True Then
            Do While dr.Read() = True                                               '如果有记录，就循环打印符合条件的记录的address字段中内容
                Debug.Print(dr.Item("id") & " " & dr.Item("sendcompany") & " " & dr.Item("totalweightlock"))
            Loop
        End If
        '结果为
        '  201906140002  aa 1
        '  201906140003  aaa 1

        dr.Close()
        cmd.Dispose()
        cn.Close()
        cn.Dispose()
 

        

六 总结

练习后发现，VB.NET操作SQL SERVER数据库的方法与VB.NET操作ACCESS数据库的方法一样，简单明了，仅仅变量类型有点小变动。