Solved

Dual-coding for SQL and SQL CE

Posted on 2013-01-10
6
214 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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