Microsoft.SqlServer.Smo, where have you been all my life?
I have had a need, for quite some time, to have a comprehensive SQL Server tool where I could execute scripts, import/export tables, and generally just run a few queries. In the past, I have been using the free SQL Management Studio Express edition, which works just fine. The problem with this utility, is using it at customer installations. The install requires administrator privileges, which work for most places, but make it impossible to install at others. So, the need to have a small utility that I can run along side my application is pretty great.
I started out just using the SQLClient name space. While it worked well for simple queries, trying to run a comprehensive database update script just killed it. While it would probably work, I would have to parse the script first, stripping out all the GO statements and breaking them up into separate scripts. That worked fine until I ran into the first transaction… Not good either, apparently.
Feeling dead in the water, I did some searching and came across a name space I had not known existed. It’s Microsoft.SqlServer.Smo. It is the replacement for Nmo and has quite a few really nice features.
One such feature is the ability to use .ExecuteNonQuery() without having to break apart or parse existing update scripts, including ones with built in transaction support!
It’s pretty simple to use and combined with the SqlClient name space, I was able to create a pretty comprehensive solution for my needs.
Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Common
Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection()) Dim srv As New Server(conn) dropdown.Items.Clear() For i As Integer = 0 To srv.Databases.Count - 1 dropdown.Items.Add(srv.Databases.Item(i).Name) Next conn.Disconnect()
That’s as simple as it gets to iterate through all the databases on the server. The SQLAuthInfo is a class I designed that will just create a SqlConnection object based on preset values.
Need to execute a script? No problem…
Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection()) Dim srv As New Server(conn) Dim cmd As String = ScriptMemoEdit.Text srv.ConnectionContext.ExecuteNonQuery(cmd, ExecutionTypes.ContinueOnError) conn.Disconnect()
It didn’t take me long to use many of the features… For import and export, I just used a select query to get all the data from one table and saved it into a dataset. Then I used the dataset’s WriteXML and WriteXMLSchema functions to save it to files. Then with the import function, I just read in the schema and data into a dataset and used the SqlBulkCopy object to save it to the target database table. Pretty easy stuff. This also gives me some new options for our server installation program I need to fix (i.e. create).