Connection Property for Query-based TableAdapter?

[Mark Olbert]

I've just discovered something really annoying: if you drag and drop a stored procedure onto DataSet design surface in VS2005 you
get what >>looks<< like a TableAdapter...only it isn't. Specifically, it doesn't expose a Connection property.

The same thing happens if you create a DataTable object on the DataSet design surface and have it initialize itself from a stored
procedure (rather than from SQL statements). No Connection property!

This is really, really annoying, when it comes time to deploy a website application to a production server: the query-derived
TableAdapters still "point" at the development database. What's worse, the problem doesn't surface until you go to a page on the
production server that uses on of these query-based adapters...and then the page blows up because it can't access the development
database server.

So, several questions:

1) What's the workaround? The only one I've been able to come up with is to make separate production and development DataSets in the
VS2005 project. I guess I could wrap a class around them which returned the "right" one for the active environment. But that sounds
ugly and flaky.

2) Why is there a distinction between TableAdapters derived from stored procs and TableAdapters based on SQL statements?

3) If the answer to #2 is "it's a bug", why wasn't it fixed in VS2005 SP1? (It wasn't; I'm running SP1)

4) When will the fix be made available?

- Mark

 

[Mark Olbert]

Some further info: There is an exposed connection property...but it's marked as internal, and so is only accessible within the
App_Code namespace. Since, by default, all the rest of the website is not within the App_Code namespace it's not accessible, by
default, from within a typical webpage.

So those questions boil down to just one: why mark the property as internal?

- Mark

 

[WenYuan Wang]

Hi Mark,
Thanks for your further information.Now,I understand your main concern is
why the connection property is marked as internal and how to resolve it.
Please don't hesitate to correct me if I have misunderstood anything here.

In genereal, the connection string is read from web.config file in Web
Application (app.config in Winform Appliation). For this reason, we needn't
to access the connection object of TableAdapter. We can change the
connectstring for database by the connectString in web.config (or
app.config).I thnink this is why connection property can be marked as
internal.

Additonally, TableAdapter is generated by VS 2005 IDE. It's not a class in
.net framework 2.0. For this reason, we can do some change to this
TableAdapter according to our need.
if you really want to access TableAdapter's Connection object in Web
Application, you can do the follow steps to achieve this.
1. Creat a ClassLibrary Project in your solution and add it into your
webapplicaiton as reference.
2. in new Class Library Project, create the Typed Dataset and drop the SP
or Table into design surface.
3. Double-Click the [DataSet.Designer.cs] file and found the statement
which defining connection object.

Replace "internall" with "public".
For example:
[Before]
internnal System.Data.SqlClient.SqlConnection Connection {
[After]
public System.Data.SqlClient.SqlConnection Connection {

Save and build the ClassLibrary Project.After that, you can access the
connection of TableAdatper now.

Please feel free to reply me if you have anything unclear. I'm very glad to
assist you.
Hope this helps.
Wen Yuan

 

[Mark Olbert]

WenYuan,

There's an even easier workaround: create a helper class in the App_Code directory which changes the Connection property when you
retrieve a property from the helper class. Since the helper class is in App_Code, it has access to the "internal" property.

Changing code in machine-generated files is something I try to stay away from, having been burned by that too many times in the
past.

- Mark

 

[WenYuan Wang]

Thanks for your workaround, Mark.
Great! This is really an easier way.
Creating a class wrapping the TableAdapter and changing the connection by
this class.

Best regards,
Wen Yuan

 

[Mark Olbert]

An even better way would be to get the VS team to fix the code generator and make the property public.

- Mark

 

[WenYuan Wang]

Thanks, Mark.

I think the reason why this property is stated as internal by VS team is
there is some sensitive information in the connection (such as: database
name, server name, username and password). For this reason, some developers
will not want this connection object could be accessed.

But, however, if you really have a concern on this, we suggest you may
submit this feedback to our product feedback center. Our development team
may communicate with you directly on the issue there:
http://connect.microsoft.com/site/sitehome.aspx?SiteID=210

We are indeed sorry for any inconvenience this may have caused.
Have a great day.
Best regards,
Wen Yuan

 

[koen.roos]

Guys,

I made a blog-post about this subject, see http://knrs.blogspot.com/

cheers,
Koen

 

[koen.roos]

Thanks, Mark.

I think the reason why this property is stated as internal by VS team is
there is some sensitive information in theconnection(such as: database
name, server name, username and password). For this reason, some developers
will not want thisconnectionobject could be accessed.

But, however, if you really have a concern on this, we suggest you may
submit this feedback to our product feedback center. Our development team
may communicate with you directly on the issue there:http://connect.microsoft.com/site/sitehome.aspx?SiteID=210

We are indeed sorry for any inconvenience this may have caused.
Have a great day.
Best regards,
Wen Yuan

Guys,

I made a post-blog about this subject, see: http://knrs.blogspot.com/