Microsoft SQL Server, ORACAL, and Sybase are currently popular database management systems (RDMS). With the continuous improvement and development of SQL Server, and the perfect combination with the network operating system Windows NT, it is designed for distributed client-server structure programming. It provides a good development environment and therefore attracts more and more attention.
Microsoft SQL Server is a scalable, high-performance database management system that provides integration with Windows NT Threading and Scheduling Services, Performance Monitor and Event Browser, is based on the Windows management interface and provides access to remote data on multiple servers Visual drag-and-drop control for replication management, server management, diagnostics and tuning.
Distributed Management Objects (DMO) provide an object interface to the SQL Server engine and services. SQL-DMO is a 32-bit OLE automation object provided for Windows 95 and Windows NT operating systems. OLE is a standard and method for maintaining application shared objects. It is a programming method that allows applications to manage or access shared objects for data. DMO was developed using the OLE structure. It provides OLE-compatible applications with an interface to all SQL Server management functions. DMO makes it possible for software developers to directly access SQL Server data objects. We can use distributed management objects to achieve direct access to SQL Server using programming tools such as Visual Basic and Visual FoxPro.
The tools provided by Microsoft SQL Server enable clients to access data on the server through a variety of methods. The core part of these tools is Transact-SQL (transaction SQL) code. Transact-SQL is an enhanced version of Structured Query Language (SQL) that provides many additional features and functions. Using Transact-SQL, you can create database devices, databases, and other data objects, extract data from databases, modify data, and dynamically change settings in SQL Server. Therefore, using Transact-SQL greatly improves the practicality of the application.
When we develop a Client/Server structure project, we need to establish the structure of several data objects. If the project being developed is for a specific customer, it is feasible to manually establish database equipment, databases and tables on the Server, but if When the project being developed is released as software rather than for a specific customer, it is undoubtedly unrealistic to manually establish the structure of the data object. We need to solidify the process of creating databases and tables in the program, and dynamically when the program is running. Build databases and tables on the Server. Therefore, using distributed management objects and Transact-SQL is an important means for programming Client/Server structure.
The following will discuss how to use DMO and Transact-SQL in Visual Basic 5.0 to create database devices, databases and other data objects on SQL Server. Let's start with the specific problem: Create 200M and 400M database devices Device_1 and Device_2 on SQL Server. Create two databases Db1 and DB2 with a capacity of 100M on Device_1 and Device_2 respectively. Create table T_1 in the Db1 database. There are four Domains: Name, Age, Sex, ID_Code, with ID_Code as the key and Name as the index. Create table T_2 in the Db2 database. There are five fields: Name, Age, Sex, Department, No, with Department and No as the keys. Character.
To access SQL Server, you must first open SQL Service Manager. SQL Service Manager is used to start, pause, continue, and stop SQL Server services, including MSSQLServer and SQLExecutive. You can open these two services manually, or you can open them through the command line in the program.
If you use the manual method, open the SQL Service Manager, and directly activate the Start/Continue green light of the services MSSQLServer and SQLExecutive.
Using the line command startup method, you can use the VB Shell command:
X=shell("Sqlmgr.exe",1) to display the SQL Service Manager window;
X=shell("net start mssqlserver") starts the MSSQLServer service
X=shell("net start sqlexecutive") starts the SQLExecutive service.
Because here we hope to dynamically establish the data object structure on SQL Server through programming, it is recommended to use the line command method.
After starting SQL Server Manager, we can use DMO and Transact-SQL to create data objects according to the following steps.
1. In order to use DMO, you first need to open Reference in the VB toolbar and select Microsoft Windows Common Control 5.0. If this item is not found in the reference bar, select Browse and change the directory under WINNTSystem32 Comctl32.oca is added to Reference.
2. Create a SQLOLE object in the module file (.BAS):
Global OServer as New SQLOLE.SQLServer
3. Establish a connection with SQL Server:
OServer.Connect ServerName:=registered SQL Server name, Login:=login name (usually sa), Password:=password
4. Create database devices Device_1 and Device_2:
dim transql as string
transql="use master" & _
"DISK INIT" & _
"Name='Device_1'," & _
"Physname='D:SQLdatadevice1.dat'," & _
"vdevno=10," & _
"size=102400" & _
"DISK INIT" & _
"Name='Device2'," & _
"Physname='D:SQLdatadevice2.dat'," & _
"vdevno=11," & _
"size=204800"
OServer.ExecuteImmediate Command:=transql,exectype:=SQLOLEExec_Default
5. Create databases Db_1 and Db_2:
transql = "CREATE DATABASE Db_1 ON Device_1=100 "
OServer.ExecuteImmediate Command:=transql, exectype:=SQLOLEExec_Default
transql = "CREATE DATABASE Db_2 ON Device_2=100 "
OServer.ExecuteImmediate Command:=transql, exectype:=SQLOLEExec_Default
6. Create a table in the database:
Create table T_1 on database Db_1:
transql = "use Db_1 " & _
"create table T_1 " & _
"(Name char(8) null, " & _
" Age numeric(2) null, " & _
" Sex smallint null, " & _
" ID_Code char(16) not null, " & _
" constraint c_1 primary key clustered (ID_Code))"
OServer.ExecuteImmediate Command:=transql,exectype:=SQLOLEExec_Default
Create an index on the Name column of table T_1:
transql="CREATE INDEX index_1 ON T_1 (Name)"
OServer.ExecuteImmediate Command:=transql,exectype:=SQLOLEExec_Default
Create table T_2 on database Db_2:
transql = "use Db_2 " & _
"create table T_2 " & _
"(Name char(8) null, " & _
" Age numeric(2) null, " & _
" Sex smallint null, " & _
" Department char(16) not nill, " & _
"No char(4) not null," & _
"constraint c_2 primary key clustered (Department,No))"
OServer.ExecuteImmediate Command:=transql, exectype:=SQLOLEExec_Default
At this point, we have completed the coding work of establishing the data object. After the program is compiled and executed, the database device, database and tables in the library will be automatically created on the SQL Server.
For the database device, you can use DISK REINIT, DISK REFIT, and DISK RESIZE to operate it. If you want to expand the capacity of the database device Device_1 to 200M, you can use the following method:
DISK RESIZE
Name='Devive_1',
Size = 102400
The structure of the database and tables already established on SQL Server can also be modified and deleted through DMO and transaction SQL. Similar to the above coding, you can use transaction SQL statements such as Alter Database Db_1, Alter DatabaseDb_2, Alter Table T_1, Alter Table T_2 to modify the structure of the established database and tables, and use Drop Database, Drop Table transaction statements to delete The database and tables created.
The above has discussed how to dynamically establish and access the structure of data objects on SQL Server. As for accessing the contents of databases and tables on SQL Server, you can use ODBC (Open Database Connectivity), DAO (Data Access Object), RDO ( Remote Data Objects) or DB-Library for programming.