Manipulating an Access database by using C #

Without Microsoft Access at hand, how to build a database, everything remains simple.

First, refer to C:Program FilesCommon FilesSystemadomsadox.dll, which contains the ADOX namespace;

Next, refer to C:Program FilesCommon FilesSystemadomsjro.dll, which contains the JRO namespace

SxS: If the import DLL is unsuccessful, import the COM component as a. NET component manually and import it with the Vs.net tool

Using System;

Using System.IO;

Using ADOX; The namespace contains classes (methods) that create access--Solution ==> Reference ==> Add Reference ==> Tour find. dll

Using JRO; The namespace contains classes that compress access (methods)

public class Access

... {

/**////Create an Access database based on the specified file name

Mdbpath: Access absolute path to create a piece

public void Create (string mdbpath)

... {

if (file.exists (Mdbpath))//check that the database already exists

... {

throw new Exception ("target database already exists, cannot be created");

}

You can add a password so that the created database must enter a password to open

Mdbpath = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mdbpath;

Creates an instance of a Catalogclass object,

ADOX. Catalogclass cat = new ADOX. Catalogclass ();

Create an Access database by using the Create method of the Catalogclass object

Cat. Create (Mdbpath);

}

/**////compression fixes an Access database, Mdbpath the absolute path to the database

public void Compact (string mdbpath)

... {

if (! File.exists (Mdbpath))//check if the database already exists

... {

throw new Exception ("destination database does not exist, cannot be compressed");

}

Declaring the name of the staging database

String temp = DateTime.Now.Year.ToString ();

Temp + = DateTime.Now.Month.ToString ();

Temp + = DateTime.Now.Day.ToString ();

Temp + = DateTime.Now.Hour.ToString ();

Temp + = DateTime.Now.Minute.ToString ();

Temp + + DateTime.Now.Second.ToString () + ". Bak";

temp = mdbpath.substring (0, Mdbpath.lastindexof ("") +1) + temp;

Define a connection string for the staging database

Temp2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + temp;

Define the connection string for the target database

MdbPath2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mdbpath;

Create an instance of a Jetengineclass object

JRO. Jetengineclass JT = new JRO. Jetengineclass ();

To compress a repair database using the CompactDatabase method of the Jetengineclass object

Jt.compactdatabase (MdbPath2, TEMP2);

Copy temporary database to target database (overwrite)

File.Copy (temp, Mdbpath, true);

Finally delete the staging database

File.delete (temp);

}

/**////backup Database, MDB1, source database absolute path; MDB2: Target Database absolute path

public void Backup (string mdb1, String mdb2)

... {

if (! File.exists (MDB1))

... {

throw new Exception ("source database does not exist");

}

Try

... {

File.Copy (MDB1, MDB2, true);

}

catch (IOException ixp)

... {

throw new Exception (IXP. ToString ());

}

}

/**////Restore the database, mdb1 the absolute path to the backup database, mdb2 the absolute path to the current database

public void Recover (string mdb1, String mdb2)

... {

if (! File.exists (MDB1))

... {

throw new Exception ("Backup database does not exist");

}

Try

... {

File.Copy (MDB1, MDB2, true);

}

catch (IOException ixp)

... {

throw new Exception (IXP. ToString ());

}

}

}

******************************************************************************************************

In the BETA2,. NET provides the following namespace:

System.Data Namespace

System.Data.OleDb (and BETA1 is already different, so if you take the program in BETA1 to BETA2 to run it is certainly not possible)

If you want to be clear about these things, I do not think that I can do, so I want to use a number of specific procedures to the database of our most basic operations (SELECT, UPDATE, DELETE, insert, etc.) to demonstrate, the other still need friends in the learning process to slowly experience!

To operate a database, regardless of that operation, the first thing to do is to open the database, we use an Access database to illustrate how to open a database connection! Here we need to use: System.Data.OleDb.OleDbConnection class ! (If you are working with SQL databases, we'd better use the System.Data.SqlClient.SqlConnection Class)

I first write the program I use:

Using System.Data

Using System.Data.OleDb

Public OleDbConnection Getconn ()

{

String connstr= "provider=microsoft.jet.oledb.4.0;D ata Source=f:webnotesbookclassleavenotes.mdb";

OleDbConnection tempconn= New OleDbConnection (CONNSTR);

return (Tempconn);

}

I believe that as long as the use of ADO friends should be able to understand! We first define a string type variable that holds the connection string for our connection to the database, and then defines a System.Data.OleDb.OleDbConnection the object of the type and instantiate it, and finally return the object! To illustrate, I did not put the sentence: Tempconn. Open (); Put it in this function, cause I'm going to explain later, here's just a reminder!

Through the above function, we have got similar to ADO in the Connection object connection! The following is the specific operation of the database!

Before I go into specifics, I think it's necessary to get to know the following two classes:

System.Data.OleDb.OleDbDataAdapter

System.Data.OleDb.OleDbDataReader

System.Data.OleDb.OleDbDataAdapter: can direct and dataset contact, and manipulate the data source, its function is relatively strong some, therefore also consumes the system resources!

System.Data.OleDb.OleDbDataReader: Some are similar to the read-only forward Recordset in ADO, which is most commonly used when you need to read and display data sequentially, compared System.Data.OleDb.OleDbDataAdapter, he consumes a small system resource! In fact, OleDbDataReader can achieve the functions, OleDbDataAdapter can be achieved, However, from the point of view of resource utilization we should try to use the former! But some functions, but must use OleDbDataAdapter to be able to achieve!

Note : For more wonderful tutorials, please pay attention to the Triple design Tutorials section,