This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

SQL/MTS: Automating the Creation of COM+ Components for a Database Schema

Aleksandr Mikunov

This article assumes you're familiar with SQL, C++, MTS/COM+ Level of Difficulty   1   2   3  Code for this article: Mikunov0700.exe (32KB) Using Microsoft Windows DNA architecture as a guideline, it's possible to create scalable multitier database applications with COM+ and Microsoft Transaction Services. In fact, you can use existing table definitions to automatically build the MTS/COM+ business logic layer and data access components for your application.       This article will walk you through the development of database transactions by mapping the transactions to automatically generated MTS/COM+ components. This technique can greatly simplify the task of creating components for a large project.

icrosoft® Windows® DNA provides a guideline for building three-tier, COM-based applications that use Microsoft Transaction Services (MTS) as an execution environment to create a scalable middle tier. One of the most important functions of MTS is to act as a transaction monitor.       The MTS/COM+ environment uses the Microsoft Distributed Transaction Coordinator (DTC) as a transaction manager and transaction coordinator. DTC is a service that Microsoft SQL Serverâ„¢ provides to coordinate transactions that are distributed across two or more databases, message queues, file systems, or other transaction-protected resource managers. DTC implements "ACID" properties (atomicity, consistency, isolation, and durability) by means of OLE Transaction interfaces, and provides an interface to applications that update distributed data. MTS calls the DtcGetTransactionManager function to obtain the ITransactionDispenser interface and issue ITransactionDispenser::BeginTransaction, ITransaction::Commit, and ITransaction::Abort.       From that point of view, MTS is just a client of the DTC. MTS uses the two-phase commit protocol to decide whether to commit or abort a transaction. SQL Server provides a description of the transactions it wants to execute. An MTS-based description of database transactions looks very natural if you take into account the original SQL Server description. In effect, you are utilizing the entity-relationship description of your business rulesâ€"ER-diagrams, Data Definition Language (DDL) descriptions, and metadata information.       To illustrate this, let's start from the simplest database example. I'll first create a database transaction. The SQL description of it will then be translated to an MTS-style transaction. In this situation, the MTS-based description can be thought of as another language to describe the same activity. If you don't already have an understanding of the basic concepts of databases, such as DDL, ER-diagrams, and transactions, plus knowledge of the Windows DNA architecture and MTS, you should look into these a bit before you continue with this article. Mapping Transactions to MTS/COM+ Components       Consider the following scenario. Assume that I have tables A, B, and C as shown in Figure 1. Figure 1Entity Relationship Diagram There are two one-to-many relationships between table A and table B and between table C and table B. Using SQL Server 6.5/7.0 Transact-SQL, these entity relationships are described in DDL like so:

create table A (i int identity PRIMARY KEY, f1 int)
create table C (i int identity PRIMARY KEY, f2 int)
create table B (i int identity, f3 int REFERENCES A(i),
    f4 int REFERENCES C(i))
go

Note that table B contains two foreign keys, f3 and f4, that reference primary keys in tables A and C.
      Now let's create a database transaction that adds a new value to table B. You could easily write a stored procedure (sp_AddNew, for example) that inserts new values into tables A, C, and B. By wrapping the insert statements in a transaction, each insert can be observed before the work is committed. If the transaction is terminated, the stored procedure ensures that all those modifications are rolled back. Otherwise, the stored procedure ensures that all modifications within the transaction are committed.
       Figure 2 shows the implementation of sp_AddNew. As you can see, you can also use the standard SQL Server variable @@IDENTITY to get a new ID for a given table.
      Let's assume that sp_AddNew corresponds to the business transaction T, and that T is implemented by means of MTS/COM+ components. Using sp_AddNew, it's possible to create a prototype of an MTS transaction in Visual C++® or Visual Basic®.
      To do this I need to create a business object, ComponentT. The first object enlisted in the transaction becomes the root of the transaction, and this transaction will exist until the root object issues either IObjectContext::SetComplete or IObjectContext::SetAbort. Thus, it must have the transactional attribute Requires a Transaction or Requires a New Transaction. The component has a method (T_Add) that actually implements the functionality of sp_AddNew. According to the Windows DNA architecture, the business component controls transactional integrity and security (based on roles and packages) and should not talk directly to the database. The proper way to have database access is to use a data access layer (see Figure 3).

Figure 3Using the Data Access Layer

      The business object, ComponentT, should implement the following general steps:

1. Start a new transaction (this is automatic when the appropriate transactional attribute is set).
2. Check security attributes to make sure access is set correctly.
3. Instantiate the data object A to insert into table A.
4. Instantiate the data object C to insert into table C.
5. Instantiate the data object B to insert into table B.
6. Commit or roll back the transaction.

      Now look at the COM component in Figure 4. (The highlighted lines are most important.) Note that the MTS context is the same for components A, B, and C, because ComponentT uses:

pObjectContext->CreateInstance()

to instantiate subordinate objects. The data access components, which correspond to tables A, B, and C, can follow the code outline shown in Figure 5.
      A subordinate component obtains a reference to the ObjectContext that's associated with the current MTS object and uses it to vote to commit or roll back the transaction. In other words, ComponentA can be thought of as the implementation of the "Insert into table A" SQL statement. The only difference is the environment with which it works. The other two data access components, shown in Figure 6, look very similar to ComponentA. The transactional attribute of the data access components should be set to either Requires a Transaction or Supports Transactions. Otherwise, the components will not be enlisted in the caller's transaction.
       Figure 7 shows the implementation of the stored procedure sp_AddNew in Visual Basic. The data access components can be seen in Figure 8.
      Now that I've designed all the parts, I can put them together to create an MTS-compliant business transaction. This involves the following five steps:

1. Specify the tables used within this transaction (A, B, and C).
2. Create a SQL (Transact-SQL) description of the transaction (sp_AddNew).
3. Map the SQL description to the business component (ComponentT).
4. Create data access components for each participating table (ComponentA, ComponentB, and ComponentC).
5. Set the transactional attributes. (This sample app uses either Requires a New Transaction or Requires a Transaction for business components and Requires a Transaction or Supports Transactions for data access components.)

      This application may contain high-level business objects that encapsulate real-world business operations and may use security checking as well as other business and data access components. To simplify this task, I assume that every business component uses only the services of other data access components and the business transactions modify a group of tables (via the data access components) by means of either insert, update, or delete operations in SQL. Note that this assumption simplifies the second and third steps. That means that you can implement the same generic scenario by mapping database transactions to the MTS components as shown in Figure 9.
      This new code in Figure 9 is similar to the previous version you saw. This is the same code that you would write to implement other operations like update or delete. The major difference between insert and delete operations is that the order of delete statements is usually reversed from the order of insert statements.
      Obviously, by using this technique you might use the original description of business rules in terms of entity-relationship or DDL descriptions. The most generic approach is to use the special server tables (known as system tables), which contain meta information for every database object. These tables store database-level system information for each database.
      For my purposes I am going to use four tables: sysobjects, syscolumns, sysindexes, and sysforeignkeys. The sysobjects table contains one row for each database object created within a database. Sysindexes contains one row for each index and table in the database. Syscolumns contains one row for each column in every table or view, and a row for each parameter in a stored procedure. The sysforeignkeys table contains information regarding the FOREIGN KEY constraints in table definitions.
      You could create a class generator utility that uses this information to automatically create business and data access components. Sysobjects, sysforeignkeys and sysindexes are used to get the ER- descriptions, PRIMARY KEY and FOREIGN KEY constraints, IDENTITY properties, and index information from the database. Syscolumns is used to get the list of stored procedure parameters, types, lengths of columns, and so on. Access to this information is the most important reason to write the utility in Transact-SQL as a set of stored procedures. Although I won't get into the details of doing this until later when I discuss creating the data access objects, it's important to understand the relationships between SQL Server-specific descriptions in Transact-SQL and MTS-specific descriptions written in Visual Basic.

Creating Data Access Components

      My data access layer uses the ADO programming model. There are several ways to do this: the Connection Execute method, Command objects, or Recordset objects. I used the Island Hopper sample from Microsoft to implement a similar style of data access using the Command object and the dynamic creation of parameterized SQL statements. Figure 10 shows the implementation of ComponentA (I have highlighted the lines that depend on the specific description of table A).
      This example assumes you already have a new ID value and that table A's IDENTITY property is turned off. That assumption simplifies the structure of the generated code, but it could actually be eliminated by using an alternative approach, as demonstrated by the code in Figure 11.
      The stored procedures in this sample contain the following code:

Create procedure sp_A_Add
@f1 int,
@ID int OUT â€" a new ID
As
 insert into A values(@f1)
 If @@ERROR  <> 0 GoTo ErrorHandler
 select @ID = @@IDENTITY
 Return (0)
ErrorHandler:
 Return  (-13)
go

To generate a data access object, classgen analyzes the system tables for a given user-defined table (Table A) and creates a temporary table that contains the necessary information for the automatic creation of the component:

• table name (A)
• name of the IDENTITY column (i)
• names of the PRIMARY KEY (i) and FOREIGN KEY (NULL) columns
• column names (i, f1)
• column types (int, int)
• lengths of columns (4 bytes, 4 bytes)
• nullable (i = No, f1 = Yes)

      After this information is gathered, the next step is to interpret that information to generate an MTS-style component. All you need to do is modify the highlighted lines in the templates shown in Figures 12 and 13. The table name is used to generate method names (A_Add, A_Update, and A_Delete), stored procedure names (sp_A_Add, sp_A_Update, and sp_A_Delete), and SQL statements (INSERT INTO A VALUES, UPDATE A SET, and DELETE A.). The utility fetches column names and their descriptions to complete the creation of SQL statements and generate the list of I/O and ADO parameters. For the table called SomeTable, defined with the following SQL script:

CREATE  SomeTable (Col1 Col1type IDENTITY PRIMARY KEY, 
Col2 Col2type, ..., ColN ColNtype)

you would end up with the data component shown in Figure 12, and the Visual Basic-based stored procedure template shown in Figure 13. Finally, the template for the stored procedure itself can be seen in Figure 14.

Implementing the Class Generator

      The class generator utility (classgen) is a set of stored procedures that consists of the following:

• sp_drop_ALL_temp
• sp_make_classfile_ALL
• sp_make_DAL_header
• sp_make_DAL_body
• sp_dal_gen
• sp_classgen
• sp_classgen_ALL

      Sp_drop_ALL_temp and sp_make_classfile_ALL are helper stored procedures. Sp_drop_ALL_temp deletes the helper temporary tables that are being used by the generation process. sp_make_ classfile_ALL is used to make class files.
      To run classgen, type the following SQL statements:

use someDATABASENAME 
go
exec sp_classgen_all
go

For each table in a given database, this stored procedure calls:

sp_classgen @tableName

Sp_classgen prepares meta information for a given table using the temporary table #DAL:

create table #DAL (
TName nvarchar(30), â€" table name
IDName nvarchar(30), â€" IDENTITY column name
PKCOL_NAME nvarchar(30), â€" Primary key column
CLUSTERCOL_NAME nvarchar(30), â€" Clustered index column 
colname nvarchar(30), â€" column name
typeName nvarchar(30), â€" type of the column
length int, â€" length of the column
nullable tinyint) â€" is nullable? 0-No, 1-Yes

Sp_classgen then calls the following:

sp_dal_gen @tableName

This stored procedure creates a temporary table using the table name as follows:

EXEC ('CREATE TABLE ' + '##tmp_' + @tablename + 
    '(L nVARCHAR(1255))')

This temporary table is used to store the component's code.
      Sp_dal_gen then calls two more stored procedures:

exec sp_make_DAL_header @tablename
exec sp_make_DAL_body @tablename

The first call creates a header of the class (standard comments and parameter description), and the second one generates a body for that class. Both of them use the

'##tmp_' + @tablename 

table to store the information about the class.
      Finally, the other stored procedure, sp_make_classfile_ALL, is used to upload (with the bulk copy bcp utility) the class information from the

'##tmp_' + @tablename 

From the July 2000 issue of MSDN Magazine.