Advanced Basics: Enterprise Services, SQL Script Editing

Article
10/22/2019

Advanced Basics

Enterprise Services, SQL Script Editing

Ken Spencer

Code download available at:AdvancedBasics0310.exe(150 KB)

Q I have lots of experience developing ASP applications that use Visual J++® components wrapped in COM running in Microsoft® Transaction Services (MTS). I am currently developing an application with the Microsoft .NET Framework and have written several classes that perform the bulk of my data access. I then use these classes in my ASPX pages. This application will have as many as 10,000 concurrent users.

When I create component assemblies do I need to use MTS and COM+ at all or does the common language runtime (CLR) take care of these classes? COM+ and MTS handle the transactions and object pooling, but can I really use components without COM+ and MTS?

A When you create an application that uses classes, should you put those assemblies in MTS or COM+? First let's define a few things for clarity. MTS provided services to COM components on Windows NT® 4.0. In Windows® 2000, MTS and many other services were exposed as COM+ 1.0 components. With the advent of the CLR, the same services were exposed to .NET Framework components via System.EnterpriseServices. The purpose of COM+ and System.EnterpriseServices is to provide services to COM components and .NET Framework components. These services include transactions, the QueueComponent, RoleBased Security, Object Pooling, Hosting, Availability, packaging, and a whole slew of others. More information can be found at COM+ Services.

Now, back to the general question. Should you use Enterprise Services at all to host your assemblies? This is a great question which, at first, seems quite simple. If you need any of the aforementioned services, then you need to use System.EnterpriseServices.

For all of the code that just reads information from a database or handles business rules and does not do any updates, you probably don't need to avail yourself of Enterprise Services at all. If you want to take advantage of things like object pooling, which Enterprise Services offers, you can use it for those features. However, you would need to carefully test your application's performance to see if it runs faster in Enterprise Services.

Second, you could use Enterprise Services to handle your database updates to take advantage of its transactional features. The question is, do you really need it? If I am handling transactions across two or more databases in a single transaction, then my rule of thumb is to use Enterprise Services for those classes. If the application only updates a single database, I usually do not use Enterprise Services, since ADO.NET handles this nicely with SQL Server™ and other databases, as long as the provider supports it. The Data Access Application Block from Microsoft (see Microsoft Application Blocks for .NET) supports transactions without using Enterprise Services. This makes it easy to handle transactions without using Enterprise Services . However, if you do plan to use multiple databases or use other transacted resources (such as MSMQ) in your application, then Enterprise Services provides an easy way to manage distributed transactions.

Another consideration is the ServicedComponent class in the .NET Framework. To use Enterprise Services, you must derive your component from this class. The following short sample from the MSDN® docs shows how simple this is to set up and how attributes can be applied to your class:

Imports System.EnterpriseServices <Transaction(TransactionOption.Required)> Public Class Account Inherits ServicedComponent <AutoComplete()> Shared Sub Main() End Sub End Class

This code is nice and simple, but if you designed your application with a class hierarchy containing multiple levels of inheritance, this presents a potential problem. In this case, your base class must derive from ServicedComponent.

System.EnterpriseService is a managed namespace of these services and is provided by COM+, so if you are in the managed world and want to use these services, you would use System.EnterpriseServices to get at these services and/or express them as attributes like any other class in the .NET Framework.

Q I'm just learning to use Visual Studio® .NET 2003 and I need to execute a SQL statement that exists in a file without creating a solution/project/database reference. I want to perform file-based SQL script editing without being persistently connected to a SQL Server (like Query Analyzer), but still want to have the ability to execute statements when needed. I know there are third-party tools that do this, but does Microsoft have one?

A You don't need to buy a tool in order to execute SQL statements. Of course you can use Query Analyzer, as you mentioned. You can also use the third-party tool ISQL from the command line, but you must install it first. Then there is Visual Studio .NET, but that requires opening the IDE, creating a project, and then opening Query Builder. For a quick and easy solution, I created a simple SQL tool which you can modify.

Let's start with a look at the interface for the tool (see Figure 1). It's a Windows Form MDI application. As you can see from the interface, the tool executes queries in a manner similar to Query Analyzer. You can type in a query, select a connection string from the list, and then click Execute. The results show up in the grid below the query. Since it is an MDI application, you can open many windows with different queries.

Figure 1 Simple SQL Tool

Figure 1** Simple SQL Tool **

As you requested, the tool only connects to the database to execute the query; the connection is then closed. This is because the tool uses a DataSet and data adapter to perform the work.

Now, let's walk through the tool. First, I created a new Windows-based application in Visual Studio .NET. Then I set the isMDIContainer property to True. This makes the first form the MDI parent. I then added a MainMenu and set the top-level menus to the following:

Name	Text
mnuitmWindow	&Window
mnuitmExit	&Exit
mnuitmNew	&New Window
mnuitmOptions	&Options

To mnuitmOptions I added a submenu named mnuitmConnections with a Text property of "Connections". The mnuitmWindow menu has the MDIList property set to True.

Next, I added a second Windows Form to the project and named it frmSQLWindow. The two buttons on the left are named cmdOpen and cmdSave, respectively, and use icons that are included with Visual Studio .NET. The Execute button is named cmdExecute. The combo list is named cboConnection and holds the list of connections that the tool uses.

The DataSet radio button is named rdoDataSet and has the Checked property set to True. The No Return radio button is named rdoExecuteOnly. The Textbox control showing the number 1 is named txtIterate. The control in the lower half of the form is a DataGrid named dgrdOutput, and the Dock property is set to Bottom. The Textbox above it is named txtSQL and its Dock property is also set to Bottom.

Now, let's wire up this tool. Add the code shown in Figure 2 and Figure 3 to the project. These two modules handle the data access and the loading of the data connections from an XML file. Open Form1 and set the Click event for the Exit menu to Application.Exit. Set the code for mnuitmNew to:

Dim frmSQL As New frmSQLWindow frmSQL.MdiParent = Me frmSQL.Show()

This will create a new child form each time you click the menu.

Figure 3 Load Data Connections

Imports System.Data.SqlClient Module modDB Friend frnConnectionString As String Private pvtDataSet As DataSet Private pvtConnection As SqlConnection Private pvtCommand As SqlCommand Private pvtSQLDataAdapter As SqlDataAdapter Friend WithEvents frnCurrentConnection As DataSet Friend Function runSQLDataSet(ByVal SQL As String) _ As DataSet Try pvtConnection = New _ SqlConnection(frnConnectionString) pvtCommand = New SqlCommand(SQL, pvtConnection) pvtDataSet = New DataSet pvtSQLDataAdapter = New SqlDataAdapter(pvtCommand) pvtSQLDataAdapter.Fill(pvtDataSet) Return pvtDataSet Catch ExceptionObject As Exception Throw New Exception(ExceptionObject.Message) Finally pvtConnection.Close() End Try End Function Friend Sub runSQL(ByVal SQL As String) Try pvtConnection = New _ SqlConnection(frnConnectionString) pvtCommand = New SqlCommand(SQL, pvtConnection) pvtCommand.ExecuteNonQuery() Catch ExceptionObject As Exception Throw New Exception(ExceptionObject.Message) Finally pvtConnection.Close() End Try End Sub End Module

Figure 2 Data Access

Imports System.IO Module modGeneral Friend sFile As String Friend Function LoadConnectionstrings() As DataSet Dim ds As New DataSet sFile = Application.ExecutablePath() sFile = Replace(sFile, "\SQLExecutor.exe", "") sFile &= "\ExecuteSQLConnections.xml" If File.Exists(sFile) Then ds.ReadXml(sFile) End If Return ds End Function End Module

Now add this code to Form_Load:

frnCurrentConnection = New DataSet frnCurrentConnection = LoadConnectionstrings()

Next, add the following code to mnuitmConnections_Click. This will fire up frmConnections, which allows you to maintain the connection list:

Dim frm As New frmConnections If frm.ShowDialog() = DialogResult.OK Then frnCurrentConnection.Clear() frnCurrentConnection = LoadConnectionstrings() End If

Open frmSQLWindow. Add the code in Figure 4 to cmdOpen_ Click to allow you to open a file and load it into the texbox. Then add the following code to cmdSave to save the SQL query:

Dim lclwriter As StreamWriter sveSQLFile.InitialDirectory = "c:\sql" sveSQLFile.Filter = "SQL files (*.sql)|*.txt|All files (*.*)|*.*" sveSQLFile.FilterIndex = 2 sveSQLFile.RestoreDirectory = True If sveSQLFile.ShowDialog = DialogResult.OK Then lclwriter = New StreamWriter(sveSQLFile.FileName) lclwriter.WriteLine(txtSQL.Text) lclwriter.Close() End If

Figure 4 Open and Load File

Dim lclStream As StreamReader opnSQLFile.InitialDirectory = "c:\sql" opnSQLFile.Filter = "SQL files (*.sql)|*.txt|All files (*.*)|*.*" opnSQLFile.FilterIndex = 2 opnSQLFile.RestoreDirectory = True If opnSQLFile.ShowDialog() = DialogResult.OK Then lclStream = New StreamReader(opnSQLFile.FileName) If Not (lclStream Is Nothing) Then lclStream.BaseStream.Seek(0, SeekOrigin.Begin) txtSQL.Text = lclStream.ReadToEnd lclStream.Close() End If End If

Now add the code from Figure 5 to cmdExecute. This code executes the SQL query you enter. Notice that you can iterate the SQL statement by changing the iterate value to a number greater than 1. This allows you to perform some testing of your SQL code with SQL Profiler.

Figure 5 Executing a SQL Query

Dim ds As DataSet Dim i, iIterate As Integer If IsNumeric(txtIterate.Text) Then iIterate = CInt(txtIterate.Text) Else iIterate = 1 End If If rdoDataSet.Checked Then frnConnectionString = cboConnection.Text For i = 1 To iIterate ds = runSQLDataSet(txtSQL.Text) Next If Not IsNothing(ds) Then dgrdOutput.DataSource = ds.Tables(0) End If Else frnConnectionString = cboConnection.Text For i = 1 To iIterate ds = runSQLDataSet(txtSQL.Text) Next End If

The only trick in frmConnections involves the code to save the connection strings in the list. Here is the code from cmdOK:

Dim ds As New DataSet ds.Merge(CType(dgrdConnections.DataSource, DataTable)) ds.WriteXml(sFile) Me.DialogResult = DialogResult.OK Me.Close()

The line that merges the DataSet has to use DataTable as the new type instead of DataSet, as suggested in the docs. This is because the application uses the DataTable, not the DataSet as the input into the DataSource.

Send your questions and comments for Ken to basics@microsoft.com.

Ken Spencer works for 32X Tech (https://www.32X.com), where he provides training, software development, and consulting services on Microsoft technologies.

Additional resources