Solved

Dual-coding for SQL and SQL CE

Posted on 2013-01-10
6
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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