form1.cs

C#访问数据库,并建立多个表之间的关系,查询多个表,包含了数据库Northwind

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace CustomerEditor
{
    public partial class Form1 : Form
    {    
        private OleDbConnection _connection;
        public Form1()
        {
            
            InitializeComponent();
            //
        }


        private void MenuDataConnect_Click(object sender, EventArgs e)
        {
            Connect();
        }
        public void Connect()
        {
            if (openDialogFile.ShowDialog(this) == DialogResult.OK)
            {
                try
                {
                    string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=;Password=;", openDialogFile.FileName);
                    OleDbConnection newConnection = new OleDbConnection(connectionString);
                    newConnection.Open();
                    Connection = newConnection;
                }
                catch (Exception ex)
                {
                    HandleException("A connection could",ex);
                }
            }
        }
        public void HandleException(string message, Exception ex)
        {
            MessageBox.Show(this, string.Format("{0}\n{1}:{2}",message,ex.GetType().ToString(),ex.Message));
        }
        public OleDbConnection Connection
        {
            get
            {
                return _connection;
            }
            set
            {
                Disconnect();
                _connection=value;
            }
        }
        public void Disconnect()
        {
            if (_connection != null)
            {
                if (_connection.State != ConnectionState.Closed)
                    _connection.Close();
                   _connection = null;
            }
        }

        private void loadToolStripMenuItem_Click(object sender, EventArgs e)
        {
            LoadData();
        }
        public void LoadData()
        {
            if (_connection == null)
            {
                MessageBox.Show(this, "You must connect to a database");
                return;
            }
            //OleDbCommand command = null;
           // OleDbDataAdapter adapter = null;
            try
            {
                DataSet dataset = new DataSet();
                DataTable customers = CreateAndFill(dataset,"Customers");
                DataTable orders = CreateAndFill(dataset, "Orders");
                DataTable orderDetails = CreateAndFill(dataset,"Order Details");

                DataRelation customersToOrders = new DataRelation("OrdersForCustomers",customers.Columns["CustomerID"],
                                                             orders.Columns["CustomerID"]);
                dataset.Relations.Add(customersToOrders);

                DataRelation ordersToOrderDetails = new DataRelation("OrderDetailsForOrder",orders.Columns["OrderID"],
                                                              orderDetails.Columns["OrderID"]);
                dataset.Relations.Add(ordersToOrderDetails);
                 
                //Show the dataset
                datagridCustomers.DataSource = dataset;
                datagridCustomers.DataMember = customers.TableName;
            }
            catch (Exception ex)
            {
                HandleException("The data could not be load", ex);
                
            }
            
        }
        protected DataTable CreateAndFill(DataSet dataset, string tableName)
        {
            DataTable table = new DataTable(tableName);
            dataset.Tables.Add(table);
            Fill(table);
            return table;

        }
        protected void Fill(DataTable table)
        {
            OleDbCommand command = null;
            OleDbDataAdapter adapter = null;
            try
            {
                command = Connection.CreateCommand();
                command.CommandText = table.TableName;
                command.CommandType = CommandType.TableDirect;
                adapter = new OleDbDataAdapter(command);
                adapter.Fill(table);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (command != null)
                    command.Dispose();
                 
 
                
            }
        }

        private void menuDataSaveChanges_Click(object sender, EventArgs e)
        {
            SaveChanges();
        }
        public void SaveChanges()
        {
            if (_connection == null)
            {
                MessageBox.Show("You must connect to a database");
                return;
            }
            DataSet dataset = (DataSet)datagridCustomers.DataSource;
            if (dataset == null)
            {
                MessageBox.Show("You must load a DataSet");
                return;
            }
            OleDbCommand command = null;
            OleDbDataAdapter adapter = null;
            try
            {
                command = _connection.CreateCommand();
                command.CommandText = "Customers";
                command.CommandType = CommandType.TableDirect;
                adapter = new OleDbDataAdapter(command);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                adapter.Update(dataset);
                MessageBox.Show("changes have been saved");
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (command != null)
                    command.Dispose();
            }
        }

        

        
    }
}