Go to VB6.0 database access technology and routine analysis

 

 

Go to VB6.0 database access technology and routine analysis

 

This article briefly analyzes several techniques for accessing the database through VB6.0, and summarizes several practical database development experiences.

 

 

Introduction

As an excellent rad tool of Microsoft, VB has powerful capabilities in database application development. Microsoft has designed a variety of database access methods. The following is an in-depth analysis of various techniques for accessing the database using VB, and summarizes some experiences in actual development. I hope it will be helpful for beginners of VB. (Note that all the descriptions in this article are in Windows 2000 Professional and Visual Basic 6.0 environments)

I. Dao, rdo, ODBC, and ADO

In the development environment of VB, three database access methods can be used: Data Access Object (DAO), remote data object (rdo), and ADO object model.

Dao:

The data access object is used to expose the Microsoft Jet Database Engine (which was first used for Microsoft Access and now supports other databases) and allows developers to directly connect to other databases through ODBC, directly connect to the access table. Dao is most suitable for single-system applications or local distribution in a small range. Its internal access to the Jet Database has been accelerated and optimized, and it is also very convenient to use. Therefore, if the database is an Access database and is used locally, we recommend that you use this access method.

VB has encapsulated the DaO model into a data control, and set the corresponding databasename attribute and recordsource attribute to connect the data control with the record source in the database. You can use the data control to operate the database later.

Rdo

Rdo (Remote Data Objects) Remote Data Objects are an ODBC-oriented data access interface. They are combined with easy-to-use DAO styles and provide an interface, shows the underlying functions and flexibility of all ODBC databases. Although rdo is restricted in its access to jet or isam databases, it can only access relational databases through the existing ODBC driver. However, rdo has proved to be the best interface that many SQL Server, Oracle, and other large relational database developers often choose. Rdo provides more complex objects, attributes, and methods used to access stored procedures and complex result sets.

Like Dao, it is also encapsulated as rdo control in VB, and its usage is the same as that of Dao control.

ODBC

ODBC (Open Database Connectivity) is an integral part of the database in Microsoft's wosa (Windows Open Services Architecture). It establishes a set of specifications, it also provides a set of standard APIs for database access (application programming interfaces ). These APIs use SQL to complete most of their tasks. ODBC also provides support for the SQL language. You can directly send SQL statements to ODBC.

An ODBC-based application does not rely on any DBMS for database operations and does not directly deal with DBMS. All database operations are completed by the corresponding dbms odbc driver. That is to say, both FoxPro, access, and Oracle databases can be accessed using ODBC APIs. It can be seen that the biggest advantage of ODBC is that it can process all databases in a unified manner.

A complete ODBC consists of the following components:

 

1. Application)

ODBC Manager (administrator ). The program is located in 32-bit ODBC on Windows 95 control panel. Its main task is to manage the installed ODBC driver and manage the data source.

Driver manager ). The driver manager is included in odbc32.dll and transparent to users. Its task is to manage ODBC drivers and is the most important component in ODBC.

2. ODBC API

ODBC driver. Is a DLL that provides interfaces between ODBC and databases.

Data source. The data source contains information such as the database location and database type, which is actually an abstraction of data connections.

Currently, ODBC connections are only limited to relational databases. Other data sources such as Excel and text files cannot be accessed, and many DBMS (Database Management System) cannot fully support all their functions. In contrast, oledb can access any form of data, so its function is quite powerful, and it also guides the current development direction of technology.

ADO

ADO (ActiveX Data Object) is a successor of Dao/rdo. Ado 2.0 is more functionally similar to rdo, and generally there is a similar ing between the two models. Ado "extends" the object model used by Dao and rdo, which means it contains fewer objects, more attributes, methods (and parameters), and events.

As the latest database access mode, ADO is also easy to use, so Microsoft has clearly stated that it will focus on ADO in the future and will not upgrade Dao/rdo, therefore, ADO has become the mainstream of database development.

Ado involves three Data Storage Methods: DSN (data source name), ODBC (open data connection), and ole db. The following routine will explain in detail the specific access implementation of these three methods.

To use ADO, you must understand the object hierarchy of ADO, which is roughly divided into the following seven object layers:

1. Command object: contains information about a command, such as query strings and parameter definitions. The command object is similar to rdo's rdoquery object in function.

2. connection object: contains information about a data provider. The connection object is similar to the rdo rdoconnection object in function and contains information about the structure description. It also contains functions of some rdoenvironment objects, such as the transaction control.

3. Error object: contains the extended information when the data provider fails. The error object is similar to rdo's rdoerror object in function.

4. Field object: contains the information of a single column in the record set. The Field object function is similar to the rdocolumn object of rdo.

5. Parameter object: contains the information of a single parameter of the parameterized command object. This command object has a set of parameters containing all its parameter objects. The parameter object is similar to the rdoparameter object of rdo.

6. property object: contains the features defined by the provider of An ADO object. There is no rdo equivalent to this object, but Dao has a similar object.

7. recordset object: used to store the record set returned by data operations. This object and connection object are the two most important objects of all objects.

Of course, for novice users, we only need to master the connection object and recordset object to implement basic database operations. I will give a detailed introduction in the following experience.

 

 

Ii. Summary of development experience:

 

This section describes the Database Access Technologies on the VB Platform. These technologies are also the mainstream of the current database technologies. I will give some development experience and tips based on the development experience in recent years for your reference:

 

Experience 1: if the data volume is small and the development cycle is short, we recommend that you use DAO + ACCESS. Although Dao is not powerful, it accelerates and optimizes the jet engine, therefore, this combination should be a good choice.

 

Experience 2: rdo is rarely used at present. It is generally replaced by ADO.

 

Experience 3: (this experience is very important) when using ADO for development, there are three ways to connect to the database (as described above ), the DSN must first set the database to be manipulated as the database source and name it. You can configure it under control panel> Administrative Tools> data source (ODBC. For example, if the data source name is data, you can use the following lines of code to connect to the database:

 

Dim conn as new ADODB. Connection

Dim RS as new ADODB. recordset

'Connect to the database

Conn. Open "DSN = Data"

'Open the data source (that is, the Data Object of the selected Operation)

Rs. Open SQL statement, Conn

However, if you use ODBC and oledb for connection, you must know the long string of code on the connection string. Not to mention that for beginners, it is for experienced programmers, it is also difficult to remember this string of code. So how can we remember this string of code? In fact, the method is very simple. You only need to add an ADO data control, and then use the wizard in its attribute box to set it, then the Wizard will automatically generate a connectionstring, you only need to copy this line of code and place it in conn. the location of the connection string parameter after open is enough. This experience is intended for readers to remember that it is very practical in actual development.

 

Experience 4: As mentioned earlier, you can manually set the ODBC data source through a program. The first thing to note is that ODBC details are all stored in the following key values in the Registry: "HKEY_LOCAL_MACHINE/software/ODBC ", you can modify the corresponding key value by calling the Win32 function writeprofilestring.

 

Experience 5: the connection code and resource release code of the database are usually put into their specific functions, because the two functional modules need to be called frequently in larger programs, in this way, the efficiency can be improved. Of course, it is often necessary to write the form of a connection pool.

 

Experience 6: when using VB to process strings with single quotes in SQL, single quotes are used for string data, for example:

 

Select * From mytable where id = 'firstid'

If the firstid is the first 'Id, that is, there is a single quotation mark in the middle, the above writing will cause an error. The solution is to replace each single quotation mark in the string with two double quotation marks, the following function strtosql completes this function and causes the processed string with single quotes:

 

Private function strtosql (byval strvalue as string) as string

Strtosql = "'" + Replace (strvalue, "'", "'' ") + "'"

End Function

When writing SQL statements, you can use the following string data, no matter whether it contains single quotes:

 

Strvalue = "first 'id"

Strsql = "select * From mytable where id =" + strtosql (strvalue)

 

 

Iii. instance resolution

 

Next we will introduce an example to digest the above knowledge. Here, I implemented Dao, ADO, ODBC, and oledb database access methods in a program interface.

 

 

Program code analysis:

 

 

'The entire program function is to select different connection modes for display. The display effect is identical in the three modes.

'Below is the main program process

Private sub commandementclick ()

Dim selitem as integer

'Determine how to connect to the database

If option1.value = true then

Selitem = 1

Else

If option2.value = true then

Selitem = 2

Else

Selitem = 3

End if

End if

 

'Select different database connection methods

Select case selitem

Case 1:

'Database connection method using Dao

Call showbydao

Case 2:

'Database connection using ADO

Call showbyado

Case 3:

'Odbc database connection method

Call showbyodbc

Case 4:

'Use oledb Database Connection Methods

Call showbyoledb

End select

End sub

 

Private sub showbydao ()

'Database connection method using Dao

Dim dB as database

Dim RS as recordset

Dim sqlstr $ 'stores query statements

Set DB = opendatabase (App. Path & "/db1.mdb ")

Sqlstr = "select * from orders table"

Set rs = dB. openrecordset (sqlstr)

'Display result

Call gridshow (RS)

End sub

 

Sub showbyado ()

Dim conn as new ADODB. Connection

Dim RS as new ADODB. recordset

'Use the data source to connect to the database

Conn. Open "DSN = Data"

Rs. cursortype = adopenkeyset

Rs. locktype = adlockoptimistic

Rs. Open "select * from orders table", Conn

Call gridshowofado (RS)

End sub

 

Sub showbyodbc ()

Dim conn as new ADODB. Connection

Dim RS as new ADODB. recordset

'Use the data source to connect to the database

Conn. Open "provider = msdasql.1; persist Security info = false; Data Source = Data"

Rs. Open "select * from orders table", Conn

'Display result

Call gridshowofado (RS)

End sub

 

Sub showbyoledb ()

Dim conn as new ADODB. Connection

Dim RS as new ADODB. recordset

'Use the data source to connect to the database

Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" + app. Path & "/db1.mdb" + "; persist Security info = false"

Rs. Open "select * from orders table", Conn

'Display result

Call gridshowofado (RS)

End sub

 

Sub gridshow (RS as recordset)

'Display the DAO method.

Msflexgrid1.textmatrix (0, 0) = "name"

Msflexgrid1.textmatrix (0, 1) = "gender"

Msflexgrid1.textmatrix (0, 2) = ""

Msflexgrid1.textmatrix (0, 3) = "Mathematics"

Msflexgrid1.textmatrix (0, 4) = "English"

Rs. movelast

Msflexgrid1.rows = Rs. recordcount + 1

Msflexgrid1.cols = Rs. Fields. Count

Dim I %

I = 1

Rs. movefirst

While (not Rs. EOF)

Msflexgrid1.textmatrix (I, 0) = Rs. Fields (0)

Msflexgrid1.textmatrix (I, 1) = Rs. Fields (1)

Msflexgrid1.textmatrix (I, 2) = Rs. Fields (2)

Msflexgrid1.textmatrix (I, 3) = Rs. Fields (3)

Msflexgrid1.textmatrix (I, 4) = Rs. Fields (4)

Rs. movenext

I = I + 1

'If (Rs. EOF = true) then

'Exit

Wend

End sub

 

Sub gridshowofado (RS as ADODB. recordset)

'Display the ADO method.

Msflexgrid1.textmatrix (0, 0) = "name"

Msflexgrid1.textmatrix (0, 1) = "gender"

Msflexgrid1.textmatrix (0, 2) = ""

Msflexgrid1.textmatrix (0, 3) = "Mathematics"

Msflexgrid1.textmatrix (0, 4) = "English"

'Note that the recordcount attribute must return the correct value only when the current record pointer is in the last record.

Rs. movelast

Msflexgrid1.rows = Rs. recordcount + 1

Msflexgrid1.cols = Rs. Fields. Count

Dim I %

I = 1

Rs. movefirst

While (not Rs. EOF)

Msflexgrid1.textmatrix (I, 0) = Rs. Fields (0)

Msflexgrid1.textmatrix (I, 1) = Rs. Fields (1)

Msflexgrid1.textmatrix (I, 2) = Rs. Fields (2)

Msflexgrid1.textmatrix (I, 3) = Rs. Fields (3)

Msflexgrid1.textmatrix (I, 4) = Rs. Fields (4)

Rs. movenext

I = I + 1

Wend

End sub

 

Private sub command2_click ()

End

End sub

The above code has been successfully debugged in Windows 2000 Professional and Visual Basic 6.0 environments.

 

Iv. Summary

 

This article analyzes and summarizes Database Access Technologies and puts forward some valuable experiences. Learning from these experiences will bring convenience to development.