SqlCe

Gepost op 2005.05.01 | Reacties (0) | Microsoft Internship, Technologie

Instead of making a webservice call each time certain data was needed, the data was stored in the SqlCe database on the Pocket PC, to retrieve when needed. This allowed quickly displaying data after having retrieved it once, while still giving the possibility to retrieve the latest data, and update the local cache with it as well.

To implement this, a Db class was used with the Singleton pattern to provide database access to the local SqlCe engine. A database on the Pocket PC is simply a file on the file system, MediaService.sdf in this case.

In the CheckDb method, the database was created in case it did not exist. This was done with normal SQL queries defining Create Table commands.

The following code made up the base functionality of the Db class:

PLAIN TEXT
C#:
  1. using System;
  2. using System.IO;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.Common;
  6. using System.Data.SqlServerCe;
  7. using System.Collections;
  8.  
  9. namespace MediaService.Pocket {
  10.   public class Db {
  11.     private const String DB_NAME = "MediaService.sdf";
  12.       private static Db instance = null;
  13.  
  14.       public Db() { }
  15.  
  16.       public static Db NewInstance() {
  17.         lock(typeof(Db)) {
  18.           if (instance == null) {
  19.             instance = new Db();
  20.           }
  21.           return instance;
  22.         }
  23.       } /* NewInstance */
  24.  
  25.       private void CheckDB() {
  26.         if (!File.Exists(DB_NAME)) {
  27.           SqlCeConnection conn = null;
  28.           SqlCeTransaction trans = null;
  29.           SqlCeEngine engine = new SqlCeEngine("Data Source = " + DB_NAME);
  30.           engine.CreateDatabase();
  31.           try {
  32.             conn = new SqlCeConnection("Data Source = " + DB_NAME);
  33.             conn.Open();
  34.             SqlCeTransaction trans = conn.BeginTransaction();
  35.  
  36.             SqlCeCommand availableTable = conn.CreateCommand();
  37.             availableTable.Transaction = trans;
  38.             availableTable.CommandText = "CREATE TABLE Available(songId int,
  39.                          songTitle nvarchar(200), songArtist nvarchar(200))";
  40.             availableTable.ExecuteNonQuery();
  41.  
  42.             trans.Commit();
  43.           } catch {
  44.             trans.Rollback();
  45.           } finally {
  46.             if (conn != null && conn.State == ConnectionState.Open) {
  47.               conn.Close();
  48.             }
  49.           }
  50.         }
  51.       } /* CheckDb */

Storing songs in the database was done every time results were returned from the webservice with the following code:

PLAIN TEXT
C#:
  1. private void OnGetSongs(IAsyncResult songsResult) {
  2.   this.availableSongsCache = this.GetService().EndGetSongs(songsResult);
  3.   Db.NewInstance().StoreSongs(this.availableSongsCache);

To store the songs, the table was first emptied, after which the new results were inserted all at once by using the following method:

PLAIN TEXT
C#:
  1. public void StoreSongs(Song[] songs) {
  2.   this.CheckDB();
  3.  
  4.   SqlCeConnection conn = null;
  5.   SqlCeTransaction trans = null;
  6.  
  7.   try {
  8.     conn = new SqlCeConnection("Data Source = " + DB_NAME);
  9.     conn.Open();
  10.     trans = conn.BeginTransaction();
  11.     SqlCeCommand deleteSong = conn.CreateCommand();
  12.     deleteSong.Transaction = trans;
  13.     String deleteSql = "DELETE FROM Available";
  14.     deleteSong.CommandText = deleteSql;
  15.     deleteSong.ExecuteNonQuery();
  16.  
  17.     SqlCeCommand insertSong = conn.CreateCommand();
  18.     String insertSql = "INSERT INTO Available(songId, songTitle, songArtist)
  19.                                               VALUES (?, ?, ?)";
  20.     insertSong.Transaction = trans;
  21.     insertSong.CommandText = insertSql;
  22.  
  23.     foreach (Song song in songs) {
  24.       insertSong.Parameters.Clear();
  25.       insertSong.Parameters.Add("@songId", song.ID);
  26.       insertSong.Parameters.Add("@songTitle", song.Title);
  27.       insertSong.Parameters.Add("@songArtist", song.Artist);
  28.       insertSong.ExecuteNonQuery();
  29.     }
  30.     trans.Commit();
  31.   } catch (SqlCeException ex) {
  32.     trans.Rollback();
  33.     System.Windows.Forms.MessageBox.Show(FormatErrorMessage(ex));
  34.   } finally {
  35.     if (conn != null && conn.State == ConnectionState.Open) {
  36.       conn.Close();
  37.     }
  38.   }
  39. } /* StoreSongs */

Retrieving the songs can be done exactly as with the regular SqlClient classes.

Pocket PC

Gepost op | Reacties (0) | Microsoft Internship, Technologie

On the client-side, a Pocket PC application was used. Since this has no guaranteed connectivity, some additional techniques had to be used to improve the end-user experience.

First of all, when calling a webservice from a Pocket PC, it was possible that the call would take a long time. If this would have been done synchronously, the application would lock up as long as the call was being processed. To prevent this, the call was made asynchronously and a progress bar was displayed.

To achieve this, a Timer was used from the System.Threading class, to update the progress bar when it was visible. This caused the timer to run on a different thread from the application, and make call-backs at certain intervals to update the user interface containing the progress bar.

The following code was used to easily start and stop the progress bar:

PLAIN TEXT
C#:
  1. using System;
  2. using System.Threading;
  3.  
  4. namespace MediaService.Pocket {
  5.   public class MediaForm : System.Windows.Forms.Form {
  6.     private System.Threading.Timer progressTimer;
  7.     private OpenNETCF.Windows.Forms.ProgressBarEx asyncProgress;
  8.     private System.Windows.Forms.Label asyncLabel;
  9.  
  10.     public MediaForm(Int32 userId, String authTicket) {
  11.       TimerCallback progressDelegate = new TimerCallback(this.UpdateProgress);
  12.       this.progressTimer = new System.Threading.Timer(progressDelegate, null,
  13.                                           Timeout.Infinite, Timeout.Infinite);
  14.     } /* MediaForm */
  15.  
  16.     private void StartProgress(ProgressEnum progressType) {
  17.       // Reset progressbar and show
  18.       this.asyncProgress.Value = this.asyncProgress.Minimum;
  19.       this.asyncProgress.Visible = true;
  20.       this.asyncLabel.Visible = true;
  21.       this.asyncLabel.Text = "Retrieving Content";
  22.       this.progressTimer.Change(0, 100);
  23.     } /* StartProgress */
  24.  
  25.     protected void UpdateProgress(Object state) {
  26.       if (this.asyncProgress.Value + 1> this.asyncProgress.Maximum) {
  27.         this.asyncProgress.Value = this.asyncProgress.Minimum;
  28.       } else {
  29.         this.asyncProgress.Value++;
  30.       }
  31.     } /* UpdateProgress */
  32.  
  33.     private void StopProgress() {
  34.       this.progressTimer.Change(Timeout.Infinite, Timeout.Infinite);
  35.       this.asyncProgress.Visible = false;
  36.       this.asyncLabel.Visible = false;
  37.     } /* StopProgress */

After the progress bar was started, an asynchronous call was made to the webservice, preventing the application to lock up, using the following syntax:

PLAIN TEXT
C#:
  1. AsyncCallback callBack = new AsyncCallback(this.OnGetSongs);
  2. IAsyncResult songsResult = this.GetService().BeginGetSongs(callBack, null);

This started the call to the webservice on a different thread, and when the webservice call finished, it called back to the OnGetSongs method in this case. In this method, the results were retrieved and the user interface was updated.

PLAIN TEXT
C#:
  1. private void OnGetSongs(IAsyncResult songsResult) {
  2.   this.availableSongsCache = this.GetService().EndGetSongs(songsResult);
  3.   if (this.InvokeRequired()) {
  4.     this.Invoke(new EventHandler(this.UpdateAvailableSongs));
  5.   } else {
  6.     this.UpdateAvailableSongs(this, System.EventArgs.Empty);
  7.   }
  8. } /* OnGetSongs */

It was possible that the callback occurred from a different thread. In that case it was not possible to update the user interface, since the thread did not own the form controls. To detect if the callback occurred on another thread or not, the following code was used:

PLAIN TEXT
C#:
  1. namespace MediaService.Pocket {
  2.   public class MediaForm : System.Windows.Forms.Form {
  3.     private readonly Thread formThread = Thread.CurrentThread;
  4.  
  5.     private Boolean InvokeRequired() {
  6.       return !this.formThread.Equals(Thread.CurrentThread);
  7.     } /* InvokeRequired */

If the callback happened on another thread, the Invoke method had to be used to handle the update of the user interface on the thread that owned the interface. For this reason, the method updating the interface had to have the following signature:

PLAIN TEXT
C#:
  1. private void UpdateAvailableSongs(object sender, EventArgs e) {

At this point, it was possible to make a webservice call without locking the user interface, and informing the user something is going on thanks to the progress bar.

Data Layer – SqlServer

Gepost op 2005.04.25 | Reacties (0) | Microsoft Internship, Technologie

After having implemented a data layer in the Data project, it was time to make a real data implementation. A Sql Server 2000 implementation was the default data source, located in the Data.SqlServer project.

Enterprise Library was used to provide the data access to Sql Server. This contained a Data Access Application Block, which allows configuring the connection string through the Enterprise Library Configuration tool.

A reference to Microsoft.Practices.EnterpriseLibrary.Data was needed, together with the Configuration and Common assemblies of Enterprise Library.

Through the Enterprise Library Configuration tool, an existing App.config was loaded, where the Data Access Application Block was added. The database and server values had to be configured to the actual server being used, together with the database containing the data. Additional connection string properties could be added as well, for example, the Integrated Security property, which is set to True.

After saving this file, it was possible to create a data implementation for each Accessor interface previously defined in the Data project, as for example this code:

PLAIN TEXT
C#:
  1. using System;
  2. using System.Data;
  3. using System.Collections;
  4.  
  5. using MediaService.Logging;
  6. using MediaService.Objects;
  7. using MediaService.Data.Accessors;
  8.  
  9. using Microsoft.Practices.EnterpriseLibrary.Data;
  10. using Microsoft.Practices.EnterpriseLibrary.Logging;
  11.  
  12. namespace MediaService.Data.SqlServer {
  13.   public class SongDataAccessor: ISongDataAccessor {
  14.   } /* SongDataAccessor */
  15. } /* MediaService.Data.SqlServer */

Thanks to the Enterprise Library Data Access Application Block, the Sql Server implementation used best practices from the Microsoft Patterns & Practices group, which followed Microsoft guidelines and were optimized for performance.

To get an array of objects from the database, a new Database object had to be created, after which a stored procedure was wrapped, called and read from to get for example Song objects. This was done with the following code:

PLAIN TEXT
C#:
  1. public Song[] GetSongs() {
  2.   Database db = DatabaseFactory.CreateDatabase("MediaServiceSqlServer");
  3.  
  4.   DBCommandWrapper dbCommandWrapper =
  5.                         db.GetStoredProcCommandWrapper("GetSongs");
  6.  
  7.   Logger.Write("Retrieving songs.", Category.SqlServer,
  8.                 Priority.Lowest, 1, Severity.Information);
  9.  
  10.   ArrayList songs = new ArrayList();
  11.   using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper)) {
  12.     while (dataReader.Read()) {
  13.       songs.Add(new Song(dataReader.GetInt32(0), dataReader.GetString(1),
  14.                          dataReader.GetString(2), dataReader.GetString(3),
  15.                          dataReader.GetString(4), dataReader.GetString(5),
  16.                          dataReader.GetString(6), dataReader.GetInt32(7),
  17.                          dataReader.GetInt32(8), dataReader.GetInt32(9)));
  18.     }
  19.   }
  20.  
  21.   Logger.Write(String.Format("Retrieved {0} {1}.", songs.Count,
  22.                              (songs.Count == 1) ? "song" : "songs"),
  23.                Category.SqlServer, Priority.Lowest, 1, Severity.Information);
  24.  
  25.   return (Song[])songs.ToArray(typeof(Song));
  26. } /* GetSongs */

Updating an item by using a stored procedure which uses parameters, was done by using the following code:

PLAIN TEXT
C#:
  1. public void UpdateSongPlayCount(Int32 songId) {
  2.   Database db = DatabaseFactory.CreateDatabase("MediaServiceSqlServer");
  3.  
  4.   DBCommandWrapper dbCommandWrapper =
  5.                         db.GetStoredProcCommandWrapper("UpdateSongPlayCount");
  6.   dbCommandWrapper.AddInParameter("@songId", DbType.Int32, songId);
  7.  
  8.   Logger.Write(String.Format("Updating play count for song: {0}.", songId),
  9.                Category.SqlServer, Priority.Lowest, 1, Severity.Information);
  10.  
  11.   try {
  12.     db.ExecuteNonQuery(dbCommandWrapper);
  13.   } catch (Exception ex) {
  14.     Logger.Write(String.Format("Failed to update play count for song: {0}.
  15.                                 Error: {1}", songId, ex.ToString()),
  16.                  Category.SqlServer, Priority.Highest, 1, Severity.Error);
  17.   }
  18. } /* UpdateSongPlayCount */

Using stored procedures made it possible to have another layer of abstraction. This made it easy changing an existing stored procedure to keep track of statistics, without having to change any code of the implementation. At the same time, using stored procedures also protected against Sql Injection attacks. After all Accessors were implemented, it was possible to use this implementation by deploying the SqlServer dll and selecting it as data source.

Data Layer

Gepost op 2005.04.24 | Reacties (0) | Microsoft Internship, Technologie

Any application using data benefits from having a separate data layer. This enables the administrator to select which data source to use. It also makes your application have an advantage, making it easier to sell.

Besides from the advantages for the end-users, it’s also best practices to separate the data layer from your presentation and business logic layer.

To provide the data layer to the application a Data project was added. The layers above the data layer never accessed the real data implementations, but worked with objects which implemented certain data interfaces. This way, it was possible to define all possible data related methods in an interface and afterwards implement them in a real implementation.

A logical grouping was applied when creating the interfaces, starting from a generic IDataAccessor from which every other interface inherited from.

PLAIN TEXT
C#:
  1. using System;
  2.  
  3. namespace MediaService.Data.Accessors {
  4.   public interface IDataAccessor {
  5.   } /* IDataAccessor */
  6. } /* MediaService.Data.Accessors */

One of the logical sections was for example everything related to Song objects:

PLAIN TEXT
C#:
  1. using System;
  2.  
  3. using MediaService.Objects;
  4.  
  5. namespace MediaService.Data.Accessors {
  6.   public interface ISongDataAccessor: IDataAccessor {
  7.     Song[] GetSongs();
  8.     Song[] GetQueue();
  9.     Song[] GetMostPlayed(Int32 numberOfSongs);
  10.     Song[] GetMostPopular(Int32 numberOfSongs);
  11.   } /* ISongDataAccessor */
  12. } /* MediaService.Data.Accessors */

Since the other projects did not have a reference to the real data implementations, but only to the Data project, this project had to take care of loading the correct implementation. Loading the correct class in the real implementation is done by using factories. For every Accessor interface a factory exists, returning an instance of the data implementation, using the following code:

PLAIN TEXT
C#:
  1. using System;
  2. using MediaService.Data.Accessors;
  3.  
  4. namespace MediaService.Data.Factory {
  5.   internal class SongFactory: Factory {
  6.     internal static ISongDataAccessor Create() {
  7.       return Factory.Create(Accessor.Song) as ISongDataAccessor;
  8.     } /* Create */
  9.   } /* SongFactory */
  10. } /* MediaService.Data */

In the Data project, there was one Factory class, responsible for loading the correct assembly containing the data implementation and instantiating the correct Accessor class. This was done by using Reflection together with Configuration to retrieve the location. The location consisted out of the class name and the assembly name, separated by a comma, as for example the location for the SongDataAccessor:

PLAIN TEXT
XML:
  1. <songDataAccessor>
  2.   MediaService.Data.SqlServer.SongDataAccessor,MediaService.Data.SqlServer
  3. </songDataAccessor>

This location data was retrieved by configuration, after which it was separated into assembly and class parts and loaded with Reflection with the following code:

PLAIN TEXT
C#:
  1. using System;
  2. using System.Reflection;
  3.  
  4. using MediaService.Configuration;
  5. using MediaService.Data.Accessors;
  6.  
  7. using Microsoft.Practices.EnterpriseLibrary.Configuration;
  8.  
  9. namespace MediaService.Data.Factory {
  10.   internal enum Accessor {
  11.     Song
  12.   } /* Accessor */
  13.  
  14.   internal class Factory {
  15.     internal static IDataAccessor Create(Accessor accessorType) {
  16.       DatabaseData configData = LoadConfiguration();
  17.  
  18.       if (configData == null) {
  19.         throw new ApplicationException("Could not load configuration.");
  20.       }
  21.  
  22.       String blockToLoad = String.Empty;
  23.       switch (accessorType) {
  24.         case Accessor.Song: blockToLoad = configData.SongDataAccessor; break;
  25.       }
  26.  
  27.       if (blockToLoad == String.Empty) {
  28.         throw new ApplicationException(String.Format(
  29.                   "Type entry not found for {0}.", accessorType.ToString()));
  30.       }
  31.  
  32.       Int32 index = blockToLoad.IndexOf(",");
  33.       string typeToLoad = blockToLoad.Substring(0,index);
  34.       string assemblyToLoad = blockToLoad.Substring(typeToLoad.Length + 1,
  35.                               blockToLoad.Length - typeToLoad.Length - 1);
  36.       return (IDataAccessor)Assembly.Load(
  37.                             assemblyToLoad).CreateInstance(typeToLoad);
  38.     } /* Create */
  39.  
  40.     private static DatabaseData LoadConfiguration() {
  41.      ConfigurationManager.ClearSingletonSectionCache("databaseConfiguration");
  42.      return ConfigurationManager.GetConfiguration(
  43.                            "databaseConfiguration") as DatabaseData;
  44.     } /* LoadConfiguration */
  45.   } /* Factory */
  46. } /* MediaService.Data.Factory */

All of the Factories were marked internal because they are just meant for internal workings of the data layer, while all Accessors remain public because they had to be accessible to implement in the real data implementation.

Besides the Accessor interfaces, the Data project also exposed one public class, named Dalc. This class contained static properties for each logical data section, returning an instantiated Accessor from the configured data source.

PLAIN TEXT
C#:
  1. using System;
  2.  
  3. using MediaService.Data.Accessors;
  4. using MediaService.Data.Factory;
  5.  
  6. namespace MediaService.Data {
  7.   public class Dalc {
  8.     public static ISongDataAccessor Song {
  9.       get { return SongFactory.Create();  }
  10.     } /* Song */
  11.   } /* Dalc */
  12. } /* MediaService.Data */

After this, it was possible to access data by adding a reference to the Data project, adding a real data implementation assembly to the deployed location and configuring it. For example, the following code retrieved all songs from the data source:

PLAIN TEXT
C#:
  1. using MediaService.Objects;
  2. using MediaService.Data;
  3. using MediaService.Data.Accessors;
  4.  
  5. namespace MediaService.Web {
  6.   public class Media {
  7.     public Song[] GetSongs() {
  8.       return Dalc.Song.GetSongs();
  9.     } /* GetSongs */

With this data layer, all details about data access are contained in the real data implementations, while nowhere else there is specific data source code. The entire application works on data objects, which implement the data interfaces, while under the hood, the correct data source is selected through the configuration file.

« Oudere posts