Introduction to SQL-DMO
Microsoft SQL Server is currently a popular database management system. With the continuous improvement and development of SQL Server, and the perfect combination with Microsoft's Windows Series operating system, there is a good performance and ease of use on the Windows platform, so it is increasingly attached to people.
The full name of SQL-DMO is SQL Distributed Management Objects. It is a collection containing a set of COM with double interface. We can control database engine and replication management of SQL Server through SQL-DMO. Since SQL-DMO is a set of COM, any development tool that can use COM can use it, including VB, Delphi, VC, BCB, ASP, etc. Almost all Windows platform development tools.
SQL-DMO
Why do we need to use SQL-DMO to manage the database? Do we use Enterprise Manager and T-SQL scripts to manage the database is not enough?
Flexible, customizable, efficient database management activities with SQL-DMO. Since SQL-DMO is provided by SQL Server management interface, you can customize management activities in your own needs. For example, if we manage a large organization, there are many SQL Server instances. Now you have to add a user to all SQL Server instances in your organization. If you handle each SQL Server instance by Enterprise Manager is a time-consuming labor, it is easy to operate, if you use SQL-DMO programming implementation, Use a loop operation to complete and efficiently.
In the author's view, more important is: Use SQL-DMO to manage MSDE (Microsoft SQL Server DeskTop Engine). MS SQL Server is expensive, while MSDE is free. In some small and medium applications, MSDE is sufficient. However, since the MSDE only provides a database engine does not provide a visual management interface, the database in MSDE cannot be regularly managed, which limits the scope of the MSDE. At this point, if we use SQL-DMO to provide your own database management interface, you can make certain regular management of the database in MSDE. If time and cost is not considered, you can do a functionality similar to the management interface of Enterprise Manager. In fact, most of Enterprise Manager is also implemented by using SQL-DMO. (Use SQL-NS (SQL-NAMESPACE, which is also a COM object collection, providing access to access function of the object name space in SQL Server), but SQL-NS itself is also the architecture on SQL-DMO)
SQL-DMO host and composition
SQL-DMO host is SqldMo.dll, located in X: / Program Files / Microsoft SQL Server / 80 / Tools / BINN. In addition, it also requires some files:
l SqldMo.dll (Main Type Library)
l SqldMo.rll (language resource file)
l SQLSVC.DLL (Database Management Type Library)
l SQLSVC.RLL (language resource file)
l SQLunirl.dll (Unicode redirection layer)
l SQLRSLD.DLL (resource load)
l ODBC (including ODBC32.DLL, SQLSRV32.DLL, ODBCBCP.DLL, etc.)
Here is an illustration of a call structure (Figure 1):
Your Application
Odbc32.dllsqlsvc.dll
SQLRSLD.DLL
Sqldmo.rll
SQLSVC.RLL
Our application creates SQL-DMO object here
SQL Server
Network boundary
SqldMo.dll
Figure
1 SQL-DMO
Call structure
SQL-DMO object tree
Let's take a look at the huge complicated object tree of SQL-DMO (see Figure 3):
SQLSERVER object is the trunk of this object tree, and this trunk has three big branches:
l Database branch, a series of objects achieved database architecture and maintenance tasks in SQL Server.
l JobServer branch, a series of objects under this implementation of the SQL Server Agent (Agent) service.
l Replication branch, a series of objects that implement SQL Server replication services.
It also needs to be described:
l The object tree does not list the Application object, in fact, this is a more important object. Any SQL-DMO object has an attribute pointing to the Application object. The Application object represents a global application that contains two collection objects of Servers and ServerGroups. Figure 2:
Figure 2 Application object
l The object tree only indicates the structure of SQL-DMO and does not list all objects, such as NameList objects, ServerGroup objects, and more.
The SQL-DMO object tree structure is complicated. There are many objects, and it is not possible to introduce each of the objects per method for each object, and some methods will be explained in the specific code below.
Figure 3 SQL-DMO object tree
Delphi package SQL-DMO implementation
Here are the components of the SQL-DMO packaged component TDBCTRL using Delphi.
The author will eventually choose MSDE because of a free database engine in a project, but MSDE does not have a management interface, just a engine. So I have a simple and clear management interface to MSDE. We don't need to provide so many management functions of Enterprise Manager, we only need to provide the most common functionality. Considering easy usability and scalability, deciding to provide with components. The implemented component provides a layer of packaging for SQL-DMO, encapsulates some common functions, simplifies usage.
Note that compiling this component needs to introduce the type library of SQL-DMO and generate a corresponding description code. The code contains many conventional usage and customary representations of many Delphi to COM objects. Not discussing how Delphi uses COM here, readers can refer to the relevant books.
This component contains the following properties:
Property Host: String Read Fhost Write Fhost;
/ / The host name, that is, the SQL Server instance name to connect
Property Password: String Read FPWD Write FPWD;
// User password used to connect
Property Login: String Read Flogin Write Flogin
// User name for connecting
Property loginsecure: Boolean Read Floginsecure Write Floginsecure;
// Login mode, TRUE represents uses NT authentication mode, FALSE represents SQL Sever authentication mode
In addition, this component aggregates a SQL Server object, with a private data member: SQL Server saves. Its type is _SQLServr.
The constructor and destructive function of this component are also simple. The constructor is a connection to create a SQLServer object. The destructor is also only destructured for the SQL Server object. Constructor TDBCTRL.CREATE (Aowner: Tcomponent);
Begin
inherited;
SQLServer: = cosqlser.create; // cosqlserver is a factory plant_sqlserver
END;
Destructor TDBCTRL.DESTROY;
Begin
SQLSERVER: = NIL; // After setting this object to NIL, Delphi will complete the description of the COM object after the scene.
inherited;
END;
If we want to manage and operate a SQL Server instance, first we have to connect to this instance. The following CONNECTTOSERVER function implements how to connect to a functionality of a SQL Server instance.
Function TDBCTRL.CONNECTOSERVER: BOOLEAN;
Begin
SQLSERVER.LOGINSecure: = floginsecure;
// Set whether it is a NT authentication mode, if it is true, it will ignore the login and password;
Try
SQLServer.connect (fHOST, FLOGIN, FPWD);
RESULT: = TRUE;
Except
ON E: EoleException DO
Begin
ShowMessage (E.MESSAGE);
Result: = FALSE;
END;
END;
END;
After the connection is completed, the control of this instance is obtained (depending on the logged in user, there will be different control permissions). Some methods listed below show some features.
The GetTableList method obtains all the table names of a database and put it in a TSTRINGLIST.
Function TDBCTRL.GETTABLIST (DBNAME: String;
Var TableList: tstringlist): boolean;
VAR
NowDB: _Database; // Database
NOWTABLE: _TABLE; / / Table
I: integer;
Begin
Try
TableList.clear; / / Clear Stringlist
NowDB: = SQLSERVER.DatabaseS.Item (DBNAME, SQLSERVER);
/ / Take the Database object according to the database name
For i: = 1 to nowdb.tables.count do
Begin
NOWTABLE: = nowDb.tables.Item (i, 0);
TableList.add (nowTable.name);
End; // Take all the table names in this database, add TSTRINGLIST
RESULT: = TRUE;
Except
Tablelist.clear;
Result: = FALSE;
END;
END;
Similarly, with similar methods can achieve all the views (a list of values), a table or view all fields, all data file groups, and data files (fileGroups, dbfiles), etc., and data files (fileGroups, dbfiles), etc. Specific reference to SQL-DMO object tree.
The use of SQL-DMO objects can follow the following basic steps: take an object or set object through the upper object, and then use an object or acquire an object in a set object to use. In general, if there is a set object, there is an ITEM method, you can return a collection member object according to INDEX or NAME. It should be noted that index starts from 1 instead of starting from 0.
The above methods are to demonstrate how to get information from the database. Here I demonstrate a method, the functionality of this method is to back up the database to a file. Through this method, it shows how to maintain the database. Function TDBCTRL.BACKUPDB (DBNAME, Backupfile: String): Boolean;
// Introduced parameters to want to back up the database name and backup file name
VAR
DMOBACK: _BACKUP; // Backup object
BackupDRV: _BackupDevice; // Backup device object
Begin
/ / Set backup device
Try
Deletefile; // If the backup file already deletes it
Try
DMOBACK: = COBACKUP.CREATE
Backupdrv: = COBACKUPDEVICE.CREATE; / / Create an object
Backupdrv.name:=dbname 'bak '; // Specify the name of the backup device
Backupdrv.type_: = sqldmodevice_diskdump;
Backupdrv.physicalLocation: = backupfile;
// Add a backup device
Try
SQL Server.BackupDevices.Add (Backupdrv);
Except
// Because the exception here does not return a value, there is no way, it can only be considered that the addition is due to this backup set.
END;
/ / Backup
DMOBACK.ACTION: = SQLDMOBACKUP_DATABASE;
DMOBACK.DATABASE: = DBNAME;
DMOBACK.DEVICES: = DBNAME 'BAK';
DMOBACK.SQLBACKUP (SQLServer);
RESULT: = TRUE;
Except
ON E: EoleException DO
Begin
ShowMessage (E.MESSAGE);
Result: = FALSE;
END;
END;
Finally
DMOBACK: = NIL;
Backupdrv: = NIL;
END;
END;
Also, I will also give an example to explain how to use the Application object that you just mentioned. This method is the name of all available SQL Server instances available (but also accessible) SQL Server instances.
Function TDBCTRL.GetServerList (var serverlist: tstringlist): boolean;
VAR
App: _Application; // Application object
NList: Namelist; // This namelist is also an object in SQL-DMO
I: integer;
Begin
Try
App: = coApplication.create;
NList: = app.listavailablesqlservers;
/ / Get all available SQLServer instance names
For i: = 0 to nlist.count-1 do
ServerList.Add (NList.Item (i));
Finally
App: = nil; // Release the object
END;
END;
Of course, there are many methods of TDBCTRL components, but the code implementation is similar, such as the recovery method corresponding to the backup, and I will not be listed in the connection method. The reader can refer to the sample code and the SQL Server's online book, and the other three is to realize the features you want. If all the code for this component can ask yourself. other instructions
SQL-DMO's 2000 version is fully compatible with SQL Server 7.0 and SQL Server 2000. But providing more than 80 new interfaces to support new features of SQL Server 2000. These new interfaces are ended with "2", which is the supercoming of those interfaces in the original SQL Server 7.0. That is to say, if you want to use the new features of SQL Server 2000, you should use the interface "2" interface.
bibliography
l Microsoft MSDN
l Microsoft SQL Server Online Book
l "Delphi COM in-depth programming" (US) Eric Harmon Machinery Industry Press
