C # Connect to an Access database (detailed)

As a VS2012 C # connection to the Access database of the memo, SQL database is powerful, big Microsoft's strong technical support, LINQ convenient operation, but if you write a small program on the database is not a big requirement, In the future, you can choose to use an Access database for your database, which saves you a certain amount of money.

First, we build a database, because people use different versions of Office, useful 2000 of the useful 2003 is also useful for 2007 or 2010. I use 2007, but whichever version you use, build the database as an . mdb End of the 2003 version of Do not build. accdb end of.

To build a table, please first design the column name, primary key and type in the Design view here we make a simple fruit table, your database name is Fruit.mdb, this is a database file, which can contain a variety of tables, Inside we built a table also called fruit please distinguish. After the table is built, save the path by saving the. mdb file in 2002-2003 format.

_________________________________________________ is still the gorgeous dividing line _____________________________________________________

The table is finished, run VS2012, we do a WinForm small application.

Drag into a DataGridView and a button to make an interface

Code

usingSystem.Data;usingSystem.Data.OleDb;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;namespacewinform_access{ Public Partial classForm1:form { PublicForm1 () {InitializeComponent (); }Private voidButton1_Click (Objectsender, EventArgs e) {OleDbConnection conn=NewOleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\\fruit.mdb"); OleDbCommand cmd=Conn.            CreateCommand (); Cmd.commandtext="select * from Fruit"; Conn.                        Open (); OleDbDataReader Dr=cmd.            ExecuteReader (); DataTable DT=NewDataTable (); if(Dr. HasRows) { for(inti =0; I < Dr. FieldCount; i++) {dt. Columns.Add (Dr.                GetName (i)); } dt.            Rows.clear (); }             while(Dr. Read ()) {DataRow row=dt.                NewRow ();  for(inti =0; I < Dr. FieldCount; i++) {Row[i]=Dr[i]; } dt.            Rows.Add (row); } cmd.            Dispose (); Conn.            Close (); Datagridview1.datasource=DT; }    }}

In this connection statement and SQL very much like, is to change SqlConnection to OleDbConnection, do not forget to add Reference using System.Data.OleDb;

"provider=microsoft.jet.oledb.4.0;data source=f:\\fruit.mdb"   stitching String,

If you're afraid of spelling mistakes, you can run the View-Server Explorer-right-click Data Connection-Add an Access database source and locate the file path, then copy the string connection in the property after the build is finished.

After the test link is successfully copied and pasted into OleDbConnection ().

If the translation is not recognized please change \ to \ or "" before adding @ such as "provider=microsoft.jet.oledb.4.0;data source=f:\\fruit.mdb" or @"provider=microsoft.jet.oledb.4.0;data source=f:\\fruit.mdb"

_________________________________________________ is still the gorgeous dividing line _____________________________________________________

If the "microsoft.jet.oledb.4.0" Provider is not registered on the local computer, it appears.

In the project Properties menu at the bottom of the menu  , select the Build tab and change the target platform from "Amy CPU" or "*64" to "*86".

Click Configuration Manager to create a new x86

Run

_________________________________________________ is still the gorgeous dividing line _____________________________________________________

Foreign articles

This is the if (Dr. HasRows) and while (Dr. Read ())

If you use

    if(Dr. Read ()) { for(inti =0; I < Dr. FieldCount; i++) {dt. Columns.Add (Dr.                GetName (i)); } dt.            Rows.clear (); }             while(Dr. Read ()) {DataRow row=dt.                NewRow ();  for(inti =0; I < Dr. FieldCount; i++) {Row[i]=Dr[i]; } dt.            Rows.Add (row); }

Data is missing the first!,read () read to the data return true but start reading from the second bar

DataReader. HasRows is only a check in the library, if there is a record, if any, return true, you have to read (). Datareader.read () is read back from the beginning of the data, if the library has been read to the end (may not have a record) returns false, if used in the IF, is automatically read one, else it does not have to use Read (), if there is a while (with the read () condition), then automatically start reading from the second conclusion: a single can be read () detection, more than one used while when using hasrows detection ... Disclaimer: SqlDataReader provides a way to read a forward-only stream of rows from a SQL Server database. This class cannot be inherited. I was having a little problem writing a small program: using SqlDataReader instance Reader's hasrows to determine if there is data in the data stream, and then performing the output operation of the data, which uses the code as follows SqlDataReader reader=Command.ExecuteReader (); while(reader. hasrows) {reader. Read (); Qx_str= reader[0]. ToString ();//Error Location}reader. Close (); Sqlcon. Close (); error after running in red position: invalid read attempt when no data is present this is obviously the location where the reader ran to no data stream, unable to perform the output of the data at this point, it is determined that the judgment condition is wrong, In the MSDN documentation, query to: HasRows property//HasRows Gets a value that indicates whether System.Data.SqlClient.SqlDataReader contains one or more rows. The original is out of the use of hasrows, as long as the SqlDataReader existence of the data stream (the data stream is not empty) the return of the bool value is true, so that the loop is always executed (no wonder use try:CatchSuspended animation). When the data is empty, read[0] When reading the data, an error will occur! So how do you avoid this error and still use the HasRows property? You can think of just using the IF statement to cancel the loop state!! So what do you do with while? Using the Read () method of the SqlDataReader instance, yes! Let's Meet the Read () method://Summary: Make System.Data.SqlClient.SqlDataReader advance to the next record. //returns the result: true if more than one row is present, otherwise false. you only need to send reader. Read () replaces reader. HasRows the execution loop, why is it OK? The default location of the SqlDataReader is [before the first record]. Therefore, you must call Read to start accessing any data. Read () advances to the next record. When the loop executes to read () No data, the loop ends!!        However, the read () in the loop is removed, and two rows of data are moved once per loop. }

FieldCount

is how many column fields are read, and here is an integer that is returned, the number of columns to read.

C # Connect to an Access database (detailed)