The SQL Server of Micro-Soft Division is one of the commonly used data library management systems in the current small network. The procedure of the application of this network database is also increasing, and this network data library should be used in the normal operation of the system, which relies on the employed user database. Creating a data library for operations to be completed with SQL Enterprise Manager tools for SQL Server, but if you can provide a custom data library management tool, specializen management should be used in the data library and its equipment to be used, and the user is not doubtful.
Existing Problems
---- We know that before making a Create Database statement creates a data library, you must save the data library equipment with the remaining spaces, or first use the Disk init statement to create a new device. But this statement contains a lot of necessary parameters, and if it does not use SQL Server's management tool, many parameter values are difficult to determine.
---- DISK INIT statement with the creation of the data library is as an example, the complete language of this statement is as follows:
Disk init
Name = '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, and the numbness is the two parameters of the physical name PhysName and the virtual equipment. The former needs to be a full-time path name on a server; the latter requires to find a number that is not occupied by other equipment between 1 to 255. When writing the data library management program, there are any setup numbers that have been occupied. Which driven is installed in SQL Server, which is not expected.
---- Although, using SQL Server management tool SQL Enterprise Manager, you can easily create, delete database devices, or expand an existing database, you can also create, delete or modify a database, but this The tool still requires us to enter a lot of less common parameters, and the interface is slightly complicated.
---- What if you think is: Users only need to press the next command button, the data library and its equipment that should be used will be created automatically.
solution
---- For the real-world goals, we must try to solve the parameter setting questions in the SQL statement.
---- 1 . Create a statement of the clause of the device ---- Create a statement of the statement, the DISK INIT statement mentioned in front.
---- For the simplified questions, we can specify the name document names with the data library name, and save the device file in the subdirects of the master device. The database name is determined when the designer should be determined; and the subdirects of the Master device can be queried from the system table sysdevices. This, the material name parameter of the device file is determined.
---- Question with the virtual equipment is more complex, because there is no "virtual setup number" in the SYSDEVICES system, this must do something to do.
---- 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 virtual Device No. In this way, we only need to find a set number in a loop is not existing in sysdevices, you can determine the virtual equipment numbers available for us.
---- As for the size of the data library, we don't want to set it a bigger, or let the user can specify it.
---- 2 . Create a statement of the database
---- Creating the statement of the database is as follows:
Create Database data_name
[ON {Default | Database_Device} [= size]
[, Database_Device [= size]] ...]
[Log on database_device [= size]
[, Database_Device [= size]] ...]
[For loading]
---- Among them, 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, and size have been determined when the device is created, so this There is no problem with the parameters of the statement.
Implementation
---- Using Pu Tong's application-to-tool Visual Basic, we can realize a customized data library management process.
---- For the connection between real and data library server, we must choose a data library access interface. Although there are many interfaces available from VB to SQL Server, it is available for choice, but micro-soft latest data library access interface ADO (Active Data Objects) is the most prestably, because it is the implementation of a browser-based database application system to provide accessibility.
---- The following is a common function for the data library and its equipment.
---- 1 . Before taking the workforce data library --- Because of the management task must be completed in the Master library, this, before the implementation of the management task, it is best to keep the front work in the preparation, so that you will be completed after the task is completed.
Public Function SqlgetCurrentDatabaseName (CN As Adodb.connection) AS String
DIM SSQL AS String
DIM RS As New Adodb.Recordset
ON Error Goto ErrsqlgetCurrentDatabaseNamename
SSQL = "SELECT CURRENTDB = DB_NAME ()"
RS.Open SSQL, CN
SqlgetCurrentDatabaseName = TRIM $ (RS! Currentdb)
Rs.close
EXIT FUNCTION
ErrsqlgetCurrentDatabaseNamename:
SqlgetcurrentDatabaseName = ""
END FUNCTION
---- 2 . Judging a data library device is not
---- Public Function SQLEXISTDEVICENAME (CN As Adodb.connection, SDEVNAME AS STRING) AS Boolean
---- '- According to the name, it is determined that a device is not stored. If the fruit is stored, return to 1, and if you return 0
DIM SSQL AS String
DIM RS As New Adodb.Recordset
DIM BTMP As Boolean
ON Error Goto ErrsqlexiStdevicename
SSQL = "SELECT CNTDEV = Count (*) from master.dbo.sysdevices where name = '" & sdevname & "'"
RS.Open SSQL, CN
IF RS! CNTDEV = 0 THEN BTMP = false else btmp = TRUE
Rs.close
SQLEXISTDEVICENAME = BTMP
EXIT FUNCTION
ErrsqlexistDeviceName:
SQLEXISTDEVICENAME = FALSE
END FUNCTION
---- 3. Judging a virtual setup number is not occupied: SqleXistDeviceNumber.
---- Editor Note: Function Source Code The Add Table is on the WWW site of this newspaper, the address is: http://www.computerworld.com.cn/98/skill/default.htm. Same. Welcome to visit!
---- 4. Find a minimum unusually used virtual device: SqlgetunusedDevicenumber.
---- 5. Take the Data sub-directory path under the SQL Server installation: SqlgetDataPath.
---- 6. Create a new device: SqlcreateDevice.
---- 7. Create a new data library: SQLCReatedTabase65. ---- 8 . Take a detailed information of the data library equipment: SqlgetDeviceInfo.
---- 9 . Expand the size of the data library equipment: SQLEXPANDDEVICE.
---- Database application system After a period of operation, the increase in data volume is often increased to increase the data library, and to expand the preparation size. The size parameter that the Disk Resize must be obtained is a new size that is expanded, not the size of the need to add. At that, you must first check the original size of the device before you can use the Disk Resize statement.
---- 10. Judging a database is not stored in: SQLEXISTDATABASE.
---- 11. Delete a database: SqldropDatabase.
---- 12. Delete a database device: SqldropDevice.
---- 13. Take the version of SQL Server: Sqlgetversionstring.
---- In SQL Server 7.0, the SQL Server 7.0 that will be sent, no longer have the concept of data library equipment, and create a data library will become more simple. When creating a specific user data library, the difference between the different version is different, and the version of SQL Server is not important.
---- "Under the Visual Studio opens a web application" series (9)
