How to improve the compatibility of MSSQL and access

As you all know, the most common use of ASP is access and MSSQL two databases. Many programs want to be developed into two databases can be used by the model, but due to the two database some SQL statements inconsistent, so many programs are divided into two unnecessary versions, Access and MSSQL version! In fact, we can control the compatibility of the whole program by judging and recognizing the code.

In other words, two versions are compatible with one system. For example, my ASP Feiyun novel system, is the two kinds of database can operate the program.

So, how do you make your program database compatible and efficiently run it? Below I will take my Feiyun novel system to do the example, said some of my control method and the code.

First, we have to control a parameter to let the program know what database we're working on.

such as: config.asp file parameters in the program of Feiyun novel

The following are the referenced contents: Const Systemdatabasetype = "Access" System database type, "SQL" is a MSSQL 2000/2005 database, "Access" is a MS Access 2000 database

This is the basis of the beginning of the compatibility, is the whole program database discrimination. So it is very necessary to set such a parameter.

Second, the distinction between database connection mode and common function

In my program, there is a section of code conn.asp inside:

Code: If Systemdatabasetype = "SQL" Then ConnStr = "Provider = SQLOLEDB; User ID = "& Sqlusername &"; Password = "& SQLPassword &"; Initial Catalog = "& Sqldatabasename &"; Data Source = "& Sqlhostip &"; Fy_true = "1" Fy_false = "0" Fy_now = "GetDate ()" Fy_ordertype = "desc" Fy_datepart_d = "D" fy_datepart_y = "yyyy" Fy_datepart_m = "M" Fy_datepart_w = "WW" Fy_datepart_h = "HH" Else connstr= "Provider = Microsoft.jet.oledb.4.0;data Source =" & Server.MapPath (MDB) Fy_true = "True" Fy_false = "False" Fy_now = "Now ()" Fy_ordertype = "ASC" Fy_datepart_d = "' D '" fy_datepart_y = "' yyyy '" Fy_datepart_m = "' M '" Fy_datepart_w = "' ww '" Fy_datepart_h = "' H '" End If

This code, in addition to distinguishing the way the database is connected, also assigns the variables commonly used by MSSQL and access. The advantage of doing this is that you can use the method of calling the variable directly when you use the relevant content, instead of having to judge the database type.

For example, on the DateDiff function of the contrast time:

Code: "SELECT * from [table] where DateDiff ('&fy_datepart_d&", database time, ' variable name ') =0 ORDER by ID Desc "

This eliminates the database judgment, reduces the code quantity and the practicality.

Third, the correct use of compatibility code, rather than the use of special code.

Many people write the MSSQL database are very, very like to use the Conn.execute statement, rather than using the open method. Although in speed, a lot of time Conn.execute will be higher than the open way to a little bit faster. But this is not a generalized.

For example, in a process algorithm that determines if it already exists. If you have used the open for BOF or EOF judgment, then you have to use Conn.execute to judge, that speed, certainly is not as good as in the open directly modified.

Also, the compatibility of open mode in code is far higher than conn.execute. such as the use of the time function now (). (This function in access and the difference between MSSQL, everyone to go to Baidu)

Four, in the pursuit of compatibility, sacrificing a little efficiency is also necessary.

In the 3rd, if your process does not need to determine whether it exists, it is without opening the open. You can use Conn.execute. But mine is not a generalized. For example, if it is a less used, not a frequent read process. I will choose to use the open method instead of using the Conn.execute method.

Although the speed of this process will be reduced. But this does not affect my use, so I choose the Open method. The reason is very simple, is the choice of compatibility.

Finally, when necessary to abandon the pursuit of compatibility, the use of different database code.

Before 1th, we've made a set of code that is the database type setting. So when we encounter something that can't be solved through compatibility, or a pursuit like Conn.execute and open way. We can use the parameter settings of the previous Systemdatabasetype to judge, and then use different code accordingly.

Such as: If systemdatabasetype = "SQL" Then ' The code used when using the MSSQL database Else ' Code used by the Access database End If

This ensures the efficiency and stability of the program. This is one of the compatibility of the entire program.

After the series:

Or the last tutorial "How to buy your own business program version" on the PS class time to write a tutorial, quack! Because the time is relatively short, there may be some problems are not involved, or the process of what the wrong place, I hope everyone comments correct.