Solved

Dual-coding for SQL and SQL CE

Posted on 2013-01-10
6
206 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

706 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

23 Experts available now in Live!

Get 1:1 Help Now