Using excel as database using vb.Net Or excel and VB.Net

Solution code:

Imports System.Data.Oledb

Public class form1

Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=c:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim pram As OleDbParameter

Dim dr As DataRow

Dim olecon As OleDbConnection

Dim olecomm As OleDbCommand

Dim olecomm1 As OleDbCommand

Dim oleadpt As OleDbDataAdapter

Dim ds As DataSet

Try

    olecon = New OleDbConnection

    olecon.ConnectionString = connstring

    olecomm = New OleDbCommand

    olecomm.CommandText = "Select FirstName, LastName, Age, Phone from [Sheet1$]"

    olecomm.Connection = olecon

    olecomm1 = New OleDbCommand

    olecomm1.CommandText = "Insert into [Sheet1$] " & _

    "(FirstName, LastName, Age, Phone) values (@FName, @LName, @Age, @Phone)"

    olecomm1.Connection = olecon

    pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)

    pram.SourceColumn = "FirstName"

    pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)

    pram.SourceColumn = "LastName"

    pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)

    pram.SourceColumn = "Age"

    pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)

    pram.SourceColumn = "Phone"

    oleadpt = New OleDbDataAdapter(olecomm)

    ds = New DataSet

    olecon.Open()

    oleadpt.Fill(ds, "Sheet1")

    If IsNothing(ds) = False Then

        dr = ds.Tables(0).NewRow

        dr("FirstName") = "Yogesh"

        dr("LastName") = "Mehla"

        dr("Age") = 22

        dr("Phone") = 90232625

        ds.Tables(0).Rows.Add(dr)

        oleadpt = New OleDbDataAdapter

        oleadpt.InsertCommand = olecomm1

        Dim i As Integer = oleadpt.Update(ds, "Sheet1")

        MessageBox.Show(i & " row affected")

    End If

Catch ex As Exception

    MessageBox.Show(ex.Message)

Finally

    olecon.Close()

    olecon = Nothing

    olecomm = Nothing

    oleadpt = Nothing

    ds = Nothing

    dr = Nothing

    pram = Nothing

    End Try

End Sub

 End Class

Output:

Explanation:

The.NET Framework Data Provider for OLE DB describes a collection of classes used to access an OLE DB data source in the managed space. Using the OleDbDataAdapter, you can fill a memory-resident DataSet that you can use to query and update the data source.