SQLServer programming series (1): SMO Introduction

I recently used programming implementation on SQL Server Management Tasks in my project. I have some experiences and want to share with you some knowledge about SMOSQLCLRSSIS in my work, in China

Recently, I have used programming for SQL Server Management Tasks in my project and have some experiences. I would like to share with you here, I have used SMO, SQLCLR, and SSIS knowledge in my work.

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, you can view the classes in the namespace: (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, VS2010 is not so convenient. It uses the filter attribute (and cannot be disabled or set) and Hong Kong space, making it impossible to add these assemblies, such:

I saw someone asking me the same question in Stack Overflow: server space. Someone gave me the answer: Install Muse in VS2010. VSExtensions to solve the problem. installation address: it is not ideal if I try it, but Muse. VSExtensions also provides other functions (such as removing unused assembly.

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:

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 skipped after SMO is used. The convenience of using SMO to manage databases can be seen on the U.S. server. 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

This article is from the "Zhou Gong (Zhou Jinqiao)" blog. Be sure to keep this source