Geek Answers Handbook

Need help with 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 data rows corresponding to ISBN = 12345, but the two tables have different data about this ISBN.

So, I am trying to figure out how to display data from both tables into a single dataGridView. I tried some SQL commands that I found on the Internet, but it looks like C # commands may be different from regular SQL queries.

Suppose that table 1 has the following fields: ISBN, color, size, and table2 have the fields ISBN, weight.

I need a way to display ISBN, color, size and weight in a datagrid view. I think I will have to somehow do this with the adapter. I can connect and make queries in tables separately and show this data in my datagridview, but I cannot figure out how to mix data from two separate tables.

If you have a good resource, I can read about it, I would like to receive it, my google-fu fails.

Here is an example of what 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 would like to do what I did above, but I want to also include data from another table. Another table also has an ISBN key field and contains ISBN values ​​that correspond to the first table.

+5
source share
3 answers

JOIN, JOIN ed together ON

.

# OLEDB - SQL.

, , :

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

( alias table1 T1 - , , )

  • ISBN , ; T1 T2, .
  • , , , .
+3
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();

. , , DataTable SqlDataReader, .

MSSQL System.Data.SqlClient. - OleDb, System.Data.OleDb.

+1

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 will need to manually fill in "67890" in both places, using any method more convenient in your situation.

0
source

More articles:


All Articles