". Net deep breaths" points to note when connecting to an Access database

Local database can have y choice, such as SQLite, SQL Server Express, SQL local DB, SQL Server CE, access, etc., this article used the more famous access local database, the connection database should pay attention to several things.

The Access database is a member of the Office family with a long history and I'm sure you won't be unfamiliar. However, there are things you may not have noticed before, so let's take a look around.

First, determine whether the provider for the connection is available

In the past, connecting to an Access database, we all use OLE DB's microsoft.jet.oledb.4.0 provider, which is also provided by default, so it's especially handy.

However, Jet.oledb is typically 32-bit, and an exception occurs if the application compiles to the x64 version. As shown in.

If you change to x86, you can run normally. And, as you can see, the use of any CPU will work as well.

Now that you open the Project Properties window and switch to the Build tab, do you see an option called "Preferred 32-bit" in the Any CPU option? OK, now you remove the "preferred 32-bit" tick, as shown in.

Then, you run on a 64-bit platform, and the same exception occurs. If the "preferred 32-bit" is checked, no exception occurs.

Everything is changing, the access version is constantly updated, from 2007 onwards, the. mdb file becomes a. accdb file, so using the Jet.oledb engine is not possible to connect the. mdb file.

The provider that connects the. accdb file is microsoft.ace.oledb.12.0, that is, the connection string should read:

Provider=microsoft.ace.oledb. 12.0;D ata source=| Datadirectory|\\students.accdb

For the connection string, you can go to the following website to copy: http://www.connectionstrings.com/

This site can find a variety of database connection strings, resource-rich, quite worth

System default is not with microsoft.ace.oledb.12.0 driver, you get here Download: https://www.microsoft.com/zh-cn/download/details.aspx?id=13255

When connecting the. accdb file, be sure to build on the version, if you install the engine is 32-bit, the application should be born into the x86 version, if the installed engine is 64-bit, then your application should generate x64 version.

A friend of the big friends will certainly ask, that there is no way to determine whether the system with a specific provider, if not, you can prompt users to install? The answer is naturally yes, come on, take a look at this example first.

Look at the code:

            OleDbDataReader reader = oledbenumerator.getrootenumerator ();             New DataTable ();            Table. Load (reader);             = table. DefaultView;

Or, you can do this:

            New oledbenumerator ();             = Enumerator. GetElements ();             = dt. DefaultView;

Such a trick will list the currently supported OLE DB providers. Such as.

Where the Sources_name column is the name of the provider, so in the program code, as long as you detect whether the provider you want to exist, you can know that there is no relevant engine installed.

For example, the following code verifies whether the current running environment can connect to the. accdb file.

            stringACCDBPRD ="microsoft.ace.oledb.12.0"; Oledbenumerator ENU=NewOledbenumerator (); DataTable DTPRD=ENU.            GetElements (); //Linq            varQR = fromRinchDTPRD. AsEnumerable ()wherer.field<string> ("Sources_name") ==ACCDBPRDSelectR; if(QR. Count () = =0) MessageBox.Show ("Alas, the relevant provider has not been installed. "); ElseMessageBox.Show ("You can connect to the database. ");

Second, how to connect the. accdb file

This has been said above, as long as the connection string in the provider to microsoft.ace.oledb.12.0, or refer to the old week above the information of the site, anyway, this you will.

Three, about | datadirectory| placeholder

Data Source sets the path to the database file, which is usually written for convenience: Data source= |datadirectory|\\mydb.accdb. The DataDirectory placeholder is case insensitive. By default, for Web projects, DataDirectory points to the App_Data in the project directory, which is the custom of ASP, not much of the old week. For other applications, DataDirectory points to the directory where the application runs, and if the program is running under the \bin\debug directory, then the data directory is \bin\debug.

However, DataDirectory can be changed, and the following example changes the data directory to the "documents" directory of the currently logged-in user.

            string Docpath = Environment.getfolderpath (Environment.SpecialFolder.MyDocuments);            AppDomain.CurrentDomain.SetData ("DataDirectory", Docpath);

Calling the SetData method of the AppDomain modifies the data directory that the |datadirectory| placeholder points to. Now I put the. accdb file under "Documents" under my user name, C:\users\<my name>\documents.

The connection string can then be written like this:

Provider=microsoft.ace.oledb. 12.0;D ata source=| Datadirectory|\\pigstore.accdb

The actual file path pointed to is: C:\users\<my name> \documents\pigstore.accdb.

OK, well, useful things on the here, useless words old week has been omitted, this article is written here, it is time to fill the belly.

". Net deep breaths" points to note when connecting to an Access database