Managing SQL Server databases with ADO

ado|server|sql| Data | Database Microsoft SQL Server is one of the most commonly used database management systems in small networks today. The application of this kind of network database is also increasing, the normal operation of this kind of network database application system, usually depends on the existing user database. The work of creating a database can certainly be done with SQL Server-provided Enterprisemanager tools, but it is no doubt more desirable for users to provide a custom database management tool that specializes in managing the databases and their devices needed by the application system.

Existing problems

We know that before you create a database using the CreateDatabase statement, you must have a database device with remaining space, or you can use the Diskinit statement to create a new device beforehand. However, these statements contain a number of required parameters, and many parameter values are often difficult to determine without using SQL Server's Administrative tools.

Take the Diskinit statement that creates the database device as an example, the complete syntax for this statement is as follows:

Diskinitname= ' Logical_name ', physname= ' physical_name ', vdevno=virtual_device_number,size=number_of_2k_blocks[, Vstart=virtual_address]

The two parameters, name and size, are easy to get, and the problem is the physical name Physname and the virtual device number VDEVNO. The former requires a full pathname of the physical file on a server, and the latter requires a number that is not occupied by another device between the 1~255. When you write a database management program, the device numbers on the user's server are already occupied, and SQL Server is mounted on which drive is unpredictable.

Although using SQL Server's Administrative Tools Sqlenterprisemanager, you can easily create, delete, or expand a database that already exists, or you can easily create, delete, or modify a database, but This tool still requires us to enter a number of less commonly used parameters, the interface slightly more complex.

So the ideal scenario is that the user simply presses a command button, and the database and its devices that the application needs are automatically created immediately.

Solution

In order to achieve this goal, we must find a way to solve the problem of parameter setting in SQL statements.

1. To create a statement parameter for a device

The statement that creates the device is the Diskinit statement mentioned earlier.

To simplify the problem, we can specify the device file name that is the same as the database name and save the device file in the subdirectory where the master device resides. The database name is determined while the application is being designed, and the subdirectory where the master device resides can be queried from the system table sysdevices. In this way, the physical name parameter of the device file is determined.

The problem with virtual device numbers is more complicated because there is no "virtual device number" in the sysdevices system table, so you have to find another way.

After analyzing the SQL Server system stored procedure sp_helpdevice, we found that the virtual device number is "hidden" in the low field of the sysdevices system table, and the virtual device number of each device can be found by using another system table spt_values. In this way, we only need to find out in a loop whether a device number exists in the sysdevices, you can
Determine which virtual device number we are now available.

As for the size of the database equipment, we might as well set the larger, or let the user specify that it can.

2. To create a statement parameter for a database

The statement to create the database is as follows:

Createdatabasedatabase_name[on{default|database_device}[=size][,database_device[=size]]
[Logondatabase_device[=size][,database_device[=size]] [Forload]

Where most of the parameters are optional, we only need to specify a device name and the size of the database, and the database name, device name, size in the creation of the device has been determined, so the parameters of this statement is not a problem.

Concrete implementation

Using the Common Application development tool VisualBasic, we can implement a custom database management program.

In order to connect to the database server, we must select a database access interface. Although accessing SQL Server from VB
There are many interfaces to choose from, but Microsoft's latest database access interface, ADO (activedataobjects), is undoubtedly the most promising because it provides the possibility for the implementation of browser-based database application systems.

Here are some common functions used for database and its device management.

1. Take the current working database----because administrative tasks generally have to be done in the master library, it is a good idea to save the current library before you perform the task before you switch back.

Publicfunctionsqlgetcurrentdatabasename (cnasadodb.connection) asstringdimssqlasstringdimrsasnewadodb.recordset
Onerrorgotoerrsqlgetcurrentdatabasenamessql= "Selectcurrentdb=db_name ()" RS. Openssql,
cnsqlgetcurrentdatabasename=trim$ (rs! CurrentDb) RS. Closeexitfunctionerrsqlgetcurrentdatabasename:sqlgetcurrentdatabasename
= "" Endfunction

2. Determine if a database device exists

Publicfunctionsqlexistdevicename (cnasadodb.connection,sdevnameasstring) Asboolean

'--judge whether a device exists by name, or if it exists, return 1, otherwise return 0
Dimssqlasstringdimrsasnewadodb.recordsetdimbtmpasbooleanonerror
Gotoerrsqlexistdevicenamessql= "
Selectcntdev=count (*) frommaster.dbo.sysdeviceswherename= ' "&sdevname&" "RS. openssql,cnifrs! cntdev=0thenbtmp=falseelsebtmp=
Truers.closesqlexistdevicename=btmpexitfunctionerrsqlexistdevicename:

Sqlexistdevicename=falseendfunction

3. Determine if a virtual device number is occupied: Sqlexistdevicenumber.

4. Find one of the smallest virtual device numbers that have not yet been occupied: Sqlgetunuseddevicenumber.

5. Get the Data subdirectory path under the SQL Server installation directory: Sqlgetdatapath.

6. Create a new device: Sqlcreatedevice.

7. Create a new database: SQLCreateDatabase65.

8. Take the database device details: Sqlgetdeviceinfo.

9. Enlarge the size of the database device: Sqlexpanddevice.

After a period of operation of database application system, the increase of data volume often requires the database to enlarge, and then the equipment size should be enlarged. Unfortunately, the Diskresize statement requires the size parameter to be the enlarged new dimension, not the size that needs to be increased. Therefore, you must check the original size of the device before you can use the Diskresize statement.

10. Determine if a database exists: Sqlexistdatabase.

11. Delete a database: Sqldropdatabase.

12. Delete a database device: Sqldropdevice.

13. Take version information for SQL Server: Sqlgetversionstring.

In the upcoming SQLServer7.0, the concept of a database device is no longer available, and creating a database becomes simpler. When creating a specific user database, it is important to get a version of SQL Server to differentiate between different versions of the operation.