Manage SQL Server Database and Equipment with ADO
From Bean Technology Network
Microsoft's SQLServer is one of the current database management systems commonly used in small networks. Applications for this network database are also increasing, this network database application system is running, generally depends on the existing user database. Of course, you can use the SQL EnterpriseManager tools provided by SQL Server to complete, however, if you can provide a custom database management tool, dedicated to the database and equipment required by the application system, which is undoubtedly more ideal for users.
Existing Problems
We know that there is a database device with the remaining space before using the CreateDatabase statement, or use the Diskinit statement in advance to create a new device in advance. But these statements contain a lot of necessary parameters, and many parameter values are often difficult to determine if you do not use SQL Server management tools.
Take the Diskinit statement of the database device as an example, the full 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 of Name and Size are easy to get, and trouble is the two parameters of physical name PhysName and virtual device number VDEVNO. The former requires a full path name on a server; the latter requires to find a number that is not occupied by other devices from 1 to 255. When writing a database management program, which device numbers on the user have been occupied. Which drive of SQLServer is installed, it is unpredictable.
Although, using SQLServer management tool SQLEnterpriseManager, you can easily create, delete database devices, or expand a existing database, you can also create, delete or modify a database, but this tool still requires us to enter a lot Too often used parameters, the interface is slightly complicated.
Therefore, the ideal case is that the user only needs to press the next command button, the application needs of the application and its devices can be automatically created automatically.
solution
In order to achieve such a goal, we must find a way to solve parameter settings in the SQL statement.
1. Create a statement parameter
Create a device's statement that is the diskinit statement mentioned earlier.
To simplify the problem, we can specify the same device file name as the database name and save the device file in the subdirectory where the Master device is located. The database name is determined when designing an application; and the subdirector of the master device can be queried from the system table sysdevices. In this way, the physical name parameters of the device file are determined.
The problem with the virtual device number is more complicated because there is no "virtual device number" in the Sysdevices system table, so you must find another way.
After analyzing 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, with another system table SPT_VALUES, you can find the virtual device number of each device. In this way, we only need to find some device number in a loop to exist in sysdevices, you can determine that the virtual device number we can use now.
As for the size of the database device, we may wish to set it, or let the user specify it.
2. Create a statement parameter
The statement to create a database is as follows:
CreateDatabaseDatabase_name [on {default | Database_Device} [= size] [, Database_Device [= size]] ...] [logondatabase_device [= size] [, Database_Device [= size]]]] ...] [forload], most parameters They are all optional, we only need to specify a device name and database size, and the database name, device name, size is determined when creating the device, so the parameters of this statement do not have problems.
Implementation
Using a normal application development tool VisualBasic, we can implement a custom database management program.
In order to achieve the connection with the database server, we must choose a database access interface. Although there are many interfaces available from VB to SQLServer, 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 for databases and their device management.
1. Take the current working database ---- Because management tasks must generally be completed in the Master library, it is best to save the current work library before performing the management task to switch back after completing the task.
PublicFunctionSQLGetCurrentDatabaseName (CnAsADODB.Connection) AsStringDimsSQLAsStringDimRSAsNewADODB.RecordsetOnErrorGoToerrSQLGetCurrentDatabaseNamesSQL = RS.OpensSQL, CnSQLGetCurrentDatabaseName = Trim "selectCurrentDB = () DB_NAME" $ (RS CurrentDB!) RS.CloseExitFunctionerrSQLGetCurrentDatabaseName: SQLGetCurrentDatabaseName = "" EndFunction
2. Determine if a database device exists
PublicfunctionsqlexistDevicename (cnasadodb.connection, sdevnamesstring) Asboolean
'- determine if a device exists, if there is, return 1, otherwise returns 0
DimsSQLAsStringDimRSAsNewADODB.RecordsetDimbTmpAsBooleanOnErrorGoToerrSQLExistDeviceNamesSQL = "selectCntDev = count (*) frommaster.dbo.sysdeviceswherename = '" & sDevName & "'" RS.OpensSQL, CnIfRS CntDev = 0ThenbTmp = FalseElsebTmp = TrueRS.CloseSQLExistDeviceName = bTmpExitFunctionerrSQLExistDeviceName:! SQLExistDeviceName = FalseEndFunction
3. It is judged whether a virtual device number is occupied: SqlexistDeviceNumber.
Editor Note: The function source code is published on the WWW site of the newspaper, the address is: http://www.computerworld.com.cn/98/skill/default.htm. Same. Welcome to visit!
4. Find a minimum virtual device number that has not been occupied: SqlgetunusedDevicenumber. 5. Get the DATA subdirectory path in the SQL Server installation directory: SqlgetDataPath.
6. Create a new device: SqlcreateDevice.
7. Create a new database: SqlcreatedTabase65.
8. Details of the database device: SqlgetDeviceInfo.
9. Expand the size of the database device: SQLEXPANDDEVICE.
After running a database, the increase in data is often required to increase the database, which in turn requires expansion of the device size. Unfortunately, the DISKRESize statement requires the size parameters expanded to the new size, not the need to increase the size. Therefore, you must check the original size of the device in advance to use the DiskResize statement.
10. Determine if a database exists: SQLEXISTDATABASE.
11. Delete a database: SQLDROPDATABASE.
12. Delete a database device: SQLDropDevice.
13. Take the version of SQLServer: SqlgetversionsString.
In the upcoming SQLServer7.0, no longer have the concept of a database device, and create a database will become simpler. When creating a specific user database, the version of the SQL Server is very important in order to distinguish between different versions.
