Enhancing the SMO Backup Application
Our application backs up the database, but it has two obvious shortcomings: the database name is hardcoded and the backup isn't verified. It's easy to improve the application to solve these two problems, as you can see in this example when we provide the user with a list of database names to choose from, and we verify the backup by instantiating a Restore object and calling its Verify method.
Now that you've seen the basics of building an application with Visual Studio 2005, let's create an application that has a Windows UI. Windows applications require considerably more code than do console applications, so we start by using a Windows Application template to create a new project. Visual Studio 2005 automatically adds the additional code that the project needs. Unlike earlier Visual Studio releases, Visual Studio 2005 stores autogenerated code in a file separate from user-written code. In Visual Basic .NET, this file is named formname.Designer.vb. In C#, the file is named formname.Designer.cs. This file is hidden by default; to see the file in Solution Explorer, you must select the Show All Files option. You don't manually change the code in the Designer file, although you do change the code indirectly when you add controls to a form. User-written code belongs in either the formname.vb or formname.cs file. If you keep the default form name of Form1, the file for your code will be named Form1.vb or Form1.cs.
In Visual Studio 2005, go to the menu bar and choose File, New, Project. Select a Windows Application template for the language you prefer. Type VerifiedBackup for the project name. The Visual Designer appears with a blank Form object. Using the Toolbox, drag a Button control and a ListBox control onto the Form. Using the Properties window, name the ListBox control Databases and the Button control DoBackup with a Text property of Backup. As in the previous example, add a reference to Microsoft.SqlServer.Smo. You must also add a reference to Microsoft.SqlServer.ConnectionInfo. Double-click the Button control to create an event handler for the button and also to switch to Code View. Your screen should now look similar to Figure 4.
Now we want to make the backup application more user friendly by giving the user a list of database names to choose from. Enhance the SqlBackup application by adding code to a Form Load event handler that will populate the ListBox control with the names of the databases on the server. To back up the database, the user can simply select a database and click Backup. Finally, to verify the backup, add a Restore object and use a MessageBox control to provide backup verification status to the user. Listing 2 and Web Listing 2 show you how.
As with the backup application example, you can replace all the code in Form1.vb with the Listing 2's code or replace the code in Form1.cs with the code in Web Listing 2. First, you must double-click the Form control and the Button control to register your event handlers with the Visual Designer code file. Be sure to use the Form, ListBox, and Button control names Form1, Databases, and DoBackup to match this article's code examples. Keep in mind that C# is a case-sensitive language, which means that doBackup and DoBackup represent different objects.
To make coding even easier and to avoid errors, you may want to download and work with the complete solution instead. After downloading the code, double-click the vbproj or csproj file to load the project into Visual Studio 2005. You might see error messages such as "The automatically saved settings file '%user_documents%\visualstudio\settings\visualstudio\8.0\currentsettings.vssettings' cannot be found." Don't worry; just click OK. You can ignore these messages because Visual Studio creates the settings when you load the project into Visual Studio.
Now you're ready to run the enhanced backup application. Press F5 or click the Start icon (green triangle). Select a database, then click Backup. As Figure 5 shows, when the backup is complete, a message box appears, showing the backup status. Now let's move on to the third example, creating a database and a table using SMO.
Creating a Database and a Table Using SMO
For many years, database developers have used program code to create tables indirectly. The general approach is to create a string consisting of a T-SQL CREATE TABLE statement that the code sends to the database server to be executed. SMO offers a cleaner, better, object-oriented way of creating database objects directly through code without using T-SQL as an intermediary.
Listing 3 and Web Listing 3 show you how to create both a new database and a table completely in code without writing any T-SQL statements. We use a Database object to create the database and a Table object to create the table. To keep the coding simple, choose the Console Application template and name it CreateTable. (You can add the code to other templates, depending on the type of UI you want.) Next, add references to both Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo.
Now run the application. Use SQL Server Management Studio to examine the database and table you've created. (If SQL Server Management Studio is already running, you may need to first click the Refresh icon in Object Explorer.)
Take Advantage of SMO
As you've seen in these examples, coding using SMO and Visual Studio 2005 is fast and easy. SMO and Visual Studio 2005 offer DBAs and developers tremendous flexibility in developing powerful database applications while minimizing complexity and development time. With better performance, better scalability, and more features than SQL-DMO, as well as backward compatibility to SQL Server 2000 and SQL Server 7.0, SMO in SQL Server 2005 is something to look forward to.