Dynamic Creation and access of SQL Server Data Object Structure

Dynamic Creation and access of SQL Server Data Object Structure

Microsoft SQL Server, marshal, and Sybase are currently popular database management systems (RDMS). With the continuous improvement and development of SQL Server and the perfect combination with Windows NT, the network operating system, it provides a good development environment for Distributed Client Server structure program design, so it has been paid more and more attention.

Microsoft SQL Server is a scalable and high-performance database management system that integrates with Windows NT threads and scheduling services, performance monitors, and event browsers, based on the Windows Management Interface, it provides visual drag-and-drop control for Remote Data Replication management, server management, diagnosis, and adjustment on multiple servers.

DMO provides an object interface for SQL Server engines and services. SQL-DMO is a 32-bit OLE Automation Object provided for Windows 95 and Windows NT operating systems, Ole is the standard and method for maintaining application shared objects, is a programming method that allows applications to manage or access shared objects. DMO is developed using the OLE structure. It provides interfaces for ole-compatible applications to manage all SQL servers. DMO enables software developers to directly access SQL server data objects. With Visual Basic, Visual FoxPro, and other programming tools, we can use distributed management objects to achieve direct access to SQL Server.

The tools provided by Microsoft SQL server allow the client to access data on the server in multiple ways. The core of these tools is the transact-SQL (transaction SQL) code. Transact-SQL is an enhanced version of Structured Query Language (SQL). It provides many additional functions and functions. With Transact-SQL, you can create database devices, databases, and other data objects, extract data from the database, modify data, or dynamically change the settings in SQL Server. Therefore, the use of transact-SQL greatly improves the practicability of applications.

When developing a Client/Server structure project, we need to establish several data object structures. If the project we are developing is for a specific customer, it is feasible to manually create database devices, databases, and tables on the server. However, if the project is developed as a software release rather than for a specific customer, it is unrealistic to manually create the structure of data objects. We need to solidify the process of creating databases and tables in the program, and dynamically create databases and tables on the server when the program is running. Therefore, using distributed management objects and transact-SQL is an important means of designing Client/Server structured programs.

In Visual Basic 5.0, we will discuss how to use DMO and transact-SQL to create database devices, databases, and other data objects on SQL Server. Start with the specific problem: on SQL Server, set up database devices device_1 and device_2 of M and M, and set up two databases db1 and DB2 with a capacity of m on device_1 and device_2 respectively, create Table T_1 in database db1, which has four fields: name, age, sex, id_code. Use id_code as the keyword and name as the index. Create Table T_2 in database db2, there are five domains: name, age, sex, department, and No. The keyword department and no is used.

To access SQL Server, you must first enable SQL Service Manager. SQL Service Manager is used to start, pause, continue, and stop SQL Server services, including MSSQLServer and sqlexecutive. You can manually open these two services, or you can open them in a program by using the command line.

If you use the manual method, enable SQL Service Manager. For the services MSSQLServer and sqlexecutive, directly start their start/continue.

You can use the shell command of VB to start using the line command:

X = shell ("sqlmgr.exe", 1) displays the SQL Service Manager window;
X = shell ("Net start MSSQLServer") Start MSSQLServer Service
X = shell ("Net start sqlexecutive") Start the sqlexecutive Service

This is because we want to establish a dynamic structure of data objects on SQL Server through programming. Therefore, we recommend using the line command method.

After starting SQL Server Manager, we can use DMO and transact-SQL to create data objects by following these steps.

1. to use DMO, first 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 ), add comctl32.oca in the \ winnt \ system32 directory 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 (generally SA), password: = Password

4. Create the database device device_1 and device_2:
Dim transql as string
Transql = "use master "&_
"Disk init "&_
"Name = 'device _ 1 ',"&_
"Physname = 'd: \ SQL \ data \ device1.dat ',"&_
"Vdevno = 10 ,"&_
"Size = 102400 "&_
"Disk init "&_
"Name = 'device2 ',"&_
"Physname = 'd: \ SQL \ data \ device2.dat ',"&_
"Vdevno = 11 ,"&_
"Size = 204800"
Oserver. executeimmediate command: = transql, exectype: = sqloleexec_default

5. Create Database db_1 and db_2:
Transql = "create database db_1 on device_1 = 100"
Oserver. executeimmediate command: = transql, exectype: = sqloleexec_default
Transql = "create databases 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 for the name column of Table T_1:
Transql = "create index index_1 on T_1 (name )"
Oserver. executeimmediate command: = transql, exectype: = sqloleexec_default
Create a 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 data object encoding. After the program is compiled and executed, the SQL server will automatically create database devices, databases, and tables in the database.

You can use disk reinit, disk refit, and disk resize to operate a database device. To expand the device_1 capacity of the database device to 200 MB, use the following method:

Disk resize
Name = 'device_1 ',
Size = 102400

You can also modify and delete databases and table structures created on SQL Server through DMO and transaction SQL. Similar to the preceding encoding, you can use alter database db_1, alter databasedb2. alter table T_1, alter table T_2, and other transaction SQL statements to modify the structure of the created database and table, the drop database and drop table transaction statement are used to delete the created database and table.

The above discusses how to dynamically establish and access the structure of data objects on SQL Server. As for access to the content of databases and tables on SQL Server, ODBC (Open Database interconnection) can be used), Dao (Data Access Object), rdo (Remote Data Object), or DB-library for programming.