Inner join three tables with different database in C# and display in data grid view

Question

0.00/5 (No votes)

See more:

I want to join three table that is in different database and show the result in datagrid view.

First table contain
ID, Group_Id, Voucher, Bill, Vendor_Id, Amount

Second Table Contain
Group_Id, Group_Name

Third Table Contain
Vendor_Id, Vendor_Name, Vendor_Address

I want result like
ID, Group_Name, Voucher, Bill, Vendor_Name, Vendor_Address, Amount

And after joining I want to show these data in Datagridview in C#

My problem is, I am only able to find Inner Join with same database over the internet. But I cant find Inner Join of Tables with different database on same server.

What I have tried:

str1 = "select `ID`, `Group_ID`, `Voucher_No`, `Bill_No`, `Date`, `Vendor_Id`, `Amount` from BillTable where (Group_ID= ''+ @searchsys + '')";
                cmd1 = new OleDbCommand(str1, cn);

                cmd1.Parameters.Add(new OleDbParameter("@searchsys", Convert.ToString(groupidDBS)));


                cmd1.ExecuteNonQuery();
                da1 = new OleDbDataAdapter(cmd1);
                da1.Fill(dt1);

                OleDbDataReader reader4 = cmd1.ExecuteReader();

Posted 17-Jul-20 1:21am

The Digigraphy

Updated 17-Jul-20 1:47am

CHill60

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-07-17T01:31:00

You have tagged this as Access but refer to

Quote:
different database on same server.

therefore I am going to assume it is some kind of SQL.

You can refer to a table by connecting to a specific database and just using its name e.g.

SQL

select [ID] from Table1

but you can also give it its "full name" - including the database name and the schema name e.g.

SQL

select [ID] from [database2].[dbo].Table1

If you are using Access and the table is in a different .accdb file then use Linked Tables (intro here[^] - but don't call the service!)

EDIT - also - avoid SQL Injection attacks (see SQL Injection | OWASP[^] ) by using Parameterised Queries (see OleDbCommand.Parameters Property (System.Data.OleDb) | Microsoft Docs[^] )

Maciej Los · Answer 2 · 2020-07-17T01:47:00

[EDIT]

The Digigraphy wrote:
My Database Location of First Table is- C:\Users\Digi\Source\Soft\BillData.accdb
And Second Database Location is
C:\Users\Digi\Source\Soft\Master\GroupList.accdb

Both these database are in different location. And I want to join them

You've got two ways to achieve that:
1) create one database and
a) link all tables from all databases
or
b) move (import) all tables from different databases into one
See: Import or link to data in another Access database - Access[^]
Then use this query:

SQL

SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 
    INNER JOIN SecondTable t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable t3 ON t1.Vendor_Id = t3.Vendor_Id
--WHERE t1.Group_Id = ?

I'd reocmmend to use this way.

2) use IN clause[^]:
You'll need to modify the body of query this way:

SQL

SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 --this database is initial database (defined in connection string)
    INNER JOIN SecondTable IN 'C:\Users\Digi\Source\Soft\Master\GroupList.accdb' t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable IN 'C:\Users\Digi\Source\Soft\ThirdDatabase.accdb' t3 ON t1.Vendor_Id = t3.Vendor_Id
WHERE t1.Group_Id = ?

Above should also work, but it's more complicated.

So, changed code may looks like:

VB.NET

Dim firstFileName As String = "C:\Users\Digi\Source\Soft\BillData.accdb"
Dim secondFileName As String = "C:\Users\Digi\Source\Soft\Master\GroupList.accdb"
Dim thirdFileName As String = "C:\Users\Digi\Source\Soft\Master\ThirdDatabase.accdb"
Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};;Persist Security Info=False;", firstFileName)
Dim dt As DataTable = New DataTable()

'1.
Using connection AS OleDbConnection = New OleDbConnection(sConStr)
    Dim sql As String = String.Format("SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount{0}FROM FirstTable t1{0}    INNER JOIN SecondTable IN '{1}' t2 ON t1.Group_Id = t2.Group_Id{0}    INNER JOIN ThirdTable IN '{2}' t3 ON t1.Vendor_Id = t3.Vendor_Id{0}WHERE t1.Group_Id = ?", vbCrLf, secondFileName, thirdFileName)
    connection.Open()
    '2.
    Using command As OleDbCommand= New OleDbCommand(sql, connection)
        command.Parameters.Add(New OleDbParameter() With {.OleDbType = OleDbType.VarChar, .Value=2})
        '3.
        Dim reader As OleDbDataReader = command.ExecuteReader()
        '4.
        dt.Load(reader)
    End Using
End Using
'load data into dgv component
DataGridView1.DataSource = dt

Access connection strings - ConnectionStrings.com[^]

Inner join three tables with different database in C# and display in data grid view

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0