Original: SQL Server Programming Series (1): SMO introduction
Sequel: SQL Server Programming Series (2): About operations for SMO common objects
Recently used in the project on the SQL Server management tasks of the programming implementation, with some of their own experience, want to share with you, in the work used to smo/sql clr/ssis and other aspects of knowledge, in the domestic article is not uncommon, there are some sporadic applications, In particular, the SSIS part of the domestic and foreign articles are mostly to explain how to drag the control, in the development process, in addition to refer to the SQL Server Help documents, MSDN and StackOverflow sites, these sites are basically English, in order to facilitate some poor English developers to learn, Zhou public in their own understanding to be organized into series, not to the place please everyone understanding.
about SMO
SMO is an abbreviation for the English SQL Server Management objects, which means that the SQL Server Management object family contains some column namespaces (namespace), dynamic-link libraries (DLLs), and classes (Class). These classes focus on the management of SQL Server and interact with SQL Server at the bottom level through the classes under the SQL Server database provider (System.Data.SqlClient). SMO can be used programmatically to manage versions of SQL Server7.0 and above (SQL Server 7.0/2000/2005/2008), and if SQL Server is lower than the previous version, it is not possible to use SMO to manage (except for historical legacy systems, In today's development, those unsupported SQL Servers are the same antiques as Windows95. At the same time, if you want to use SMO, you must install the SQL Server Native Client, which is installed automatically when we install the. Net Framework2.0 or above version of SQL Server2005.
Under 32-bit systems, if SQL Server2005 is installed and the installation path is not changed, the path to the SMO assembly is: C:\Program Files\Microsoft SQL Server\90\sdk\assemblies, corresponding, If you are installing SQL Server2008, the path to the SMO assembly is C:\Program Files\Microsoft SQL Server\100\sdk\assemblies, if installed under a 64-bit system, depending on the SQL installed Server version to determine whether it is in Program Files (x86) or under the corresponding directory under Program files.
The following namespaces are 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, about which DLLs these namespaces are in and what classes they have under that namespace, you can consult the SQL Server Help article or check out the online MSDN For example, view classes under namespaces 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 is the top-level SQL Server instance, each instance will have multiple databases, each database will have multiple tables, stored procedures, functions, logins, etc., each table will have columns, indexes, primary key information, each column will have column name, default value, field size and other information, There is a set of class architectures in the SMO that correspond to them.
Each element in the DatabaseCollection is an instance of the database class, corresponding to one of the databases in the data instance; in Tablecollection, each element is an instance of a table that corresponds to a table in the database. In Columncollection, each element is an instance of the column class that corresponds to each column in the table, respectively, These classes are located under the Microsoft.SqlServer.Management.Smo namespace in the Microsoft.SqlServer.Smo.dll DLL.
Of course, the classes in the Microsoft.SqlServer.Management.Smo namespace are much more than those mentioned above, and the table above simply makes a simple analogy.
SMO usage Examples
The above article just made a simple introduction, perhaps through the above boring introduction people have no impression, the following through a simple code to illustrate the use of a simple, first to add a reference to the response. In VS2008, you can add references directly in the following ways:
However, it is not so convenient in VS2010, it uses filtering properties (and cannot be disabled or set), so that these assemblies cannot be added, such as:
I saw someone in the stack overflow and I doubt the same problem, others gave an answer is to install the muse.vsextensions in the VS2010 to solve, install Address:/HTTP visualstudiogallery.msdn.microsoft.com/36a6eb45-a7b1-47c3-9e85-09f0aef6e879/, I tried a little bit, not very ideal, However, Muse.vsextensions also provides other features (such as removing unused assemblies) and is good.
Note that you need to add a reference to Microsoft.sqlserver.connectioninfo.dll and Microsoft.sqlserver.smo.dll before you can translate the following code.
The code is as follows:
Using system;using microsoft.sqlserver.management.common;//need to add Microsoft.sqlserver.connectioninfo.dll reference using microsoft.sqlserver.management.smo;//need to add Microsoft.sqlserver.smo.dll references namespace ssisstudy{//<summary>// SQL Server Programming columns article (1): The companion code for SMO///week///Date Created: 2012-05-17//Blog address: HTTP://BLOG.CSDN.NET/ZHOUFOXCN Http://zhoufo xcn.blog.51cto.com///Sina Weibo address: HTTP://WEIBO.COM/ZHOUFOXCN//</summary> class SMODemo01 {// <summary>///show a summary of the database///</summary> public static void Showserverinfo () { Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa; Password=root; "; Instantiating Server server server = new server (connection); Console.WriteLine ("Activedirectory:{0}", Server. ActiveDirectory); Console.writEline ("instancename:{0}", Server. INSTANCENAME); Lists the login user foreach (login login in server) in the current SQL Server instance. Logins) {Console.WriteLine ("Name:{0},createdate:{1},defaultdatabase:{2}", login. Name, Login. CreateDate, Login. DefaultDatabase); }//Index to the master database db = 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); Lists the summary information for each table under 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 summary information for each column in each table foreach (Column column in table. Columns) {Console.WriteLine ("\t\tcolumn:{0},datatype:{1},nullable:{2}", column. Name, column. DataType, column. Nullable); }}//lists 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 execution effect of the program is as follows:
The above code can get a lot of information about the database, and in the code we did not write a line of SQL statements, and in the previous week, the public wrote a blog "in. NET based on SQL Server system tables to obtain database management information," In order to find this information, the week public is a lot of data to know the wording of the SQL statement, using SMO after the SQL statements are exempt, it can be seen using SMO to manage the database convenience. In the following pages, Zhou Gong will tell how to use SMO to get the creation statements of databases in SQL Server and how to use SMO to create a job.
Zhou Gong
2012-05-17
sequel: SQL Server Programming Series (2): About operations for SMO common objects
SQL Server Programming Series (1): SMO introduction
