SMO Capture Mode - Capture SQL Scripts With SQL Server Management Objects - SMO Tutorials
by David Hayden ( ASP.NET Developer )
I am starting to think that Microsoft thought of everything when creating SQL Server Management Objects. Not only can you create a table in SQL Server using SMO or create a stored procedure in SQL Server using SMO, for example, but you can also capture the scripts / commands that SMO is sending to SQL Server under the covers. In fact, not only does SMO capture the SQL Scripts so you could play them back later, but you can also set it up so that SQL Server Management Objects doesn't actually execute the commands to SQL Server, but just gives you the scripts.
I didn't get a chance to talk about this SMO Capture Mode during my SQL Server Management Objects Presentation at the Orlando CodeCamp, but certainly I will include this in any future SMO Presentations.
SQL Server Managment Objects Works For SQL Server 7, SQL Server 2000, and SQL Server 2005
In the past I have received a number of questions as to whether SMO will only work with SQL Server 2005. The answer is no. SMO works with all versions of SQL Server since SQL Server 7. Therefore, you can use SMO with SQL Server 7, SQL Server 2000, and SQL Server 2005.
I have personally only used SMO with SQL Server 2000 and 2005, but the documentation clearly states it will work with SQL Server 7.
Create a SQL Server Table Using SMO and C#
Before we can talk about SMO Capture Mode, we need to first create a small SMO Application using C# that creates a table in our local instance of SQL Server.
Here is the SMO Application:
// Local Instance of SQL Server
Server server = new Server();
// Get Test Database
Database db = server.Databases["Test"];
// Creating New Table, called Contacts
Table contacts = new Table(db, "Contacts");
// Create ID Column
Column id = new Column(contacts, "ID",
DataType.UniqueIdentifier);
id.AddDefaultConstraint("DF_Contacts_ID")
.Text = "newid()";
// Create FirstName and LastName Columns
Column firstName = new Column(contacts,
"FirstName", DataType.NVarChar(50));
Column lastName = new Column(contacts,
"LastName", DataType.NVarChar(50));
// Add Columns to Table
contacts.Columns.Add(id);
contacts.Columns.Add(firstName);
contacts.Columns.Add(lastName);
// Create Table
contacts.Create();
If you run the above code, it will will create a new table in SQL Server as you would expect.
Specifying SMO Capture Modes
The Server Object in SMO has a ConnectionContext Object that has a property called SqlExecutionModes that governs how SMO executes in the background. SqlExecutionModes is an enum that can be one of three options:
- CaptureSql - Provides scripts but doesn't execute them on the server
- ExecuteAndCaptureSql - Execute the scripts on the server and capture them
- ExecuteSql - Execute scripts on server, but don't capture them
You can see the coolness here. If I set SqlExecutionModes to CaptureSql, this will allow me to generate the scripts but not do anything on the server. Hence my table that I created above will only exist in memory, but I can play with it like it actually exists on the server.
And, of course, ExecuteAndCaptureSql and ExecuteSql complete the full range of options, allowing you to both execute and capture the SQL or just execute the SQL on the server.
Capture SQL Scripts using SMO
Let's change the code above ever so slightly to only give us the SQL Scripts. Changes in red:
// Local Instance of SQL Server
Server server = new Server();
// Capture Scripts Only
server.ConnectionContext.SqlExecutionModes
= SqlExecutionModes.CaptureSql;
// Get Test Database
Database db = server.Databases["Test"];
// Creating New Table, called Contacts
Table contacts = new Table(db, "Contacts");
// Create ID Column
Column id = new Column(contacts,
"ID", DataType.UniqueIdentifier);
id.AddDefaultConstraint("DF_Contacts_ID")
.Text = "newid()";
// Create FirstName and LastName Columns
Column firstName = new Column(contacts,
"FirstName", DataType.NVarChar(50));
Column lastName = new Column(contacts,
"LastName", DataType.NVarChar(50));
// Add Columns to Table
contacts.Columns.Add(id);
contacts.Columns.Add(firstName);
contacts.Columns.Add(lastName);
// Create Table - Well Not Really!
contacts.Create();
// Get Script
StringCollection commands =
server.ConnectionContext.CapturedSql.Text;
Here is the value of CapturedSql.Text:
USE [Test]
CREATE TABLE [dbo].[Contacts]
(
[ID] [uniqueidentifier] CONSTRAINT
[DF_Contacts_ID] DEFAULT newid(),
[FirstName] [nvarchar](50),
[LastName] [nvarchar](50)
)
Since I have set the SqlExecutionModes to only CaptureSql, the table in this case was never actually created in the database.
Conclusion
SQL Server Management Objects has a lot of power. In this SMO Tutorial I have shown how to capture SQL Scripts generated by SMO using SMO Capture Mode.
Source: David Hayden ( ASP.NET Developer )
SMO Tutorials and Examples