Download the Source
A key benefit brought about through graphical user interfaces (GUIs), namely
Microsoft Windows, is the ability for applications to easily interact with each
other. When applications interact with other applications, the entire operating
system becomes more robust and easier to use. Furthermore, entire subsystems,
suites, and extended applications can be developed by utilizing applications
that interact with each other. Microsoft's solution to application interaction
prior to the introduction of .NET was called the Component Object Model (COM).
Most recent applications are created using object-oriented development environments
and expose themselves to other applications as an object. The design, or structure,
of the classes that make up an application is called the object model of the
application. Applications that are designed to be used in other applications
as tools or building blocks are called components. Hence Microsoft's standard
defining how components should be designed so that they can consistently interact
with other components is called the Component Object Model (COM).
COM components that were developed using different development environments
and programming languages faced a major obstacle when communicating between
themselves or attempting to pass data to each other. The difficulty in passing
data between components was attributed to differing data type definitions used
by various development environments and programming langauges. For instance,
a component developed using Microsoft Visual Basic 6 had difficulty communicating
with a component developed using Microsoft Visual C++ due to the differing data
type definitions supported. .NET resolved this issue by requiring that all .NET-compliant
development environments support a common set of data types known as the Common
Type System (CTS). By applications supporting a common set of data type definitions
through the CTS, inter-application communication becomes seamless and streamlined.
As mentioned, most applications expose themselves as an object and Microsoft SQL Server is no exception. The functionality of SQL Server is exposed through a COM component called SQLDMO (SQL Server Distributed Management Objects).
Accessing SQLDMO within Visual Studio .NET
COM components are designed differently than .NET assemblies and communicate
differently. However, it is very easy to communicate with a COM component from
within a .NET application; particularly if the .NET application is designed
using Visual Studio .NET. To access a COM component from within Visual Studio
.NET, in the Solution Explorer, right-click on the project where the code that
requires access to the COM component resides and select Add Reference. The Add
Reference dialog box is presented, as shown in FIGURE 1. Select the second tab
that is named COM. When Microsoft SQL Server is installed, the SQLDMO COM component
should be included in the listbox under the name of Microsoft SQLDMO Object
Library. Scroll to this item in the listbox, select it, click Select, and then
click OK to add a reference to the SQLDMO COM component to the project.
FIGURE 1: The Visual Studio .NET Add Reference dialog box with the SQLDMO Object
Library COM component selected.
Once a reference to the SQLDMO COM component has been added to a project,
the reference should appear under the References node for the project in the
Solution Explorer as shown in FIGURE 2.
FIGURE 2: The Visual Studio .NET Solution Explorer with a reference to the SQLDMO COM component added.
When a reference is created to a COM component, behind the scenes Visual Studio
.NET creates a wrapper assembly in the bin folder of the project that allows
code in the project to reference the COM component just as if it were another
.NET assembly. This wrapper assembly is called a Runtime Callable Wrapper (RCW)
or a .NET Interop assembly (see FIGURE 3). Interop assemblies (RCW) can also
be created ouside of Visual Studio .NET or manually. However, manual creation
of RCWs is beyond the scope of this article. In addition, wrappers may be created
that behave in just the opposite manner as an RCW where they allow COM components
to call .NET assemblies as though they were another COM component. These wrapper
components are called COM Callable Wrappers (CCW) and are also beyond the scope
of this article.
FIGURE 3: The COM Interop assembly created by Visual Studio .NET.
Writing .NET Code to Utilize SQLDMO
The process outlined in this article and the example shown below illustrate
how to utilize the SQLDMO component from an ASP.NET application to create a
simple stored procedure. Be aware that the same steps may be followed to utilize
any COM component from .NET and SQLDMO may be used to work with virtually all
database objects in Microsoft SQL Server.
The example illustrated below accesses the SQLDMO object from the code-behind
page of an ASP.NET Web Form. The example creates a stored procedure using a
script pulled from a textbox on the Web Form. The syntax entered into the textbox
is not validated as Transact-SQL in this example.
Referencing the SQLDMO Component Namespace
When the Interop assembly is created by Visual Studio .NET, by default, a
namespace with the same name as the component is created. As an industry convention
and to aid in less code that is more readable, reference the namespace at the
top of the code-behind file like this:
// Visual C# .NET Example.
// Reference custom namespaces.
using SQLDMO;
|
|
' Visual Basic .NET Example.
' NOTE: In Visual Basic .NET, namespaces may also be referenced using the project Properties dialog box under the Imports option.
' Reference custom namespaces.
Imports SQLDMO
|
Storing Connection Settings in the Web.config File
A common place to store configuration settings, and particularly database
connection settings, in .NET applications is in the app.config file (non-Web-based
applications) or the web.config (Web-based applications). More complex custom
settings may be stored in the app.config/web.config file, however, to store
simple settings, add an <appSettings></appSettings>
element just inside the <configuration>
opening tag. Inside the <appSettings>
element, add an <add /> self-closing tag.
Each <add /> tag must contain a key
and a value attribute as shown below:
<configuration>
<appSettings>
<!--Settings for database connection.-->
<add key="Server" value="Shannon-Laptop" />
<add key="UserName" value="sa" />
<add key="Password" value="password" />
</appSettings>
<system.web>....
|
The code below retrieves the settings from the web.config file. Be sure to reference the System.Configuration .NET Framework namespace when attempting to retrieve settings from the app.config or web.config file.
Declaring a Global Instance of the SQL Server Class
Just inside the main class definition for the code-behind page but just prior
to the Page_Load function member definition, data members
should be declared that represent each control used on the ASP.NET Web Form.
This is a prime location for declaring an instance of an object that should
be made available throughout the entire life cycle of the page:
///
/// Visual C# .NET Example.
///
/// Declare a global instance of the SQLDMO SQL Server object.
///
private SQLServer myServer = new SQLServer();
|
|
' Visual Basic .NET Example.
'
' Declare a global instance of the SQLDMO SQL Server object.
Private myServer As New SQLServer
|
Coding the Page_Load Function Member
The code included in the Page_Load function member
determines if this cycle of the function member is being processed in response
to a page postback. If a page postback is being processed, the SQL Server connection
settings are retrieved from the web.config file and are used to connect to SQL
Server. A new instance of a stored procedure is created and populated with the
text entered on the Web Form. The stored procedure is then added to the database
and the connection is closed. Feedback is writen to the page for the user regardless
of whether the stored procedure is successfully added to the database or an
error is encountered.
/// <summary>
///
/// Visual C# .NET Example.
///
/// This is the page load function member for the page.
/// </summary>
/// <param name="sender">Reference to the caller.</param>
/// <param name="e">Any event arguments.</param>
private void Page_Load(object sender, System.EventArgs e)
{
// If this is a postback, we'll assume that valid values have been entered and that we should
// create a stored procedure.
if (Page.IsPostBack)
{
// Get the connection string from the web.config file.
string serverName = ConfigurationSettings.AppSettings["Server"].ToString();
string userName = ConfigurationSettings.AppSettings["UserName"].ToString();
string password = ConfigurationSettings.AppSettings["Password"].ToString();
try
{
// Connect to SQL Server.
myServer.Connect(serverName, userName, password);
// Create a new stored procedure.
StoredProcedure storedProcedure = new StoredProcedure();
// Configure the stored procedure.
storedProcedure.Text = txtScript.Text;
// Add the stored procedure to the database.
myServer.Databases.Item("SqlJunkies20030915", "dbo").StoredProcedures.Add(storedProcedure);
// Give the user some feedback.
lblFeedback.Text = "Stored procedure created successfully!";
}
catch (Exception ex)
{
// Give the user some feedback.
lblFeedback.Text = "" + ex.Message + "";
}
finally
{
// Close the database connection.
myServer.DisConnect();
}
// Reset the textboxes.
txtScript.Text = "";
}
}
|
|
'
' Visual Basic .NET Example.
'
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' If this is a postback, we'll assume that valid values have been entered and that we should
' create a stored procedure.
If Page.IsPostBack Then
' Get the connection string from the web.config file.
Dim serverName As String = ConfigurationSettings.AppSettings("Server").ToString()
Dim userName As String = ConfigurationSettings.AppSettings("UserName").ToString()
Dim password As String = ConfigurationSettings.AppSettings("Password").ToString()
Try
' Connecto to SQL Server.
myServer.Connect(serverName, userName, password)
' Create a new stored procedure.
Dim storedProcedure As New storedProcedure
' Configure the stored procedure.
storedProcedure.Text = txtScript.Text
' Add the stored procedure to the database.
myServer.Databases.Item("SqlJunkies20030915", "dbo").StoredProcedures.Add(storedProcedure)
' Give the user some feedback.
lblFeedback.Text = "Stored procedure created successfully!"
Catch ex As Exception
' Give the user some feedback.
lblFeedback.Text = "" + ex.Message + ""
Finally
' Close the database connection.
myServer.DisConnect()
End Try
' Reset the textbox.
txtScript.Text = ""
End If
End Sub
|
The Example Web Form
FIGURE 4 illustrates the ASP .NET Web Form prior to submission.
FIGURE 4: The SQLDMO Stored Procedure ASP .NET Web Form prior to submission.
FIGURE 5 illustrates the ASP.NET Web Form after successful submission.
FIGURE 5: The SQLDMO Stored Procedure ASP.NET Web Form after successful submission.
FIGURE 6 illustrates the stored procedure that was created in the SQL Server
2000 Enterprise Manager.
FIGURE 6: The stored procedure that was successfully created as displayed in the SQL Server 2000 Enterprise Manager.
Finally, FIGURE 7 illustrates the ASP.NET Web Form when encountering a SQL
Server exception.
FIGURE 7: The SQLDMO Stored Procedure ASP.NET Web Form after encountering an
exception.
Conclusion
As mentioned above, the steps outlined in this article may be applied in order to work with any COM component from a .NET application. Furthermore, the simple SQLDMO example illustrated in this article barely scratches the surface of the possible uses for SQLDMO.
The full source code of the sample application (including both Visual C# .NET
and Visual Basic .NET examples) shown here can be downloaded from https://backup1.bullguard.com/backup/%7B56A62C2E-29CE-11D7-826C-0008C70899F3%7D/public/PersonalWeb/Source%20Code%20Downloads/20040315 - Using SQLDMO in ASP .NET.zip.