Below is the "IRemoteUpdatableDataService" interface.

using System;
using System.Data;

namespace RemoteServices
{
/// <summary>
/// Summary description for IRemoteUpdatableDataService.
/// </summary>
public interface IRemoteUpdatableDataService
{
	void Update(ref DataSet dataSet);
}
}

Let's take a brief look in the "Service" assembly.

The class "Customers" derives from "RemoteDataService" and only can retrieve rows from the Customers table in the Northwind database. Below you'll see the method "GetFilledDataTable()" that is overriding the base class method of the same name and implementing the row-retrieval functionality.

public override DataTable GetFilledDataTable(string criteria)
{
	DataTable dataTable = new DataTable("Customers");

	SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
 
	sqlDataAdapter.SelectCommand = this.SelectCommand;
	sqlDataAdapter.SelectCommand.Parameters[0].Value = criteria;  

sqlDataAdapter.Fill(dataTable);

	sqlDataAdapter.Dispose(); 
 
	return dataTable;
}

private SqlCommand SelectCommand
{
get {
SqlCommand sqlCommand = new 
SqlCommand("MilkShakeDotNetGetCustomers",      
Connection.SqlConnection); 

	sqlCommand.CommandType = CommandType.StoredProcedure;
 
	sqlCommand.Parameters.Add("@Criteria", SqlDbType.NVarChar, 50);
 
	return sqlCommand;
}
}

Next we'll see the class Orders and what it can do.

The class Orders derives from "RemoteDataService" and "IRemoteUpdatableDataService". This means that the class can read and write in the Northwind database. The row-retrieval functionality is very similar to the others found in the classes exposed by the "Service" assembly and we won't get focus on that, instead we'll get focus on the update functionality. The following method is the "Orders" class implementation of the "IRemoteUpdatableDataService.Update" method.

public void Update(ref DataSet dataSet)
{
sqlDataAdapter.Update(dataSet);
}

Delete command:

private SqlCommand DeleteCommand
{
get {
SqlCommand sqlCommand = new 
SqlCommand("MilkShakeDotNetDeleteOrder", Connection.SqlConnection); 
	
sqlCommand.CommandType = CommandType.StoredProcedure;
	
sqlCommand.UpdatedRowSource = UpdateRowSource.None;

	sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int, 4, "OrderID");
				
	return sqlCommand;
}
}

Insert command:

private SqlCommand InsertCommand
{
get {
SqlCommand sqlCommand = new 
SqlCommand("MilkShakeDotNetInsertOrder", Connection.SqlConnection); 
	
sqlCommand.CommandType = CommandType.StoredProcedure;
	sqlCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

	SqlParameterCollection sqlParams = sqlCommand.Parameters;

	sqlParams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
	sqlParams.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
	sqlParams.Add("@OrderDate", SqlDbType.DateTime, 8, "OrderDate");

SqlParameter sqlParam = sqlParams.Add("@OrderID", SqlDbType.Int, 4, 
"OrderID");
	
sqlParam.Direction = ParameterDirection.Output; 
				
sqlParam = sqlParams.Add("@Row_Version", SqlDbType.Timestamp, 8, 
"Row_Version");
	
sqlParam.Direction = ParameterDirection.Output;

	return sqlCommand;
}
}

None: Any returned row or parameter is ignored.

FirstReturnedRecord: Returns the first returned row and that row is automatically mapped to the object being updated.

OutputParameters: The output parameters are mapped to the object being updated.

Both: Both first returned row and output parameters are mapped to the object being updated.

The object that I'm talking about could be a DataSet, a DataTable or an array of DataRow objects. We will see how it works later. After set the UpdatedRowSource we begin create the parameters and set their directions. The Direction property of the SqlParameter can be set to one of those members found in the ParameterDirection enum.

Input: The parameter is an input value.

Output: The parameter is an output value.

InputOutput: The parameter is both an input and an output value.

ReturnValue: The parameter is a return value.

Update command:

private SqlCommand UpdateCommand
{
get {
SqlCommand sqlCommand = new 
SqlCommand("MilkShakeDotNetUpdateOrder", Connection.SqlConnection); 
	
sqlCommand.CommandType = CommandType.StoredProcedure;
	sqlCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

	SqlParameterCollection sqlParams = sqlCommand.Parameters;

	sqlParams.Add("@OrderID", SqlDbType.Int, 4, "OrderID");
	sqlParams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
	sqlParams.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
	sqlParams.Add("@OrderDate", SqlDbType.DateTime, 8, "OrderDate");
sqlParams.Add("@Row_Version", SqlDbType.Timestamp, 8, 
"Row_Version");
				
	return sqlCommand;
}
}

Be aware when configuring SqlCommand objects. If your stored proc has a return value this must be the first parameter to be added to the SqlParameterCollection. The order of the parameters in the collection must follow the same order of your stored proc.

There's nothing different with the "UpdateCommand" except for the UpdatedRowSource property that is set to FirstReturnedRecord. We can build command objects for the SqlDataAdapter object using a SqlCommandBuilder object but this approach is not efficient as setting all the command properties of the SqlDataAdapter manually. Depending on the case the performance can be ten to fifteen times more slowly using a SqlCommandBuilder to build the command objects.

Now let's see the Server.exe.

The Server.exe is nothing more than a host to our remote service. On this sample we'll use a Server Activated Object activation type. The host application starts registering a channel that will be used for the communication, later on it will configure our service classes in the .NET Remoting runtime as SingleCall objects. The advantage of a SingleCall object is the scalability offered by the stateless model of these objects. When you call a method on a Single Call object the method is executed and then the object resources are released. Each request to a SingleCall object creates a new and independent object on the server. Let's see a fragment of the code that initializes our service.

tcpChannel = new TcpChannel(2121);
  
ChannelServices.RegisterChannel(tcpChannel);

RemotingConfiguration.RegisterWellKnownServiceType (typeof (Countries),                                                                                            
"CountriesUri", WellKnownObjectMode.SingleCall);

Since our server app is done and ready to receive client requests let's move on to the client side.

The first thing we must do in the client app is create a channel and register it like we did in the server but a little different. See the code of the ConnectToService() Method bellow.

IDictionary props = new Hashtable();
 
props["name"] = "ClientChannel";

tcpChannel = new TcpChannel(props, null, null);
					
channelServices.RegisterChannel(tcpChannel); 

countries = ((RemoteDataService)Activator.GetObject(typeof(RemoteDataService), "tcp://localhost:2121/CountriesUri")); The countries object above is of type RemoteDataService (the Countries class found in the Service.dll is derived from the abstract class RemoteDataService).

I used this approach to show you how to use shared interfaces and abstract classes with .NET Remoting. The great thing about this model is the maintainability of the application. If something goes wrong inside the method GetFilledDataTable() of the Countries class found in the assembly Service.dll after its deployment we only need to fix that problem, recompile the Service.dll and replace the old one. This could be very useful for a server application that serves hundreds of clients because the need to configure client machines after some problem is dramatically reduced since the client apps doesn't know anything about the code implementation found in the Countries class.

Now we need to fill a ComboBox control with a list of countries.

cbxCountries.DataSource = countries.GetFilledDataTable(); cbxCountries.DisplayMember = "Country";

This step is very easy as you can see. We need only to set the DataSource property of the ComboBox with the DataTable returned from the countries.GetFilledDataTable() method and then set the DisplayMember property of the ComboBox to the column we want to see on it.

To display a list of customers from that country on a DataGrid we need only to set the DataSource property of the DataGrid on the event SelectedIndexChanged of the ComboBox as you can see in the following code snippet.

private void cbxCountries_SelectedIndexChanged(object sender, 
System.EventArgs e) 
{	
dataGrid.DataSource = customers.GetFilledDataTable(cbxCountries.Text);
}

When we call the OrdersWindow form a DataSet is built by calling the CreateDataSet() method. Let's see how.

private void CreateDataSet()
{
dataSet = new DataSet("Orders_OrdersDetails");

dataSet.Tables.Add(employees.GetFilledDataTable());
dataSet.Tables.Add(orders.GetFilledDataTable(customerId));
dataSet.Tables.Add(orderDetails.GetFilledDataTable(customerId));
.
.
.

.
.
.
dataSet.Relations.Add("EMPLOYEES_ORDERS", dataSet.Tables[0].Columns[0], 
dataSet.Tables[1].Columns[2]);

dataSet.Relations.Add("ORDERS_ORDERDETAILS", 
dataSet.Tables[1].Columns[0], dataSet.Tables[2].Columns[0]);

tbxOrderID.DataBindings.Add("Text", dataSet, "Orders.OrderID");

tbxOrderDate.DataBindings.Add("Text", dataSet, "Orders.OrderDate");
.
.
.

cbxEmployees.DataSource = dataSet;
cbxEmployees.DisplayMember = "Employees.EmployeeName";
cbxEmployees.ValueMember = "Employees.EmployeeID";
cbxEmployees.DataBindings.Add("SelectedValue", dataSet, 
"Orders.EmployeeID");

dataGrid.SetDataBinding(dataSet, "Orders.ORDERS_ORDERDETAILS");

With the data binding configurations done we can now add other features to the form such as navigation.

Let's see how we can add navigation functionality to the form.

To navigate through the records of the Orders DataTable we can use a CurrencyManager object. To set the CurrencyManager we can do the following.

currencyManagerOrders = ((CurrencyManager)this.BindingContext[dataSet, "Orders"]);

To navigate to the first record in the Orders DataTable.

currencyManagerOrders.Position = 0;

To navigate to the last record in the Orders DataTable.

currencyManagerOrders.Position = currencyManagerOrders.Count - 1;

To navigate to the previous record in the Orders DataTable.

currencyManagerOrders.Position--;

And to navigate to the next record in the Orders DataTable.

currencyManagerOrders.Position++;

With the code navigation done, let's build the code that will allow us insert, update and delete the rows in the Orders table. To insert a row(s) we can use the following code.

currencyManagerOrders.AddNew();

Calling AddNew() method of the currencyManagerOrders object will automatically add a new row to Orders DataTable and prepare the bound controls to the new row.
To edit the row you have only to change the data on the bound controls. After add a new row or edit one, call the method EndCurrentEdit() of the currencyManagerOrders object. You can even cancel the operation at any time by calling the method CancelCurrentEdit() of the currencyManagerOrders object. To mark a row as deleted we can do the following.

((DataRowView)currencyManagerOrders.Current).Delete();

Using the code above will mark the row as a pending deletion.

Ok, now we must submit the cached changes in the client app to the database.

To submit the changes do the following.

DataSet dataSetChanges = dataSet.GetChanges();

((IRemoteUpdatableDataService)orders).Update(ref dataSetChanges);

((IRemoteUpdatableDataService)orderDetails).Update(ref dataSetChanges);

dataSet.Merge(dataSetChanges.Tables[1]);
dataSet.Merge(dataSetChanges.Tables[2]);

foreach (DataRow dataRow in dataSet.Tables[1].Select(null, null, 
DataViewRowState.Added))
dataSet.Tables[1].Rows.Remove(dataRow);

Handling Optimistic Concurrency

To handle optimistic concurrency on this sample I used the ability of SQL Server to handle batch queries and also created a new column called Row_Version. The Row_Version column is of type timestamp, this kind of column have the ability to change its contents every time a row is inserted or updated. So, to handle this type of concurrency using SQL Server as a database the only thing we need to do is capture the new value of the timestamp column every time we insert or update the database. How we can do it? Let's see the stored proc bellow.

CREATE PROCEDURE MilkShakeDotNetInsertOrder
(
	@CustomerID nchar(5),
	@EmployeeID int,
	@OrderDate datetime,
	@OrderID int OUTPUT,
	@Row_Version timestamp OUTPUT
) AS

INSERT INTO Orders(CustomerID, EmployeeID, OrderDate)
VALUES(@CustomerID, @EmployeeID, @OrderDate)

IF (@@ROWCOUNT > 0)
	SELECT @OrderID=OrderID, @Row_Version=Row_Version
	FROM Orders
	WHERE OrderID=@@IDENTITY

private SqlCommand InsertCommand
{
get {
	SqlCommand sqlCommand = new 
SqlCommand("MilkShakeDotNetInsertOrder", Connection.SqlConnection); 
	sqlCommand.CommandType = CommandType.StoredProcedure;
	sqlCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

	SqlParameterCollection sqlParams = sqlCommand.Parameters;

	sqlParams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
	sqlParams.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID");
	sqlParams.Add("@OrderDate", SqlDbType.DateTime, 8, "OrderDate");

	SqlParameter sqlParam = sqlParams.Add("@OrderID", SqlDbType.Int, 4, 
"OrderID");
	sqlParam.Direction = ParameterDirection.Output; 
				
sqlParam = sqlParams.Add("@Row_Version", SqlDbType.Timestamp, 8, 
"Row_Version");
	sqlParam.Direction = ParameterDirection.Output;

	return sqlCommand;
}
}

Testing The Sample

1-Start SQL Server

2-Run the SQL script Change_Northwind_Database.sql with Query Analyzer.

3-Open Visual Studio .NET (final release) and then open MilkShake.NET project.

4-The Server app references RemoteServices project and Service project. The Client app references only RemoteServices project. The Service project references RemoteServices project. Make sure that references are correctly configured.

5-The sample is configured to start both the server app followed by the client app. If this configuration is lost right-click MilkShake.NET solution in "Solution Explorer" and then configure the properties as follows.

6-Now run the sample. To test the optimistic concurrency feature start another client app with Windows Explorer and then change the rows as you wish.

Download MilkShake.NET

Ok, that's all for now... See ya next time…