Multiple Connection Types for one Designer Generated TableAdapter

I have a Windows Forms application with a DataSet (.xsd) that is currently set to connect to a Sql Ce database. Compact Edition is being used so the users can use this application in the field without an internet connection, and then sync their data at day's end.

I have been given a new project to create a supplemental web interface for displaying some of the same reports as the Windows Forms application so certain users can obtain reports without installing the Windows app.

What I've done so far is create a new Web Project and added it to my current Solution. I have split both the reports (.rdlc) and DataSets out of the Windows Forms project into their own projects so they can be accessed by both the Windows and Web applications. So far, this is working fine.

Here's my dilemma:

As I said before, the DataSets are currently set up to connect to a local Sql Ce database file. This is correct for the Windows app, but for the Web application I would like to use these same TableAdapters and queries to connect to the Sql Server 2005 database. I have found that the designer generated, strongly-typed TableAdapter classes have a ConnectionModifier property that allows you to make the TableAdapter's Connection public. This exposes the Connection property and allows me to set it, however it is strongly-typed as a SqlCeConnection, whereas I would like to set it to a SqlConnection for my Web project.

I'm assuming the DataSet Designer strongly-types the Connection, Command, and DataAdapter objects based on the Provider of the ConnectionString as indicated in the app.config file. Is there any way I can use some generic provider so that the DataSet Designer will use object types that can connect to both a Sql Ce database file AND the actual Sql Server 2005 database?

I know that SqlCeConnection and SqlConnection both inherit from DbConnection, which implements IDbConnection. Relatively, the same goes for SqlCeCommand/SqlCommand:DbCommand:IDbCommand. It would be nice if I could just figure out a way for the designer to use the Interface types rather than the strong types, but I'm hesitant that that is possible.

I hope my problem and question are clear. Any help is much appreciated. Let me know if there's anything I can clarify.

Well, here's an update to what I've experimented with. Maybe it will spark some more ideas for a possible solution. I tried using OleDb connections because the OleDb provider will work for both Compact and Sql Server. I was hoping that by doing this, the designer would generate the objects as OleDbConnection, OleDbCommand, etc. I felt like I was on the right track, but as it turns out, you cannot use OleDb for Sql Ce IN the DataSet Designer. The OleDb connection works when tested in code, but the designer doesn't support it. I just get weird OleDb errors. So at this point I'm stumped.

2009-09-18 19:19:51

here what i ended up doing: 1. I made a copy of the designer.cs file2. I did a source code replace that turned all of SlqCe related objects (adapters, connections, etc.) into Sql objects.3. You could take this a step further and create a macro that runs on prebuild.HTH

unclepaul84 2009-10-28 00:19:41

ansaurus

tags:

views:

answers:

Multiple Connection Types for one Designer Generated TableAdapter

related questions