vb.net 操作sqlserver

 Imports system.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim objconnection As New SqlConnection("server=HUO-YA-LI-PC;database=database;Integrated Security=True")
    'Provider=SQLOLEDB.1;Password=jaya2008;Persist Security Info=True;User ID=sa;Initial Catalog=yaoidian;Data Source=HUO-YA-LI-PC
    Dim objdataadapter As New SqlDataAdapter("select Sno,Sname,Ssex,Sbirthday,Sid,Scollege,Sclass,Sindate from studentinfo", objconnection)
    Dim objdataset As DataSet
    Dim objdataview As DataView
    Dim objcurrencymanager As CurrencyManager
    Private Sub filldatasetandview()
        objdataset = New DataSet()
        objdataadapter.Fill(objdataset, "studentinfo")
        objdataview = New DataView(objdataset.Tables("studentinfo"))
        objcurrencymanager = CType(Me.BindingContext(objdataview), CurrencyManager)
    End Sub
    Private Sub bindfields()
        txtsno.DataBindings.Clear()
        txtsname.DataBindings.Clear()
        txtssex.DataBindings.Clear()
        txtsbirthday.DataBindings.Clear()
        txtsid.DataBindings.Clear()
        txtscollege.DataBindings.Clear()
        txtsclass.DataBindings.Clear()
        txtsindate.DataBindings.Clear()
        txtsno.DataBindings.Add("text", objdataview, "Sno")
        txtsname.DataBindings.Add("text", objdataview, "Sname")
        txtssex.DataBindings.Add("text", objdataview, "Ssex")
        txtsbirthday.DataBindings.Add("text", objdataview, "Sbirthday")
        txtsid.DataBindings.Add("text", objdataview, "Sid")
        txtscollege.DataBindings.Add("text", objdataview, "Scollege")
        txtsclass.DataBindings.Add("text", objdataview, "Sclass")
        txtsindate.DataBindings.Add("text", objdataview, "Sindate")
        ToolStripStatusLabel1.Text = "ready"
    End Sub
    Private Sub showposition()
        If txtsbirthday.Text <> "" And txtsindate.Text <> "" Then
            Try
                txtsbirthday.Text = Format(CType(txtsbirthday.Text, Date), "MM/dd/yyyy")
                txtsindate.Text = Format(CType(txtsindate.Text, Date), "MM/dd/yyyy")
            Catch ex As Exception
                txtsbirthday.Text = ""
                txtsindate.Text = ""
                txtsbirthday.Text = Format(CType(txtsbirthday.Text, Date), "MM/dd/yyyy")
                txtsindate.Text = Format(CType(txtsindate.Text, Date), "MM/dd/yyyy")
            End Try
            txtRecordPosition.Text = objcurrencymanager.Position + 1 & " of " & objcurrencymanager.Count()
            txtsno.Enabled = objdataview(objcurrencymanager.Position).Row.RowState = DataRowState.Added
        Else
            MessageBox.Show("记录为空！无法执行转换。", "错误！")

        End If

    End Sub
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cbofield.Items.Add("Sno")
        cbofield.Items.Add("Sname")
        cbofield.Items.Add("Ssex")
        cbofield.Items.Add("Sbirthday")
        cbofield.Items.Add("Sid")
        cbofield.Items.Add("Scollege")
        cbofield.Items.Add("Sclass")
        cbofield.Items.Add("Sindate")
        cbofield.SelectedIndex = 0
        filldatasetandview()
        bindfields()
        showposition()
    End Sub

    Private Sub btnmovefirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovefirst.Click
        objcurrencymanager.Position = 0
        showposition()

    End Sub

    Private Sub btnmoveprevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmoveprevious.Click
        objcurrencymanager.Position -= 1
        showposition()
    End Sub

    Private Sub btnmovenext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovenext.Click
        objcurrencymanager.Position += 1
        showposition()
    End Sub

    Private Sub btnmovelast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnmovelast.Click
        objcurrencymanager.Position = objcurrencymanager.Count - 1
        showposition()
    End Sub

    Private Sub btnPerformSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPerformSort.Click
        Select Case cbofield.SelectedIndex
            Case 0
                objdataview.Sort = "Sno"
            Case 1
                objdataview.Sort = "Sname"
            Case 2
                objdataview.Sort = "Ssex"
            Case 3
                objdataview.Sort = "Sbirthday"
            Case 4
                objdataview.Sort = "Sid"
            Case 5
                objdataview.Sort = "Scollege"
            Case 6
                objdataview.Sort = "Sclass"
            Case 7
                objdataview.Sort = "Sindate"
        End Select
        btnmovefirst_Click(Nothing, Nothing)
        ToolStripStatusLabel1.Text = "Records Sorted"
    End Sub

    Private Sub btnperformsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnperformsearch.Click
        Dim intposition As Integer
        Select Case cbofield.SelectedIndex
            Case 0
                objdataview.Sort = "Sno"
            Case 1
                objdataview.Sort = "Sname"
            Case 2
                objdataview.Sort = "Ssex"
            Case 3
                objdataview.Sort = "Sbirthday"
            Case 4
                objdataview.Sort = "Sid"
            Case 5
                objdataview.Sort = "Scollege"
            Case 6
                objdataview.Sort = "Sclass"
            Case 7
                objdataview.Sort = "Sindate"
        End Select
        If cbofield.SelectedIndex = 3 Or cbofield.SelectedIndex = 7 Then
            intposition = objdataview.Find(CType(txtSearchCriteria.Text, Date))
        Else
            intposition = objdataview.Find(txtSearchCriteria.Text)
        End If
        If intposition = -1 Then
            ToolStripStatusLabel1.Text = "Record Not Found"
        Else
            ToolStripStatusLabel1.Text = "Record Found"
            objcurrencymanager.Position = intposition
        End If
        showposition()
    End Sub

    Private Sub btnnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnew.Click
        objcurrencymanager.Position = (objcurrencymanager.Count + 1)
        txtsno.Text = ""
        txtsname.Text = ""
        txtssex.Text = ""
        txtsbirthday.Text = ""
        txtsid.Text = ""
        txtscollege.Text = ""
        txtsclass.Text = ""
        txtsindate.Text = ""
        txtsno.Enabled = True
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim objcommand As SqlCommand = New SqlCommand()
        Dim intposition As Integer
        objconnection.Open()
        objcommand.Connection = objconnection
        intposition = objcurrencymanager.Position
        If txtsno.Text = "" Then
            MessageBox.Show("Sno不能为空", " 错误!")
        Else
            objcommand.CommandText = "insert into studentinfo(Sno,Sname,Ssex,Sbirthday,Sid,Scollege,Sclass,Sindate)values(@Sno,@Sname,@Ssex,@Sbirthday,@Sid,@Scollege,@Sclass,@Sindate) "
            objcommand.Parameters.AddWithValue("@Sno", txtsno.Text)
            objcommand.Parameters.AddWithValue("@Sname", txtsname.Text)
            objcommand.Parameters.AddWithValue("@Ssex", txtssex.Text)
            objcommand.Parameters.AddWithValue("@Sbirthday", txtsbirthday.Text)
            objcommand.Parameters.AddWithValue("@Sid", txtsid.Text)
            objcommand.Parameters.AddWithValue("@Scollege", txtscollege.Text)
            objcommand.Parameters.AddWithValue("@Sclass", txtsclass.Text)
            objcommand.Parameters.AddWithValue("@Sindate", txtsindate.Text)
            Try
                objcommand.ExecuteNonQuery()
            Catch sqlexceptionerr As Exception
                MessageBox.Show(sqlexceptionerr.Message)
            End Try
            objconnection.Close()
            filldatasetandview()
            bindfields()
            showposition()
            objcurrencymanager.Position = intposition
            ToolStripStatusLabel1.Text = "Record Added"
        End If
    End Sub
    Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        Dim intposition As Integer
        Dim objcommand As SqlCommand = New SqlCommand()
        intposition = objcurrencymanager.Position
        objcommand.Connection = objconnection
        objcommand.CommandText = "update studentinfo Set Sname = @Sname,Ssex = @Ssex,Sbirthday = @Sbirthday,Sid = @Sid,Scollege = @Scollege,Sclass = @Sclass,Sindate = @Sindate where Sno=@Sno"
        objcommand.CommandType = CommandType.Text
        objcommand.Parameters.AddWithValue("@Sno", txtsno.Text)
        objcommand.Parameters.AddWithValue("@Sname", txtsname.Text)
        objcommand.Parameters.AddWithValue("@Ssex", txtssex.Text)
        objcommand.Parameters.AddWithValue("@Sbirthday", txtsbirthday.Text)
        objcommand.Parameters.AddWithValue("@Sid", txtsid.Text)
        objcommand.Parameters.AddWithValue("@Scollege", txtscollege.Text)
        objcommand.Parameters.AddWithValue("@Sclass", txtsclass.Text)
        objcommand.Parameters.AddWithValue("@Sindate", txtsindate.Text)
        objconnection.Open()
        objcommand.ExecuteNonQuery()
        objconnection.Close()
        filldatasetandview()
        bindfields()
        showposition()
        objcurrencymanager.Position = intposition
        ToolStripStatusLabel1.Text = "Record Updated"
    End Sub
    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        Dim intposition As Integer
        Dim objcommand As SqlCommand = New SqlCommand
        intposition = objcurrencymanager.Count - 1
        If intposition < 0 Then
            intposition = 0
            MessageBox.Show("无记录可删！", "错误！")
        End If
        objcommand.Connection = objconnection
        objcommand.CommandText = "delete from studentinfo where Sno=@Sno"
        objcommand.Parameters.AddWithValue("@Sno", BindingContext(objdataview).Current("Sno"))
        objconnection.Open()
        objcommand.ExecuteNonQuery()
        objconnection.Close()
        filldatasetandview()
        bindfields()
        showposition()
        Me.BindingContext(objdataview).Position = intposition
        ToolStripStatusLabel1.Text = "Record Deleted"
    End Sub
End Class