Microsoft: Making a simple process impossible to use in .NET 2.0

[Chester.West2]

In previous versions of .NET (1.0/1.1), I was able to get the easily
able to get a count of items contained in a SQL table by creating a
dataadapter with a select command similar to the following:

SELECT COUNT(*) from <tablename here>

In .NET 2.0, when you run the wizard to you can generate the same table
adapter...with .NET generating for you a partial class.

Works fine....Except if you have multiple tables!.. (unless of course
you don't mind generating a adapter, etc. for each and every table and
adding code to deal with it in whatever code that needs the count)

Why? Simple...Microsoft was smart enough to realize that you might
want to change the select statement....Therefore they made the
commandCollection PRIVATE (i.e. the _commandCollection cannot be
reached).

Now...I could modify the property and it public, or make a public
method....BAD IDEA!...Microsoft uses it smarts to determine if you've
made changes to the generated class...automatically re-generating and
wiping out your modifications for you (THANKS AGAIN MICROSOFT!)

I tried subclassing the partial class, but again it seems as if I still
couldn't get access to the
_commandCollection....although I'm not absolutely sure I'm subclassing
correctly.

DOES ANYBODY HAVE A SOLUTION to this problem ?????(other then creating
a old-style connection object along with a sql data adapter, and
setting things up)


ALSO...While I'm asking...I noticed that Microsoft automatically
generates non-scalable code for you when you use the wizard (i.e. they
generate using SQL Select statements, NOT stored procedures...to which
you can then add methods to call stored procedures...but Microsoft
seems to insure that you get left with code bloat since it doesn't
allow you to delete off the non-stored procedure sql statements).

 

[mabster]

In previous versions of .NET (1.0/1.1), I was able to get the easily
able to get a count of items contained in a SQL table by creating a
dataadapter with a select command similar to the following:

SELECT COUNT(*) from <tablename here>

In .NET 2.0, when you run the wizard to you can generate the same table
adapter...with .NET generating for you a partial class.

Works fine....Except if you have multiple tables!.. (unless of course
you don't mind generating a adapter, etc. for each and every table and
adding code to deal with it in whatever code that needs the count)

I could be wrong, but can't you right-click on the "background" of the
typed-dataset design surface and add arbitrary SQL commands without
requiring a TableAdapter for each one?

I haven't used this, but I've seen it somewhere.

 

[Cor Ligthert [MVP]]

Chester,

I don't see why you need in this to use the command from the tableadapter.
What you want is a command on its own.

The connection you can get very easy by getting that from whatever generated
tableadapter as

\\\
conn = mytableadapter.connection
dim cmd as XXXXXCommand(SELECT COUNT(*) from <tablename here>, conn)
conn.open
myCount =cmd.executescalar
conn.close
///

If you really want to use the commands, than you can use Reflection,
although I find that one of the worst instructions in Net. (For that
commandcollection are on Internet C# samples. I have not yet seen VBNet
samples, maybe are VB.Net programmers keeping it more clean with OO and
don't hack the class).



I hope this helps,

Cor

 

[cw]

Chester,

I don't see why you need in this to use the command from the tableadapter.
What you want is a command on its own.

The connection you can get very easy by getting that from whatever generated
tableadapter as

\\\
conn = mytableadapter.connection
dim cmd as XXXXXCommand(SELECT COUNT(*) from <tablename here>, conn)
conn.open
myCount =cmd.executescalar
conn.close
///

If you really want to use the commands, than you can use Reflection,
although I find that one of the worst instructions in Net. (For that
commandcollection are on Internet C# samples. I have not yet seen VBNet
samples, maybe are VB.Net programmers keeping it more clean with OO and
don't hack the class).



I hope this helps,

Cor

Cor -

The particular reason I need to change commands is that the screen I am
working on allows the end user to indicate both the order in which
items appear in dropdowns as well as well as the order.

I could well indeed generate a SQL connection/command/data adapter
object manually. It would mean more work, and I'd have to go into the
System.Configuration.Settings to get the connection string at this time
(before I change things over to use web services).

However...the point is...WHY SHOULD I HAVE TO DO THIS? If there was a
modification to make the command object public, then it would be a
simple matter to get around this.

Am I missing something?

Developers have enough problems without having Microsoft put out such
lame code...

 

[cw]

I could be wrong, but can't you right-click on the "background" of the
typed-dataset design surface and add arbitrary SQL commands without
requiring a TableAdapter for each one?

I haven't used this, but I've seen it somewhere.

You are correct...you can add SQL commands...both as stored procedures
OR as non-stored procedures. You do this by opening up the dataset,
then right clicking the table adapter and selecting to add more
queries. However....Microsoft will NOT allow you to delete off the
original select statements generated (i.e. the non-stored procedure
select statements for the Select, Insert, Delete, and Update
statements).

I don't know about you...but I do mind code bloat. And it makes for a
maintenance hassle when there is more then one programmer (you can't
tell everyone "Don't use the non-stored procedure generated code"...I
wish you could...but it doesn't work that way).

I would think Microsoft would be smart enough to realize that
scalability is achieved through stored procedures...especially since
they wrote SQL (ohh...sorry I meant Bought the code and modified it!)
and would use this method as the default if they were going to force
one method or another. The old code wizard from the 1.1 version
allowed you to simply specify which method was desired...something that
it appears made things to easy for people to get work done....Enough
said..

Thanks for your input though....

 

[Cor Ligthert [MVP]]

Chester,

Am I missing something?

Not something, a lot.

You were talking about the "Select Count", you cannot use that using an
adapter, you need the executescalar for that.

Therefore I showed you the most easy way there is, you will never find any
shorter code for that. Or maybe did you not believe that what I showed was
all, because there is no connectionstring from the config.sys needed.

The sortorder and selection of a combobox is using the dataview class, which
is included as property in a datatable, there is not any problem to let the
enduser do choose the sortorder using the UI.

Datatable.defaultview.sort = "TheSortCol Asc" ' or desc
Combobox.datasource = datatable.defaultview

You see coding using Net is very simple, maybe are you searching to much to
the difficult ways.

I hope this helps,

Cor

 

[mabster]

You are correct...you can add SQL commands...both as stored procedures
OR as non-stored procedures. You do this by opening up the dataset,
then right clicking the table adapter and selecting to add more
queries. However....Microsoft will NOT allow you to delete off the
original select statements generated (i.e. the non-stored procedure
select statements for the Select, Insert, Delete, and Update
statements).

No - I'm not talking about adding commands to a TableAdapter.

Try this:

Open your DataSet, and right-click somewhere OTHER than on a DataTable
or TableAdapter. ie. Right-click on the "background" of the design surface.

Select "Add|Query" from the popup menu.

This will add a new "QueriesTableAdapter" object which is a central
repository for any old query you want to create.

Does that help?

Matt