ADO.NET for the Java Programmer(转)

 

ASP.NET Technical Articles  

ADO.NET for the Java Programmer 
Microsoft Corporation
October 2003

Applies to
    Microsoft ASP.NET
    Microsoft ADO.NET

Summary: Learn how to migrate from Java-based data access technologies such as JDBC to database access interactions using ADO.NET. (22 printed pages)

Contents
Introduction
JDBC
JLCA Conversion of JDBC
Conversion of Sample Code
Conversion of www.codenotes.com
ADO.NET
Conclusion

Introduction
Data access technologies have been continuously evolving. At first, database vendors released proprietary C libraries that bound applications natively to their database management system. Since then, Microsoft? has released numerous data access tools: ODBC, DAO, OLE DB, RDO and ADO to name a few. ODBC was the first widely accepted technology to harness the flexibility of generic database programming. OLE DB and ADO were built to supply data access tools to the world of COM programmers. Microsoft ADO.NET, the most recent evolution in this family of technology, integrates relational database access into the Microsoft .NET environment.

On the Java side, JDBC was modeled after the success of ODBC. The idea was to define generic data access interfaces for developers; independent vendors would then provide competing implementations of these interfaces. Developers would then be free to plug-in whatever implementation suited their application best.

This article will first give a brief review of database access in Java. It will then discuss the changes the Java Language Conversion Assistant (JLCA) will make to database-accessing code during the conversion process, and the changes you will need to make to this code. Finally, it will provide an overview of the various ways to interact with a database using ADO.NET.

JDBC
JDBC encapsulates relational database access through various Java classes. All JDBC operations require a JDBC connection object that must be explicitly opened and closed. With a connection, a JDBC Statement object is normally used to specify the SQL operation to be performed. Both normal SQL statements, such as INSERT, UPDATE, and DELETE, and stored procedures can be executed using the appropriate subclass of the Statement interface. The results of the execution of a statement are usually stored in a JDBC record set object, which exposes the results of a query in a row-by-row format, similar to the format represented by a relational database. Listing 1.1 shows a simple example of using a Statement object to perform a simple SQL query, and an example of using a CallableStatement to call a stored procedure that updates the database.

Listing 1.1. Interacting with a database using JDBC

import java.sql.*;
public class SimpleQuery {
/* it is only necessary to have the Class.forName() call
to be executed once during the running of the application,
but it is included in each method in this example to
demonstrate that Class.forName() must be called before you can
create a connection
*/
// sql is any SQL statement, for example:
// SELECT * FROM books
public void executeQuery(String sql) {
   try {
      Class.forName("com.microsoft.jdbc.sqlserver.SqlServerDriver");

      Connection conn =
         DriverManager.getConnection("jdbc:microsoft:sqlserver:" +
         "//databaseName=codenotes", "sa", "");
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);

      long numCols = rs.getMetaData().getColumnCount();
      while (rs.next()) {
         for (int i=1; i             if (i>1)
               System.out.print(", ");

               System.out.print(rs.getString(i));
         } // for  System.out.println();
      } // while

      rs.close();
      stmt.close();
      conn.close();

      } catch (Exception e) {
         e.printStackTrace();
      } // try
   } // executeQuery

   public void execStoredProcExample() {
      try {
         Class.forName("com.microsoft.jdbc.sqlserver.SqlServerDriver");

         Connection conn =
            DriverManager.getConnection("jdbc:microsoft:sqlserver:" +
            "//databaseName=codenotes","sa", "");
         CallableStatement cStmt =
            conn.prepareCall("{call sp_addFeaturedArticle(?)}");
         cStmt.setString(1, "AS010010");
         cStmt.execute();

         cStmt.close();
         conn.close();
         } catch (Exception e) {
            e.printStackTrace();
      } // try
   }
} // SimpleQuery

Notice that we use question mark placeholders to use parameters when calling the stored procedure.

On the CodeNotes site, all database access is performed using JDBC. Interaction with the CodeNotes database is done entirely through stored procedures, with the exception of third-party software components that need access to the database, such as Jive Forums. The execution of all stored procedures is controlled by various methods contained within the com.codenotes.db.util.DBUtil class. The DBUtil class exposes methods that accept a Connection, a String representing the name of the stored procedure to execute, and a Vector containing the parameters for the stored procedure. The DBUtil class also has various private methods which create a CallableStatement corresponding to the given stored procedure name and parameters. The methods in the DBUtil class are called by the various DBManager classes (located in the com.codenotes.db.dbmanager package), which interpret information entered by the user into the parameters and stored procedures required to interact with the database.

JLCA Conversion of JDBC
The JLCA will convert your JDBC code to C# code using the OLE DB data provider. The OLE DB data provider is not specific to a single relational database system, and will work for your project provided that you have an OLE DB driver for your database. However, as we will discuss later in the Data Providers topic, using the OLE DB data provider is a slower way to interact with your database than using a database-specific provider, such as the Oracle data provider contained within the System.Data.OracleClient namespace. Because most projects do not require the flexibility of changing database systems, and there is no loss of functionality associated with choosing a database-specific data provider, you will want to incorporate your database data provider as soon as possible. As we will also discuss in the Data Providers topic, all data providers implement the same basic interfaces, so cleaning up the converted code to work with a database-specific data provider is usually not much extra work than cleaning up the code to work with the OLE DB data provider. For this reason, you should directly convert your code to using the data provider for your DBMS instead of temporarily converting it to OLE DB.

Regardless of whether you will be using the OLE DB data provider or a database-specific data provider, you will have to change the connection string used in your code. The format of a connection string is specific to the data provider you are using, but if you are using the OLE DB data provider with a Microsoft SQL Server? database, your connection string will look like the following:

Listing 1.2. An OLE DB connection string for SQL Server

Provider=SQLOLEDB;Integrated
Security=SSPI;Data Source=localhost;Initial Catalog=pubs

In addition to the connection string, you may also have to make changes to the SQL statements themselves. If you are using plain SQL statements, such as INSERT or UPDATE statements, the majority of the code produced by the JLCA will work without any modifications by you. Although, if you use String concatenation involving user input to create your SQL statements, you will want to take advantage of the parameters functionality of the ADO.NET Command object to protect yourself from the threat associated with malicious user input. However, if instead of plain SQL statements, you use stored procedures extensively, the code produced by the JLCA will not be functional, and you will have to manually convert all stored procedure calls into the format accepted by ADO.NET.

Conversion of Sample Code
The conversion of Listing 1.1 required us to make three changes to each method:

We removed the explicit loading of the JDBC driver manager. In ADO.NET, the driver manager is automatically loaded, so there is no need to call the C# equivalent of the Class.forName() method to instantiate the driver manager. The JLCA flags this error as a possible problem caused by the conversion of Class.forName() to System.Type.GetType().
We needed to convert the connection string into ADO.NET format. The desired format of the connection string will depend on the data provider you choose to use, but for the OLE DB data provider, the connection string has the following format:
"Provider=SQLOLEDB;Database=codenotes;User ID=sa;Password="

We needed to delete the calls to Statement.close(). In ADO.NET, Command objects, which are the equivalent of Statement objects, do not need to be closed. Because of this, there is no C# equivalent of the Statement.close() method so the JLCA leaves the original method call in the converted code and flags the error as an upgrade issue.
In addition to the three changes that we had to make to each method, we also had to change the way that the stored procedure was called. This required a rewrite of some of the lines in execStoredProcExample(). In ADO.NET, when calling a stored procedure, the Command object's CommandText property is set to the name of the stored procedure and all parameters are added to the Command object's Parameters property according to their name. Because this is completely different than the Java syntax we used, where parameters were set according to the index of their associated question mark in the SQL string, the JLCA was not able to convert this code cleanly. A more in-depth discussion of the Command object and the differences between the Command object and the Statement object is found in the ADO.NET section.

The converted code from Listing 1.1 with the post-conversion alterations is shown in Listing 1.3. Bolded lines indicate where code was added to the JLCA conversion.

Listing 1.3. After cleaning up the JLCA-converted code from Listing 1.1

using System;
public class SimpleQuery {

/* it is only necessary to have the Class.forName() call to be
executed once during the running of the application, but it is
included in each method in this example to demonstrate that
Class.forName() must be called before you can create a connection
*/

// sql is any SQL statement, for example:
// SELECT * FROM books
public virtual void executeQuery(System.String sql) {
   try {
      System.Data.OleDb.OleDbConnection temp_Connection;
      temp_Connection = new
         System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;" +
         "Database=codenotes;User ID=sa;Password=");
      temp_Connection.Open();
      System.Data.OleDb.OleDbConnection conn = temp_Connection;
      System.Data.OleDb.OleDbCommand stmt =
      SupportClass.TransactionManager.manager.CreateStatement(conn);

      System.Data.OleDb.OleDbCommand temp_OleDbCommand;
      temp_OleDbCommand = stmt;
      temp_OleDbCommand.CommandText = sql;
      System.Data.OleDb.OleDbDataReader rs =
         temp_OleDbCommand.ExecuteReader();
      long numCols = rs.GetSchemaTable().Rows.Count;

      while (rs.Read()) {
         for (int i = 1; i < numCols; i++) {
            if (i > 1)
               System.Console.Out.Write(", ");
               System.Console.Out.Write(System.Convert.ToString(rs[i –
               1]));

         } // for
         System.Console.Out.WriteLine();
      } // while
      rs.Close();
      conn.Close();
   } catch (System.Exception e) {
      SupportClass.WriteStackTrace(e, Console.Error);
   } // try
} // executeQuery

public virtual void execStoredProcExample() {
   try {
      System.Data.OleDb.OleDbConnection temp_Connection;
      temp_Connection = new
         System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;" + "
         Database=codenotes;User ID=sa;Password=");

      temp_Connection.Open();
      System.Data.OleDb.OleDbConnection conn = temp_Connection;
      System.Data.OleDb.OleDbCommand cStmt = conn.CreateCommand();
      oStmt.CommandType = System.Data.CommandType.StoredProcedure;

      cStmt.CommandText = "sp_addFeaturedArticle";
      cStmt.Parameters.Add("@articleID", "AS010010");
      cStmt.ExecuteNonQuery();

      conn.Close();
   } catch (System.Exception e) {
      SupportClass.WriteStackTrace(e, Console.Error);
   } // try
}

} // SimpleQuery

Conversion of www.codenotes.com
The majority of the initial post-conversion work on the CodeNotes site involved fixing up the conversion of the database interaction code. There were three major tasks involved:

We changed the use of the OLE DB data provider to the SQL Server data provider, which was contained in the System.Data.SqlClient namespace.
Instead of using a CachedRowSet object we had to update our code to using a DataSet object. CachedRowSet was provided by our JDBC driver and is an extension to the RowSet interface, which allows a RowSet to be examined after the connection to the database has been closed. Because it is a third-party class, the JLCA did not convert any of the code using CachedRowSet.
We modified DBUtil's methods to work with ADO.NET syntax instead of with JDBC syntax.
Each of these steps was actually performed as part of our Phase 1 conversion. We decided it was quicker and easier to simply convert straight to the SQL Server data provider than it was to try to make the OLE DB data provider do what we wanted. You may find that this is often the case in your application.

ADO.NET
JDBC and ADO.NET, while functionally similar, have different underlying assumptions and architecture. JDBC was designed for general, all-purpose data access while retaining the Java programming language's primary goals: platform-independence and innovation within specification. ADO.NET, on the other hand, in addition to general all-purpose data access, was also designed to accommodate data access in a distributed environment. In particular, ADO.NET implements two distinct data models: a connected and disconnected model. The connected data model works much the same as JDBC, with all data operations contained within the scope of an open database connection. The disconnected data model only opens a database connection when absolutely necessary; that is only during a database query or modification. When the operation is executed, the database connection is implicitly opened and closed at the start and end of the operation respectively. This data model greatly enhances the ability to share connections among multiple users (which has obvious advantages in a Web-based environment). A Java equivalent of the disconnected model may or not be available as part of your JDBC driver, however in all ADO.NET data providers the disconnected model must be present. ADO.NET has also tightly integrated itself with XML.

Data Providers
ADO.NET data providers play a synonymous role in the .NET environment as JDBC drivers play in the Java environment. A data provider, much like a JDBC driver, is a set of interfaces and classes that together, provide access to an underlying data source. All data providers expose a minimum set of interfaces that all developers can rely on being implemented; however, a provider is free to extend the minimum required functionality. For example, the SQL Server data provider allows you to work with the System.Data.SqlTypes namespace, which contains classes for SQL Server data types, preventing type conversion errors from SQL Server data types to .NET Framework data types.

All data providers must implement at least the following four interfaces:

System.Data.IDbConnection establishes a connection with the actual database. This interface provides functionality similar to the java.sql.Connection interface.
System.Data.IDbCommand executes commands over the database connection. This interface provides similar functionality to the java.sql.Statement interface and its descendant interfaces java.sql.PreparedStatement and java.sql.CallableStatement.
System.Data.IDataReader iterates over the results of an SQL query. Requires an open data connection. The functionally of this interface is similar to the java.sql.ResultSet interface.
System.Data.IDataAdapter is used in disconnected scenarios, and acts as the intermediary between the database and an application. It does not require an open data connection. There is no JDBC equivalent to this interface.
The .NET Framework ships with five different data providers, each of which is contained within a specific namespace. Three of the data providers are database-specific:

The Oracle data provider (in the System.Data.OracleClient namespace)
The SQL Server data provider, for SQL Server 7.0 and higher (the core of which is contained in the System.Data.SqlClient namespace, but, as mentioned previously, additional classes are also contained in the System.Data.SqlTypes namespace.)
The SQL Server CE data provider (in the System.Data.SqlServerCE namespace)
The other two data providers are not database-specific and they use two of Microsoft's older data access tools to interact with a data source:

The ODBC data provider (in the System.Data.Odbc namespace)
The OLE DB data provider (in the System.Data.OleDb namespace)
Provided that you have an ODBC or OLE DB driver for your database, you can use the corresponding database-generic data provider to perform your ADO.NET operations. However, because these two providers are not database-specific, there are more layers of software to go through than a native data provider adding memory and performance overhead. Also, database-specific functionality is lost due to abstraction into generic method calls, so it is impossible to fully leverage your database management system.

Commands
The ADO.NET command objects provide similar functionality to the JDBC statement objects. Both are responsible for the submission of SQL and retrieval of results from the underlying DBMS. However, unlike JDBC, which has the Statement class for dynamic SQL statements, the PreparedStatement class for static, reusable SQL statements, and the CallableStatement class for calling stored procedures, all SQL statements and stored procedure calls are executed using instances of the command class.

There are three properties in a command object that are used to specify the interaction with the database:

CommandText—When calling stored procedures, it contains the name of the stored procedure to call. When executing SQL text, it contains the SQL statements to be executed.
CommandType—Specifies the type of statement being executed, such as a stored procedure or SQL text.
Parameters—Specifies the parameters used when calling the stored procedure or SQL text. ADO.NET allows you to use parameters with SQL text commands instead of using string concatenation to avoid the security threat posed by string concatenation.
In the following sections, we will see how a command object can be used to interact with a database; but in Listing 1.4 we demonstrate how to create a command object to interact with a database using SQL text, SQL text using user input, or to call a stored procedure.

Listing 1.4. Creating command objects for plain SQL text, SQL text with user input, and stored procedure calls

// conn is an instance of the System.Data.IDbConnection interface
// creating a plain text command object
IDbCommand cmdPlainText = conn.CreateCommand();
cmdPlainText.CommandType = System.Data.CommandType.Text;
cmdPlainText.CommandText = "SELECT * FROM titles";

// creating a plain text command object using user input
// the resulting statement will be
// "SELECT * FROM titles WHERE title_id=myTitleID"
IDbCommand cmdUserInputText = conn.CreateCommand();
cmdUserInputText.CommandType= System.Data.CommandType.Text;
cmdUserInputText.CommandText = "SELECT * FROM titles WHERE
   title_id=@title_id";

/* note the previous line could have been:
cmdUserInputText.CommandText =
"SELECT * FROM titles WHERE title_id='" + "myTitleID" + "'"
and have the same effect on the database,
but that would expose a security risk */
IDbDataParameter userParam = cmdUserInputText.CreateParameter();
userParam.ParameterName = "@title_id";
userParam.Value = "myTitleID";
userParam.DbType = System.Data.DbType.String;
cmdUserInputText.Parameters.Add(userParam);

/* creating a command to call the stored procedure
"spMyStoredProcsName" which has a single parameter "@spParam". */
IDbCommand cmdStoredProc = comm.CreateCommand();
cmdStoredProc.CommandType = System.Data.CommandType.StoredProcedure;
cmdStoredProc.CommandText = "spMyStoredProcsName";
IDbDataParameter spParam = cmdStoredProc.CreateParameter();
spParam.ParameterName = "@spParam";
spParam.Value = "spParamValue";
spParam.DbType = System.Data.DbType.String;
cmdStoredProc.Parameters.Add(spParam);

In addition to the generic parameter types that we used in our example, such as System.Data.DbType.String, some data providers will also provide parameter types that map to data types specific to their database, such as the System.Data.SqlDbType.VarChar used to represent VarChar data in a SQL Server database. In order to use the database-specific data types, you must use the data provider's parameter class instead of the generic IDbDataParameter. Using database-specific data types will avoid any data loss in translating from the database data type to the .NET Framework data type; however, it will make it more difficult to modify your .NET Framework application to work with a different database in the future. Choosing which data types to use is a decision that will be based on the required precision of the data in your database (most data types will translate with no data loss) and whether or not you plan to change databases in the future.

You will notice that when creating the cmdUserInputText command, instead of simply concatenating the value "myTitleID" to the string, we used a parameter object. This is because straight string concatenation using a user's input is a security threat. For example, if we were to use the following line of code:

// userInput is a String the user entered
myCommand.CommandText = "SELECT * FROM titles WHERE title_id=' +
   userInput + "'";

A user could cause an INPUT, or any other SQL statement, to be executed by entering a value of "INSERT into." Using parameters instead of string concatenation removes the potential for this malicious action. The JDBC equivalent of parameters is to use question mark placeholders.

Querying the Database
The simplest pattern used to query a database in ADO.NET is similar to the pattern used in JDBC. You create a connection object and a command object (the ADO.NET equivalent of a statement object). The connection to the database is maintained while the query is executed and while each row from the query is retrieved one-by-one. Once the method has completed processing all rows the connection is closed. In the simple example below, we use this pattern to display the results of a query.

Listing 1.5. Querying the database in ADO.NET using the connected model

using System.Data;
using System.Data.SqlClient;
public class SimpleQuery {
// sqlCommand is a SELECT sql statement, such as
// "SELECT * FROM titles"
public void ExecuteQuery(String sqlCommand) {
   try {
      IDbConnection conn =
         new SqlConnection("Initial Catalog=myDB;"
         "Data Source=localhost;Integrated Security=SSPI");
      IDbCommand cmd = conn.CreateCommand();
      cmd.CommandText = sqlCommand;
      conn.Open();
      IDataReader reader = cmd.ExecuteReader();

      while (reader.Read()) {
         for (int i=0; i             if (i>0)
               Console.Out.Write(", ");
            Console.Out.Write(reader[i]);
         } // for
         Console.Out.WriteLine();
      } // while
   } catch (Exception e) {
      Console.Out.WriteLine(e.Message);
   }
}
}

Notice that in the declaration of the three main objects (conn, cmd, and reader) the class type begins with an "I" (IDbConnection, IDbCommand, and IDataReader). As mentioned in the Data Providers section, these classes are interfaces that are implemented by each data provider. Since we are not using any functionality specific to the SQL Server database, using these interfaces makes it easier for us to adopt future changes to a different data provider without suffering any loss of functionality. For example, if we wanted to use the OLE DB data provider, we would only have to change two things in our code:

Instead of using the System.Data.SqlClient namespace, we would use the System.Data.OleDb namespace.
Change the creation of the conn object to the following:
IDbConnection conn = new
OleDbConnection("oledbconnectionstring");

Querying Using the Disconnected Data Model
As mentioned previously, ADO.NET also allows you to interact with the database without maintaining an open connection throughout the entire duration of your interaction. The pattern for the disconnected data model is similar to the pattern for the connected model, except you use different objects and delegate some of the operations to these objects. A simple example is shown in Listing 1.6.

Listing 1.6. Querying the database with the disconnected model

private void DisconnectedQuery(String sql) {
   try  {
      IDbConnection conn =
         new SqlConnection("Integrated Security=" +
         "SSPI;Data Source=localhost;Initial Catalog=pubs");
      SqlDataAdapter da = new SqlDataAdapter(sql,(SqlConnection)conn);
      DataSet ds = new DataSet();
      da.Fill(ds);
      foreach (DataRow r in ds.Tables[0].Rows) {
         for (int i=0; i             if (i>0)
               Console.Out.Write(", ");
            Console.Out.Write(r.ItemArray[i]);
         } // for
         Console.Out.WriteLine();
      } // foreach
      textBox1.Text = sb.ToString();
   } catch (Exception x) {
      Console.Out.WriteLine(x.Message);
   } // try
}

At the end of the Fill() method, the DataSet object contains an in-memory snapshot of the result of the query in one of its tables. When executing a single SELECT statement, as in Listing 1.6, the results of the query are in the first table in the Tables collection, which is why we access the results using ds.Tables[0]. As we will see in the following section, we are able to modify the data snapshot in-memory and submit the changes back to the database. In this manner, the database connection is maintained for the least amount of time possible, drastically improving the reusability of the connection object. Note, however, that the disconnected data model may not be appropriate for memory-constrained situations.

In Listing 1.6 all the database activity occurs within the da.Fill() method. The Fill() method has the following responsibilities:

Open the database connection (passed in the constructor).
Submit the SQL command (also passed in the constructor) to the database.
Return all rows resulting from the query back to the application. In particular, persist all rows inside a table in the DataSet object.
Close the database connection.
Alternatively, instead of using a string for the SQL text, the data adapter can work with command objects, discussed previously in the Commands section, allowing you the flexibility to call stored procedures or to use parameters when executing SQL text. However even if you are using command objects, the Fill() method is still responsible for the same four duties.

Listing 1.7. A DataAdapter using a command object

// myCmd is a command objected created in any of the
// manners shown in Listing 1.4
DataSet ds = new DataSet();
SqlDataAdatper da = new SqlDataAdapter(myCmd);
da.Fill(ds);

Modifying Database State
The flip side to querying a database is inserting, updating, or deleting rows. ADO.NET updates databases in a similar manner as JDBC. Modifying a database in ADO.NET is almost the same as querying the database in ADO.NET, except the ExecuteNonQuery() method is called instead of the ExecuteQuery() method. Also, because you are executing a non-query statement, an int, reflecting the number of rows that were updated or added to the database, instead of a DataReader object, is returned from ExecuteNonQuery(). A simple method for updating a database is shown in Listing 1.8. Notice that aside from the expected SQL command and the method called to update the statement, the ADO.NET code is the same as the code in Listing 1.5.

Listing 1.8. Updating a database using ADO.NET

using System.Data;
using System.Data.SqlClient;
public class SimpleNonQuery {
   // sql is any string that would cause the db to be
   // updated, such as
   // "UPDATE Products SET UnitPrice = UnitPrice * 1.1"
   public void ExecuteUpdateDB(String sql) {
      try {
         IDbConnection conn =
            new SqlConnection("Integrated Security=SSPI;" +
            "Data Source=localhost;Initial Catalog=pubs");
         conn.Open();
         IDbCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         cmd.CommandType = System.Data.CommandType.Text;
         int result = cmd.ExecuteNonQuery();
         conn.Close();
      } catch (Exception e) {
         Console.Out.WriteLine(e.Message);
      } // try
   } // ExecuteNonQuery
} // SimpleNonQuery

Updating Using the Disconnected Data Model
Updating the database using the disconnected data model can be thought of as the reverse of the process for querying the database using the disconnected model. Instead of performing the SQL statement and then creating the DataSet, the DataSet is examined to create the SQL statements, which are then executed. The IDataAdapter.Update() method, which accepts a DataSet as a parameter, performs the updating of the database. During the execution of the Update() method, the DataAdapter performs the following actions:

Opens a connection to the database.
Examines each row in the DataSet, row-by-row.
If the row has been modified since the previous interaction with the database, an INSERT, UPDATE, or DELETE SQL statement is executed accordingly. The state of the row (unmodified, inserted, modified or deleted) is maintained as a property in the DataRow object, which is the building block of a DataTable, in turn the building block of a DataSet.
Closes the connection.
The actual INSERT, UPDATE, and DELETE SQL commands performed are maintained as properties of the DataAdapter object. You must explicitly set the SQL commands for the INSERT, UPDATE, and DELETE operations before calling the Update() method. Otherwise, an exception will be thrown and the database will not be modified if the appropriate command does not exist. For example, if a row has been deleted from a DataSet, and the DeleteCommand property has not been set, an exception will be thrown when calling the Update() method and the deleted row will remain in the database. Each of the data providers included in the .NET framework include a CommandBuilder object which will create the necessary SQL statements for you, provided that the contents of the DataTable objects in the DataSet each represent a single table in the database (i.e. the DataTable is not the result of a join operation). However, it is not a requirement for a data provider to have a CommandBuilder object, but because of its usefulness and the fact that all data providers included in the .NET Framework have CommandBuilder classes, most third-party data providers will also have a similar utility. Listing 1.9 uses the SQL Server data provider and the classes we just discussed to change the information in the database by altering a DataSet.

Listing 1.9. Updating a database using the disconnected model

// uses the System.Data namespace and the
// System.Data.SqlClient namespace
IDbConnection conn = new SqlConnection("Integrated Security=SSPI;" +
   Data Source=localhost;Initial Catalog=pubs");
String sql = "SELECT * FROM titles";
SqlDataAdapter da = new SqlDataAdapter(sql, (SqlConnection)conn);
DataSet ds = new DataSet();
da.Fill(ds);
SqlCommandBuilder myBuilder = new SqlCommandBuilder(da);
ds.Tables[0].Rows[0]["title"] = "Twenty Years After";
da.Update(ds);

It is important to note that the SQL statements in the Update() method do not occur in batch and are instead executed one-at-a-time. Also, most data providers provide multiple overrides to the Update() method, allowing you to make updates to the database using a variety of objects. For example, one of the many update options in SQL Server is to use an array of DataRow objects instead of a DataSet object.

Connection Pooling
Pooling connections can significantly enhance the performance and scalability of your application. In ADO.NET, connection pools are the responsibility of the data provider and there is no set standard that must be followed. In this section, we will discuss connection pooling using the SQL Server data provider, but the process is similar for the other data providers included in the .NET Framework. Please see MSDN? for more information on the data provider that you elect to use.

The actual connections to the database are only created by the SQL Server data provider when you call the connection.Open() method. If you create multiple connection objects, but never call the Open() method, the SQL Server data provider will never actually create a connection to the database. When the Open() method is called, the SQL Server data provider has two main responsibilities.

First, it checks to see if a connection pool exists for the given connection string. Each connection pool is associated with a distinct connection string. If no connection pool has been created for the given connection string, a new connection pool is created. Once a connection pool is created, it is not destroyed until the main program process terminates. That is, once you have a connection pool, you keep the same one for the lifetime of your program.

Then, the data provider checks if the minimum number of connection objects has been created for this connection pool. If the minimum number has not been created, the data provider creates a connection object and returns it. If the minimum number of connection objects has been created and there is a free connection object, one of the free connection objects is returned (connection objects are released and made free to the pool by calling the Connection.Close() or Connection.Dispose() method). If the minimum number of connection objects has been created and there are no free connections, then the data provider checks if the maximum number of connection objects has been created. If the maximum has not been reached, a new connection object is created; otherwise, the request for a connection is placed in a queue until an existing connection becomes free.

Remember, that once you open a connection object, this connection is not available to the pool again until the Close() or Dispose() method is called. For this reason, you should not Open() a connection until it is necessary and you should call Close() or Dispose() as soon as reasonable. Connection objects that go out of scope without calling Close() (for example, instantiating and opening a connection object in a method but never closing the connection object within the method) will not be immediately returned to the connection pool. Out-of-scope connections will only be returned to the connection pool when the maximum pool size has been reached.

You can configure the connection pool using parameters in your connection string. For example, use the Max Pool Size and Min Pool Size to set the minimum and maximum number of connection objects in the pool, respectively, as shown in Listing 1.10.

Listing 1.10. Configuring the size of a connection pool

IDbConnection conn =
   new SqlConnection("Integrated Security=SSPI;" +
      "Data Source=localhost;Initial Catalog=pubs;"
      "Max Pool Size=15;Min Pool Size=5");

Transactions
Occasionally you will want to execute multiple SQL statements in an all-or-nothing manner. For example, in a bank transfer from one client to another, there must be a debit in one client's account and a debit to the other client's account. Just performing the debit or just performing the credit is not acceptable. In instances like this, you will use database transactions.

Transactional processing involves the same basic steps as standard database modification with a couple of extra steps. At the start of the transaction, you must create an instance of the IDbTranscation interface, which is done by calling the Connection.BeginTransaction() method. Then, you must assign the transaction object to a command object using the Command.Transaction. This command object will be used to execute all statements for the transaction. Finally, when the transaction is complete you will call the IDbTransaction.Commit() method to update the database. If an error occurs, or you wish to abort the transaction for any other reason, call the IDbTransaction.Rollback() method to revert the database to its state before the transaction. Listing 1.11 uses transactional processing to insert a purchase order of three different items into the sales table of the pubs database.

Listing 1.11. Transactional processing in ADO.NET

IDbConnection conn = new
   SqlConnection("Integrated Security=SSPI;" +
      "Data Source=localhost;Initial Catalog=pubs");
conn.Open();
IDbTransaction trans = conn.BeginTransaction();
IDbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try {
   cmd.CommandType = System.Data.CommandType.Text;
   cmd.CommandText = "INSERT INTO sales" +
      (stor_id, ord_num, ord_date, qty, payterms, title_id)" +
      " VALUES ('7896', 'P3456', '04/15/93', '10', 'Net 30',
        'BU1111')";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO sales" +
      (stor_id, ord_num, ord_date, qty, payterms, title_id)" +
      " VALUES ('7896', 'P3456', '04/15/93', '15', 'Net 30',
        'BU1032')";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO sales" +
      (stor_id, ord_num, ord_date, qty, payterms, title_id)" +
      " VALUES ('7896', 'P3456', '04/15/93', '10', 'Net 30',
        'PS2091')";
   cmd.ExecuteNonQuery();
   trans.Commit();
} catch (Exception x) {
   trans.Rollback();
} // try

Notice that we open the connection to the database before we create the transaction. This is because the creation of the transaction object requires interaction with the database and will not work otherwise. Also, notice that the command object, aside from assigning the Transaction property, is used the same way as command objects in modifying databases without a transaction.

XML Support in ADO.NET
Database vendors have recognized the importance of XML in the future of their industry. Native support for XML has been built into a variety of proprietary database products and the use of XML will continue to grow with the emergence of protocols like Web services. With that in mind, Microsoft has integrated XML into ADO.NET. For example, it is possible to interchange between XML and a DataSet with a single method call. This means that it is a simple and straightforward process to take an XML file and flush its contents to a database, or contrarily, to represent a database query as XML. Also, a DataSet object is able to model and enforce column constraints and table relationships in addition to storing raw data. These constraints can also be turned into XML by way of XML Schemas. Listing 1.12 uses the XML functionality of ADO.NET to perform a simple query on the database and save the contents of the query to an XML file. A sample of the XML file is shown in Listing 1.13.

Listing 1.13. The contents of the XML file produced by Listing 1.12

IDbConnection conn =
   new SqlConnection("Integrated Security=SSPI;" +
      "Data Source=localhost;Initial Catalog=pubs");
String sql = "SELECT * FROM titles";
SqlDataAdapter da = new SqlDataAdapter(sql, (SqlConnection)conn);
DataSet ds = new DataSet();
da.Fill(ds);
ds.WriteXml("Titles.XML", XmlWriteMode.WriteSchema);
Listing 1.12 Saving the contents of a query to an XML file

      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
   
       msdata:Locale="en-CA">
     
       
         
           
             
               
                   type="xs:string" minOccurs="0" />
               
                   type="xs:string" minOccurs="0" />
               
                   type="xs:string" minOccurs="0" />
               
                   type="xs:string" minOccurs="0" />
               
                   type="xs:decimal" minOccurs="0" />
               
                   type="xs:decimal" minOccurs="0" />
               
                   type="xs:int" minOccurs="0" />
               
                   type="xs:int" minOccurs="0" />
               
                   type="xs:string" minOccurs="0" />
               
                   type="xs:dateTime" minOccurs="0" />
             
           
         
       
     
   
 
 

BU1111

business
1389
11.9500
5000.0000
10
3876
Helpful hints on how to use your electronic resources to the
      best advantage.
1991-06-09T00:00:00.0000000-04:00
                                       

BU2075

business
0736
2.9900
10125.0000
24
18722
The latest medical and psychological techniques
    for living with the electronic office. Easy-to-understand
      explanations.
1991-06-30T00:00:00.0000000-04:00
                   

The only new method in Listing 1.12 is the DataSet.WriteXml() method, which, as the name implies, writes the contents of the query in XML format to the specified file. The entire XML is encapsulated within a element. Each row in the titles table is encapsulated within a

elements instead of element. This is because the results of a SQL Server SELECT statement are an unnamed table. We could have provided a name to the table via the DataSet by using the command shown in Listing 1.14 instead of calling da.Fill(ds), as we did in Listing 1.13 If we had used the code in Listing 1.14 , each entry in the XML file would have been encapsulated within

Listing 1.14 Naming the table in a dataset

da.Fill(ds, "NewTableName");

Also, as can be seen in Listing 1.13 , the XML Schema was embedded into the resulting XML file. An explanation of XML Schemas is beyond the scope of this whitepaper; however, note that each column is given a name and a corresponding type. It was purely optional that the example embedded the XML Schema within the XML. Another option would be to only write the XML to the file, using the XmlWriteMode.IgnoreSchema enumeration when calling DataSet.WriteXml() and either not write the XML Schema to a file or write the XML Schema to its own file, using the DataSet.WriteXmlSchema() method.

Reading an XML file into a DataSet is also very simple to accomplish. Simply use the DataSet.ReadXml() method. For example:

ds.ReadXml("Titles.XML", XmlReadMode.Auto);

In this case, the data contained within the Titles.XML file will be stored in the DataSet object. The XmlReadMode we specify depends on whether we know if an XML Schema is embedded in the XML file. If we know for a fact that the incoming XML will contain an embedded XML Schema, then we can use XmlReadMode.Fragment. Other options include ignoring the schema, inferring a schema if one is not present, or, as we specified, automatically performing the most appropriate action.

Conclusion
JDBC and ADO.NET provide similar functionality when accessing databases. Although there is similar functionality between the two technologies and the logic behind their use is similar, the differences between the classes and interfaces in the two packages are great enough that the JLCA will most likely not convert all of your code cleanly. However, the required changes will be rather simple and straightforward. While fixing the conversion errors, you will also most likely convert your code to using a data provider specific to your database instead of the generic OLE DB data provider used by the JCLA during conversion, thereby speeding up the interaction between your application and your database.

--------------------------------------------------------------------------------

Send feedback to Microsoft

? Microsoft Corporation. All rights reserved.

elements.