0

I'm working on an application and am trying to offer the ability to use a "Local" database or a SQL Server database. What I'm trying to work out is the best Local DB format to use and how to write the queries.

At the moment i have been playing with SQL Server CE and it seems to work fine, but I then need to write each code block twice for any queries (once for SQL and once for SQL Server CE). Is there any solutions to this? Is there some way i can just pass a different connection string?

What i have at the moment is a "sub" that i have a check if using local (my.setting.uselocal) and then call either function Query_SQL or Query SQL. I imagine it will get tiring soon to have to have two blocks for each database query?

Any suggestions on how to do this more efficiently?

Cheers

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stephen Pefanis
  • 305
  • 1
  • 3
  • 16
  • You are searching the holy grail of every database developer. One code to rule them all. I suggest to look at ORM (Object Relational Mapper). Some of these libraries are awesome like the one behind SO ([Dapper](https://github.com/StackExchange/dapper-dot-net)) – Steve Jan 23 '17 at 22:11
  • I thought so. It's fairly simple to copy/paste the code and just have to change 4 items to work. But it would be great if there was a simpler way :) – Stephen Pefanis Jan 23 '17 at 22:45
  • Again, the (albeit partial) solution is to divide your code in logical layers. The model that describes your data, the business logic that acts over that data, the UI layer that presents your data and the database layer that interacts with the database. This last part could be handled by an ORM like Dapper and isolate your code from the nasty details of database differences. Search for some examples with dapper and you will understand its power. – Steve Jan 23 '17 at 22:51
  • Just use SQL Server Express (free) locally, and full SQL Server centrally – ErikEJ Jan 24 '17 at 06:51
  • Thanks, I was trying to see if i can provide a solution that didn't require any extra installation on the local machine (i.e. portable). – Stephen Pefanis Jan 24 '17 at 22:08

1 Answers1

0

You could try creating a .sdf file. These can be created programmatically, along with a databse(s)/tables etc in the file.

It's been a long time since I've used them but IIRC you can write read/write with the same tsql syntax. I would have thought it would allow you the same query functions and you can then pass in whatever connection.

user3585124
  • 53
  • 4
  • Thats what i have been working with at this stage, but when using in VB.NET need to have differetn declerations for the connection/command based on SQL or SQLCE. – Stephen Pefanis Jan 23 '17 at 22:44