Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dual-coding for SQL and SQL CE

Posted on 2013-01-10
6
Medium Priority
?
217 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
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…

715 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