 |
|
|
|
| ||
|
||
|
|
|
|
Get the sample source code! Overview Following is an overview of the main elements of the ported Data Access Application Block for .NET CF:  Figure 1. Data Access Application Block for .NET CF As shown in Figure 1, the object model is somewhat reduced. The most important differences are:
Sample Building on the sample included with the Data Access Application Block for .NET, the sample shows how to use the Data Access Application Block for .NET CF through a number of examples. This sample application is built for .NET CF with Visual Studio .NET 2003 and it looks like this:  Figure 2. Data Access Application Block for .NET CF Sample First, the Connection String must be entered, and it defaults to the included database. Just as the original sample, the examples use the Northwind sample database, and a version of that database for SQL Server CE is included in the sample. The sample includes the relevant examples as menu options that fill the Results textbox. Let's have a look at the code behind some of the examples. Code To get an idea of how it works, the code for the first example (DataReader, Multiple Rows) looks like this:
SqlCeConnection connection = GetConnection();
string sql = "SELECT ProductID, ProductName, QuantityPerUnit," +
" UnitPrice FROM Products WHERE CategoryID = ?";
SqlCeDataReader dr = SqlCeHelper.ExecuteReader(connection, sql, 1);
string s = string.Empty;
while (dr.Read())
s += dr.GetValue(1) + "\r\n";
resultsTextBox.Text = s;
After the connection is created, the command text is set with a parameter for category
identity. Note that since the managed provider for SQL Server CE (System.Data.SqlServerCe)
does not support named parameters, we need to keep the order of the parameters correct.
The call to the reader takes the connection, the command and the parameters (in this
case only one). Note that the third parameter could be a SqlCeParameter, and
then the call would look like this:
SqlCeDataReader dr = SqlCeHelper.ExecuteReader(connection, sql,
new SqlCeParameter("", 1) );
When the reader is retrieved, the second column (product name) of each row is read and
inserted into the results text box as separate rows. Note that there's no need to
specify command type (text, stored procedure, etc), which have been removed from all
method calls, as only text commands are supported.
The following examples works in similar ways until we get to the update examples. The example that replicates the original is the first (Update Data Source), and the code looks like this:
SqlCeConnection connection = GetConnection();
DataSet ds = new DataSet();
string sql = "SELECT ProductID, ProductName, QuantityPerUnit," +
" UnitPrice FROM Products WHERE CategoryID = ?";
SqlCeHelper.FillDataset(connection, sql, ds,
new string[] { "Products" }, 1);
DataTable table = ds.Tables["Products"];
DataRow addedRow = table.Rows.Add(new object[]
{ DBNull.Value, "New product", DBNull.Value, 10 } );
table.Rows[0]["ProductName"] = "Modified product";
SqlCeCommand insertCommand = SqlCeHelper.CreateCommand(connection,
"INSERT INTO Products (ProductName, UnitPrice) VALUES(?, ?)",
"ProductName:String", "UnitPrice:Currency");
SqlCeCommand updateCommand = SqlCeHelper.CreateCommand(connection,
"UPDATE Products SET ProductName = ? WHERE ProductID = ?",
"ProductName:String", "ProductID:Int32" );
SqlCeCommand deleteCommand = SqlCeHelper.CreateCommand(connection,
"DELETE Products WHERE ProductID = ?", "ProductID:Int32" );
SqlCeHelper.UpdateDataset(insertCommand, deleteCommand,
updateCommand, ds, "Products");
resultsTextBox.Text = "Rows updated";
Just as before, the connection is created, and a DataSet is filled with the same command
as in the first example. Then a row is added and another (the first) is updated. Each of
the commands (insert, update, and delete) is created with the desired parameters. Note
that since parameter types are not retrieved, the data types of each parameter (actually
source column name) have to be specified. This is done by adding both a colon (:) to the
source column name and the name of the type (from the DbType enum). Finally, the update
method (UpdateDataSet) is called to update the database.
As a complement (and not present in the original application block), another overload of the update method (UpdateDataSet) has been provided to simplify for the normal update operations. This overload is used in the next example (Update Data Source (auto)), and the code to use it looks like this:
SqlCeConnection connection = GetConnection();
DataSet ds = new DataSet();
string cols = "ProductID, ProductName, QuantityPerUnit, UnitPrice";
string sql = "SELECT " + cols +
" FROM Products WHERE CategoryID = ?";
SqlCeHelper.FillDataset(connection, sql, ds,
new string[] { "Products" }, 1);
DataTable table = ds.Tables["Products"];
DataRow addedRow = table.Rows.Add(new object[]
{DBNull.Value, "New product", DBNull.Value, 10});
table.Rows[0]["ProductName"] = "Modified product";
SqlCeHelper.UpdateDataset(connection, ds, "Products", cols);
resultsTextBox.Text = "Rows updated";
Here, the commands are automatically generated in the update method (using a
SqlCeCommandBuilder), and the code becomes somewhat simpler. The columns used to
create the commands can be supplied, but there is also an overload that creates all
commands for all columns, and the call would then look like this:
SqlCeHelper.UpdateDataset(connection, ds, "Products");The sample code also includes an example on how to fill a strongly typed DataSet.
SqlCeConnection connection = GetConnection();
ProductDS productDS = new ProductDS();
string sql = "SELECT ProductID, ProductName, QuantityPerUnit," +
" UnitPrice FROM Products WHERE CategoryID = ?";
SqlCeHelper.FillDataset(connection, sql, productDS,
new string[] { "Products" }, 1); // or new SqlCeParameter("", 1) );
resultsTextBox.Text = productDS.GetXml();
Note that even if the native DataSet method to get the XML (GetXml) was
used, the typed DataSet can be used like this:
resultsTextBox.Text = "Product Name: " + productDS.Products[0].ProductName;The typed DataSet (ProductDS) was taked from the original sample, and all the unsupported functionality was manually removed (serialization, etc). If you want to use your own typed DataSets in your applications, you need to generate them in a desktop project (using an XML schema) and remove the unsupported functionality yourself. There is some extra effort to it, but it can definitely be done! For a more details, see the sample source code, and a small apology is in place because my time was not sufficient to update the code documentation (comments) to reflect all changes done when porting the code. Conclusion Many good things available to desktop programmers can be made available to mobile developers with a certain amount of effort. Building on the knowledge brought into creating the original Data Access Application Block (of course, all thanks and credits goes to its creators), this port can hopefully ease data access in your .NET CF applications. Also, if you have been using the desktop version of the application block, you can now leverage that knowledge in building mobile applications. Any comments? |
|||||||||||||||||||||||
 |
||
| ©2001-2009 Christian Forsberg & Andreas Sjöström |
||
