• Access databases (.mdb files) can be quite efficient solutions: they are binary, they use indexes and can be used by ADO.NET when Access is not installed.

• Access is a quite comfortable solution for small databases. It allows to create relational databases, design user interface (forms), prepare queries (using SQL), design reports and web pages. Visual Basic for Applications can be used to create non-standard solutions.

• Create a new Windows Application project.
• Add a connection to cd.mdb file:
  • In Server Explorer window right click on 'Data Connections', choose 'Add Connection' and select 'Microsoft Access Database File'.
  • Browse for cd.mdb file (extracted from cd.zip file).
  • The file is not protected by a password, so keep a password field empty.
  • Take a look at advanced options ('Advanced...' button); there is no need to change anything here.
  • Test the connection ('Test Connection' button).
• When the connection has been added to the Server Explorer window, you are able to view a structure and edit a content of the database using Visual Studio. Structure of a SQL Server database can be also modified here (this option is not available for MS Access databases).
• Add a Data Source to the project.
  • Choose Data / Show Data Sources menu item.
  • Click on 'Add New Data Source' link and choose 'Database' option.
    
  • Choose existing (created in previous step) connection to cd.mdb file, check a connection string.
  • Decide if the data file should be included in the project 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 'Edit Data with Designer' option (from a context menu or a toolbar in 'Data Sources' window).
    • Check a relation between tables.
    • Investigate properties of all objects (open the 'Properties' window), in particular check commands of adapters (SQL code can be found here).
• Add a SplitContainer and two ListBoxes (named: authorsListBox and recordsListBox) to the form.
  • Drag both tables from the 'Data Sources' window and drop them on ListBoxes.
  • Set the binding to tblAuthors table as a value for 'DataSource' property of authorsListBox (new tblAuthorsBindingSource will be created and used automatically).
    
  • Set 'Name' value for 'DisplayMember' property.
  • Run the project and admire the ListBox filled with names of all authors from the database.
    • Investigate a source code of the form, make sure to understand the meaning of the following line:
      
      this.tblAuthorsTableAdapter.Fill(this.cdDataSet.tblAuthors);
  • Set the binding to tblRecords table as a value for 'DataSource' property of recordsListBox (new tblRecordsBindingSource will be created and used automatically).
  • Set 'Name' value for 'DisplayMember' property.
  • Run the project. 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() (this is a handler for SelectedIndexChanged event for authorsListBox)
      
  • To sort items alphabetically:
    • Add the following lines to Form1_Load() method:

      cdDataSet.tblAuthors.DefaultView.Sort = "Name asc";
      authorsListBox.DataSource = cdDataSet.tblAuthors.DefaultView;
      cdDataSet.tblRecords.DefaultView.Sort = "Name asc";
      recordsListBox.DataSource = cdDataSet.tblRecords.DefaultView;
      

    • Run the project and notice that changing a content of a ListBox with records stopped working. Modify the showRecordsForCurrentAuthor() method (the DataSource of recordsListBox has changed and it is an object of a DataView class):

    void showRecordsForCurrentAuthor()
    {
        DataView dv = recordsListBox.DataSource as DataView;
        if (dv != null) {
            dv.RowFilter = "IDAuthor = " + authorsListBox.SelectedValue;
        }
    }
    

• 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 all necessary controls.
  • Drag both tables from 'Data Sources' window and drop them on the ListBox.
    
  • Set values for the ListBox's (recordsListBox) properties:
    • DataSource: tblRecordsBindingSource
    • DisplayMember: Name
    • ValueMember: ID
  • Bind the TextBox's (authorTextBox) Text property to the Name column from tblAuthors.
    
  • Make the OneAuthorForm form to be the main form of the application.
    • Modify parameter of calling Application.Run() method in Program.cs file.
  • Run the project.
  • Make buttons 'Next', 'Prev', 'First', and 'Last' to start working (according to alphabetical order of authors' names).
    • Add the following lines to OneAuthorForm_Load() method.

    tblAuthorsBindingSource.Sort = "Name asc";
    tblRecordsBindingSource.Sort = "Name asc";
    
    tblAuthorsBindingSource.PositionChanged += new EventHandler(tblAuthorsBindingSource_PositionChanged);
    tblAuthorsBindingSource_PositionChanged(this, EventArgs.Empty);
    

    • Add handlers for 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();
    }
    

    • Implement handler for PositionChange event:

    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 FormClosing event.
      • Be aware of possibility of replacing the database file (cd.mdb) in an output directory with the one existing in a source directory (it depends on the option chosen during adding connection to the file to a project). In such case, changes will be saved but replaced again with original data during the next running the project from Visual Studio.

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

SqlConnection myConn = new SqlConnection(
    "Server=MOSS\\SQLEXPRESS;Integrated security=SSPI;database=master");
try {
    myConn.Open();

    string str = "CREATE DATABASE mySecondDatabase";
    SqlCommand myCommand = new SqlCommand(str, myConn);
    myCommand.ExecuteNonQuery();

    MessageBox.Show("DataBase is Created Successfully");
}
catch (System.Exception ex) {
    MessageBox.Show(ex.ToString());
}
finally {
    if (myConn.State == ConnectionState.Open) {
        myConn.Close();
    }
}

• Make sure to change the connection string, in particular the name of the SQL Server instance
• If you want to create your database in non-standard location or to specify some advanced properties, you can define more details in CREATE DATABASE command:

str = "CREATE DATABASE MyDatabase ON PRIMARY " +
      "(NAME = MyDatabase_Data, " +
      "FILENAME = 'C:\\MyDatabaseData.mdf', " +
      "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
      "LOG ON (NAME = MyDatabase_Log, " +
      "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
      "SIZE = 1MB, " +
      "MAXSIZE = 5MB, " +
      "FILEGROWTH = 10%)";

SqlConnection myConn = new SqlConnection(
    "Server=MOSS\\SQLEXPRESS;Integrated security=SSPI;database=mySecondDatabase");
try {
    myConn.Open();

    string str = "CREATE TABLE tblAuthors (" +
        "ID int PRIMARY KEY, " +
        "Name varchar(100))";
    SqlCommand myCommand = new SqlCommand(str, myConn);
    myCommand.ExecuteNonQuery();

    str = "CREATE TABLE tblRecords (" +
        "ID int PRIMARY KEY, " +
        "IDAuthor int FOREIGN KEY REFERENCES tblAuthors (ID), " +
        "Name varchar(100))";
    myCommand.CommandText = str;
    myCommand.ExecuteNonQuery();

    MessageBox.Show("Tables in the Database are Created Successfully");
}
catch (System.Exception ex) {
    MessageBox.Show(ex.ToString());
}
finally {
    if (myConn.State == ConnectionState.Open) {
        myConn.Close();
    }
}

• Note the name of a database used in a connection string.

• The simplest way to find correct connection string to any database is to use Visual Studio's Server Explorer
  
• Use the following code to copy all data from Access to SQL Server database.

  OleDbConnection accConn = new OleDbConnection(
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"D:\\Moje dokumenty\\cd.mdb\"");
  SqlConnection sqlConn = new SqlConnection(
      "Server=MOSS\\SQLEXPRESS;Integrated security=SSPI;database=mySecondDatabase");
  
  try {
      accConn.Open();
      sqlConn.Open();
  
      // Authors
      string str = "SELECT ID, Name FROM tblAuthors";
      OleDbCommand accCommand = new OleDbCommand(str, accConn);
      OleDbDataReader reader = accCommand.ExecuteReader();
      if (reader.HasRows) {
          while (reader.Read()) {
              int id = reader.GetInt32(0);
              string name = reader.GetString(1);
              name = name.Replace("\'", "\'\'");
  
              str = "INSERT INTO tblAuthors (ID, Name) " +
                  "VALUES (" + id + ", \'" + name + "\')";
              SqlCommand sqlCommand = new SqlCommand(str, sqlConn);
              sqlCommand.ExecuteNonQuery();
          }
      }
      reader.Dispose();
  
      // Records
      str = "SELECT ID, IDAuthor, Name FROM tblRecords";
      accCommand.CommandText = str;
      reader = accCommand.ExecuteReader();
      if (reader.HasRows) {
          while (reader.Read()) {
              int id = reader.GetInt32(0);
              int idAuthor = reader.GetInt32(1);
              string name = reader.GetString(2);
              name = name.Replace("\'", "\'\'");
  
              str = "INSERT INTO tblRecords (ID, IDAuthor, Name) " +
                  "VALUES (" + id + ", " + idAuthor + ", \'" + name + "\')";
              SqlCommand sqlCommand = new SqlCommand(str, sqlConn);
              sqlCommand.ExecuteNonQuery();
          }
      }
      reader.Dispose();
  
      MessageBox.Show("The data has been copied successfully");
  }
  catch (Exception ex) {
      MessageBox.Show(ex.ToString());
  }
  finally {
      if (accConn.State == ConnectionState.Open) {
          accConn.Close();
      }
      if (sqlConn.State == ConnectionState.Open) {
          sqlConn.Close();
      }
  }
  

  • Notice the order of adding data to SQL Server's database: records after authors. Such order is necessary due to contraints declared in tables (foreign key in tblRecords table).
  • Notice also duplicating apostrophes in strings added to SQL Server tables.

• Add two labels presenting current provider's name (providerLabel) and used connection string (connectionStringLabel).
• Add a DataGridView control for presenting the data.

• Replace a content of this file with the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="myConnectionString"
        connectionString="Server=MOSS\SQLEXPRESS;Integrated security=SSPI;database=mySecondDatabase;"
        providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>