Chapter 20. Data access using ADO.NET

• Consistent and comprehensive database provider interfaces for both .NET 1.1 and 2.0

• Integration with Spring's transaction management features.

• Template style use of DbCommand that removes the need to write typical ADO.NET boiler-plate code.

• 'One-liner' implementations for the most common database usage patterns lets you focus on the 'meat' of your ADO.NET code.

• Easy database parameter creation/management

• Provider independent exceptions with database error codes and higher level DAO exception hierarchy.

• Centralized resource management for connections, commands, data readers, etc.

• Simple DataReader to Object mapping framework.

• Motivations - describes why one should consider using Spring's ADO.NET features as compared to using 'raw' ADO.NET API.

• Provider Abstraction - a quick overview of Spring's provider abstraction.

• Approaches to ADO.NET Data Access - Discusses the two styles of Spring's ADO.NET data access classes - template and object based.

• Introduction to AdoTemplate - Introduction to the design and core methods of the central class in Spring's ADO.NET support.

• Exception Translation - Describes the features of Spring's data access exceptions

• Parameter Management - Convenience classes and methods for easy parameter management.

• Custom IDataReader implementations - Strategy for providing custom implementations of IDataReader. This can be used to centralized and transparently map DBNull values to CLR types when accessing an IDataReader or to provide extended mapping functionality in sub-interfaces.

• Basic data access operations - Usage of AdoTemplate for IDbCommand 'ExecuteScalar' and 'ExecuteNonScalar' functionality

• Queries and Lightweight Object Mapping - Using AdoTemplate to map result sets into objects

• DataSet and DataTable operations - Using AdoTemplate with DataSets and DataTables

• Modeling ADO.NET operations as .NET objects - An object-oriented approach to data access operations.

1. Define connection parameters

2. Open the connection

3. Specify the command type and text

4. Prepare and execute the statement

5. Set up the loop to iterate through the results (if any)

6. Do the work for each iteration

7. Process any exception

8. Display or rollback on warnings

9. Handle transactions

10. Close the connection

Each database vendor is associated with a particular implementation of the IDbProvider interfaces. A variety of implementations are provided with Spring such as SqlServer, Oracle and MySql. Refer to the documentation on Spring's DbProvider for creating a configuration for database that is not yet provided. The programmatic way to create an IDbProvider is shown below

IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");

The Execute method and its associated callback function/inteface is the basic method upon which all the other methods in AdoTemplate delegate their work. If you can not find a suitable 'one-liner' method in AdoTemplate for your purpose you can always fall back to the Execute method to perform any database operation while benefiting from ADO.NET resource management and transaction enlistment. This is commonly the case when you are using special provider specific features, such as XML or BLOB support.

In this example a simple query against the 'Northwind' database is done to determine the number of customers who have a particular postal code.

public int FindCountWithPostalCode(string postalCode)
{
  return adoTemplate.Execute<int>(delegate(DbCommand command)
         {
             command.CommandText =
                 "select count(*) from Customers where PostalCode = @PostalCode";
                                                    
             DbParameter p = command.CreateParameter();
             p.ParameterName = "@PostalCode";
             p.Value = postalCode;
             command.Parameters.Add(p);
                                                         
             return (int)command.ExecuteScalar();

          });

}

The DbCommand that is passed into the anonymous delegate is already has it Connection property set to the corresponding value of the dbProvider instance used to create the template. Furthermore, the Transaction property of the DbCommand is set based on the transactional calling context of the code as based on the use of Spring's transaction management features. Also note the feature of anonymous delegates to access the variable 'postalCode' which is defined 'outside' the anonymous delegate implementation. The use of anonymous delegates is a powerful approach since it allows you to write compact data access code. If you find that your callback implementation is getting very long, it may improve code clarity to use an interface based version of the callback function, i.e. an ICommandCallback shown below.

As you can see, only the most relevant portions of the data access task at hand need to be coded. (Note that in this simple example you would be better off using AdoTemplate's ExecuteScalar method directly. This method is described in the following sections). As mentioned before, the typical usage scenario for the Execute callback would involve downcasting the passed in DbCommand object to access specific provider API features.

There is also an interface based version of the execute method. The signatures for the delegate and interface are shown below

public delegate T CommandDelegate<T>(DbCommand command);  


public interface ICommandCallback
{
  T DoInCommand<T>(DbCommand command);
}

While the delegate version offers the most compact syntax, the interface version allows for reuse. The corresponding method signatures on Spring.Data.Generic.AdoTemplate are shown below

public class AdoTemplate : AdoAccessor, IAdoOperations
{
   ...

   T Execute<T>(ICommandCallback action);

   T Execute<T>(CommandDelegate<T> del);

   ...
}

While it is common for .NET 2.0 ADO.NET provider implementations to inherit from the base class System.Data.Common.DbCommand, that is not a requirement. To accommodate the few that don't, which as of this writing are the latest Oracle (ODP) provider, Postgres, and DB2 for iSeries, two additional execute methods are provided. The only difference is the use of callback and delegate implementations that have IDbCommand and not DbCommand as callback arguments. The following listing shows these methods on AdoTemplate.

public class AdoTemplate : AdoAccessor, IAdoOperations
{
   ...

   T Execute<T>(IDbCommandCallback action);

   T Execute<T>(IDbCommandDelegate<T> del);

   ...
}

where the signatures for the delegate and interface are shown below

public delegate T IDbCommandDelegate<T>(IDbCommand command);


public interface IDbCommandCallback<T>
{
  T DoInCommand(IDbCommand command);
}

Internally the AdoTemplate implementation delegates to implementations of IDbCommandCallback so that the 'lowest common denominator' API is used to have maximum portability. If you accidentally call Execute<T>(ICommandCallback action)and the command does not inherit from DbCommand, an InvalidDataAccessApiUsageException will be thrown.

Depending on how portable you would like your code to be, you can choose among the two callback styles. The one based on DbCommand has the advantage of access to the more user friendly DbParameter class as compared to IDbParameter obtained from IDbCommand.

AdoTemplate differs from its .NET 2.0 generic counterpart in that it exposes the interface IDbCommand in its 'Execute' callback methods and delegate as compared to the abstract base class DbProvider. Also, since anonymous delegates are not available in .NET 1.1, the typical usage pattern requires you to create a explicitly delegate and/or class that implements the ICommandCallback interface. Example code to query In .NET 1.1 the 'Northwind' database is done to determine the number of customers who have a particular postal code is shown below.

public virtual int FindCountWithPostalCode(string postalCode)
{
  return (int) AdoTemplate.Execute(new PostalCodeCommandCallback(postalCode));
}

and the callback implementation is

private class PostalCodeCommandCallback : ICommandCallback
{
    private string cmdText = "select count(*) from Customer where PostalCode = @PostalCode";

    private string postalCode;
    
    public PostalCodeCommandCallback(string postalCode)
    {
        this.postalCode = postalCode;
    }

    public object DoInCommand(IDbCommand command)
    {
        command.CommandText = cmdText;

        IDbDataParameter p = command.CreateParameter();
        p.ParameterName = "@PostalCode";
        p.Value = postalCode;
        command.Parameters.Add(p);
        
        return command.ExecuteScalar();

    }
}

Note that in this example, one could more easily use AdoTemplate's ExecuteScalar method.

The Execute method has interface and delegate overloads. The signatures for the delegate and interface are shown below

public delegate object CommandDelegate(IDbCommand command);

public interface ICommandCallback
{
  object DoInCommand(IDbCommand command);
}

The corresponding method signatures on Spring.Data.AdoTemplate are shown below

public class AdoTemplate : AdoAccessor, IAdoOperations
{
   ...

   object Execute(CommandDelegate del);

   object Execute(ICommandCallback action);

   ...

}

Note that you have to cast to the appropriate object type returned from the execute method.

There are many methods in AdoTemplate so it is easy to feel a bit overwhelmed when taking a look at the SDK documentation. However, after a while you will hopefully find the class 'easy to navigate' with intellisense. Here is a quick categorization of the method names and their associated data access operation. Each method is overloaded to handle common cases of passing in parameter values.

The generic 'catch-all' method

The following methods mirror those on the DbCommand object.

Mapping result sets to objects

Mapping result set to a single object

Query with a callback to create the DbCommand object. These are generally used by the framework itself to support other functionality, such as in the Spring.Data.Objects namespace.

DataTable and DataSet operations

	Note
	These methods are not currently in the generic version of AdoTemplate but accessible through the property ClassicAdoTemplate.

Parameter Creation utility methods

In turn each method typically has four overloads, one with no parameters and three for providing parameters. Aside from the DataTable/DataSet operations, the three parameter overloads are of the form shown below

The CallbackInterfaceOrDelegate is one of the three types listed previously. The parameters setting arguments are of the form

The first overload is a convenience method when you only have one parameter to set. The database enumeration is the base class 'Enum' allowing you to pass in any of the provider specific enumerations as well as the common DbType enumeration. This is a trade off of type-safety with provider portability. (Note generic version could be improved to provide type safety...).

The second overload contains a collection of parameters. The data type is Spring's IDbParameters collection class discussed in the following section.

The third overload is a callback interface allowing you to set the parameters (or other properties) of the IDbCommand passed to you by the framework directly.

If you are using .NET 2.0 the delegate versions of the methods are very useful since very compact definitions of database operations can be created that reference variables local to the DAO method. This removes some of the tedium in passing parameters around with interface based versions of the callback functions since they need to be passed into the constructor of the implementing class. The general guideline is to use the delegate when available for functionality that does not need to be shared across multiple DAO classes or methods and use interface based version to reuse the implementation in multiple places. The .NET 2.0 versions make use of generics where appropriate and therefore enhance type-safety.

AdoTemplate has the following properties that you can configure

Instead of creating a parameter on one line of code, then setting its type on another and size on another, a builder and parameter interface, IDbParametersBuilder and IDbParameter respectfully, are provided so that this declaration process can be condensed. The IDbParameter support chaining calls to its methods, in effect a simple language-constrained domain specific language, to be fancy about it. Here is an example of it in use.

IDbParametersBuilder builder = CreateDbParametersBuilder();
builder.Create().Name("Country").Type(DbType.String).Size(15).Value(country);
builder.Create().Name("City").Type(DbType.String).Size(15).Value(city);


// now get the IDbParameters collection for use in passing to AdoTemplate methods.


IDbParameters parameters = builder.GetParameters();

Please note that IDbParameters and IDbParameter are not part of the BCL, but part of the Spring.Data.Common namespace. The IDbParameters collection is a frequent argument to the overloaded methods of AdoTemplate.

The parameter prefix, i.e. '@' in Sql Server, is not required to be added to the parameter name. The DbProvider is aware of this metadata and AdoTemplate will add it automatically if required before execution.

An additional feature of the IDbParametersBuilder is to create a Spring FactoryObject that creates IDbParameters for use in the XML configuration file of the IoC container. By leveraging Spring's expression evaluation language, the above lines of code can be taken as text from the XML configuration file and executed. As a result you can externalize your parameter definitions from your code. In combination with abstract object definitions and importing of configuration files your increase the chances of having one code base support multiple database providers just by a change in configuration files.

This class is similar to the parameter collection class you find in provider specific implementations of IDataParameterCollection. It contains a variety of convenience methods to build up a collection of parameters.

Here is an abbreviated listing of the common convenience methods.

Here a simple usage example

// inside method has has local variable country and city...

IDbParameters parameters = CreateDbParameters();
parameters.AddWithValue("Country", country).DbType = DbType.String;
parameters.Add("City", DbType.String).Value = city;


// now pass on to AdoTemplate methods.

The parameter prefix, i.e. '@' in Sql Server, is not required to be added to the parameter name. The DbProvider is aware of this metadata and AdoTemplate will add it automatically if required before execution.

ExecuteNonQuery is used to perform create, update, and delete operations. It has four overloads listed below reflecting different ways to set the parameters.

An example of using this method is shown below

        public void CreateCredit(float creditAmount)
        {
            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                    String.Format("insert into Credits(creditAmount) VALUES ({0})",
                    creditAmount));
        }

An example of using this method is shown below

int iCount = (int)adoTemplate.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM TestObjects");
                

• IResultSetExtractor / ResultSetExtractorDelegate - hands you a IDataReader object for you to iterate over and return a result object.

• IRowCallback / RowCallbackDelegate - hands you a IDataReader to process the current row. Returns void and as such is usually stateful in the case of IRowCallback implementations or uses a variable to collect a result that is available to an anonymous delegate.

• IRowMapper / RowMapperDelegate - hands you a IDataReader to process the current row and return an object corresponding to that row.

The ResultSetExtractor gives you control to iterate over the IDataReader returned from the query. You are responsible for iterating through all the result sets and returning a corresponding result object. Implementations of IResultSetExtractor are typically stateless and therefore reusable as long as the implementation doesn't access stateful resources. The framework will close the IDataReader for you.

The interface and delegate signature for ResutSetExtractors is shown below for the generic version in the Spring.Data.Generic namespace

public interface IResultSetExtractor<T>
{
  T ExtractData(IDataReader reader);
}

public delegate T ResultSetExtractorDelegate<T>(IDataReader reader);

The definition for the non-generic version is shown below

public interface IResultSetExtractor
{
    object ExtractData(IDataReader reader);
}

public delegate object ResultSetExtractorDelegate(IDataReader reader);

Here is an example taken from the Spring.DataQuickStart. It is a method in a DAO class that inherits from AdoDaoSupport, which has a convenience method 'CreateDbParametersBuilder()'.

        public virtual IList<string> GetCustomerNameByCountryAndCityWithParamsBuilder(string country, string city)
        {

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("Country").Type(DbType.String).Size(15).Value(country);
            builder.Create().Name("City").Type(DbType.String).Size(15).Value(city);
            return AdoTemplate.QueryWithResultSetExtractor(CommandType.Text,
                                                           customerByCountryAndCityCommandText,
                                                           new CustomerNameResultSetExtractor<List<string>>(),
                                                           builder.GetParameters());
        }
        

The implementation of the ResultSetExtractor is shown below.

    internal class CustomerNameResultSetExtractor<T> : IResultSetExtractor<T> where T : IList<string>, new()
    {

        public T ExtractData(IDataReader reader)
        {
            T customerList = new T();
            while (reader.Read())
            {
                string contactName = reader.GetString(0);
                customerList.Add(contactName);
            }
            return customerList;
        }
    }

Internally the implementation of the QueryWithRowCallback and QueryWithRowMapper methods are specializations of the general ResultSetExtractor. For example, the QueryWithRowMapper implementation iterates through the result set, calling the callback method 'MapRow' for each row and collecting the results in an IList. If you have a specific case that is not covered by the QueryWithXXX methods you can subclass AdoTemplate and follow the same implementation pattern to create a new QueryWithXXX method to suit your needs.

The RowCallback is usually a stateful object itself or populates another stateful object that is accessible to the calling code. Here is a sample take from the Data QuickStart

    public class RowCallbackDao : AdoDaoSupport
    {
        private string cmdText = "select ContactName, PostalCode from Customers";

        public virtual IDictionary<string, IList<string>> GetPostalCodeCustomerMapping()
        {
            PostalCodeRowCallback statefullCallback = new PostalCodeRowCallback();
            AdoTemplate.QueryWithRowCallback(CommandType.Text, cmdText,
                                             statefullCallback);

            // Do something with results in stateful callback...
            return statefullCallback.PostalCodeMultimap;
        }
             
    }

The PostalCodeRowCallback builds up state which is then retrieved via the property PostalCodeMultimap. The Callback implementation is shown below

    internal class PostalCodeRowCallback : IRowCallback
    {
        private IDictionary<string, IList<string>> postalCodeMultimap =
            new Dictionary<string, IList<string>>();

        public IDictionary<string, IList<string>> PostalCodeMultimap
        {
            get { return postalCodeMultimap; }
        }

        public void ProcessRow(IDataReader reader)
        {
            string contactName = reader.GetString(0);
            string postalCode = reader.GetString(1);
            IList<string> contactNameList;
            if (postalCodeMultimap.ContainsKey(postalCode))
            {
                contactNameList = postalCodeMultimap[postalCode];
            }
            else
            {
                postalCodeMultimap.Add(postalCode, contactNameList = new List<string>());
            }
            contactNameList.Add(contactName);
        }
    }

The RowMapper lets you focus on just the logic to map a row of your result set to an object. The creation of a IList to store the results and iterating through the IDataReader is handled by the framework. Here is a simple example taken from the Data QuickStart application

    public class RowMapperDao : AdoDaoSupport
    {
        private string cmdText = "select Address, City, CompanyName, ContactName, " +
                             "ContactTitle, Country, Fax, CustomerID, Phone, PostalCode, " +
                             "Region from Customers";


        public virtual IList<Customer> GetCustomers()
        {
            return AdoTemplate.QueryWithRowMapper<Customer>(CommandType.Text, cmdText,
                                                  new CustomerRowMapper<Customer>());
        }
    }

where the implementation of the RowMapper is

    public class CustomerRowMapper<T> : IRowMapper<T> where T : Customer, new()
    {        
        public T MapRow(IDataReader dataReader, int rowNum)
        {
            T customer = new T();
            customer.Address = dataReader.GetString(0);
            customer.City = dataReader.GetString(1);
            customer.CompanyName = dataReader.GetString(2);
            customer.ContactName = dataReader.GetString(3);
            customer.ContactTitle = dataReader.GetString(4);
            customer.Country = dataReader.GetString(5);
            customer.Fax = dataReader.GetString(6);
            customer.Id = dataReader.GetString(7);
            customer.Phone = dataReader.GetString(8);
            customer.PostalCode = dataReader.GetString(9);
            customer.Region = dataReader.GetString(10);
            return customer;
        }
    }

You may also pass in a delegate, which is particularly convenient if the mapping logic is short and you need to access local variables within the mapping logic.

        public virtual IList<Customer> GetCustomersWithDelegate()
        {
            return AdoTemplate.QueryWithRowMapperDelegate<Customer>(CommandType.Text, cmdText,
                        delegate(IDataReader dataReader, int rowNum)
                            {
                                Customer customer = new Customer();
                                customer.Address = dataReader.GetString(0);
                                customer.City = dataReader.GetString(1);
                                customer.CompanyName = dataReader.GetString(2);
                                customer.ContactName = dataReader.GetString(3);
                                customer.ContactTitle = dataReader.GetString(4);
                                customer.Country = dataReader.GetString(5);
                                customer.Fax = dataReader.GetString(6);
                                customer.Id = dataReader.GetString(7);
                                customer.Phone = dataReader.GetString(8);
                                customer.PostalCode = dataReader.GetString(9);
                                customer.Region = dataReader.GetString(10);
                                return customer;
                            });
        }

The QueryForObject method is used when you expect there to be exactly one object returned from the mapping, otherwise a Spring.Dao.IncorrectResultSizeDataAccessException will be thrown. Here is some sample usage taken from the Data QuickStart.

    public class QueryForObjectDao : AdoDaoSupport
    {
        private string cmdText = "select Address, City, CompanyName, ContactName, " +
                     "ContactTitle, Country, Fax, CustomerID, Phone, PostalCode, " +
                     "Region from Customers where ContactName = @ContactName";
        
        public Customer GetCustomer(string contactName)
        {
            return AdoTemplate.QueryForObject(CommandType.Text, cmdText, 
                                              new CustomerRowMapper<Customer>(),
                                              "ContactName", DbType.String, 30, contactName);
        }
    }

There is a family of overloaded methods that allows you to encapsulate and reuse a particular configuration of a IDbCommand object. These methods also allow for access to returned out parameters as well as a method that allows processing of multiple result sets. These methods are used internally to support the classes in the Spring.Data.Objects namespace and you may find the API used in that namespace to be more convenient. The family of methods is listed below.

There is also the same methods with an additional collecting parameter to obtain any output parameters. These are

The IDbCommandCreator callback interface is shown below

	public interface IDbCommandCreator 
	{
	    IDbCommand CreateDbCommand();
	}

The created IDbCommand object is used when performing the QueryWithCommandCreator method.

To process multiple result sets specify a list of named result set processors,( i.e. IResultSetExtractor, IRowCallback, or IRowMapper). This method is shown below

The list must contain objects of the type Spring.Data.Support.NamedResultSetProcessor. This is the class responsible for associating a name with a result set processor. The constructors are listed below.

public class NamedResultSetProcessor  {
  
  public NamedResultSetProcessor(string name, IRowMapper rowMapper) { ... }

  public NamedResultSetProcessor(string name, IRowCallback rowcallback)  { ... } 

  public NamedResultSetProcessor(string name, IResultSetExtractor resultSetExtractor) { ... }

  . . . 

}

The results of the RowMapper or ResultSetExtractor are retrieved by name from the dictionary that is returned. RowCallbacks, being stateless, only have the placeholder text, "ResultSet returned was processed by an IRowCallback" as a value for the name of the RowCallback used as a key. Output and InputOutput parameters can be retrieved by name. If this parameter name is null, then the index of the parameter prefixed with the letter 'P' is a key name, i.e P2, P3, etc.

The namespace Spring.Data.Objects.Generic contains generic versions of these methods. These are listed below

and overloads that have an additional collecting parameter to obtain any output parameters.

When processing multiple result sets you can specify up to two type safe result set processors.

The list of result set processors contains either objects of the type Spring.Data.Generic.NamedResultSetProcessor<T> or Spring.Data.NamedResultSetProcessor. The generic result set processors, NamedResultSetProcessor<T>, is used to process the first result set in the case of using QueryWithCommandCreator<T> and to process the first and second result set in the case of using QueryWithCommandCreator<T,U>. Additional Spring.Data.NamedResultSetProcessors that are listed can be used to process additional result sets. If you specify a RowCallback with NamedResultSetProcessor<T>, you still need to specify a type parameter (say string) because the RowCallback processor does not return any object. It is up to subclasses of RowCallback to collect state due to processing the result set which is later queried.

• object Execute(IDataAdapterCallback dataAdapterCallback) - Execute ADO.NET operations on a IDbDataAdapter object using an interface based callback.

• T Execute<T>(IDataAdapterCallback<T> dataAdapterCallback) - Execute ADO.NET operations on a IDbDataAdapter object using an interface based callback.

• T Execute<T>(DataAdapterDelegate<T> del) - Execute ADO.NET operations on a IDbDataAdapter object using an delegate based callback.

DataTable operations are available on the class Spring.Data.Core.AdoTemplate. If you are using the generic version, Spring.Data.Generic.AdoTemplate, you can access these methods through the property ClassicAdoTemplate, which returns the non-generic version of AdoTemplate. DataTable operations available fall into the general family of methods with 3-5 overloads per method.

DataSet operations are available on the class Spring.Data.Core.AdoTemplate. If you are using the generic version, Spring.Data.Generic.AdoTemplate, you can access these methods through the property ClassicAdoTemplate, which returns the non-generic version of AdoTemplate. DataSet operations available fall into the following family of methods with 3-5 overloads per method.

The following code snippets demonstrate the basic functionality of these methods using the Northwind database. See the SDK documentation for more details on other overloaded methods.

public class DataSetDemo : AdoDaoSupport
{
  private string selectAll = @"select Address, City, CompanyName, ContactName, " +
                              "ContactTitle, Country, Fax, CustomerID, Phone, PostalCode, " +
                              "Region from Customers";


  public void DemoDataSetCreate()
  {
    DataSet customerDataSet = AdoTemplate.DataSetCreate(CommandType.Text, selectAll);

    // customerDataSet has a table named 'Table' with 91 rows

    customerDataSet = AdoTemplate.DataSetCreate(CommandType.Text, selectAll, new string[] { "Customers" });

    // customerDataSet has a table named 'Customers' with 91 rows

}


  public void DemoDataSetCreateWithParameters()
  {
    string selectLike = @"select Address, City, CompanyName, ContactName, " +
                         "ContactTitle, Country, Fax, CustomerID, Phone, PostalCode, " +
                         "Region from Customers where ContactName like @ContactName";

    DbParameters dbParameters = CreateDbParameters();
    dbParameters.Add("ContactName", DbType.String).Value = "M%';
    DataSet customerLikeMDataSet = AdoTemplate.DataSetCreateWithParams(CommandType.Text, selectLike, dbParameters);

   // customerLikeMDataSet has a table named 'Table' with 12 rows
  }

  public void DemoDataSetFill()
  {
    DataSet dataSet = new DataSet();
    dataSet.Locale = CultureInfo.InvariantCulture;
    AdoTemplate.DataSetFill(dataSet, CommandType.Text, selectAll);
  }

Updating a DataSet can be done using a CommandBuilder, automatically created from the specified select command and select parameters, or by explicitly specifying the insert, update, delete commands and parameters. Below is an example, refer to the SDK documentation for additional overloads

public class DataSetDemo : AdoDaoSupport
{
  private string selectAll = @"select Address, City, CompanyName, ContactName, " +
                              "ContactTitle, Country, Fax, CustomerID, Phone, PostalCode, " +
                              "Region from Customers";

  public void DemoDataSetUpdateWithCommandBuilder()
  {
    DataSet dataSet = new DataSet();
    dataSet.Locale = CultureInfo.InvariantCulture;
    AdoTemplate.DataSetFill(dataSet, CommandType.Text, selectAll, new string[]{ "Customers" } );

    AddAndEditRow(dataSet);.

    AdoTemplate.DataSetUpdateWithCommandBuilder(dataSet, CommandType.Text, selectAll, null, "Customers");
  }

  public void DemoDataSetUpdateWithoutCommandBuilder()
  {
    DataSet dataSet = new DataSet();
    dataSet.Locale = CultureInfo.InvariantCulture;
    AdoTemplate.DataSetFill(dataSet, CommandType.Text, selectAll, new string[]{ "Customers" } );

    AddAndEditRow(dataSet);.

    string insertSql = @"INSERT Customers (CustomerID, CompanyName) VALUES (@CustomerId, @CompanyName)";
    IDbParameters insertParams = CreateDbParameters();
    insertParams.Add("CustomerId", DbType.String, 0, "CustomerId");   //.Value = "NewID";
    insertParams.Add("CompanyName", DbType.String, 0, "CompanyName"); //.Value = "New Company Name";

    string updateSql = @"update Customers SET Phone=@Phone where CustomerId = @CustomerId";                
    IDbParameters updateParams = CreateDbParameters();
    updateParams.Add("Phone", DbType.String, 0, "Phone");//.Value = "030-0074322"; // simple change, last digit changed from 1 to 2.
    updateParams.Add("CustomerId", DbType.String, 0, "CustomerId");//.Value = "ALFKI";

    AdoTemplate.DataSetUpdate(dataSet, "Customers",
                                        CommandType.Text, insertSql, insertParams,
                                        CommandType.Text, updateSql, updateParams,
                                        CommandType.Text, null , null);
  }

  private static void AddAndEditRow(DataSet dataSet)
  {
    DataRow dataRow = dataSet.Tables["Customers"].NewRow();
    dataRow["CustomerId"] = "NewID";
    dataRow["CompanyName"] = "New Company Name";
    dataRow["ContactName"] = "New Name";
    dataRow["ContactTitle"] = "New Contact Title";
    dataRow["Address"] = "New Address";
    dataRow["City"] = "New City";
    dataRow["Region"] = "NR";
    dataRow["PostalCode"] = "New Code";
    dataRow["Country"] = "New Country";
    dataRow["Phone"] = "New Phone";
    dataRow["Fax"] = "New Fax";
    dataSet.Tables["Customers"].Rows.Add(dataRow);

    DataRow alfkiDataRow = dataSet.Tables["Customers"].Rows[0];
    alfkiDataRow["Phone"] = "030-0074322"; // simple change, last digit changed from 1 to 2.
  }
}

In the case of needing to set parameter SourceColumn or SourceVersion properties it may be more convenient to use IDbParameterBuilder.

AdoQuery is a reusable, threadsafe class that encapsulates an SQL query. Subclasses must implement the NewRowMapper(..) method to provide a IRowMapper instance that can create one object per row obtained from iterating over the IDataReader that is created during the execution of the query. The AdoQuery class is rarely used directly since the MappingAdoQuery subclass provides a much more convenient implementation for mapping rows to .NET classes. Another implementations that extends AdoQuery is MappingadoQueryWithParameters (See SDK docs for details).

The AdoNonQuery class encapsulates an IDbCommand 's ExecuteNonQuery method functionality. Like the AdoQuery object, an AdoNonQuery object is reusable, and like all AdoOperation classes, an AdoNonQuery can have parameters and is defined in SQL. This class provides two execute methods

This class is concrete. Although it can be subclassed (for example to add a custom update method) it can easily be parameterized by setting SQL and declaring parameters.

An example of an AdoQuery subclass to encapsulate an insert statement for a 'TestObject' (consisting only name and age columns) is shown below

public class CreateTestObjectNonQuery : AdoNonQuery
{
  private static string sql = "insert into TestObjects(Age,Name) values (@Age,@Name)";
   
  public CreateTestObjectNonQuery(IDbProvider dbProvider) : base(dbProvider, sql)
  {
    DeclaredParameters.Add("Age", DbType.Int32);
    DeclaredParameters.Add("Name", SqlDbType.NVarChar, 16);
    Compile();
  }

  public void Create(string name, int age)
  {
     ExecuteNonQuery(name, age);
  }

}

MappingAdoQuery is a reusable query in which concrete subclasses must implement the abstract MapRow(..) method to convert each row of the supplied IDataReader into an object. Find below a brief example of a custom query that maps the data from a relation to an instance of the Customer class.

public class TestObjectQuery : MappingAdoQuery
{
    private static string sql = "select TestObjectNo, Age, Name from TestObjects";
        
    public TestObjectQuery(IDbProvider dbProvider) 
        : base(dbProvider, sql)
    {           
        CommandType = CommandType.Text;
    }

    protected override object MapRow(IDataReader reader, int num)
    {
        TestObject to = new TestObject();
        to.ObjectNumber = reader.GetInt32(0);
        to.Age = reader.GetInt32(1);
        to.Name = reader.GetString(2);
        return to;
    }
}

The AdoNonQuery class encapsulates an IDbCommand 's ExecuteNonQuery method functionality. Like the AdoQuery object, an AdoNonQuery object is reusable, and like all AdoOperation classes, an AdoNonQuery can have parameters and is defined in SQL. This class provides two execute methods

This class is concrete. Although it can be subclassed (for example to add a custom update method) it can easily be parameterized by setting SQL and declaring parameters.

public class CreateTestObjectNonQuery : AdoNonQuery
{
  private static string sql = "insert into TestObjects(Age,Name) values (@Age,@Name)";
   
  public CreateTestObjectNonQuery(IDbProvider dbProvider) : base(dbProvider, sql)
  {
    DeclaredParameters.Add("Age", DbType.Int32);
    DeclaredParameters.Add("Name", SqlDbType.NVarChar, 16);
    Compile();
  }

  public void Create(string name, int age)
  {
    ExecuteNonQuery(name, age);
  }

}

The StoredProcedure class is designed to make it as simple as possible to call a stored procedure. It takes advantage of metadata present in the database to look up names of in and out parameters.. This means that you don't have to explicitly declare parameters. You can of course still declare them if you prefer. There are two versions of the StoredProcedure class, one that uses generics and one that doesn't. Using the StoredProcedure class consists of two steps, first defining the in/out parameter and any object mappers and second executing the stored procedure.

The non-generic version of StoredProcedure is in the namespace Spring.Data.Objects. It contains the following methods to execute a stored procedure

Each of these methods returns an IDictionary that contains the output parameters and/or any results from Spring's object mapping framework. The arguments to these methods can be a variable length argument list, in which case the order must match the parameter order of the stored procedure. If the argument is an IDictionary it contains parameter key/value pairs. Return values from stored procedures are contained under the key "RETURN_VALUE".

The standard in/out parameters for the stored procedure can be set programmatically by adding to the parameter collection exposed by the property DeclaredParameters. For each result sets that is returned by the stored procedures you can registering either an IResultSetExtractor, IRowCallback, or IRowMapper by name, which is used later to extract the mapped results from the returned IDictionary.

Lets take a look at an example. The following stored procedure class will call the CustOrdersDetail stored procedure in the Northwind database, passing in the OrderID as a stored procedure argument and returning a collection of OrderDetails business objects.

    public class CustOrdersDetailStoredProc : StoredProcedure
    {
        private static string procedureName = "CustOrdersDetail";

        public CustOrdersDetailStoredProc(IDbProvider dbProvider) : base(dbProvider, procedureName)
        {           
            DeriveParameters();
            AddRowMapper("orderDetailRowMapper", new OrderDetailRowMapper() );
            Compile();
        }
        
        public virtual IList GetOrderDetails(int orderid)
        {
            IDictionary outParams = Query(orderid);
            return outParams["orderDetailRowMapper"] as IList;
        }

    }

The 'DeriveParameters' method saves you the trouble of having to declare each parameter explicitly. When using DeriveParameters is it often common to use the Query method that takes a variable length list of arguments. This assumes additional knowledge on the order of the stored procedure arguments. If you do not want to follow this loose shorthand convention, you can call the method QueryByNamesParameters instead passing in a IDictionary of parameter key/value pairs.

	Note
	If you would like to have the return value of the stored procedure included in the returned dictionary, pass in true as a method parameter to DeriveParameters().

The StoredProcedure class is threadsafe once 'compiled', an act which is usually done in the constructor. This sets up the cache of database parameters that can be used on each call to Query or QueryByNamedParam. The implementation of IRowMapper that is used to extract the business objects is 'registered' with the class and then later retrieved by name as a fictional output parameter. You may also register IRowCallback and IResultSetExtractor callback interfaces via the AddRowCallback and AddResultSetExtractor methods.

The generic version of StoredProcedure is in the namespace Spring.Data.Objects.Generic. It allows you to define up to two generic type parameters that will be used to process result sets returned from the stored procedure. An example is shown below

    public class CustOrdersDetailStoredProc : StoredProcedure
    {
        private static string procedureName = "CustOrdersDetail";

        public CustOrdersDetailStoredProc(IDbProvider dbProvider) : base(dbProvider, procedureName)
        {           
            DeriveParameters();
            AddRowMapper("orderDetailRowMapper", new OrderDetailRowMapper<OrderDetails>() );
            Compile();
        }
        
        public virtual List<OrderDetails> GetOrderDetails(int orderid)
        {
            
            IDictionary outParams = Query<OrderDetails>(orderid);
            return outParams["orderDetailRowMapper"] as List<OrderDetails>;
        }

    }

You can find ready to run code demonstrating the StoredProcedure class in the example 'Data Access' that is part of the Spring.NET distribution.