none
SQLCE and SQL Connection RRS feed

  • Question

  • I'm developing an application where I want to give the user the option to either use a local SQL Server CE database or a shared SQL Server Express database.  I'm using typed datasets and I thought it would be as easy as just replacing the connection string at run-time, but it looks like it will be more difficult than that because all the connection objects in the typed dataset are unique to SQLCE.  It looks like I will have to use two different typed datasets - (1) for connecting to SQLCE and (2) for connecting to SQL - is there a better way to do that?

    Thanks,

    Chuck
    Saturday, December 26, 2009 3:36 PM

Answers

  • Thanks, I've come to that conclusion myself...creating two strongly-typed datasets and switching back-and-forth between the two isn't too bad...I was just surprised that there was so much difference between a SQLServerCE connection and a SQLServer connection.


    Chuck
    • Marked as answer by chuckc3 Wednesday, December 30, 2009 1:13 PM
    Wednesday, December 30, 2009 1:13 PM

All replies

  • Hi Chuck,

    Build your connection in the Project Settings

    - Click on Project from the VS Menu Bar
    - Click on your Project Name Properties
    - Project Properties open click Settings Tab from the Left
    - In the Name Field type a name for your Connection myConnection
    - In the Type Field select (Connection String)
    - Put your cursor in the Value Field then click the ellipsis button to build your connection string.
    - Change your datasource
    - Follow the wizard

    Now you can call your connection from the application settings! You can create 2 connections Local or Shared and let the user to toggle between 2 connections

            Using Conn As New SqlConnection(My.Settings.MyConnection)
            'Your Code Here
            End Using

    John
    Saturday, December 26, 2009 9:11 PM
  • Hi John, I tried something similar to that, but I'm not sure I see how that would work with strongly-typed datasets.  The problem with strongly-typed datasets is that VS2008 optimizes the dataset objects for the type of database (SQLCE or SQL). For example, for a connection object to connect to a SQL Server CE database, it generates a SQLCEConnection not a SQLConnection.  I would think you would get a casting error to try to cast a SQLConnection to a SQLCEConnection. That's the problem I'm wrestling with - I'm trying to use strongly-typed datasets and let VS2008 generate them for me and a lot the code it generates is unique to the type of database. And it's not just the connection object - here are some more differences in the code the typed dataset generates for each database:

           SQL Server CE                                                SQL Server

    System.Data.SqlServerCe.SqlCeDataAdapter      System.Data.SqlClient.SqlDataAdapter
    System.Data.SqlServerCe.SqlCeConnection        System.Data.SqlClient.SqlConnection
    System.Data.SqlServerCe.SqlCeTransaction       System.Data.SqlClient.SqlTransaction
    System.Data.SqlServerCe.SqlCeCommand          System.Data.SqlClient.SqlCommand

    It looks to me that if I use strongly-typed datasets that I will have to have two different versions of the dataset (one for SQL Server CE and one for SQL Server). Do you see any other way around that?

    Thanks,

    Chuck

    • Edited by chuckc3 Sunday, December 27, 2009 10:31 AM Formatting
    Sunday, December 27, 2009 10:28 AM
  • It looks to me that if I use strongly-typed datasets that I will have to have two different versions of the dataset (one for SQL Server CE and one for SQL Server). Do you see any other way around that?
    Correct. The only workaround is to create your connection at run-time as untyped dataset, you still have to incorporate a condition so when the user choose sqlserverce or sqlclient connection, you pull the data from the requested connection.


    John
    • Marked as answer by chuckc3 Wednesday, December 30, 2009 1:13 PM
    • Unmarked as answer by chuckc3 Wednesday, December 30, 2009 1:13 PM
    • Proposed as answer by PPWClev Thursday, July 15, 2010 7:03 PM
    Monday, December 28, 2009 11:23 AM
  • Thanks, I've come to that conclusion myself...creating two strongly-typed datasets and switching back-and-forth between the two isn't too bad...I was just surprised that there was so much difference between a SQLServerCE connection and a SQLServer connection.


    Chuck
    • Marked as answer by chuckc3 Wednesday, December 30, 2009 1:13 PM
    Wednesday, December 30, 2009 1:13 PM
  • The problem, IMHO, is that starting with VS2005, the DataSet designer generated TableAdapter stuff into Typed DataSets and that is just wrong. Again, IMHO, I think DataSets should be totally unaware of where the data is coming from that fills it.

    See my blog post for a rant against TableAdapters: http://geek-goddess-bonnie.blogspot.com/2009/09/tableadapters-are-____.html   Funny, this link got censored ... the underlined part in that link is supposed to be the word c-r-a-p (without the dashes, to get past the censor)

    The one thing I forgot to mention in that blog post (and maybe I'll go update it today) is that as long as you don't open an .xsd with the DataSet Designer, but open it with the XML Editor instead, you won't have to worry about getting all the extra stuff for support of TableAdapters generated in your .xsd (it's not the opening, but the saving of changes that generates the code).

    You should NOT  have to have different DataSet definitions simply to connect to different databases. That is not an acceptable design.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, December 31, 2009 4:12 PM
  • Thanks, Bonnie...I totally agree with you - unfortunately; however, I do want to use the capabilities of the data source wizard to automatically generate a strongly-typed dataset because it saves a lot of work and if I do use that capability, I don't see how to avoid this problem.

    Chuck

    Chuck
    Monday, January 4, 2010 4:18 AM
  • I used a quickie little utility that we wrote that basically does the following:

    1) Connect to your database
    2) Execute a specified stored proc (with empty parameters ... I guess this depends on how you're set up your SPs, but you should be able to figure out something). As an alternative if you don't use SPs, you could have a list of tables that you want to be included in your DataSet and SELECT from all of them in your SQL command.
    3) When you get the DataSet results back from the DataAdapter.Fill(), simply write the schema out to an .xsd file (use the ds.WriteXmlSchema("filename.xsd") method).

    Then, you simply add that .xsd to your DataSet project, right-click on the .xsd and choose "Run Custom Tool". If that option doesn't show up in your context menu, choose Properties instead and type "MSDataSetGenerator" in the Custom Tool property.

    Voila ... no connection strings, no TableAdapter, none of that junk ends up in either your .xsd or your generated typed DataSet.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, January 4, 2010 11:45 PM
  • Thanks, Bonnie...I appreciate your suggestion, but it's outside the scope of what I'm doing to do all that.  I can live with the TableAdapter approach even though I know it isn't optimal.  There's too many other problems I need to solve to devote that kind of time to replacing TableAdapters.

    Chuck

    Chuck
    Friday, January 8, 2010 8:45 AM
  • OK, it's up to you. I'd rather spend a little time writing this quickie little utility that will save you tons of time down the road ... but, maybe that's just me.  And I obviously don't know anything about the scope of what you're doing ... big app, little app, business app, app for personal use, school app ... whichever kind of app you're writing will obviously impact how much extra work you'll want to put into it.

    Keep it in mind for future projects though. =0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, January 9, 2010 2:43 AM
  • I've read through these posts and you've been given some good advice (thanks Bonnie). However, I have a better solution. Instead of trying to have the database hosted on two different databases, how about setting up a local data cache (that the TableAdapters connect to) that synchronizes with a SQL Server 2008 (any Edition) database. This way you can get multiple clients to share data, have a disconnected scenario and still have only one set of TableAdapters. IIRC I've written about the Local Data Cache a couple dozen times (http://www.bing.com/search?q=william+vaughn+local+data+cache&form=QBRE&qs=n). This approach is easy to code and deals with many of the CRUD issues that can make cross-database applications a nightmare.

    hth
    William (Bill) Vaughn -- Mentor, Author, Dad and MVP
    Saturday, February 20, 2010 3:42 AM
    Moderator
  • Hi Bill -- I've not actually used synchronization nor Local Data Cache, but if Chuck's scenario was one of syncing between SQL and SQL CE in a disconnected scenario, then you're definitely pointing him in the correct direction.

    Chuck -- if that was not your intention (synchronization), but simply that some of your users would have SQL Server and some would have SQL CE, then another way to do it is to have a set of DataAccess classes that will allow you to do both easily.

    I wrote a 3-part series on DataAccess in my blog:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity but more flexiblity. However, even just the information in the first post is enough to get you going and is a great starting point for those wishing to have more control over their data access (and to move away from using those confounded TableAdapters) In the first post is a link to my rant against TableAdapters.  ;0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 21, 2010 4:56 PM
  • Bonnie, I agree but in many cases one can use synchronization as an easy back door to a multiuser solution--in a connected or occasionally connected environment. The extra security of having a local (encrypted) store is interesting as well as not being dependant on the server for real-time functionality. It's an approach we used with minicomputers in the 70's that made a lot of sense. When the link to the mainframe went down the clients continued to work. Behind the scenes the applications kept the local client store(s) in sync. The applications didn't have to worry a lot about handing raw UPDATES etc. as that was handled by the sync layer.

    hth
    William (Bill) Vaughn -- Mentor, Author, Dad and MVP
    Sunday, February 21, 2010 9:28 PM
    Moderator
  • Bill, yes I agree with you. At the company where I used to work, we were just starting to design and implement a not-always-connected scenario utilizing SQL CE ... unfortunately (or not, depending on your point of view), I was laid off before much had been done with that, so I don't know what they ended up doing or how well they did it.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, February 22, 2010 1:27 AM
  • Great Tammie ... I'm glad I could help you! =0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, August 14, 2010 9:02 PM