Saturday, June 30, 2007

Mobile Client Software Factory: Data Access Application Block - Part 2

This article is a second part on demonstrating a simple example using the Data Access Application Block. You have haven't read Part 1, then you can do so here: Mobile Client Software Factory: Data Access Application Block - Part 1.

As always, if you haven't already downloaded and installed the Smart Client Software Factory, you can get it here.

In this article I will build on the previous article which will demonstrate adding transaction support to the CustomersDB class. As in the last article I added a Save() method which accepted a CustomerInfo object. In this article, we will create an overloaded Save() method but this method will accept a CustomerCollection object which will hold a collection of CustomerInfo objects that need to be written to the Customers table.

1. Firstly we will need to add the CustomerCollection class. Again we would normally separate this class from the CustomersDB namespace but we have included it here for simplicity.

namespace MyCompany.DAL
{
public class CustomerCollection : CollectionBase
{
#region ctor
public CustomerCollection()
{
}
#endregion

#region Methods
public int Add(CustomerInfo customer)
{
return base.List.Add(customer);
}

public void Insert(int index, GatewayInfo gateway)
{
base.List.Insert(index, gateway);
}

public bool Contains(CustomerInfo Customer)
{
return base.List.Contains(customer);
}

public void Remove(CustomerInfo customer)
{
base.List.Remove(customer);
}
#endregion


#region Indexers
public CustomerInfo this[int index]
{
get
{
return (CustomerInfo)List[index];
}
set
{
List[index] = value;
}
}
#endregion
}
}



2. Now you'll need to add the new overloaded Save() method which accepts the collection of customer objects and wraps this process up in a transaction. There are a couple of things that has changed in the CustomersDB class in this step. The first is the Fields region. I have added a couple new variable objects. The Dispose method has also changed to ensure objects get finalized.

The code is as follows:-

using System;
using System.Collections.Generic;
using Microsoft.Practices.Mobile.DataAccess;
using System.Data.SqlServerCe;
using System.Data.Common;

namespace MyCompany.DAL
{
public class CustomersDB : IDisposable
{
#region Fields
private SqlDatabase database = null;
private DbTransaction transaction = null;
private DbCommand cmd = null;
#endregion

#region ctor
public CustomersDB()
{
database = new SqlDatabase("Data Source = \Storage Card" +
"\MyDatabase.sdf; password=mypassword;encrypt "+
"database = TRUE");
}
#endregion

#region Methods
public CustomerInfo GetCustomer(Guid guid)
{
CustomerInfo customer = null;
if (guid = Guid.Empty)
return customer;

DbCommand cmd =
database.DbProviderFactory.CreateCommand();
DbParameter pname = cmd.CreateParameter();
id.DbType = System.Data.DbType.Guid;
id.ParameterName = "@CustomerID";
id.Value = guid;

cmd.Parameters.Add(id);

//Now for the SQL.
cmd.CommandText =
"select * from Customers where CustomerID = @CustomerID";
DbDataReader reader = null;

try
{
reader = database.ExecuteReader(cmd, null);
if (reader.Read())
customer = GetCustomer(reader);
}
catch (SqlCeException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null)
reader.Close();
cmd.Dispose();
}
return customer;
}

public bool Save(ref CustomerInfo customer)
{
if (customer == null)
throw new ArgumentException
(Properties.Resources.CustMustNotBeNull);

DbCommand cmd =
database.DbProviderFactory.CreateCommand();
DbParameter id = cmd.CreateParameter();
DbParameter address = cmd.CreateParameter();
DbParameter name = cmd.CreateParameter();
DbParameter changeStamp = cmd.CreateParameter();
DbParameter phone = cmd.CreateParameter();

id.DbType = System.Data.DbType.Guid;
id.ParameterName = "@CustomerID";

address.DbType = System.Data.DbType.String;
address.ParameterName = "@Address";
address.Value = customer.Address;

name.DbType = System.Data.DbType.String;
name.ParameterName = "@Name";
name.Value = customer.Name;

phone.DbType = System.Data.DbType.String;
phone.ParameterName = "@Phone";
phone.Value = customer.Phone;

changeStamp.DbType = System.Data.DbType.DateTime;
changeStamp.ParameterName = "@ChangeStamp";
changeStamp.Value = DateTime.Now;

cmd.Parameters.Add(id);
cmd.Parameters.Add(address);
cmd.Parameters.Add(name);
cmd.Parameters.Add(phone);
cmd.Parameters.Add(changeStamp);

int i = 0;
try
{
if (customer.Id == Guid.Empty)
{
//Then we are adding a new
//customer to the database.
Guid guid = Guid.NewGuid();
id.Value = guid;
customer.Id = guid;
cmd.CommandText =
"insert into Customers(CustomerID, Address," +
" Name, ChangeStamp, Phone) " +
"Values(@CustomerID, @Address, @Name, " +
"@ChangeStamp, @Phone)";
}
else
{
//Then we are updating a database.
id.Value = customer.Id;
cmd.CommandText =
"update Customers set Address = " +
"@Address, Name = @Name, " +
"ChangeStamp = @ChangeStamp, "+
"Phone = @Phone where " +
"CustomerID = @CustomerID";
}
i = database.ExecuteNonQuery(cmd, null);
}
catch (SqlCeException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}

//Lets check if we saved the customer ok by
//evaluating the effected rows.
if (i > 0)
return true;
else
return false;
}

public bool Save(CustomerCollection customerCollection)
{
if (customerCollection == null)
throw new ArgumentException
(Properties.Resources.CustCollMustNotBeNull);

cmd =
database.DbProviderFactory.CreateCommand();
DbParameter id = cmd.CreateParameter();
DbParameter address = cmd.CreateParameter();
DbParameter name = cmd.CreateParameter();
DbParameter changeStamp = cmd.CreateParameter();
DbParameter phone = cmd.CreateParameter();

id.DbType = System.Data.DbType.Guid;
id.ParameterName = "@CustomerID";

address.DbType = System.Data.DbType.String;
address.ParameterName = "@Address";

name.DbType = System.Data.DbType.String;
name.ParameterName = "@Name";

phone.DbType = System.Data.DbType.String;
phone.ParameterName = "@Phone";

changeStamp.DbType = System.Data.DbType.DateTime;
changeStamp.ParameterName = "@ChangeStamp";

cmd.Parameters.Add(id);
cmd.Parameters.Add(address);
cmd.Parameters.Add(name);
cmd.Parameters.Add(phone);
cmd.Parameters.Add(changeStamp);
transaction = database.GetConnection().BeginTransaction();
cmd.Transaction = transaction;

foreach (CustomerInfo customer in customerCollection)
{
id.Value = customer.Id;
address.Value = customer.Address;
name.Value = customer.Name;
changeStamp.Value = DateTime.Now;
phone.Value = customer.Phone;

int i = 0;
try
{
if (customer.Id == Guid.Empty)
{
//Then we are adding a new
//customer to the database.
Guid guid = Guid.NewGuid();
id.Value = guid;
customer.Id = guid;
cmd.CommandText =
"insert into Customers(CustomerID, Address," +
" Name, ChangeStamp, Phone) " +
"Values(@CustomerID, @Address, @Name, " +
"@ChangeStamp, @Phone)";
}
else
{
//Then we are updating a database.
id.Value = customer.Id;
cmd.CommandText =
"update Customers set Address = " +
"@Address, Name = @Name, " +
"ChangeStamp = @ChangeStamp, " +
"Phone = @Phone where " +
"CustomerID = @CustomerID";
}
i = database.ExecuteNonQuery(cmd, null);

if (i <= 0) break; } catch (SqlCeException ex) { transaction.Rollback(); throw ex; } catch (Exception ex) { transaction.Rollback(); throw ex; } } } if (i > 0)
{
transaction.Commit();
return true;
}
else
{
transaction.Rollback();
return false;
}
}



#endregion

#region Private helper methods
private CustomerInfo GetCustomer(DbDataReader reader)
{
CustomerInfo customer = new CustomerInfo();
customer.Id = (Guid)reader["CustomerID"];
if (reader["Name"] != DBNull.Value)
customer.Name = (string)reader["Name"];
if (reader["ChangeStamp"] != DBNull.Value)
customer.ChangeStamp =
(DateTime)reader["ChangeStamp"];
if (reader["Address"] != DBNull.Value)
customer.Address = (int)reader["Address"];
if (reader["Phone"] != DBNull.Value)
customer.Phone = (string)reader["Phone"];
return customer;
}
#endregion

#region IDisable members
public void Dispose()
{
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}

if (cmd != null)
{
cmd.Dispose();
cmd = null;
}

if (database != null)
{
database.Dispose();
database = null;
}
}
#endregion
}

public class CustomerInfo
{
#region Fields
private Guid _id = Guid.Empty;
private string _name = string.Empty;
private string _address = string.Empty;
private string _phone = string.Empty;
private Nullable<DateTime> _changeStamp = null;
#endregion

#region Properties
public Guid Id
{
get
{
return _id;
}
set
{
_id = value;
}
}

public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}

public string Address
{
get
{
return _id;
}
set
{
_id = value;
}
}

public string Phone
{
get
{
return _phone;
}
set
{
_phone = value;
}
}

public Nullable<DateTime> ChangeStamp
{
get
{
return _changeStamp;
}
set
{
_changeStamp = value;
}
}
}

public class CustomerCollection : CollectionBase
{
#region ctor
public CustomerCollection()
{
}
#endregion

#region Methods
public int Add(CustomerInfo customer)
{
return base.List.Add(customer);
}

public void Insert(int index, GatewayInfo gateway)
{
base.List.Insert(index, gateway);
}

public bool Contains(CustomerInfo Customer)
{
return base.List.Contains(customer);
}

public void Remove(CustomerInfo customer)
{
base.List.Remove(customer);
}
#endregion


#region Indexers
public CustomerInfo this[int index]
{
get
{
return (CustomerInfo)List[index];
}
set
{
List[index] = value;
}
}
#endregion
}

}


We can use the foreach keyword in the newly added Save method because the CustomerCollection inherits from CollectionBase which implements the IEnumerable interface for us so we don't have to.

The newly added Save method could of course be refactored to make use of the existing Save method as there is some repeated code there. My concentration was on providing an example on the transaction support.

1 comment:

Anonymous said...

hey!,I like yourblog so much! share we have a talk more about your weblog on Yahoo? Looking forward to see you.

-