Use ADO to manage SQL Server databases and their devices

Microsoft's SQLServer is one of the commonly used database management systems in small networks. Network database applications are also increasing. The normal operation of such network database application systems is generally dependent on existing user databases. You can use the SQL EnterpriseManager tool provided by SQL Server to create a database. However, if you can provide a custom database management tool to manage the databases and devices required by the application system, it is undoubtedly more ideal for users.

　　Existing Problems

We know that before using the CREATEDATABASE statement to create a database, you must have a database device with sufficient space or use the DISKINIT statement to create a new device. However, these statements contain many required parameters, and if you do not use SQLServer's management tool, many parameter values are often difficult to determine.

Take the DISKINIT statement for creating a database device as an example. The complete Syntax of 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. The trouble is the physical name physname and virtual device number VDEVNO. The former must be the full path name of physical files on a server; the latter must be 1 ~ Between 255, find a number that is not occupied by another device. However, when writing database management programs, it is unpredictable whether the device numbers on the user's server are occupied or on which drive SQL Server is mounted.

Although SQL Server's management tool SQLEnterpriseManager can easily create or delete database devices, expand an existing database, and easily create, delete, or modify a database, however, this tool still requires us to enter a lot of parameters that are not commonly used, and the interface is a little complicated.

Therefore, the ideal situation is that you only need to press the next command button, and the databases and devices required by the application can be automatically created immediately.

　　Solution

To achieve this goal, we must find a solution to the parameter setting problem in SQL statements.

1. Create device statement Parameters

The statement used to create a device is the aforementioned DISKINIT statement.

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 is located. The database name is determined when the application is designed. The subdirectory where the master device is located can be queried from the system table sysdevices. In this way, the physical name parameter of the device file is determined.

The problem of Virtual Device numbers is complicated because the sysdevices system table does not have a field such as "Virtual Device Number". Therefore, you must try another method.

After analyzing the system storage process sp_helpdevice of sqlserver, we found that the virtual device number is "hidden" in the low field of the sysdevices system table, with the help of another system table spt_values, you can find the virtual device number of each device. In this way, you only need to find out whether a device number exists in sysdevices in a cycle to determine the number of available virtual devices.

As for the size of the database device, we may wish to set it bigger or let the user specify it.

2. Create Database statement Parameters

The statement for creating a database is as follows:

Createdatabasedatabase_name [ON {default | database_device} [= size] [, database_device [= size]...] [Logondatabase_device [= size] [, database_device [= size]...] [forload]

Most of the parameters are optional. You only need to specify a device name and the size of the database. The database name, device name, and size are determined when the device is created, there is no problem with the parameters of this statement.

　　Implementation

With Visual Basic, a common application development tool, we can implement a customized database management program.

To connect to the database server, we must select a database access interface. Although there are many interfaces available to access sqlserver from VB, 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.

The following are some common functions for database and device management.

1. take the current work database-because the management tasks must generally be completed in the master database, it is best to save the current work database before executing the management task so that the task can be switched back after completion.

Publicfunctionsqlgetcurrentdatabasename (cnasadodb. Connection) asstringdimssqlasstringdimrsasnewadodb. recordset Onerrorgotoerrsqlgetcurrentdatabasenamessql = "selectcurrentdb = db_name ()" Rs. openssql, Cnsqlgetcurrentdatabasename = trim $ (RS! Currentdb) Rs. closeexitfunctionerrsqlgetcurrentdatabasename: sqlgetcurrentdatabasename = "" endfunction

2. Determine whether a database device exists

Publicfunctionsqlexistdevicename (cnasadodb. Connection, sdevnameasstring) asboolean '-- Determines whether a device exists by name. If yes, 1 is returned; otherwise, 0 is returned. Dimssqlasstringdimrsasnewadodb. recordsetdimbtmpasbooleanonerrorgotoerrsqlexistdevicenamessql =" SelectCntDev = count (*) frommaster. dbo. sysdeviceswherename = '"& sDevName &"' "RS. OpensSQL, CnIfRS! CntDev = 0 ThenbTmp = FalseElsebTmp = TrueRS. CloseSQLExistDeviceName = bTmpExitFunctionerrSQLExistDeviceName: SQLExistDeviceName = FalseEndFunction

3. Determine whether a virtual device number is occupied: sqlexistdevicenumber.

Editor's note: the source code of the function is published on our WWW site at http://www.computerworld.com.cn/98/skill/default.htm. The same below. Welcome!

4. Find the smallest unused Virtual Device number: sqlgetunuseddevicenumber.

5. Obtain the path of the Data subdirectory under the sqlserver installation directory: sqlgetdatapath.

6. Create a new device: sqlcreatedevice.

7. Create a new database: sqlcreatedatabase65.

8. Obtain the detailed information of the database device: sqlgetdeviceinfo.

9. Expand the size of the database device: sqlexpanddevice.

After a database application system is running for a period of time, increasing the data volume usually requires increasing the database size, which in turn requires increasing the device size. Unfortunately, the size parameter required by the diskresize statement is the new size after expansion, rather than the size to be increased. Therefore, you must check the original size of the device before using the diskresize statement.

10. Determine whether a database exists: sqlexistdatabase.

11. delete a database: sqldropdatabase.

12. delete a database device: sqldropdevice.

13. Obtain the sqlserver version: sqlgetversionstring.

In the forthcoming sqlserver7.0 release, the concept of database devices is no longer available, making it easier to create databases. When creating a specific user database, it is very important to obtain the sqlserver version to differentiate different versions for different operations.