none
Typed DataSet mapped to SqlServerCe AND SqlClient RRS feed

  • Question

  • Hi all,

    I posted this question here yesterday and came back to check today to find it had disappeared. Don't know what happened there.

    Anyways, we have an application that has been developed against an SQL Server database. We also need the same application to run against an SQL Server Ce database for some handheld devices (these will be synced using ADO.NET Sync Services). The reason for this is that the same functionality is required on both the server and the clients. The heart of the application is a typed DataSet that has been primarily built using the built-in designer in VS2008. As this has been built against an SQL Server database, and this the SqlClient provider, it will not run against an SQL Server Ce database, which requires the SqlServerCe provider. What is the best way to get it to work against SQL Server Ce?

    Obviously we would like to avoid having 2 versions of the application so I was thinking of using a provider factory, for example to return either a SqlCommand or a SqlCeCommand through the DbCommand super-type, but this could take a lot of work to implement so I thought I would check with you guys to find out if there are any other options.

    Regards,

    Stephen

    Friday, March 27, 2009 9:37 AM

Answers

  • Well, I'm on a fairly tight deadline so I decided to spend my Saturday figuring this one out. I found that the simplest way was to implement a Provider Factory and to update the designer generated code to use the System.Data.Common classes, such as DbConnection, DbCommand and DbTransaction. I also wrote some logic into the factory to handle the connection strings. So now when the application launches it simply checks for the current app mode (Client or Server) in the App.config file and uses the appropriate connection string and provider classes. One hurdle I had to overcome was to keep was the ability to develop the DataSet using the designer. Obviously the designer has a tendency to undo the search and replace changes that implemented the System.Data.Common classes and reverts the DataAdapters back to using the SqlClient classes. To overcome this I have simply written a Macro called PrepareForProviderFactory that does a search and replace of the designer file. So now we can edit the DataSet using the designer and once were done we can run the Macro to implement the Provider Factory. All in all it took about 7 hours work to get this working correctly. :D
    Saturday, March 28, 2009 5:11 PM

All replies

  • Well, I'm on a fairly tight deadline so I decided to spend my Saturday figuring this one out. I found that the simplest way was to implement a Provider Factory and to update the designer generated code to use the System.Data.Common classes, such as DbConnection, DbCommand and DbTransaction. I also wrote some logic into the factory to handle the connection strings. So now when the application launches it simply checks for the current app mode (Client or Server) in the App.config file and uses the appropriate connection string and provider classes. One hurdle I had to overcome was to keep was the ability to develop the DataSet using the designer. Obviously the designer has a tendency to undo the search and replace changes that implemented the System.Data.Common classes and reverts the DataAdapters back to using the SqlClient classes. To overcome this I have simply written a Macro called PrepareForProviderFactory that does a search and replace of the designer file. So now we can edit the DataSet using the designer and once were done we can run the Macro to implement the Provider Factory. All in all it took about 7 hours work to get this working correctly. :D
    Saturday, March 28, 2009 5:11 PM
  • sroughley,

    Can you share with us some of the main points of the design or parts of the code. In concept, I understand the idea, but I doubt I will be able to implement it myself within seven hours. This is my starting point : http://msdn.microsoft.com/en-us/library/t9f29wbk(VS.80).aspx


    Regards,

    kuklei
    Friday, February 5, 2010 11:12 PM