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 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 <br/> Public static bool createmdbdatabase (string mdbpath) <br/>{< br/> try <br/>{< br/> ADOX. catalogclass cat = new ADOX. catalogclass (); <br/> cat. create ("provider = Microsoft. jet. oledb.4.0; Data Source = "+ mdbpath +"; "); <br/> cat = NULL; <br/> return true; <br/>}< br/> catch {return false ;}< br/>}< br/>

2. Create a specific table. The routine is as follows:
Generally, an MDB can contain N tables. The following Program Create a table.
// Create an MDB table <br/> // mdbhead is an arraylist that stores the specific column names in the table. <Br/> Public static bool createmdbtable (string mdbpath, string tablename, arraylist mdbhead) <br/>{< br/> try <br/>{< br/> ADOX. catalogclass cat = new ADOX. catalogclass (); <br/> string saccessconnection <br/> = @ "provider = Microsoft. jet. oledb.4.0; Data Source = "+ mdbpath; <br/> ADODB. connection Cn = new ADODB. connection (); <br/> CN. open (saccessconnection, null, null,-1); <br/> cat. activeconnection = cn; </P> <p> // create a table <br/> ADOX. tableclass TBL = new ADOX. tableclass (); <br/> TBL. parentcatalog = cat; <br/> TBL. name = tablename; </P> <p> int size = mdbhead. count; <br/> for (INT I = 0; I <size; I ++) <br/>{< br/> // Add a text field <br/> ADOX. columnclass col2 = new ADOX. columnclass (); <br/> col2.parentcatalog = cat; <br/> col2.name = mdbhead [I]. tostring (); // column name <br/> col2.properties ["jet oledb: Allow zero length"]. value = false; <br/> TBL. columns. append (col2, ADOX. datatypeenum. advarwchar, 500); <br/>}< br/> cat. tables. append (TBL); // Add the table to the database (very important) <br/> TBL = NULL; <br/> cat = NULL; <br/> CN. close (); <br/> return true; <br/>}< br/> catch {return false;} <br/>}

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 <br/> Public static datatable readalldata (string tablename, string mdbpath, ref bool success) <br/>{< br/> datatable dt = new datatable (); <br/> try <br/>{< br/> datarow Dr; <br/> // 1. Establish a connection <br/> string strconn <br/> = @ "provider = Microsoft. jet. oledb.4.0; Data Source = "+ mdbpath +"; Jet oledb: Database Password = haoren "; <br/> oledbconnection odcconnection = new oledbconnection (strconn ); <br/> // 2. Open the connection <br/> odcconnection. open (); <br/> // create an SQL query <br/> oledbcommand odcommand = odcconnection. createcommand (); <br/> // 3. Enter the query statement <br/> odcommand. commandtext = "select * from" + tablename; <br/> // create read <br/> oledbdatareader odrreader = odcommand. executereader (); <br/> // query and display data <br/> int size = odrreader. fieldcount; <br/> for (INT I = 0; I <size; I ++) <br/> {<br/> datacolumn DC; <br/> Dc = new datacolumn (odrreader. getname (I); <br/> DT. columns. add (DC); <br/>}< br/> while (odrreader. read () <br/>{< br/> DR = DT. newrow (); <br/> for (INT I = 0; I <size; I ++) <br/>{< br/> Dr [odrreader. getname (I)] = odrreader [odrreader. getname (I)]. tostring (); <br/>}< br/> DT. rows. add (DR); <br/>}< br/> // close the connection <br/> odrreader. close (); <br/> odcconnection. close (); <br/> success = true; <br/> return DT; <br/>}< br/> catch <br/> {<br/> success = false; <br/> return DT; <br/>}< br/>}

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

// Read MDB data <br/> Public static datatable readdatabycolumns (string mdbpaht, string tablename, string [] columns, ref bool success) <br/>{< br/> datatable dt = new datatable (); <br/> try <br/>{< br/> datarow Dr; <br/> // 1. Establish a connection <br/> string strconn <br/> = @ "provider = Microsoft. jet. oledb.4.0; Data Source = "+ mdbpath +"; Jet oledb: Database Password = haoren "; <br/> oledbconnection odcconnection = new oledbconnection (strconn ); <br/> // 2. Open the connection <br/> odcconnection. open (); <br/> // create an SQL query <br/> oledbcommand odcommand = odcconnection. createcommand (); <br/> // 3. Enter the query statement <br/> string strcolumn = ""; <br/> for (INT I = 0; I <columns. length; I ++) <br/>{< br/> strcolumn + = columns [I]. tostring () + ","; <br/>}< br/> strcolumn = strcolumn. trimend (','); <br/> odcommand. commandtext = "select" + strcolumn + "from" + tablename; <br/> // create read <br/> oledbdatareader odrreader = odcommand. executereader (); <br/> // query and display data <br/> int size = odrreader. fieldcount; <br/> for (INT I = 0; I <size; I ++) <br/> {<br/> datacolumn DC; <br/> Dc = new datacolumn (odrreader. getname (I); <br/> DT. columns. add (DC); <br/>}</P> <p> while (odrreader. read () <br/>{< br/> DR = DT. newrow (); <br/> for (INT I = 0; I <size; I ++) <br/>{< br/> Dr [odrreader. getname (I)] = odrreader [odrreader. getname (I)]. tostring (); <br/>}< br/> DT. rows. add (DR); <br/>}< br/> // close the connection <br/> odrreader. close (); <br/> odcconnection. close (); <br/> success = true; <br/> return DT; <br/>}< br/> catch <br/> {<br/> success = false; <br/> return DT; <br/>}< br/>

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

To add a reprinted message, please indicate the source. Thank you! Banzhiyan Ajie http://blog.csdn.net/gisfarmer/