Prerequisites:

• The CD database file (in the Microsoft Access format) available. [The CD database]

Goals:

• Use Microsoft Access database from ADO.NET.
  Microsoft Access databases (.mdb files) can be quite efficient solutions: they are binary, they use indexes and can be used by ADO.NET even when Microsoft Access is not installed.
• Create a simple Windows Forms application using basic data binding which will allow to view and edit the data (names of authors and their records).

Steps:

1. Create a new Windows Application project.
2. Add a connection to the cd.mdb file:
   • In the Server Explorer window right click on 'Data Connections', choose 'Add Connection' and select 'Microsoft Access Database File'.
   • Browse for the cd.mdb file.
   • The file is not protected by a password, so keep the password field empty.
   • Take a look at advanced options ('Advanced...' button); there is no need to change anything here.
   • Test the connection (use the 'Test Connection' button).
3. When the connection has been added to the Server Explorer window, you are able to view the structure and edit the content of the database using Visual Studio. Structure of a SQL Server database can be also modified here (this option is not available for Microsoft Access databases).
   
4. Add a Data Source to the project:
   • Choose the Data / Show Data Sources menu item.
   • Click on the 'Add New Data Source' link and choose the 'Database' option.
     
   • Choose existing (created in the previous step) connection to the cd.mdb file, check the connection string.
   • Decide if the data file should be included in the project (it is important question, please understand your answer) and choose the name of the connection used in application's configuration file.
   • Select both tables as parts of a new dataset.
     
     
   • Investigate the created DataSet - choose the 'Edit DataSet with Designer' option (from the context menu or the toolbar in 'Data Sources' window).
     • Check the relation between the tables.
     • Investigate properties of all objects (open the 'Properties' window), in particular, check commands of adapters (SQL code can be found here).
5. Add a SplitContainer and two ListBoxes (named: authorsListBox and recordsListBox) to the form.
   • Drag tables from the 'Data Sources' window and drop them on corresponding ListBoxes.
   • Note that additional components (tblAuthorsBindingSource, tblAuthorsTableAdapter and analogous for the tblRecords table) were created automatically and some properties of the ListBox controls were set:
     
   • Investigate the source code of the form, make sure to understand the meaning of automatically generated code:
     
     private void Form1_Load(object sender, EventArgs e)
     {
         // TODO: This line of code loads data into the 'cdDataSet.tblRecords' table. You can move, or remove it, as needed.
         this.tblRecordsTableAdapter.Fill(this.cdDataSet.tblRecords);
         // TODO: This line of code loads data into the 'cdDataSet.tblAuthors' table. You can move, or remove it, as needed.
         this.tblAuthorsTableAdapter.Fill(this.cdDataSet.tblAuthors);
     }
   • Run the project and test the result:
     
   • Unfortunately, all records are listed, regardless of the selected author, so it's time to use the keyboard ;).
     • Create the following method:
     void showRecordsForCurrentAuthor()
     {
         BindingSource bs = recordsListBox.DataSource as BindingSource;
         if (bs != null)
         {
             bs.Filter = "IDAuthor = " + authorsListBox.SelectedValue;
         }
     }
     • Call this method from Form1_Load() and authorsListBox_SelectedIndexChanged() (it is a handler for SelectedIndexChanged event for authorsListBox)
       
   • To sort items alphabetically, set the Sort properties of the tblAuthorsBindingSource and tblRecordsBindingSource components to 'Name ASC' (in both cases the same name of a column is used, but note that they are different columns from different tables).
6. Create a new Form which will allow to edit names of authors and change currently selected author using 'Next', 'Prev', 'First', and 'Last' buttons.
   
   • Name the form 'OneAuthorForm'.
   • Add controls to the form:
     
   • Drag both tables from the 'Data Sources' window and drop them on the recordsListBox control. As a result, new components are added to the form:
     
   • Set values for the recordsListBox control properties:
     
   • Bind the authorTextBox control's Text property to the Name column from the tblAuthors table:
     
   • Make the OneAuthorForm form to be the main form of the application:
     • Modify the parameter of calling the Application.Run() method in the Program.cs file.
   • Run the project.
   • Make buttons 'Next', 'Prev', 'First', and 'Last' start working (according to alphabetical order of authors' names).
     • As for the previous form, to sort items alphabetically set the Sort properties of the tblAuthorsBindingSource and tblRecordsBindingSource components to 'Name ASC'.
     • Add handlers for the Click events for buttons:
     private void firstButton_Click(object sender, EventArgs e)
     {
         tblAuthorsBindingSource.MoveFirst();
     }
     
     private void prevButton_Click(object sender, EventArgs e)
     {
         tblAuthorsBindingSource.MovePrevious();
     }
     
     private void nextButton_Click(object sender, EventArgs e)
     {
         tblAuthorsBindingSource.MoveNext();
     }
     
     private void lastButton_Click(object sender, EventArgs e)
     {
         tblAuthorsBindingSource.MoveLast();
     }
     • In response for the PositionChange event of the tblAuthorsBindingSource component, set the filter for the tblRecordsBindingSource:
       private void tblAuthorsBindingSource_PositionChanged(object sender, EventArgs e)
       {
           tblRecordsBindingSource.Filter = "IDAuthor=" +
               ((DataRowView)tblAuthorsBindingSource.Current)["ID"];
       }
   • Allow to save changed names of authors
     • Add the following code as a handler for the FormClosing event.
       private void OneAuthorForm_FormClosing(object sender, FormClosingEventArgs e)
       {
           if (cdDataSet.HasChanges())
           {
               switch (MessageBox.Show("The data has been changed. Press:\n" +
                       "Yes - to save data to the file\n" +
                       "No - to discard changes\n" +
                       "Cancel - to cancel closing the application",
                       "THE DATA HAS BEEN CHANGED",
                       MessageBoxButtons.YesNoCancel))
               {
                   case DialogResult.Yes:      // save data to the file
                       try
                       {
                           tblAuthorsTableAdapter.Update(cdDataSet.tblAuthors);
                       }
                       catch (Exception exc)
                       {
                           MessageBox.Show("EXCEPTION: " + exc.ToString());
                       }
                       break;
       
                   case DialogResult.No:       // discard changes
                       break;
       
                   case DialogResult.Cancel:   // cancel closing the application
                       e.Cancel = true;
                       break;
               }
           }
       }
       • Be aware of possibility of replacing the database file (cd.mdb) in an output directory with the one existing in the source directory (it depends on the option chosen during adding connection to the database file to a project). In such case, changes will be saved but replaced again with original data at the next running of the project from Visual Studio.
     • Note that changes in the current author's name are not saved in the database. The reason is that editing of the current row of the BindingSource component was not ended, so the solution is to do it manually. Add the following line at the beginning of a handler of the FormClosing event:
       tblAuthorsBindingSource.EndEdit();

[Source code]