SQL Server programming series (1): SMO Introduction

Continued: SQL Server programming series (2): operations on common SMO objects

I recently used programming implementation for SQL Server Management Tasks in my project. I have some experiences and want to share with you here, I have used SMO, SQL clr, SSIs, and other knowledge in my work. I have not seen many articles in this field in China, but I also have some sporadic applications, in particular, some articles at home and abroad on SSIS mostly explain how to drag controls. during the development process, Zhou Gong also referred to the SQL Server help documentation, msdn, stackoverflow, and other websites. These websites are basically in English, to make it easier for developers who are not good at English to learn, Zhou Gong organized his understanding into a series. If not, please forgive me.
SMO Introduction
SMO is the abbreviation of SQL Server Management objects. It refers to the series of SQL Server Management objects, including the namespace and dynamic link library (DLL) of some columns) and class ). These classes focus on the management of SQL Server and interact with SQL Server through the classes in the underlying SQL Server database provider (system. Data. sqlclient. You can use SMO to manage SQL Server 7.0 or later versions (SQL Server 2000/2005/2008/) by programming ), if the SQL Server version is earlier than above, it cannot be managed by SMO (except for the legacy systems due to historical reasons, in the current development, unsupported SQL Server is the same as Windows 95 ). To use SMO, you must install the SQL Server native client. Generally, when you install. Net framework2.0 or later or SQL server2005 or later, it is automatically installed.
In a 32-bit system, if SQL server2005 is installed and the installation path is not changed, the path of the SMO assembly is: C: \ Program Files \ Microsoft SQL Server \ 90 \ SDK \ assemblies, if SQL server2008 is installed, the path of the SMO assembly is c: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ assemblies, if it is installed in a 64-bit system, you can determine whether it is in the corresponding directory of program files (x86) or under program files based on the installed SQL Server version.
The following namespace is available in SMO: Microsoft. sqlserver. management. common, Microsoft. sqlserver. management. NMO, Microsoft. sqlserver. management. SMO, Microsoft. sqlserver. management. SMO. agent, Microsoft. sqlserver. management. SMO. broker, Microsoft. sqlserver. management. SMO. mail, Microsoft. sqlserver. management. SMO. registeredservers, Microsoft. sqlserver. management. SMO. WMI, Microsoft. sqlserver. management. trace: You can refer to the SQL Server help article or online msdn for details about the DLL in which the namespaces are stored and the classes in the namespace, for example, viewing classes under a namespace can browse: http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo (V = SQL .100)
SMO Architecture
We know that in the SQL Server System, the top layer is an SQL server instance. Each instance has multiple databases, and each database has multiple tables, stored procedures, functions, and logon accounts, each table has information such as columns, indexes, and primary keys. Each column has information such as column names, default values, and field sizes. in SMO, the table has a class architecture that corresponds to it.

In databasecollection, each element is a database instance, which corresponds to a database in the data instance. In tablecollection, each element is a table instance, which corresponds to a table in the database; in columncollection, each element is an instance of the column Class, which corresponds to each column in the table. The above classes are located in Microsoft. sqlserver. management. under the SMO namespace. sqlserver. SMO. DLL.
Of course, the classes in the Microsoft. sqlserver. Management. SMO namespace are much more than those mentioned above. The above table is just a simple analogy.
SMO usage example
The above Article is just a simple introduction. Maybe you are not impressed by the boring introduction above. Below is a simple code to demonstrate the usage, first, add a reference to the response. In vs2008, you can add reference directly using the following method:

However, in vs2010, it is not so convenient. It uses the filter attribute (and cannot be disabled or set), making it impossible to add these assemblies, such:

I have seen that someone raised the same question in stack overflow and I am confused. Someone else gave me the answer to install muse in vs2010. to solve this problem, the installation address: Remove unused assembly (for example, Remove unused assembly) is good.
Note that you must add references to Microsoft. sqlserver. connectioninfo. dll and Microsoft. sqlserver. SMO. DLL before compiling the following code.
The Code is as follows:

Using system; using Microsoft. sqlserver. management. common; // you need to add Microsoft. sqlserver. connectioninfo. DLL reference using Microsoft. sqlserver. management. SMO; // you need to add Microsoft. sqlserver. SMO. DLL reference namespace ssisstudy {/// <summary> // SQL Server programming column articles (1): related code of SMO introduction // Author: zhou Gong // creation date: 2012-05-17 // blog address: http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com // Sina Weibo address: http://weibo.com/zhoufoxcn /// </Summary> class smodemo01 {// <summary> // displays the database overview /// </Summary> Public static void showserverinfo () {// create the serverconnection instance serverconnection connection = new serverconnection (); // specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection); console. writeline ("ActiveDirectory: {0}", server. activeDirectory); console. writeline ("instancename: {0}", server. instanceName); // list the logon user foreach (login Login in server. logins) {console. writeline ("Name: {0}, createdate: {1}, defaultdatabase: {2}", login. name, login. createdate, login. defaultdatabase);} // index the master database = server. databases ["master"]; console. writeline ("Database: {0}", DB. name); console. writeline ("createdate: {0}, DBO: {1}", DB. createdate, DB. owner); console. writeline ("dataspaceusage: {0}, spaceavailable: {1}", DB. dataspaceusage, DB. spaceavailable); console. writeline ("primaryfilepath: {0}", DB. primaryfilepath); // list the summary information of each table in the master database foreach (Table table in dB. tables) {console. writeline ("\ tTable: {0}, dataspaceused: {1}, createdate: {2}", table. name, table. dataspaceused, table. createdate); // urncollection. add (table. urn); // list the summary information of each column in each table. columns) {console. writeline ("\ t \ tcolumn: {0}, datatype: {1}, nullable: {2}", column. name, column. datatype, column. nullable) ;}}// list role information in the master database foreach (databaserole dbrole in dB. roles) {console. writeline ("DB role {0} created at {1}", dbrole. name, dbrole. createdate);} console. readline ();}}}

The program execution result is as follows:

Through the above code, we can get a lot of information about the database. In the code, we didn't write a line of SQL statements, but Zhou Gong wrote a blog titled in. net to obtain database management information according to the SQL server system table, in which to find this information Zhou Gong checked a lot of information to know how to write the SQL statement, even SQL statements are free after SMO is used, which makes it easy to use SMO to manage databases. In the subsequent sections, Zhou Gong will explain how to use SMO to obtain database creation statements in SQL Server and how to use SMO to create jobs.
Zhou Gong
2012-05-17

Continued: SQL Server programming series (2): operations on common SMO objects