Access database table and Sql database table using Linq

Problem:
We need to join a table from an access database and another from an sql database and display the result in a gridview in .net.

Solution:

STEP 1: Add a dataset to the project.
Lets name the DataSet as DSEmployee.

STEP 2: Add new connections in server explorer for the access database and sqlserver database.

Lets say, we have an employee table in the access datatabse named as tblEmployeeAccess and we have the employee table in the sql server named as tblEmployeeSQL.

Create connection for access database:

>Open the dataset in design mode > Click on server explorer > Choose dataconnection in left hand panel > Right click and select ‘add new connection’. >In the Add Connection dialog box, enter the path for mdb database file and click ok. A new connection will get added to the server explorer for the access database file.

> Expand that connection in Server Explorer > Expand the item table > Choose the table EmployeeAccess (on which we want to perform join) > Then drop that to the dataset designer.

The table will get added to the dataset along with a new DataAdapter ‘tblEmployeeAccessTableAdapter’.

The connection used for this will automatically get added to the app.config file.The DataAdapter points to that access database connection string created in app.config file.

Create connection for sql database:

> We can follow the same procedure that we have done for access database, but here we have to set the datasource as sqlserver.
> Expand that connection in Server Explorer > Expand the item table > Choose the table EmployeeSQL (on which you want to perform join) > Then drop that to the dataset designer.

The table will get added to the dataset along with a new DataAdapter ‘tblEmployeeSQLTableAdapter’ and here the DataAdapter points to sql server connection string created in app.config file.

STEP 3: Add the dataset to the form which contain the grid and add code in the load event.

Now the dataset is having two tables one from the access database and another from the sql database. The corresponding connection strings are present inside the app.config file. Rebuild the solution and the added DataSet will get visible in component menu of toolbox.Just drop that dataset to the form and add the below code to the load event.

Private Sub MyForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        '**crate instance of the tables present in dataset

        'crate instance of access table
         Dim tblEmployeeAccess As New DSEmployee.tblEmployeeAccess

        'crate instance of sql table
        Dim tblEmployeeSQL As New DSEmployee.tblEmployeeSQL

        'create instance of adapter created for accesstable,use it to fill the access table
        Dim dtAdapterForAccess As New DSEmployeeTableAdapters.tblEmployeeAccessTableAdapter
        dtAdapterForAccess.Fill(tblEmployeeAccess)
      
        'create instance of adapter created for sql table, use it to fill the sql table
        Dim dtAdapterForSQL As New DSEmployeeTableAdapters.tblEmployeeSQLTableAdapter
        dtAdapterForSQL.Fill(tblEmployeeSQL)
      
       'perform join and get the desired columns from the two tables
        Dim result = From empSql In tblEmployeeSQL _
                    Join empAccess In tblEmployeeAccess _
                    On empSql.EmployeeID Equals empAccess.EmployeeID _
                    Select New With _
                    {empSql.EmployeeID, empSql.Name, empAccess.Department, empAccess.Branch}
       
        Dim lstEmployeeDetail As IList = result.ToList
       
        'set the datasource of the gridview
        grdvEmployee.DataSource = lstEmployeeDetail

End Sub

Now, on executing the grid will diplay the joined data from the access database table and sql database table.