Retrieving data from SQL Server CE

Let's revisit the SQLServerPlugin project. You will now explore how you can add functionality to retrieve data from the database you've created into an ADO.NET dataset object. The GetAccountDetails() method's job is to take in the unique identifier of a particular account (AccountGUID) and then retrieve all the relevant account details (including tasks, histories, and file attachments) from the database. It will place the data from each table in a separate Datatable object in the Dataset.

To begin, let's declare some of the variables you will need to use:

public DataSet GetAccountDetails(Guid AccountGUID) {

SqlCeConnection _connection; SqlCeDataAdapter _adapter; SqlCeCommand _command; DataSet _Resultset;

Assuming that a connection to the database has already been opened, you are now ready to create an ADO.NET command object. You can use the createcommand() method in the SqlCeConnection class to generate one. After doing so, assign the SQL statement you want to execute to the command object. Create an SqlCeDataAdapter object to run the command and fill the dataset created earlier with the retrieved data. Take note that you should specify the name of the resulting Datatable object to the Fill() method. This allows you to create multiple tables containing different data in the same Dataset object:

_command = _connection.CreateCommand;

_command.CommandText = "SELECT * FROM Accounts WHERE

_Resultset = new DataSet();

_adapter = new SqlCeDataAdapter(_command);

_adapter.Fill(_Resultset, "Accounts");

Repeat the same thing for the other three related tables, filling in different Datatables in the same DataSet object:

_command.CommandText = "SELECT * FROM AccountTasks WHERE

AccountGUID='" + AccountGUID + "' ORDER BY TaskDate DESC"; _adapter.Fill(_Resultset, "AccountTasks");

_command.CommandText = "SELECT * FROM AccountHistories WHERE

AccountGUID='" + AccountGUID + "' ORDER BY TimeStamp DESC"; _adapter.Fill(_Resultset, "AccountHistories");

_command.CommandText = "SELECT * FROM AccountFiles WHERE AccountGUID='" + AccountGUID + "' ORDER BY AttachmentID DESC";

_adapter.Fill(_Resultset, "AccountFiles");

Dispose of all of the used objects and return the Dataset object from the function.

_adapter.Dispose(); _command.Dispose(); _adapter = null; _command = null; return _resultset;

You will be able to test your data retrieval function in Chapter 3, Building the Mobile Sales Force Module, when you create the rest of the application.

Was this article helpful?

0 0

Post a comment