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 own Windows series operating systems, it provides better performance and ease of use on the Windows platform, so it is increasingly valued. The full name of SQL-DMO is SQL Distributed Management Objects. It is a collection containing a set of COMs with dual interfaces. Through SQL-DMO we can control the database engine and replication management that operates SQL Server. Since SQL-DMO is a set of COM, it can be used by any development tool that can use COM, including VB, Delphi, VC, BCB, asp, etc., almost all development tools under the Windows platform.
SQL-DMO uses
Why do we need to use SQL-DMO to manage databases? Is it not enough for us to manage the database using Enterprise Manager and T-SQL scripts? SQL-DMO can carry out flexible, customizable and efficient database management activities. Since SQL-DMO provides a management interface for SQL SERVER, through programming, you can customize management activities according to your needs. For example, if we are managing a large organization with many SQL Server instances. Now you need to add a user to all SQL Server instances in your organization. If you manually add user calls to each SQL Server instance through Enterprise Manager, it is a time-consuming and labor-intensive operation that is prone to human errors. If you use SQL-DMO programming to implement the calls, It can be completed easily and efficiently with one cycle. In the author's opinion, what is more important is that MSDE can be managed using SQL-DMO. MS SQL SERVER is expensive, while MSDE is free. In some small and medium-sized applications, MSDE is sufficient. However, since MSDE only provides a database engine but does not provide a visual management interface, it is impossible to manage the databases in MSDE regularly, which limits the scope of application of MSDE. At this time, if we use SQL-DMO, we can provide our own database management interface, so that we can perform certain regular management of the database in MSDE. If time and cost are not considered, you can create a management interface similar to that of Enterprise Manager. In fact, most of the functions of Enterprise Manager are also implemented by using SQL-DMO. (SQL-NS is used (SQL-Namespace, which is also a Com object collection, providing access to the object name space in SQL Server), but SQL-NS itself is also built on SQL-DMO)
SQL-DMO host and composition
The host of SQL-DMO is SQLDMO.DLL, located under X:/Program Files/Microsoft SQL Server/80/Tools/Binn. In addition, it also requires the following files: l
SQLDMO.DLL ( main type library) l
SQLDMO.RLL ( language resource file) l
SQLSVC.DLL ( type library for database management) l
SQLSVC.RLL ( language resource file) l
SQLUNIRL.DLL (Unicode redirection layer) l
SQLRSLD.DLL ( for resource loading) l
ODBC ( including ODBC32.DLL, SQLSRV32.DLL, ODBCBCP.DLL , etc.) Here is an explanation of the call structure (Figure 1):
| Our application creates objects using SQL-DMO here |
Figure 1 SQL-DMO call structure
SQL-DMO object tree
Let’s first look at the huge and complex object tree of SQL-DMO (see Figure 3): SQLServer object is the backbone of this object tree, and there are three main branches under this backbone: l Database branch, and a series of object implementations under it Database architecture and maintenance tasks in SQLServer. l JobServer branch, a series of objects under it implements the SQL Server Agent (Agent) service. l Replication branch, a series of objects under it implements the replication service of SQL Server. There are also things to be noted: l The object tree does not list the Application object, but this is actually a relatively important object. Any SQL-DMO object has a property pointing to the Application object. The Application object represents a global application, which contains two collection objects: Servers and ServerGroups. As shown in Figure 2: Figure 2 Application object l object tree only represents the structure of SQL-DMO and does not list all objects, such as NameList object, Servergroup object, etc. The SQL-DMO object tree has a complex structure and many objects. It is impossible to introduce and explain each object, each property and each method one by one. Some methods will be explained in the specific code below. Figure 3 SQL-DMO object tree
Implementation of Delphi encapsulated SQL-DMO
The following is a description of the component TDBCTRL for SQL-DMO encapsulation implemented by the author using Delphi. Because I needed a free database engine in a certain project, I finally chose MSDE, but MSDE has no management interface, it is just an engine. Therefore, there is the need to provide a simple and clear management interface for MSDE. We don't need to provide as many management functions as Enterprise Manager, we just need to provide the most commonly used functions. Considering ease of use and scalability, it is decided to provide it in the form of components. The implemented components provide a layer of packaging for SQL-DMO, encapsulating some commonly used functions, and simplifying the use. Note that compiling this component requires introducing SQL-DMO type library and generating corresponding description code. The code contains many Delphi's regular usage and idiomatic notation of Com objects. I will not discuss how Delphi uses COM here, readers can refer to related books. This component contains the following properties: property Host:String read FHost write FHost;//host name, that is, the SQL Server instance name to be connected property PassWord:String read FPWD write FPWD; //User password used for connection property L ogin:String read FLogin write FLogin; //Username used for connection property LoginSecure:boolean read FLoginSecure write FLoginSecure; //Login mode, True means using NT verification mode, false means SQL Sever verification mode, and this group Aggregate an SqlServer object with A private data member: sqlserver to save. Its type is _SqlServr. The constructor and destructor of this component are also very simple. The constructor is to connect to create a SQLserver object. The destructor is also just to complete the destruction of the SQLServer object. constructor TDBCTRL.Create(AOwner: TComponent); began inherited;sqlserver:=coSqlserver.Create; //CoSqlserver is the class factory end of SqlServer; destructor TDBCTRL.De stroy;begin sqlserver:=nil; //Set this object to nil After that, Delphi will complete the destruction of Com objects behind the scenes. inherited;end;If we want to manage and operate a certain SQL Server instance, we must first connect to this instance. The following ConnectToServer function implements the function of how to connect to a SQL Server instance. function TDBCTRL.ConnectToServer: Boolean; begin SqlServer.LoginSecure:= FLoginSecure;// Set whether it is NT verification mode, if it is True, Login and Password will be ignored; try sqlServer.Conne ct(FHost,FLogin,FPWD); Result:=True ; Except On E:EOleException do begin ShowMessage(e.Message); result:=false; end; end; end; end; after the connection is completed, control over this instance is obtained (there will be different controls depending on the logged in user permission). Some of the methods listed below show some features. The GetTableList method obtains all the table names of a database and returns them in a TStringList. function TDBCTRL.GetTableList(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);//Get Database object based on the database name for I:=1 to NowDB.Tables.Count do begin NowTable:=NowDB.Tables.Item(I,0); TableList.ad d (NowTable .Name); end; //All table names in this database are added to TStringList result:=True; Except TableList.Clear; result:=False; end;end; Similar, you can use a similar method to Get all views in the database (Views), all fields of a table or view (Columns), all data file groups of a database, and data files (FileGroups, DBFiles), etc. These can be specifically referred to the object structure tree of SQL-DMO . The use of SQL-DMO objects can follow the following basic steps: obtain an object or collection object through the upper layer object, and then use the object or obtain an object in the collection object before use. Generally speaking, if it is a collection object, there is an Item method, and the collection member object can be returned based on Index or Name. It should be noted that Index starts at 1 instead of 0. The above methods are all demonstrated how to obtain information from the database. Next, I will demonstrate another method. The function of this method is to back up the database to a file. This method shows how to maintain the database. function TDBCTRL.BackupDB(DBName, BackupFile: String): Boolean;//The passed parameters are the database name to be backed up and the backup file name var dmoBack:_Backup; //The backup object backupdrv:_backupde vice; //Backup device object begin // Set the backup device try DeleteFile(BAckUPFile); //If the backup file already exists, delete it. try dmoBackup:=coBackup.create; backupdrv:=coBackupDevice.Create; //Create object backupdrv .name:=DBName+'Bak'; // Specify the name of the backup device backupdrv.Type_ :=SQLDMODevice_DiskDump; backupdrv.PhysicalLocation :=BackupFile; //Add backup device try sqlServer.BackupDevices. Add(backupdrv); except // Since the exception here will not return the value, there is no way, just Can you think that the exception is added because the backup set end already exists; //Back 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;e nd;end;In addition, I want to give an example Explain how to use the Application object mentioned at the beginning. This method is to get the names of all available (that is, reachable and connectable) Sql Server instances. function TDBCTRL.GetServerList(var ServerList: TStringList): Boolean;var App:_Application; //Application object NList:NameList; //This NameList is also an object I in SQL-DMO: integer; begin try App:=CoApplication.Create ; NList:=App.ListAvailableSQLServers; //Get all available SQL Server instance names for I:=0 to NLIst.Count-1 do ServerList.Add(NList.Item(I)); finally App:=nil; // Release Object end;end; Of course, there are many methods for the TDBCTRL component, but the code implementation is similar, such as the recovery method corresponding to the backup and the disconnection method corresponding to the connection. I will not list them one by one. Readers can refer to the sample code and SQL SERVER's Online Book to learn from one example and implement the functions they want. If you need all the code for this component, you can ask me for it.
Other Instructions
Version 2000 of SQL-DMO is fully compatible with SQL SERVER7.0 and SQL SERVER 2000. However, more than 80 new interfaces are provided to support the new features of SQL SERVER 2000. These new interfaces all end with "2", which are supersets 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 need to use the interface with "2" at the end.
bibliography
l Microsoft MSDNl Microsoft SQL SERVER Online Bookl "Delphi COM In-depth Programming" (US) Eric Harmon Machinery Industry Press
