Need some help working with databases in C#

Question

I have a database with two tables. Both of these tables are related and have the same key field. For example, both of them have rows of data corresponding to ISBN = 12345, but the two tables have differing data about that ISBN.

So, I'm trying to figure out how to display data from both tables into one dataGridView. I have tried some SQL commands I found online, but it looks like commands in C# might differ from normal SQL queries.

Suppose table1 has these fields: ISBN, color, size and table2 has the fields ISBN, weight.

I need a way to display ISBN, color, size, weight in my datagrid view. I think I will have to somehow do this with an adapter. I am able to connect and do queries on the tables individually, and show that data in my datagridview, but I can't figure out how to mix data from two separate tables.

If you have a good resource I can read about this I'd love to have it, my google-fu is failing me.

Here's an example of something I can do now with my database:

    private void Form1_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'database1DataSet.Book' table. You can move, or remove it, as needed.
        this.bookTableAdapter.Fill(this.database1DataSet.Book);
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\Users\Geoff\Documents\cs 351\Database1.accdb" + ";Persist Security Info=False;";
        OleDbConnection conn = new OleDbConnection(connectionString);
        string query = "select * from Book where ISBN = 12345";
        OleDbCommand com = conn.CreateCommand();
        com.CommandText = query;
        OleDbDataAdapter adapter = new OleDbDataAdapter(com);
        DataSet data = new DataSet();
        conn.Open();
        adapter.Fill(data);

        conn.Close();
        dataGridView1.DataSource = data.Tables[0];
    }

So, essentially, I'd like to do what I've done above, but I want to include the data from a different table too. The other table also has a key field ISBN, and it contains values of ISBN that match the first table.

Answer 1

Look into the use of JOIN to return the results from two tables JOINed together ON some common value

See Also

• Wikpedia: Join (SQL)
• W3Schools: SQL Joins
• SQL-tutorial.net: SQL Join
• SO: SQL JOIN ON vs WHERE
• Coding Horror: visual explanation of SQL JOINs

There's nothing limiting this to C# or OLEDB -- it's basic SQL.

---

For the specifics of what you're asking a query might look like the following:

SELECT T1.ISBN, T1.color, T1.size, T2.weight
FROM table1 T1
  INNER JOIN table2 T2
    ON T1.ISBN = T2.ISBN
WHERE ISBN = '12345';

(There's no need to alias table1 as T1 -- I just did that as an example; in more complicated queries with longer table names, you might not want to repeat the table name all the time)

• since ISBN occurs in both tables, it must be explicitly qualified in your field-selections; either T1 or T2 can be used, as they are identical
• since color, size and weight each occur in only one table, they do NOT need to be qualified -- but it doesn't hurt.

Answer 2

var query       = "SELECT t1.isbn, t1.color, t1.size, t2.weight FROM table1 t1 JOIN table2 t2 ON t2.isbn = t1.isbn";
var connection  = new System.Data.SqlClient.SqlConnection("your SQL connection string here");
var dataAdapter = new System.Data.SqlClient.SqlDataAdapter(query, connection);
var dataSet     = new System.Data.DataSet();

dataAdapter.Fill(dataSet);

yourGridView.DataSource = dataSet;
yourGridView.DataBind();

This is one of many solutions. I think the code might be faster if you create an in-memory DataTable and use an SqlDataReader, but the sample above is simpler.

When working with MSSQL databases, you normally use the System.Data.SqlClient classes. If you - for whatever reason - use OleDb, pick the corresponding objects from the System.Data.OleDb namespace.

Answer 3

You can query records from both tables using UNION ALL

SELECT 'In table 1', book_author, book_title, book_isbn 
FROM books
WHERE book_isbn = '67890' 
UNION ALL
SELECT 'In table 2', othertable_author, othertable_title, othertable_isbn 
FROM othertable
WHERE othertable_isbn = '67890'

of course you'll need to manually fill the '67890' in both places using whatever method is more convenient in your situation.