An ADO.NET multi-database, multi-tier solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace MultiTier.Example.Data {

	public class CustomersSql : MultiTier.Data.SqlTier {

		public CustomersSql(string connection) : base(connection) {
		}

		public CustomersSql(string connection, string tableName) : base(connection, tableName) {
		}
	
		protected override void ConstructDataAdapter() {
			string strQuery = "";
			SqlCommand cmSelect;
			SqlCommand cmUpdate;
			SqlCommand cmInsert;
			SqlCommand cmDelete;

			//--- Set up the Connection
			InitializeConnection();

			//--- Set up the SELECT Command
			strQuery = @"SELECT CustomerID, CompanyName, ContactName, City, Region, Timestamp
						 FROM Customers
						 ORDER BY CompanyName";
			cmSelect = null;
			cmSelect = new SqlCommand(strQuery, this.Connection);
			cmSelect.CommandType = CommandType.Text;

			//--- Set up the UPDATE Command
			strQuery = @"UPDATE Customers
						 SET CompanyName = @CompanyName , ContactName = @ContactName, City = @City, Region = @Region
						 WHERE CustomerID = @CustomerID
						 AND Timestamp = @Timestamp";
			cmUpdate = null;
			cmUpdate = new SqlCommand(strQuery, this.Connection);
			cmUpdate.CommandType = CommandType.Text;
			cmUpdate.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"));
			cmUpdate.Parameters.Add(new SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"));
			cmUpdate.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 15, "City"));
			cmUpdate.Parameters.Add(new SqlParameter("@Region", SqlDbType.NVarChar, 15, "Region"));
			cmUpdate.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));
			cmUpdate.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.Timestamp, 8, "Timestamp"));

			//--- Set up the INSERT Command
			strQuery = @"INSERT INTO Customers (CompanyName, ContactName, City, Region, CustomerID)
						 VALUES (@CompanyName, @ContactName, @City, @Region, @CustomerID)";
			cmInsert = null;
			cmInsert = new SqlCommand(strQuery, this.Connection);
			cmInsert.CommandType = CommandType.Text;
			cmInsert.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"));
			cmInsert.Parameters.Add(new SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"));
			cmInsert.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 15, "City"));
			cmInsert.Parameters.Add(new SqlParameter("@Region", SqlDbType.NVarChar, 15, "Region"));
			cmInsert.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));

			//--- Set up the DELETE Command
			strQuery = @"DELETE FROM Customers
						 WHERE CustomerID = @CustomerID";
			cmDelete = null;
			cmDelete = new SqlCommand(strQuery, this.Connection);
			cmDelete.CommandType = CommandType.Text;
			cmDelete.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID"));
			//---------------------------------------------------------
			//--- Create and set up the DataAdapter
			//---------------------------------------------------------
			this.DataAdapter = new SqlDataAdapter();
			this.DataAdapter.SelectCommand = cmSelect;
			this.DataAdapter.UpdateCommand = cmUpdate;
			this.DataAdapter.InsertCommand = cmInsert;
			this.DataAdapter.DeleteCommand = cmDelete;
			
			//--- Destroy connection object
			this.Connection = null;
		}
	}
}