"7" Accessdb fast data access

Read Catalogue

• Comparison of C # and VB data access
• The design of Accessdb
• Connection to the database
• Three main operations
• Error output and commissioning
• Summary

Back to the top C # and VB data access comparison

To make a normal database query in C #, you need to create a connection, create the associated adapter object based on the specific database type, create the command object, execute the result, populate the dataset with the dataset, and then get the data from the DataTable. There are a variety of inconveniences in this approach:

1, a series of complex operations to complete a simple function, involving more objects, the realization of the logic and natural thinking habits are different.

2, different types of database processing is different, users need to remember the different types of database between the subtle differences.

3, some of the bottom-level work needs to be completed by the user, such as connection validity check, error after connection reconstruction work.

Looking back at the development history of Microsoft's development platform, there is no more user base than Visual Basic. VB6, launched in 1998, is still ranked among the top ten in the 2014 Tiobe programming language rankings.

In VB6, the database operation is extremely simple, as long as the control to drag a database can use most of the database operations. Even if you want to control it in a purely code way, it's easy to do it with the following steps:

Set ocn=new connectionset ors=ocn.excute (strSQL) ocn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb" while Not EOF (ORS)  ' processing  ors.movenextwend

The above code, lazy into the VB input, direct manual input, estimation also're far off, from the other side can also see the simplicity of VB code. Accustomed to such a simple way of database access, has always felt that the database processing in C # is too complex. Because of this, we continue to explore ways to simplify database access. Through the evolution of various ideas, from complex to simple, the final formation of a strong applicability, after many development practice test of fast data access mechanism ACCESSDB.

Back to the top accessdb design

From my personal experience, review the various software development projects involved, database access requirements are similar, with the 20/80 rule can be a good overview: 80% of the case, will only use 20% of the function. Therefore, as long as the ability to encapsulate the 20% function, you can adapt to the vast majority of database operation requirements. If you need exactly the remaining 20%, then follow the usual method of access.

Together, it is hoped that the data access mechanism can achieve the following goals:

1, simple enough. Simple to no memory.

2, applicable to a variety of databases. The types of databases used by small and medium-sized applications are often variable, and even the same project is common in changing databases at different stages (my habit is to use access to develop, post-SQL Server). Therefore, it is necessary to be able to migrate freely among a variety of commonly used databases. Currently supported Access/sqlserver/sqlite, can be done when switching the database, as long as the connection string changes, the code does not need to make any changes.

3, with extensibility. This data access mechanism, as the demand continues to enrich, constantly refactor, expand, and become the current look, the future may also continue to add new content. One of the most recent upgrades is the problem of having to use an absolute path when configuring the MDB file path in the previous web. config.

Based on the above design goals, a database access mechanism with ACCESSDB class as the core is realized through continuous summarization. Accessdb is an ingress class that provides a common static method without instantiation. Users can access the vast majority of database functionality simply by remembering the Accessdb class name. Throughout the Commoncode library, a similar naming style was maintained, the portal class for accessing the Web was AccessWeb, and the entry class for accessing the file was Accessfile ...

ACCESSDB creates specific data access entity objects based on the initialization criteria (configuration file or initialization command), such as Sqlserverdb, Sqlitedb, and so on. These objects all implement the IDB interface, and the functions of the IDB interface include our common database access capabilities.

Interface  The actual Access object of the IDB//database {bool Outputselectlog {get;set;}        Whether to output select log bool Outputnonquerylog {get;set;}      Whether to output the log bool IsOpen {get;}                    for the non-select operation; Whether the connection opens void Closeconn ();                       Close connection DataTable GetData (string sqlstr);       Get DataSet DataTable GetPage (String strSQL, int PageSize, int pageno, string keyfield, bool isstringkey) via SQL statement;D bresult DoN Onquery (string sqlstr);     Non-select query}

The IDB interface object is called directly in Accessdb to perform various operations. Because the primary methods of Accessdb are static methods, the IDB object must also be a static object. In other words, in a system run, there is only one database access object. In a few special cases where you need to operate multiple databases at the same time, you can access other databases by directly creating the IDB object. The structure of ACCESSDB is as follows:

is based on the class diagram of VS, modified by category to see more clearly. DBObject is the static object of the IDB, the properties and methods of the first layer of methods, are corresponding to the IDB interface, on the one hand these methods are generally strong, commonly used methods, on the other hand, deliberately these methods and IDB interface method consistent, so that, When users create Sqlserverdb, Sqlitedb objects themselves, the methods used are similar to ACCESSDB and reduce the complexity of their use.

The second class in the method is to complete the creation of static connections, maintenance, inspection errors, and so on, this part of the code has been constantly modified, has been relatively robust. In other words, the use of ACCESSDB development of the program, in connection with the database is relatively stable, whether it is a website, or desktop programs, long-term operation (the production of several sites continue to run very rarely because of database problems, the desktop program has written a data acquisition device Communications Server, can also be long-running) database functions are normal, in the case of moderate concurrent node access (when the number of cases is 200 or so of the terminal simultaneous access, sustained 2 lessons) also can work properly.

The third class in the method is the usual data operation, in another post in this series ("6" page data and the automatic switching mechanism of the controls), using Add and update and Getemptyrow, which are common methods in real data access. These methods are essentially also called the standard data provider, which provides these practical data operations only in ACCESSDB and does not provide these methods in the specific IDB.

Back to the top database connection

A connection can be created in two ways, in an implicit way, by controlling the creation of a connection through a configuration file, in the form of a display, and by invoking a method to complete the creation of the connection.

Implicit methods are commonly used in the creation of connections in Web applications, and are often applied explicitly to the creation of connections in desktop applications.

1. Implicit connection

When you create a connection from a configuration file, the database connection string used is exactly the same as the normal database connection string. The only special requirement is that the key value of the configured connection string must be mainDB2. Because the first connection string name used MainDB2, and later despite the continuous upgrade, in order to be compatible with the previous code, this has not changed.

The connection string format is compatible with the normal connection string, and the following are the various ways of connecting strings:

Standard connection strings in Web. config <configuration> <connectionStrings> <add name= "mainDB2" connectionstring= " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\cx\page.mdb "/> </connectionstrings></configuration > due to various database connection methods, the following is a variety of commonly used database connection methods, relative path connection string <add name= "mainDB2" connectionstring= "provider= microsoft.jet.oledb.4.0; Data source=db\page.mdb "/>sql Server connection <add name=" mainDB2 [connectionstring=] Provider =sqloledb;data source=192.168.1.5;initial catalog= jsjxy; Userid=sa; PASSWORD=JJSSJJ; " />sql Server local Windows way certified <add name= "mainDB2" connectionstring= "Provider=sqloledb;data source=;initial Catalog =pubs;integrated Security=sspi; " /> Alias mode <add name= "Connname" connectionstring= "maindbnew"/><add name= "maindbnew" connectionString= " provider=microsoft.jet.oledb.4.0; Data source=f:\cx\page.mdb "/>sqlite connection <add name=" mainDB2 "connectionstring=" DATASOURCE=C:\\JSJXY.DB; version=3; " /> Encryption Mode connection <add name= "mainDB2" connectionstring= "DATASOURCE=C:\\JSJXY.DB; Version=3; Password=epas1ygv1igaepas; " />

2. Creating an explicit connection

You can explicitly create a connection by using the Checkconn method. The primary function of the Checkconn method is to detect the presence of a global connection, or to return if it exists, or to create a connection based on the parameters passed-the arguments are the various forms of connection strings mentioned earlier. The Checkconn method is the primary method for establishing and maintaining connections, with the following features:

1) If the connection string is passed, the connection is created;

2) If you pass an empty string, check if the original link is valid, and if it fails, use the saved connection string to reestablish the connection;

3) automatically detects the Web. config file and creates a connection based on the specified connection string;

The implementation code for Checkconn is as follows:

public static bool Checkconn (string connectstring) {//Constructed object Createdbobj (connectstring); Return DBObject. IsOpen;}

Create a connection by calling the Createdbobj method. The Createdbobj method creates different IDB objects based on the feature information in the connection string and saves the connected configuration information so that the connection can be resumed at any time.

For ease of use, two auxiliary functions, getaccessconnectionstring and getsqlserverconnectionstring, construct a common connection string.

The connection is maintained in a passive manner. That is, instead of proactive periodic detection, it is detected every time a database function is invoked. If an exception is encountered, the connection is rebuilt and the operation is re-executed.

Back to top three main operations

1. Querying data sets

Call the GetData method in the IDB interface object DBObject to return the data.

The query method for the database is public static DataTable GetData (String sqlstr) {checkdbobj ();//Checks whether the object exists with return DBObject. GetData (SQLSTR);}

Where DBObject is the specific implementation class created from the connection.

2. Perform non-query operation

The execution of non-query operations, like queries, is done by invoking the appropriate method of the IDB object.

public static Dbresult Dononquery (String sqlstr) {checkdbobj ();//Check whether the object exists return dbobject. Dononquery (SQLSTR);}

3, by paging query

Paging is the most common feature in Web applications, but if you follow Visual Studio paging, you'll need to query out all of the data every time, and then pass it to the display control, which can be a huge waste when the volume of data is large. As a result, paging often needs to be defined by itself, retrieving only the page of data that is used for each query. The general approach is done through a stored procedure, but considering the compatibility of multiple databases, the paging functionality is integrated into the fast access framework.

The Accessdb GetPage also needs to invoke the IDB object's method:

public static DataTable GetPage (string strsql, int PageSize, int pageno, string keyfield, bool Isstringkey) {return dbobj Ect. GetPage (strSQL, PageSize, PageNo, Keyfield, Isstringkey);}

The method of pagination is nothing special, it is from the Internet, after testing, efficiency can accept it.

Back to top error output and debug

ACCESSDB debugging is simple, and all important operations track and output log information. The log system uses a log4net-based log system that is mentioned in another blog post.

When the ACCESSDB error occurs, you can see the detailed information from the error log. If you want to know the specifics of accessdb execution, you can control it through the IDB's two properties Outputselectlog, Outputnonquerylog. The Outputselectlog property, which determines whether to output every SELECT statement executed, and the Outputnonquerylog property, determines whether to output statements that execute non-query classes. By default, both are turned off.

Back to the top of the summary

With the ACCESSDB as the center, a set of fast database access mechanisms is provided, and the following steps are used in general:

1, quote Commoncode.dll,log4net.dll. Copy the Log4net.config to the application folder.

2. Initialize (Web. config or checkconn)

3, Getdata/getpage query, return DataTable

4, dononquery execution query

5. View the output under the log folder