Solved

Dual-coding for SQL and SQL CE

Posted on 2013-01-10
6
211 Views
Last Modified: 2013-07-04
I'm looking at options to make an application suitable for both MS SQL and SQL CE. The installer will choose whether they are using it single-PC or networked and, if single-PC, I don't want them to have to go through having to install SQL Express.

Problem I'm finding with .NET is that, whilst support for the two DB engines is very good, the SQL and SQLCE objects are different, so everything has to be coded twice, using the two sets of objects.

Can anyone suggest any techniques to build a class that can be told which DB engine to use, but still present a common object to the rest of the application.

Alternatively are there any 3rd party libraries that will do the job?



Jim
0
Comment
Question by:StuckInTheMud
  • 4
  • 2
6 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38763596
can u give an example of a difference?
if you mean something like sqlceCommand and SqlCommand, then you half right.
all u need to duplicate is the DB API layer.
depends on your business, create a shared interface which each of your SQL-type model is implement, so any change should be made in the future won't affect your DB layer.
for instance:
interface IDBLayer
{
DataTable ExecuteProcedure(string procedure, IDictionary<string,string> parameters);
}

public class SqlCEDBLayer:IDBLayer{
DataTable ExecuteProcedure(string constring, string procedure, IDictionary<string,string> parameters){
using (SqlCeConnection connect = new SqlCeConnection(constring))
{
  connect.Open();
SqlCeCommand cmd = connect.CreateCommand();
cmd.CommandText = procedure;
cmd.CommandType = CommandType.StoreProcedure;

foreach (var key in parameters.Keys){
    cmd.Parameters.Add(key);
    cmd.Parameters[key].Value = parameters[key];    
}

SqlCeDataReader rdr = cmd.ExecuteReader();
}
}

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38763610
for start create function for running a query with/without parameters, and running stored procedure with/without parameters for SQL and SQLCE.

that should cover most of your business needs.
0
 

Author Comment

by:StuckInTheMud
ID: 38766356
can u give an example of a difference?

For each operation, I have to code for either SQL or SQLCe objects, code snippit as an example below.
        If mySQLType = "SQL" Then
            Using myConnection As New SqlConnection("{connection string}")
                Using myCommand As New SqlCommand("{SQL}", myConnection)
                    Using myRead As SqlDataReader = myCommand.ExecuteReader()
                        'Do something with the result
                    End Using
                End Using
            End Using
        End If

        If mySQLType = "SQLCe" Then
            Using myConnection As New SqlCeConnection("{connection string}")
                Using myCommand As New SqlCeCommand("{SQL}", myConnection)
                    Using myRead As SqlCeDataReader = myCommand.ExecuteReader()
                        'Do something with the result
                    End Using
                End Using
            End Using
        End If

Open in new window

Problem is that the resultant data (in this example, DataReader) also has to be handled using the relevant object, so pretty much everything that accesses the data has to be dual-coded.

An ideal would be to be able to replace the connection, command, datareader, dataset objects with myConnection, myCommand etc. and have a single type returned irrespective of what actual type is required for physical DB access.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 42

Expert Comment

by:sedgwick
ID: 38767076
Did u read my prev comment?
This is exactly what iv'e demonstrate in the example code i posted.
If u want ill attach the interfaces and classes involved just to make it more clear 4u
0
 

Author Comment

by:StuckInTheMud
ID: 38767794
Hi sedgwick,

Yes I did read your original post and really don't understand what you are referring to, the code you posted appears to me to be a function that runs a command against the SQLCe objects.

I've obviously missed something fundamental!
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 250 total points
ID: 38769096
this was one example of object oriented implementation.
by your post i understand that you are not familiar with this approach.
on a nutshell, the idea is to provide DB access manager for each type using an interface.
each of the sql types implement this interface using their specific objects.
i've attached vb.net dll project which you can use.
you can compile the dll and add it as reference to your project or u can take only the vb files and add them to your project.

to create sql db accessor:
Dim dbAccess As IDBAccessType = DBAccessManager.CreateDBAccess(DBAccessManager.DBType.SQL)

to create sqlCe db accessor:
Dim dbAccess As IDBAccessType = DBAccessManager.CreateDBAccess(DBAccessManager.DBType.SQLCE)

you can call the following execute commands:
1. dbAccess.ExecuteNonQuery()
2. dbAccess.ExecuteScalar()
3. dbAccess.ExecuteReader()

each on the functions accept the following parameters:
connectionString As String -> the connection string
command As String -> the command (either procedure name or sql query)
params As IDictionary(Of String, String) -> dictionary of parameters to pass to the query or procedure, while key is the param name and value is the param value)
isProc As Boolean -> indicates if command is procedure or query

the 4th, ExecuteCommand() is another way to execute any type of command.
you need to pass CommandData object which has the 4 parameters above,
plus one property which is ExecuteCommandType -> type of execute to run which is one of the enum values:
NonQuery, Scalar and Reader.

all 4 execute functions return CommandDataResult which has 2 properties:
DataSet -> which being set if ExecuteReader is called
object -> which being set if ExecuteScalar is called

Calling ExecuteNonQuery returns CommandDataResult equals to Nothing.
You should consider adding exception handling to catch errors for each function.

Change extension to .rar and unrar it to get the project files.
DBAccess.txt
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now