Need some help working with databases in C#

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 wa开发者_如何学运维y 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.

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.

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.

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.