The next application block available in drop 5 of the MCSF is the Data Access block. At the moment this is a very simple block containing two classes that can be used to eliminate some of the unnecessarily repetitive code that goes into accessing databases. These classes are illustrated in the following screenshot.
The DatabaseService base class provides a series of methods that allow you to execute sql commands and retrieve a dbdatareader. Behind the scenes a database connection is opened and closed for you. As this class is an abstract class it doesn’t provide the implementation details for any specific data source. In the case of a SQL Mobile database, the QLDatabaseService class derives from the DatabaseService class, providing implementation details. If you wanted to, you could also provide an implementation that targets other database types, for example SQL Server.
To work with these classes you need to create an instance of the SqlDatabaseService class, passing in the connection string for the SQL Mobile database you want to access.
Dim ds As DatabaseService = New SqlDatabaseService(“DataSource=””Program FilesDataaccessblocksampleNorthwind.sdf”””)
At the moment the implementation of this class leaves a little to be desired as the constructor takes a single connection string parameter. Since the list of parameters for a SQL Mobile database are fairly limited, it would make sense to provide alternative constructors that could simplify the use of this class. The other recommendation would be to do some checking to validate a particular database file exists. For example, the current constructor for the SqlDatabaseService class is:
public SqlDatabaseService(string connectionString) : base(connectionString){}
Perhaps an alternative constructor could be:
public SqlDatabaseService(string databasePath) : base()
{
if (!System.IO.File.Exists(databasePath))
throw new ApplicationException(“Database doesn’t exist at path ” + databasePath);
ConnectionString = “Data Source =”” + databasePath + “”;”;
}
This could be extended to a second constructor that might accept a password or other parameters as required. For this to work the ConnectionString property would also need to be modified to have a protected set operator (using the new mixed property accessibility feature in .NET CF v2).
After you have a DatabaseService, the first thing that you will want to do is to select some data from the database, this can be done using the ExecuteReader command. This command has a couple of overloads that accept either a DBCommand or a (Select) string. In the following example we illustrate how you can use a sql string with an embedded parameter to select customer rows from the database:
Dim customerSearch As String = “CustomerParameter”
Dim sql As String = “Select * from Customers where [Customer ID] like ” & ds.ParameterName(customerSearch)
Dim para As System.Data.Common.DbParameter = ds.CreateParameter(ds.ParameterName(customerSearch))
Dim reader As Data.Common.DbDataReader = ds.ExecuteReader(sql, para)