Getting started with SQL Server Management Objects (SMO)Problem. SQL Server 2. 00. SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL- DMO) which was used for SQL Server 2. In this tip, I am going to discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language. · Microsoft distributes Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new. Solution. Although SQL Server Management Studio (SSMS) is a great tool to manage a SQL Server instance there might be a need to manage your SQL Server instance programmatically. For example, consider you are developing a build deployment tool, this tool will deploy the build but before that it needs to make sure that the SQL Server and SQL Server Agent services are running, a database is available and online. For this kind of work, you can use SMO, a SQL Server API object model. The SMO object model represents SQL Server as a hierarchy of objects. Database Microsoft Sql Server Management Smoky· Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views. Features Not Supported in a Future Version of SQL Server. The following SQL Server Database Engine features are supported in the next version of SQL Server, but will. ![]() On top of this hierarchy is the Server object, beneath it resides all the instance classes. SMO classes can be categorized into two categories: Instance classes - SQL Server objects are represented by instance classes. It forms a hierarchy that resembles the database server object hierarchy. On top of this hierarchy is Server and under this there is a hierarchy of instance objects that include: databases, tables, columns, triggers, indexes, user- defined functions, stored procedures etc. I am going to demonstrate the usage of a few instance classes in this tip in the example section below. Utility classes - Utility classes are independent of the SQL Server instance and perform specific tasks. These classes have been grouped on the basis of its functionalities. For example Database scripting operations, Backup and restore databases, Transfer schema and data to another database etc. I will discussing the utility classes in my next tip. How SMO is different from SQL- DMOSMO object model is based on managed code and implemented as . NET Framework assemblies. It provides several benefits over traditional SQL- DMO along with support for new features introduced with SQL Server 2. SQL Server 2. 00. For example It offers improved performance by loading an object only when it is referenced, even the objects properties are loaded partially on object creation and left over objects are loaded only when they are directly referenced. It groups the T- SQL statements into batches to improve network performance. It now supports several new features like table and index partitioning, Service Broker, DDL triggers, Snapshot Isolation and row versioning, Policy- based management etc. An exhaustive list of the comparisons between SQL- DMO and SMO can be found here. Example. Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. To add a reference of these assemblies, go to Solution Browser - > References - > Add Reference. Add these commonly used assemblies. Microsoft. ![]() Database Microsoft Sql Server Management SmokersSql. Server. Connection. Info. dll. Microsoft. Sql. Server. Smo. Microsoft. Sql. Server. Smo. Enum. dll. Microsoft. Sql. Server. Sql. Enum. dll. Microsoft. Sql. Server. Management. Sdk. Sfc. dll // on SQL Server/VS 2. There are a couple of other assemblies which contain namespaces for certain tasks, but few of them are essential to work with SMO. Some of the frequently used namespaces and their purposes are summarized in the below table, other namespaces are used for specific tasks like working with SQL Server Agent where you would reference Microsoft. Sql. Server. Management. Smo. Agent etc. Namespaces. Purpose. Microsoft. Sql. Server. Management. Common. It contains the classes which you will require to make a connection to a SQL Server instance and execute Transact- SQL statements directly. Microsoft. Sql. Server. Management. Smo. This is the basic namespace which you will need in all SMO applications, it provides classes for core SMO functionalities. It contains utility classes, instance classes, enumerations, event- handler types, and different exception types. Microsoft. Sql. Server. Management. Smo. Agent It provides the classes to manage the SQL Server Agent, for example to manage Job, Alerts etc. Microsoft. Sql. Server. Management. Smo. Broker. It provides classes to manage Service Broker components using SMO. Microsoft. Sql. Server. Management. Smo. Wmi It provides classes that represent the SQL Server Windows Management Instrumentation (WMI). With these classes you can start, stop and pause the services of SQL Server, change the protocols and network libraries etc. C# Code Block 1. Here I am using the Server instance object to connect to a SQL Server. You can specify authentication mode by setting the Login. Secure property. If you set it to "true", windows authentication will be used or if you set it to "false" SQL Server authentication will be used. With Login and Password properties you can specify the SQL Server login name and password to be used when connecting to a SQL Server instance when using SQL Server authentication. C# Code Block 1 - Connecting to server Server my. Server = new Server(@"ARSHADALI\SQL2. Using windows authentication. Server. Connection. Context. Login. Secure = true. Server. Connection. Context. Connect(). Server. Connection. Context. Is. Open). Server. Connection. Context. Disconnect(). Using SQL Server authentication. Server. Connection. Context. Login. Secure = false. Server. Connection. Context. Login = "SQLLogin". Server. Connection. Context. Password = "entry@2. C# Code Block 2. Once a connection has been established to the server, I am enumerating through the database collection to list all the database on the connected server. Then I am using another instance class Database which represents the Adventure. Works database. Next I am enumerating through the table, stored procedure and user- defined function collections of this database instance to list all these objects. Finally I am using the Table instance class which represents the Employee table in the Adventure. Works database to enumerate and list all properties and corresponding values. C# Code Block 2 - retrieving databases, tables, SPs, UDFs and Properties//List down all the databases on the server. Database my. Database in my. Server. Databases). Console. Write. Line(my. Database. Name). Database my. Adventure. Works = my. Server. Databases["Adventure. Works"]. //List down all the tables of Adventure. Works. foreach (Table my. Table in my. Adventure. Works. Tables). Console. Write. Line(my. Table. Name). //List down all the stored procedures of Adventure. Works. foreach (Stored. Procedure my. Stored. Procedure in my. Adventure. Works. Stored. Procedures). Console. Write. Line(my. Stored. Procedure. Name). //List down all the user- defined function of Adventure. Works. foreach (User. Defined. Function my. User. Defined. Function in my. Adventure. Works. User. Defined. Functions). Console. Write. Line(my. User. Defined. Function. Name). //List down all the properties and its values of [Human. Resources].[Employee] table. Property my. Table. Property in my. Server. Databases["Adventure. Works"]. Tables["Employee". Human. Resources"]. Properties). Console. Write. Line(my. Table. Property. Name + " : " + my. Table. Property. Value). C# Code Block 3. This demonstrates the usage of SMO to perform DDL operations. First I am checking the existence of a database, if it exists dropping it and then creating it. Next I am creating a Table instance object, then creating Column instance objects and adding it to the created Table object. With each Column object I am setting some property values. Finally I am creating an Index instance object to create a primary key on the table and at the end I am calling the create method on the Table object to create the table. C# Code Block 3 - Creating a database and table//Drop the database if it exists. Server. Databases["My. New. Database"] != null). Server. Databases["My. New. Database"]. Drop(). Create database called, "My. New. Database". Database my. Database = new Database(my. Server, "My. New. Database"). my. Database. Create(). //Create a table instance. Table my. Emp. Table = new Table(my. Database, "My. Emp. Table"). //Add [Emp. ID] column to created table instance. Column emp. ID = new Column(my. Emp. Table, "Emp. ID", Data. Type. Int). ID. Identity = true. Emp. Table. Columns. Microsoft SQL Server: Programming Forum. Join your peers on the Internet's largest technical computer professional community. It's easy to join and it's free. Here's Why Members Love Tek- Tips Forums: Register now while it's still free! Already a member? Close this window and log in. Join Us Close.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |