Faster way to Update Database.

Here is the simple and fast way to Insert, Update, Delete your database records when you are dealing with huge amount of data.
Steps :

1. Fill Dataset using DataAdapter
2. Perform Insert, Update, Delete operations on DataSet
3. Finally use Update() method of DataAdapter to reflect all changes to DataBase at once.


Check this sample Code. Very effective and fast.

Private Sub UpdateDataBase()

Dim connString As String = "server=dev14\SQLEXPRESS;database=sudhir;Integrated Security=SSPI"
Dim con As New SqlConnection(connString)

Dim oAdapter As New SqlDataAdapter("SELECT * From student", con)
Dim oCmdBuider As New SqlCommandBuilder(oAdapter)

'Add Record or Modify Record

oAdapter.InsertCommand = oCmdBuider.GetInsertCommand()
oAdapter.UpdateCommand = oCmdBuider.GetUpdateCommand()
oAdapter.DeleteCommand = oCmdBuider.GetDeleteCommand()

con.Open()

Dim oDataSet As New DataSet()
oAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

oAdapter.Fill(oDataSet, "student")


'' UPDATE OR MODIFY DATABASE ''

oDataSet.Tables("student").Rows(0)("Name") = "pramod"
oDataSet.Tables("student").Rows(0)("Phone") = 1000
oDataSet.Tables("student").Rows(0)("class") = "ClassOne"

oDataSet.Tables("student").Rows(1)("Name") = ajith"
oDataSet.Tables("student").Rows(1)("Phone") = 2000

oDataSet.Tables("student").Rows(2)("Name") = "nitin"
oDataSet.Tables("student").Rows(2)("Phone") = 5000
oDataSet.Tables("student").Rows(2)("class") = "ClassTwo"

Dim oRow As DataRow

oRow = oDataSet.Tables("student").NewRow
oRow("Name") = "Ricky"
oRow("Class") = "One"

oDataSet.Tables("student").Rows.Add(oRow)


Try
'' UPDATE THE DATABASE .''

oAdapter.Update(oDataSet, "student")

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

con.Close()
End Sub