Accessing an Access database in C #

access| Access | data | Database My motivation for writing this program is that I have no access to any information or reference material when I want to visit the MSAccess database with C sharp. All the material available on the web is SQL, so we'll write the application in two steps, First, we'll show you how to connect to the MSAccess database and see how complicated it is. Finally, we finished this program.

Gossip less preface, let's Get down to business. The process of connecting to a database has changed considerably compared to our earlier ADO connection process. The following chart is appropriate (I hope so) oledbconnection--> OleDbCommand--> OleDbDataReader. Now those who are familiar with ADO can clearly see the similarities but in order to make it clear to those who are not well adapted to ADO, here are some explanations.

OleDbConnection--> represents a single connection to the database, which gives you the ability to manipulate the database based on the functionality of the underlying database. One thing to remember is that although the OleDbConnection object is scoped, it is not automatically closed. So, You will have to display the call to this object's close () method.

OleDbCommand--> This is the usual command object, as we used in ADO. You can invoke SQL stored procedures or SQL query statements from this object.

OleDbDataReader--> This class has great importance because it provides virtually access to the underlying dataset of the database. When you call the OleDbCommand ExecuteReader method it is created,. NET Beta2 The SDK says not to directly create objects of this class.

Now you can see more about these main objects in the. NET Beta 2 documentation, which shows you how to access the source code for the database in your program.

Using System;
Using System.Data.OleDb;

Class oledbtest{

public static void Main ()
{
Creating a database connection
OleDbConnection aconnection = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db1.mdb");

Create a Command object and save the SQL query statement
OleDbCommand Acommand = new OleDbCommand ("SELECT * from Emp_test", aconnection);
Try
{
Aconnection.open ();

Create DataReader object to connect to form
OleDbDataReader areader = Acommand.executereader ();
Console.WriteLine ("This is the returned data from Emp_test table");

Looping through a database
while (Areader.read ())
{
Console.WriteLine (Areader.getint32 (0). ToString ());
}

Close Reader Object
Areader.close ();

It's important to close the connection
Aconnection.close ();
}

Some of the usual exception handling
catch (OleDbException E)
{
Console.WriteLine ("Error: {0}", E.errors[0]. message);
}
}
}

Steps to successfully run this program
1. Use MSAccess to create a database called Db1.mdb
2. Create a form called Emp_test
3. Make it include the following data fields
Emp_code int
Emp_name text
Emp_ext text
4. Save the above code in the Sample.cs file
5. Ensure that the database is located in C:\ and that mdac2.6 or newer versions have been installed
6. Compile and run

Now let's look at some details of what we see in the constructor of the OleDbConnection object, where you see things like "provider=". Here are some of the driver types that are compatible with ado.net.
SQLOLDDB--> Microsoft OLE DB Provider for SQL Server,
Msdaora--> Microsoft OLE DB Provider for Oracle,
microsoft.jet.oledb.4.0--> OLE DB Provider for Microsoft Jet
You can choose any one of them but they will need to pass different parameters, such as Jet.oledb. The name of the MDB file needs to be passed and SQLOLEDB need to pass the username and password.

All of these drivers are in the System.Data.OleDb namespace, so you have to include them, and they are not compatible with OLE DB Provider for ODBC, which means you can't use these drivers in the vb6.0 program to access the database. So don't look for explanations on why you put these databases on the C:\.

When you use Microsoft SQL Server 7.0 or later, here are some instructions from Microsoft:
It is recommended that. NET data provider Use Microsoft Data Engine (MSDE) or icrosoft SQL Server in the following scenarios using Microsoft SQL Server 7.0 or later in-tier applications 7.0 or newer, single tier applications.
It is recommended that you use OLE DB Provider for SQL Server (SQLOLEDB) and OLE DB. NET Data Provider.
For Microsoft SQL Server 6.5 and earlier versions, you must use both OLE DB Provider for SQL server and OLE Db.net Data Provider.
It is recommended that you use the OLE DB. NET Data Provider using Microsoft SQL Server 6.5 and earlier versions or Oracle middle-tier applications.
For Microsoft SQL Server 7.0 or later, the SQL Server. NET Data Provider is recommended.
It is recommended that a single tier application use a Microsoft Access database.
It is not recommended that one middle-tier program use both OLE DB. NET data provider and Microsoft Access databases.
Ole DB Provider for ODBC (MSDASQL) is no longer supported