How to organizate custom SQl queres collection

I'm developing an app for Windows Phone with SQlite and have a lot of custom SQL queries. Like

string query = "SELECT distinct(destinations.name) as Destinations 
                FROM destinations, flights 
                WHERE destinations.d_ID = flights.d_ID 
                AND flights.Date = #" + date.ToShortDateString() + "#";

then run:

 var result = (Application.Current as App).db.Query(query);

For working with SQlite i'm using http://dotnetslackers.com/articles/silverlight/Windows-Phone-7-Native-Database-Programming-via-Sqlite-Client-for-Windows-Phone.aspx#s2-introduction-to-sqlite-client-for-windows-phone and theirs DBHelper

I want all Queries will be in one place, so I can quickly change them.

Wanted to ask how to do it correctly?

create one static class

create Enum or Dictionary with queries collection

create some XML or similar file with collection -

Thanks for advise

---

I don't think any of the approaches are valid, for the following reasons:

Create one static class

This is a God object and is considered an anti-pattern, best to stay away from it. It's just going to be a nightmare to maintain.

create Enum or Dictionary with queries collection

Instead of having a God object now, you have a God collection, and are really just implementing the same anti-pattern in a different way.

Additionally, you'll have string keys (or enum keys) and there's not a strong link between the two (what if the dictionary doesn't populate for some reason?).

create some XML or similar file with collection

It could be argued that you're doing the same thing you would be doing with a dictionary; you'd have to key the query somehow and then look it up. It's a very brittle approach.

Possible solution

I recommend that you first abstract out your data layer into logical units. Create a class for data operations which are related.

For example, if you have a few queries and operations that are related to destinations, create an interface that exposes those operations:

public interface IDestinationDataOperations
{
    // Get destinations by date.
    IEnumerable<string> GetDestinationsByDate(DateTime asOf);
}

Then, create a class that implements this which is specific to SQL Lite. Where you want to make the calls, the variable is of the interface type.

The benefits of this are:

If you change the implementation from SQL Lite to some other underlying data store (web service call, JSON REST call, whatever) you only have to change where you populate the interface variable (this is where dependency injection begins to be of use), as all of your calls are against the abstraction

The interface is more easily testable:

You can test the direct implementation against any test data you want

For items that rely on the interface, you can mock the interface any way you like and not have an actual database underneath for testing.

Then, for other data operations, you can wash, rinse, and repeat.

For bonus points, you can separate out the interface into a unit-of-work for writes and a repository for reads, depending on whether what best suits your needs.

链接地址: http://www.djcxy.com/p/95990.html

上一篇: 从Android到WP 7的SQLite DB

下一篇: 如何组织自定义SQl队列收集