VB.Net/Database ADO.net/SqlCommand Update

Execute Update command with parameters

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient

public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     " Sql Select Query 
     Dim sql As String = "SELECT * FROM Employee"
     " Sql Update Statement
     Dim updateSql As String = _
        "UPDATE Employee " & _
        "SET FirstName = @FirstName " & _
        "WHERE ID = @ID"
     Try
        " Create Data Adapter
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand(sql, thisConnection)
        " Create and fill Dataset
        Dim ds As New DataSet
        da.Fill(ds, "Employee")
        " Get the Data Table
        Dim dt As DataTable = ds.Tables("Employee")
        " Display Rows Before Changed
        Console.WriteLine("Before altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
        " Modify city in first row
        dt.Rows(0)("FirstName") = "Ham"
        " Update Employees
        " 1. Create Command
        Dim UpdateCmd As New SqlCommand(updateSql, thisConnection)
        " 2. Map Parameters
        " 2.1 City
        UpdateCmd.Parameters.Add("@FirstName", _
           SqlDbType.NVarChar, 15, "FirstName")
        " 2.2 EmployeeId
        Dim idParam As SqlParameter = _
           UpdateCmd.Parameters.Add("@ID", _
           SqlDbType.Int, 4, "ID")
        idParam.SourceVersion = DataRowVersion.Original
        " Update employees
        da.UpdateCommand = UpdateCmd
        da.Update(ds, "Employee")
        " Display Rows After Alteration
        Console.WriteLine("=========")
        Console.WriteLine("After altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>

Insert command with parameters

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient

public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     " Sql Query 
     Dim sql As String = "SELECT * FROM Employee "
     Dim insertSql As String = "INSERT INTO Employee " & _
        "(ID, FirstName, LastName)VALUES" & _
        "(@ID, @FirstName, @LastName)"
     Try
        " Create Data Adapter
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand(sql, thisConnection)
        " Create and fill Dataset
        Dim ds As New DataSet
        da.Fill(ds, "Employee")
        " Get the Data Table
        Dim dt As DataTable = ds.Tables("Employee")
        " Display Rows Before Changed
        Console.WriteLine("Before altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
        " Add A Row
        Dim newRow As DataRow = dt.NewRow()
        newRow("FirstName") = "Edna"
        newRow("LastName") = "Everage"
        newRow("ID") = "2"
        dt.Rows.Add(newRow)
        " Display Rows After Alteration
        Console.WriteLine("=========")
        Console.WriteLine("After altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
        " Insert employees
        " 1. Create command
        Dim insertCmd As New SqlCommand(insertSql, thisConnection)
        " 2. Map parameters
        insertCmd.Parameters.Add("@FirstName", _
           SqlDbType.NVarChar, 10, "FirstName")
        insertCmd.Parameters.Add("@LastName", _
           SqlDbType.NVarChar, 20, "LastName")
        insertCmd.Parameters.Add("@ID", _
           SqlDbType.Int, 15, "ID")
        " 3. Insert employees
        da.InsertCommand = insertCmd
        da.Update(ds, "Employee")
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>