using System;
using System.Data;
using System.Data.OleDb;
namespace MultiTier.Example.Data {
public class CustomersSql : MultiTier.Data.OleDBTier {
public CustomersSql(string connection) : base(connection) {
}
public CustomersSql(string connection, string tableName) : base(connection, tableName) {
}
protected override void ConstructDataAdapter() {
string strQuery = "";
OleDbCommand cmSelect;
OleDbCommand cmUpdate;
OleDbCommand cmInsert;
OleDbCommand cmDelete;
//--- Set up the Connection
InitializeConnection();
//--- Set up the SELECT Command
strQuery = @"SELECT CustomerID, CompanyName, ContactName, City, Region
FROM Customers
ORDER BY CompanyName";
cmSelect = null;
cmSelect = new OleDbCommand(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";
cmUpdate = null;
cmUpdate = new OleDbCommand(strQuery, this.Connection);
cmUpdate.CommandType = CommandType.Text;
cmUpdate.Parameters.Add(new OleDbParameter("@CompanyName", OleDbType.VarWChar, 40, "CompanyName"));
cmUpdate.Parameters.Add(new OleDbParameter("@ContactName", OleDbType.VarWChar, 30, "ContactName"));
cmUpdate.Parameters.Add(new OleDbParameter("@City", OleDbType.VarWChar, 15, "City"));
cmUpdate.Parameters.Add(new OleDbParameter("@Region", OleDbType.VarWChar, 15, "Region"));
cmUpdate.Parameters.Add(new OleDbParameter("@CustomerID", OleDbType.WChar, 5, "CustomerID"));
//--- Set up the INSERT Command
strQuery = @"INSERT INTO Customers (CompanyName, ContactName, City, Region, CustomerID)
VALUES (@CompanyName, @ContactName, @City, @Region, @CustomerID)";
cmInsert = null;
cmInsert = new OleDbCommand(strQuery, this.Connection);
cmInsert.CommandType = CommandType.Text;
cmInsert.Parameters.Add(new OleDbParameter("@CompanyName", OleDbType.VarWChar, 40, "CompanyName"));
cmInsert.Parameters.Add(new OleDbParameter("@ContactName", OleDbType.VarWChar, 30, "ContactName"));
cmInsert.Parameters.Add(new OleDbParameter("@City", OleDbType.VarWChar, 15, "City"));
cmInsert.Parameters.Add(new OleDbParameter("@Region", OleDbType.VarWChar, 15, "Region"));
cmInsert.Parameters.Add(new OleDbParameter("@CustomerID", OleDbType.WChar, 5, "CustomerID"));
//--- Set up the DELETE Command
strQuery = @"DELETE FROM Customers
WHERE CustomerID = @CustomerID";
cmDelete = null;
cmDelete = new OleDbCommand(strQuery, this.Connection);
cmDelete.CommandType = CommandType.Text;
cmDelete.Parameters.Add(new OleDbParameter("@CustomerID", OleDbType.WChar, 5, "CustomerID"));
//---------------------------------------------------------
//--- Create and set up the DataAdapter
//---------------------------------------------------------
this.DataAdapter = new OleDbDataAdapter();
this.DataAdapter.SelectCommand = cmSelect;
this.DataAdapter.UpdateCommand = cmUpdate;
this.DataAdapter.InsertCommand = cmInsert;
this.DataAdapter.DeleteCommand = cmDelete;
//--- Destroy connection object
this.Connection = null;
}
}
}