One of the new features in the Mobile Line of Business Accelerator 2008 is something the team called “Managed Stored Procedures”. Now the team did not really write and add to SQL Server CE 3.5 the ability to create stored procedures. Instead we looked at what developers were really looking for that they wanted stored procedures.
The basic notion is that you want to separate out your SQL from the code. It turns out that this technique can be done with relative ease using existing feature in Visual Studio. In fact you can do this with Compact Framework 2.0 or 3.5 and VS2005 or VS2008. The trick is in the resource support for CF applications.
The resource designer supports several types of resources including strings, images, icons, audio, files and other. We played around with the resource designer. First we used “Strings” view. While this gave us the ability to embed the SQL as resource strings the approach was not optimal. While you could access the string by code using “StoredProcs.GetInventory” for example. The actual editing of the string was painful since the SQL is entered in the cell of a grid This meant there was no syntax highlighting or testing support i.e. running the SQL against a test database from within the IDE.
Next the team tried just creating files holding the SQL and setting the files as embedded resources. This had the advantage of getting some syntax highlighting along with the ability to test the SQL on some IDE SKUs. Although the testing has to be against SQL Server and not SQL Server CE it still makes creating and debugging the SQL easier. The problem with this approach was how to signal the data layer to retrieve the right SQL file since we no longer could say” StoredProcs. GetInventory”.
The last approach was to use the “File” designer of the resource manager. By dragging and dropping the files we had created into the “File” design view, the team got the best of both worlds - the ability to use “StoredProcs.GetCustomers” and when editing the SQL seeing it in a full screen editor with syntax highlighting and ability to test the SQL.
So now when using ADO.Net, the new data layer in Mobile LOB Accelerator or your own data layer you can replace the string embedded SQL with the contents of a SQL file.
cmd.CommandText = StoredProcs.GetInventory;
I hope you find this way of separating out your SQL from the code useful. Let me know what you think. You can also download the new Mobile Line of Business Accelerator 2008 from Microsoft download site.
Comments