C # Access [Up]

Source: Internet
Author: User
Tags mdb database
I have been asking questions from my siblings about c # Access operations on CSDN, so I used my spare time to summarize c # Access operations. The main problems solved are: create mdb CREATE table read table Content Query table content insert data into table delete records in table Insert Photos into table read photos in table etc.

I have been asking questions from my siblings about c # Access operations on CSDN, so I used my spare time to summarize c # Access operations. The main problems solved are: create mdb CREATE table read table Content Query table content insert data into table delete records in table Insert Photos into table read photos in table etc.

I have been asking questions from my siblings about c # Access operations on CSDN, so I used my spare time to summarize c # Access operations. The main problems solved are:
Create mdb
Create table
Read table content
Query table content
Insert data to table
Delete a record in a table
Insert a photo into a table
Read the photos in the table.
In addition, I am at a limited level. If you are not doing anything, please make an axe. Start with the question.

This article introduces the basic knowledge of C # accessing and operating the Access database, and provides a related routine. C # ADO. NET cannot create a new ACCESS (MDB) database by programming, so it can only use ADOX as the link library from COM for operations.
The main knowledge points are as follows:
Using System. Data. OleDb;
Using System. Data;
Connection String: String connectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = product. mdb ";
Establish a connection: OleDbConnection connection = new OleDbConnection (connectionString );
Use the OleDbCommand class to execute SQL statements:
OleDbCommand cmd = new OleDbCommand (SQL, connection );
Connection. Open ();
Cmd. ExecuteNonQuery ();

1. Create an mdb database. The routine is as follows:
Note that the mdbPath parameter is the complete path of mdb (excluding the table name ). Example: D: // test. mdb

// Create mdb public static bool CreateMDBDataBase (string mdbPath) {try {ADOX. catalogClass cat = new ADOX. catalogClass (); cat. create ("Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ mdbPath +"; "); cat = null; return true;} catch {return false ;}}

2. Create a specific table. The routine is as follows:
Generally, an mdb can contain n tables. The following program creates a table.
// Create an mdb table // mdbHead is an ArrayList that stores the specific column names in the table. Public static bool CreateMDBTable (string mdbPath, string tableName, ArrayList mdbHead) {try {ADOX. catalogClass cat = new ADOX. catalogClass (); string sAccessConnection = @ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ mdbPath; ADODB. connection cn = new ADODB. connection (); cn. open (sAccessConnection, null, null,-1); cat. activeConnection = cn; // create a new table named ADOX. tableClass tbl = new ADOX. tableClass (); tbl. parentCatalog = cat; tbl. name = tableName; int size = mdbHead. count; for (int I = 0; I <size; I ++) {// Add a text field ADOX. columnClass col2 = new ADOX. columnClass (); col2.ParentCatalog = cat; col2.Name = mdbHead [I]. toString (); // column name col2.Properties ["Jet OLEDB: Allow Zero Length"]. value = false; tbl. columns. append (col2, ADOX. dataTypeEnum. adVarWChar, 500);} cat. tables. append (tbl); // Add the table to the database (very important) tbl = null; cat = null; cn. close (); return true;} catch {return false ;}}

3. Read mdb content (completely read). The routine is as follows:
This routine returns a DataTable. If you need other formats, you can convert them by yourself.
// Read mdb data public static DataTable ReadAllData (string tableName, string mdbPath, ref bool success) {DataTable dt = new DataTable (); try {DataRow dr; // 1. Establish a connection string strConn = @ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ mdbPath +"; Jet OLEDB: Database Password = haoren "; OleDbConnection odcConnection = new OleDbConnection (strConn); // 2. Open the connection to odcConnection. open (); // create an SQL query OleDbCommand odCommand = odcConnection. createCommand (); // 3. Enter the query statement odCommand. commandText = "select * from" + tableName; // read OleDbDataReader odrReader = odCommand. executeReader (); // query and display data int size = odrReader. fieldCount; for (int I = 0; I <size; I ++) {DataColumn dc; dc = new DataColumn (odrReader. getName (I); dt. columns. add (dc);} while (odrReader. read () {dr = dt. newRow (); for (int I = 0; I <size; I ++) {dr [odrReader. getName (I)] = odrReader [odrReader. getName (I)]. toString ();} dt. rows. add (dr);} // close the connection to odrReader. close (); odcConnection. close (); success = true; return dt ;}catch {success = false; return dt ;}}

4. Read mdb content (read by column). The routine is as follows:
Columns array stores the name of the column you want to query (ensure that the column you want exists in the mdb table)

// Read the public static DataTable ReadDataByColumns (string mdbPaht, string tableName, string [] columns, ref bool success) {DataTable dt = new DataTable (); try {DataRow dr; // 1. Establish a connection string strConn = @ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ mdbPath +"; Jet OLEDB: Database Password = haoren "; OleDbConnection odcConnection = new OleDbConnection (strConn); // 2. Open the connection to odcConnection. open (); // create an SQL query OleDbCommand odCommand = odcConnection. createCommand (); // 3. Enter the query statement string strColumn = ""; for (int I = 0; I <columns. length; I ++) {strColumn + = columns [I]. toString () + ",";} strColumn = strColumn. trimEnd (','); odCommand. commandText = "select" + strColumn + "from" + tableName; // create an OleDbDataReader odrReader = odCommand. executeReader (); // query and display data int size = odrReader. fieldCount; for (int I = 0; I <size; I ++) {DataColumn dc; dc = new DataColumn (odrReader. getName (I); dt. columns. add (dc);} while (odrReader. read () {dr = dt. newRow (); for (int I = 0; I <size; I ++) {dr [odrReader. getName (I)] = odrReader [odrReader. getName (I)]. toString ();} dt. rows. add (dr);} // close the connection to odrReader. close (); odcConnection. close (); success = true; return dt ;}catch {success = false; return dt ;}}

I will simply write it here today, and I will complete the content later.

Http://blog.csdn.net/gisfarmer/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.