(GO) SQL Server Programming series (1): SMO introduction

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 SMOSMO 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 ArchitectureWe 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, corresponding to each column in the table, each of which is located under the Microsoft.SqlServer.Management.Smo namespace, Microsoft.SqlServer.Smo.dll in this 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 ExamplesThe 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:

2. Using System;
4. Using Microsoft.SqlServer.Management.Common; //need to add Microsoft.sqlserver.connectioninfo.dll reference
6. Using Microsoft.SqlServer.Management.Smo; //need to add Microsoft.sqlserver.smo.dll reference
8. Namespace Ssisstudy
10. {
12. // <summary>
14. ///SQL Server Programming columns article (1): Companion code for SMO introduction
16. /// Zhou Gong
18. ///Date Created: 2012-05-17
20. ///Blog address: HTTP://BLOG.CSDN.NET/ZHOUFOXCN http://zhoufoxcn.blog.51cto.com
22. ///Sina Weibo address: HTTP://WEIBO.COM/ZHOUFOXCN
24. // </summary>
26. class SMODemo01
28. {
30. // <summary>
32. /// display a summary of the database
34. // </summary>
36. public static void Showserverinfo ()
38. {
40. //Create an instance of Serverconnection
42. Serverconnection connection = new Serverconnection ();
44. //Specify connection string
46. Connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa; Password=root; ";
48. //Instantiate server
50. Server server = new server (connection);
52. Console.WriteLine ("activedirectory:{0}", Server. ActiveDirectory);
54. Console.WriteLine ("instancename:{0}", Server. INSTANCENAME);
56. //List the logged-in user in the current SQL Server instance
58. foreach (Login login in server.) Logins)
60. {
62. Console.WriteLine ("Name:{0},createdate:{1},defaultdatabase:{2}", login. Name, Login. CreateDate, Login. DefaultDatabase);
64. }
66. //Find the master database by index
68. Database db = server. databases["Master"];
70. Console.WriteLine ("database:{0}", Db. Name);
72. Console.WriteLine ("Createdate:{0},dbo:{1}", Db. CreateDate, Db. Owner);
74. Console.WriteLine ("Dataspaceusage:{0},spaceavailable:{1}", Db. DataSpaceUsage, Db. SpaceAvailable);
76. Console.WriteLine ("primaryfilepath:{0}", Db. Primaryfilepath);
78. //List summary information for each table under the master database
80. foreach (table table in db. Tables)
82. {
84. Console.WriteLine ("\ttable:{0},dataspaceused:{1},createdate:{2}", table. Name, table. dataspaceused, table. CreateDate);
86. //urncollection.add (table. URN);
88. //List summary information for each column in each table
90. foreach (column column in table. Columns)
92. {
94. Console.WriteLine ("\t\tcolumn:{0},datatype:{1},nullable:{2}", column. Name, column. DataType, column. Nullable);
96. }
98. }
100. //List role information in the master database
102. foreach (DatabaseRole dbrole in db. Roles)
104. {
106. Console.WriteLine ("DB Role {0} Created at {1}", Dbrole.name, dbrole.createdate);
108. }
110. Console.ReadLine ();
112. }
114. }
116. }

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.

(GO) SQL Server Programming series (1): SMO introduction