Monday, June 25, 2007

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

There are very few simple examples on how to use the Data Access Application Block that comes with the Mobile Client Software Factory.

For those of you who do not know what the Mobile Client Software Factory is, it is a mobile version of the desktop Enterprise Library. You can download the Mobile Client Software Factory and read about it here. You can also see the patterns and practices Developer Centre for more information.

I will show a very simple example on reading and writing to a SQL Server Mobile database using the Mobile Client Software Factory Data Access Application Block.

I have removed XML comments from the sample code to make it easier to read in an HTML page.

1. Firstly you will need to add a reference to your project to include the Microsoft.Practices.Mobile.DataAccess.dll.

This assembly can be found in the default directory:
C:\Program Files\Microsoft Mobile Client Software Factory\ApplicationBlocks\DataAccess\Src\bin\release.

Right click the References folder for your project in Visual Studio and click Add Reference.



2. You need to add the declarations to the header of your class.



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




namespace MyCompany.DAL
{
public class CustomerDB
{
}
}


3. Create an instance of the SqlDatabase class which is part of the Microsoft.Practices.Mobile.DataAccess namespace. A
good place to put this is in a constructor for your data access layer class. ie:-

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

namespace MyCompany.DAL
{
public class CustomerDB
{
#region Fields
private SqlDatabase database = null;
#endregion

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

}
}

4. Implement the IDisposable interface so that when the class is disposed the database is closed. This enables the client to use the using statement.

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;
#endregion

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

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


5. Implement a GetCustomer method which demonstrates reading from the database.

Also note, stored procedures are not supported in SQL Server Mobile, so the next best thing is parameterized queries as demonstrated below. Also note from the code, we use a simple object model to pass back the data and we use the DataReader class to do this for performance reasons. I firmly believe in object models rather than DataSets. I have included the CustomerInfo class within the same namespace as the data layer, in the real world you wouldn't do this, it's there for simplicity.

Read up on ADO.NET Performance for more information on using DataReaders and why they are best practice.

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;
#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;
}
#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 (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;
}
}
}
}

6. Now the last part is implementing a write example and updating an existing customer record. So below I have added the Save method to the CustomersDB class. Its job is to update the customer record or create a new one.

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;
#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;
}

#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 (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;
}
}
}
}

7. Calling our CustomersDB class. You would typically call this class from your business layer. If we wanted to add a new customer to our Customer table, something like the following would work quite nicely:-

using (CustomersDB customersDb = new CustomersDB())
{
CustomerInfo myCustomer = new CustomerInfo();
myCustomer.Name = "Simon Hart";
myCustomer.Address = "London, England";
myCustomer.Phone = "02012736213";
customersDb.Save(myCustomer);
}

Executing the following code would create the customer and automatically close the database as we implemented the IDisposable interface.

Note the very simple object model. Of course this type of model would be much more complex in the real world but I have kept it simple here for demo purposes.

As you can see, using the Data Access Application block makes the code very simple and clean. We need not worry about messy ADO.NET statements.

The only cumbersome part of the code is declaring the parameters - no doubt this could be improved - I'd be interested in any comments.

I believe LINQ will make this even easier when version 3.5 of the Compact Framework is released.

This article is a first part to my talk on the Data Access Application Block. I will be writing some more which will include writing Transactions and collections. The above code is fine when writing single objects, but how do we write multiple records and wrap these in a Trasaction object to enable us to rollback if a particluar object failed for whatever reason? watch this space!

If you like my blog you can subscribe using a news aggregator, see here for more information.

UPDATE: See part 2 to this series here.

1 comment:

Software Factory Grrl said...

Good article, thanks - very thorough. I was reading this too about patterns involved in these factories and it's well worth a look - http://www.xosoftware.co.uk/Articles/WCSFDesignPatterns/