在 C# 中使用數據庫需要一些幫助 (Need some help working with databases in C#)

問題描述

在 C# 中使用數據庫需要一些幫助 (Need some help working with databases in C#)

我有一個包含兩個表的數據庫。這兩個表是相關的並且具有相同的鍵字段。例如，它們都有對應於 ISBN = 12345 的數據行，但是這兩個表具有關於該 ISBN 的不同數據。

所以，我試圖弄清楚如何將兩個表中的數據顯示到一個dataGridView中。我嘗試了一些在網上找到的 SQL 命令，但看起來 C# 中的命令可能與普通 SQL 查詢不同。

假設table1有這些字段：ISBN、color、size，table2有ISBN、weight。

我需要一種在我的數據網格視圖中顯示 ISBN、顏色、大小、重量的方法。我想我將不得不以某種方式使用適配器來做到這一點。我能夠單獨連接表並對其進行查詢，並在我的 datagridview 中顯示該數據，但我不能 t 弄清楚如何混合來自兩個單獨表的數據。

如果你有一個很好的資源，我可以讀到這個，我很想擁有它，我的 google‑fu 讓我失望了。

這是我現在可以用我的數據庫做的一個例子：

    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];
    }

所以，基本上，我想做上面所做的事情，但我也想包含來自不同表的數據。另一個表也有一個關鍵字段 ISBN，它包含與第一個表匹配的 ISBN 值。

參考解法

方法 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.

方法 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.

方法 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.

(by jeff、Michael Paulukonis、KBoek、dschulz)

參考文件

1. Need some help working with databases in C# (CC BY‑SA 3.0/4.0)