C # Access [Up]

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/