003 – Connecting to MS Access Database in Visual Studio 2010

Connecting to MS Access Database in Microsoft Visual Studio 2010

The task

Make a connection to the Microsoft Access database file, that is named mydb.mdb and placed on the drive by the following path:

C:\Programs\C_Sharp\Train_10\WindowsFormsApplication1\mydb.mdb

The application that need connect to database, must be realized as Windows Forms Application.

 

Performing

1. Run MS Visual Studio.

Run the MS Visual Studio. Creating application as Windows Forms Application.

 

2. Call the master of database connection.

To access the database file should be implemented connection of this file to the application. This is done by calling the command “Add New Data Source …” from the menu “Data” (Fig. 1) or by clicking at the far left of the toolbar of the “Data Source” button.

02_02_00_003_01_

Fig. 1. Calling the master of database connection

 

3. Selection of the data source type (DB).

After that, the window of master for the connection to data source will be opened (see fig. 2).

02_02_00_003_02_

Fig. 2. Selection of connection type from which application will get data

In the window need to select one of four options of connection to the data source.

MS Visual Studio 2010 provides four connection types to data sources:

  • Database – database connection and selection of database objects;
  • Service – opens a “Add Service Reference” dialog window that allows a create connection to a service that returns data for your application;
  • Object – allows you to select objects of our application, which can later be used to create controls that bound to data;
  • Share Point – allows you to connect to a SharePoint site and choose the SharePoint sites for your application.

In our case, we choose an element Database and continue by pressing on the Next button.

 

4. Selection of the model of database connection.

Next step – selection of the model of database connection (fig. 3).

02_02_00_003_03_

Fig. 3. Selection of the model of database connection

The system offers one of two

The system offers us a choice of one of two options:

  • of the data model based on data set (Dataset);
  • Data Model Entity, which means that the system can generate a data model from a database that can be Microsoft SQL Server, Microsoftt SQL Server Compact 3.5 or Microsoft SQL Server Database File, or create an empty model as a starting point for the visual design of the conceptual model using toolbar.

In our case we select DataSet model type.

 

5. Connecting to database.

The next wizard window (Fig. 4) is the choice of a data connection which will be used by application to connect to database.

02_02_00_003_04_

Fig. 4. Selection of connection to database

To create a new connection must select the button “New Connection …“. The window “Add connection” will be opened. In this window, we need add a new Microsoft Access connection also select the path to database file. In our case the “Data source” field already contains connection type “Microsoft Access Database File (OLE DB)” that is required for us.

02_02_00_003_05_

Fig. 5. Adding a new connection and selection database file

If you want select another database, then you must use the “Change …” button, which opens window shown in Fig. 6.

02_02_00_003_06_

Fig. 6. Data source changing

In the Fig. 6. by the Microsoft Visual Studio system will be offered the following types of data sources:

  • Microsoft Access Database File – database Microsoft Access;
  • Microsoft ODBC Data Source – access to database using ODBC (Open Database Connectivity);
  • Microsoft SQL Server;
  • Microsoft SQL Server Compact 3.5;
  • Microsoft SQL Server Database File;
  • Oracle Database Oracle database.

Press the “Browse…” button and, in opened window, select the path to database file “mydb.mdb“. It is reasonable to place the database file in the same directory that contains module of executive program.

To verify the connection, you can use the button “Test Connection“.

After pressing the OK button the system will generate string “Connection string” (Fig. 7) which further useful to us for program connection to the database.

02_02_00_003_07e

Fig. 7. String “mydb.mdb” and string “Connection string

 

6. Forming configuration file of application.

After selecting the wizard button “Next” the screen appears, which offers add connection string in application configuration file (Fig. 8).

02_02_00_003_08e

Fig. 8. The proposal to save database connection string in configuration file of application

Do not change anything, leave everything as is (select button Next).

 

8. Choosing the database objects for using in program.

Last Wizard (Fig. 9) offers a select list of objects (tables, queries, macros, forms, etc.) to be used in the dataset. Normally we select all tables (the database contains three tables: “Order“, “Customer” and “Tariff“).

02_02_00_003_09_

Fig. 9. Selection of database objects

By pressing a “Finish” button the wizard of connection to database will closed. Now our application is connected to database.

 

9. What has changed in the program as a result of wizard execution?

If you select a panel Data Source (Fig. 10), you can see how the connected data set named mydbDataSet has a three tables “Order“, “Plan” and “Customer” with the appropriate fields.

02_02_00_003_10_

Fig. 10. The DataSources panel

You can also notice a changes in the panel Server Explorer (Fig. 11) where appeared database “mydb.mdb“. We can have several databases in application.

02_02_00_003_11_

Fig. 11. The panel “Server Explorer

10. Connecting of methods of operating the database.

In order to use the methods that will operate with database MS Access (not just MS Access), you must include namespace System.Data.OleDb.

For this in the main form (Form1.cs) in the Solution Explorer select the code view mode (View Code) from the context menu (Fig. 12) and at the begin of file add the following line:

using System.Data.OleDb;

02_02_00_003_12e

Fig. 12. Calling of the program code of main form (Form1.cs) in Solution Explorer

  General view of the top of the “Form1.cs” file already has the form:

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;

That’s all, the connection to database “mydb.db” was realized. Further steps are coding for manipulating data in the database.


Related topics