The Utter Coolness of Sql Mobile
I've been writing for a while about this Proof of Concept I've been doing at work and today, I just finished a major milestone. The only thing left to do now is Ink Enable the thing and actually, that's the easy part. I can't really divulge the code I'm using but I can definitely describe what we're doing and if anyone is interested in a similar project. Let me walk through what's going on. The client wants to use Yukon as well as Sql Mobile. Behind the scenes, the same API and Data Access Layer Components need to be used. The app will run in three different environments, the Desktop, the Web and the PDA. A factory is used to create the respective connection/command objects so there is an IDBConnection, IDBCommand, IDBDataAdapter and depending on the ProvideType that's passed in, either a SqlClient or a SqlServerCe instance of each respective object will be instantiated and used. How do we determine the ProviderType? There are values specified for the network db server as well as a local db file. By default, an attempt is made to open a connection to the DB server. If that fails, then it's assumed that we are to run locally. This part of the app is a little raw at the moment b/c if we are checking the network connectivtiy w/ each command and in a live app, this wouldn't be a very effective way to go about things. For this app though, we need to prove it works. So to accomplish this, we need to use the same dlls' for the web app, the desktop/tablet app and the PDA app. We need to have each one hooked to the network and 'prove' that we're writing to the network server. Then we need to pull the network cable, and 'prove' that we're writing to a local Sql Mobile file. So are we actually writing an ASP.NET app on the full framework that is using Sql Mobile as a backend? YES! Remember that the exact same API needs to be exposed regardless of which scenario we're running. We have a DTO library that contains the typed datasets we're using, and in each “Get“ instance, we're either returning a typed dataset or a scalar value. Ok, here's the cool part, at the moment, everything is wired up using both Sql Mobile and Yukon - except the PDA portion. I may have to use untyped datasets for it but there's nothing in the requirement specifying the use of typed datasets so if this presents a problem, it will be easy to work around.
Now, if you look at the diagram, a few things might look sort of sucky. For one thing, each class should IMHO implement a common interface and neither of them do. The focus here was to prove that the concept would work though, in the production application, we will definitely be programming to an interface. The other thing is that these classes aren't Remotable. Nothing is really stopping that from happening, but we didn't build it in b/c this is mainly going to be a Tablet PC application and everything will be running locally - no objects will be getting remoted.
A few things you may find interesting. 1) When you set up Replication w/ the “Configure Web Synchronization Wizard”, at the end of the wizard it actually provides the code for you in both C# and VB.NET to handle the Synchronization. This is the exact code that the Synchronization wizard provided for me except for the Try/Catch blocks I added - and the this.LocalDBFile
public void Synchronize()
{
SqlCeReplication repl = new SqlCeReplication();
repl.SubscriberConnectionString = "Data Source=" + this.LocalDBFile;
repl.InternetUrl = SyncURL;
repl.InternetLogin = String.Empty;
repl.InternetPassword = String.Empty;
repl.Publisher = Publisher;
repl.PublisherDatabase = PublisherDatabase;
repl.PublisherLogin = String.Empty;
repl.PublisherPassword = String.Empty;
repl.Publication = "t_offender";
repl.Subscriber = "t_offender_local";
repl.HostName = BadgeId;
repl.ExchangeType = ExchangeType.BiDirectional;
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
try
{
if (File.Exists(this.LocalDBFile))
{
try
{
File.Delete(this.LocalDBFile);
}
catch (System.IO.IOException FileException)
{
Debug.Assert(false, FileException.ToString());
}
}
try
{
repl.AddSubscription(AddOption.CreateDatabase);
repl.Synchronize();
}
catch (SqlCeException SyncException)
{
Debug.Assert(false, SyncException.ToString());
}
}
finally
{
/// Dispose of the Replication object.
repl.Dispose();
}
}
In order to get the Sql Mobile code to run on the full framework (BTW, You CAN use the SqlCeResultSet if you so desire) you need to make sure you have the correct dll.
File Version: 3.0.4138.0Description: Microsoft Sql MobileAssembly Version: 9.0.242.0
As cool as I think the http://www.knowdotnet.com/articles/ceresultset.html is, I'm not seeing any performance benefit when running it on the full framework - but to be honest, I haven't really tried it enought to make a firm statement. On the CF, I noticed a huge difference compared to DataReaders (which makes 0 sense to me and seems totally counterintuitive). Anwyay, the more I play with Sql Mobile, the more I love it. Why anyone would still want to use Access for instance is beyond me!
Because this is a work related project, I've been intentionally vague about the specifics of the implementations. However I'll be more than happy to help you out if you're interested in implementing anything I've mentioned here. There are many different ways you can implement this, and just the design patterns alone provide enough permutations to be substantively different. Also, if you have any problems getting replication set up, please let me know, it can be a real toe stubber up front but after you've done it once, there's really not much to it