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()
{
//Diaplay the dialog.....
if (DialogResult.OK == dialogOpenFile.ShowDialog(this))
{
//try to connect
try
{
//Create a new connection string......
string connectionString = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=;Password=;",
dialogOpenFile.FileName);
//Create a connection
OleDbConnection newConnection =
new OleDbConnection(connectionString);
//try and open it.....
newConnection.Open();
// Store it for use....
_connection = newConnection;
}
catch (Exception ex)
{
//Report the problem....
HandleException("A connection could not be made.", ex);
}
}
}
public void HandleException(string message, Exception ex)
{
//Display a message box...
MessageBox.Show(this, string.Format("{0}/n{1}:{2}",
message, ex.GetType().ToString(), ex.Message));
}
public OleDbConnection Connection
{
get
{
return _connection;
}
set
{
//Disconnect
Disconnect();
//Set
_connection = value;
}
}
public void Disconnect()
{
//Do we have a conncetion?
if(_connection !=null)
{
//Is it open?
if (_connection.State != ConnectionState.Closed)
_connection.Close();
//Clear it...
_connection = null;
}
}
private void menuDataLoad_Click(object sender, EventArgs e)
{
LoadData();
}
public void LoadData()
{
// Do we have a connection?
if (null == _connection)
{
MessageBox.Show(this, "You must connect to a database.");
return;
}
//Setup..
try
{
//Create a dataset....
DataSet dataset = new DataSet();
//Create and fill the tables.....
DataTable customers = CreateAndFill(dataset, "Customers");
DataTable orders = CreateAndFill(dataset, "Orders");
// DataTable orderDetails = CreateAndFill(dataset, "Order Details");
DataTable orderDetails = CreateAndFill(dataset, "Order Details Extended");
//Establish customers /orders relationship.....
DataRelation customersToOrders =
new DataRelation("OrdersForCustomers",
customers.Columns["CustomerID"],
orders.Columns["CustomerID"]);
dataset.Relations.Add(customersToOrders);
//Establish orders /order details relationship....
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)
{
//report the problem...
HandleException("The data could not be loaded.", ex);
}
}
protected DataTable CreateAndFill(DataSet dataset, string tableName)
{
//Create the new table...
DataTable table = new DataTable(tableName);
//add it to the dataset....
dataset.Tables.Add(table);
//Fill the table..
Fill(table);
// Retrun the table....
return table;
}
protected void Fill(DataTable table)
{
//set up command and adapter....
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 (null==_connection )
{
MessageBox.Show("You must connect to a database.");
return;
}
DataSet dataset = (DataSet)datagridCustomers.DataSource;
if (null == dataset)
{
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.Tables ["Customers"]);
MessageBox.Show("Changes have been saved.");
}
finally
{
//Claan up.....
if (null != adapter)
adapter.Dispose();
if (null != command)
command.Dispose();
}
}
}
}
{
public partial class Form1 : Form
{
private OleDbConnection _connection;
public Form1()
{
InitializeComponent();
}
private void menuDataConnect_Click(object sender, EventArgs e)
{
Connect();
}
public void Connect()
{
//Diaplay the dialog.....
if (DialogResult.OK == dialogOpenFile.ShowDialog(this))
{
//try to connect
try
{
//Create a new connection string......
string connectionString = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=;Password=;",
dialogOpenFile.FileName);
//Create a connection
OleDbConnection newConnection =
new OleDbConnection(connectionString);
//try and open it.....
newConnection.Open();
// Store it for use....
_connection = newConnection;
}
catch (Exception ex)
{
//Report the problem....
HandleException("A connection could not be made.", ex);
}
}
}
public void HandleException(string message, Exception ex)
{
//Display a message box...
MessageBox.Show(this, string.Format("{0}/n{1}:{2}",
message, ex.GetType().ToString(), ex.Message));
}
public OleDbConnection Connection
{
get
{
return _connection;
}
set
{
//Disconnect
Disconnect();
//Set
_connection = value;
}
}
public void Disconnect()
{
//Do we have a conncetion?
if(_connection !=null)
{
//Is it open?
if (_connection.State != ConnectionState.Closed)
_connection.Close();
//Clear it...
_connection = null;
}
}
private void menuDataLoad_Click(object sender, EventArgs e)
{
LoadData();
}
public void LoadData()
{
// Do we have a connection?
if (null == _connection)
{
MessageBox.Show(this, "You must connect to a database.");
return;
}
//Setup..
try
{
//Create a dataset....
DataSet dataset = new DataSet();
//Create and fill the tables.....
DataTable customers = CreateAndFill(dataset, "Customers");
DataTable orders = CreateAndFill(dataset, "Orders");
// DataTable orderDetails = CreateAndFill(dataset, "Order Details");
DataTable orderDetails = CreateAndFill(dataset, "Order Details Extended");
//Establish customers /orders relationship.....
DataRelation customersToOrders =
new DataRelation("OrdersForCustomers",
customers.Columns["CustomerID"],
orders.Columns["CustomerID"]);
dataset.Relations.Add(customersToOrders);
//Establish orders /order details relationship....
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)
{
//report the problem...
HandleException("The data could not be loaded.", ex);
}
}
protected DataTable CreateAndFill(DataSet dataset, string tableName)
{
//Create the new table...
DataTable table = new DataTable(tableName);
//add it to the dataset....
dataset.Tables.Add(table);
//Fill the table..
Fill(table);
// Retrun the table....
return table;
}
protected void Fill(DataTable table)
{
//set up command and adapter....
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 (null==_connection )
{
MessageBox.Show("You must connect to a database.");
return;
}
DataSet dataset = (DataSet)datagridCustomers.DataSource;
if (null == dataset)
{
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.Tables ["Customers"]);
MessageBox.Show("Changes have been saved.");
}
finally
{
//Claan up.....
if (null != adapter)
adapter.Dispose();
if (null != command)
command.Dispose();
}
}
}
}