Data Abstraction

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I am a SQL server 2000 developer working on a SQL CE app for the first time.
I am used to using server side functionality such as stored procedures ,
views etc.
I am aware that such functionality is not available in SQL CE.

However I don't want to include SQL statements in my application.

I was wondering if there was a recognised means of getting round these SQL
CE limitations and transfering the business logic out of the application?

Any help would be gratefully appreciated.
DD.
 
This is currently a limitation with CE/CF, it doesn't mean you can't move
the logic away from the .Net CF application.

One idea that comes to mind is containing the SQL in text files... You could
then name them according to the name you'd give a function, so that calling
them simply means extracting the text, substituting in any variables you
need before calling the query on the database.

It would move the logic and allow for code reuse, it wouldn't do much for
debugging SQL statements!

Hope that helps.

Dan.
 
Dave,

While there's no way around having SQL in your Compact Framework application
to interact with SQL CE, you can abstract much of the query and data access
code that
appears littered all over many CF applications (including most of the
sammples in the leading
books on the topic). Create a singleton DatabaseManager class that manages
(and caches)
a connection to SQL CE, handles SqlCe errors, and exposes methods to
executequery, executenonquery,
compact, etc and don't forget to add transactional support to these as well.
You can also make a single
constants class that contains all the SQL statements used by your app, so
that
maintenance of your SQL is reduced to a single file. So you'd be writing
code like
this:

SqlCeDataReader dtr =
DatabaseManager.Instance.ExecuteQuery(Constants.sqlCountRecordsToSynch);

While none of this abstracts SQL completely out of yor app, you'll find that
you can achieve
a high degree of reuse across CF projects and improve code manageability by
employing this strategy.
 
Hi Dave,

Here's a way. This will allow you to write/test your sp on Sql Server.
You need to add a table with 2 columns, sp_name and sp_code.
You then make a script/trigger/whatever that will copy all your stored
procedures
with the full code into this table, let's name it table_query.

If you use SSCE I assume at some point you synchronize your device database
with your main database. Simply include the table_query table.

Now, you have to write some code :) Write an ExecuteDataset(string sp_name,
sqlceconnection conn, params []) or something similar. The first thing you'll
have to do is extract the code for the "virtual" sp sp_name and do some
parsing to replace parameters with their values, do some error checking and
stuff. Once you have done this, it's over, you have a string that you can
send as a commandtext to SqlServer CE and get the result in a dataset.

The nice thing here is that you can continue working with Sql Server 2000 as
before and your table_query will be automatically updated on the mobile
device side.

You need to be careful when parsing the original sp code though, as you may
have to replace VARCHARs by NVARCHARs. Check against limitations with SQL CE.

This really made my life a lot easier.

HTH,
Sitar.
 
Back
Top