Fetching data from SQL Server

[Jenden0]

I'm new to C# (and Microsoft in general) so this may be a simple
problem, but I haven't been able to figure it out yet.

I've got a database with a number of different tables and I want the
user to be able to select which table they want, then have that table
pop up in a datagridview. I've figured out how to get it to work if I
specify the table adapter to use and do it all manually, but I'd like
to make a method that will populate the datagridview with data when it
is given the appropriate table name. As it is now the only way I can
get it to work it is having a giant switch statement. I'd like to go
for a much more elegant solution.

I'm using VS 2005 and right now all the database interfaces are
whatever VS auto-generated when adding a new binding source. All the
examples I've seen so far either involve specifying which data adapter
to use at compile time (giving me the big switch statement) or running
all my own SQL queries (which isn't really an ideal solution either).

 

[Nicholas Paldino [.NET/C# MVP]]

Why not just create a SqlCommand with the statement:

"select * from " + table

Then, pass that to a SqlDataAdapter and fill a dataset with it. Once
you have that, you can just bind the grid to the data set and have the grid
auto-generate the columns for you.

Of course, if you are going to update the fields, then that's going to
require more work. Generally though, you could use the SqlCommandBuilder
class to generate the Insert, Update and Delete commands to attach to the
data adapter, passing your dataset back to the adapter for updating.

Hope this helps.

 

[Jenden0]

Well, for writing back the user will have an option. They can either
write back to the database or they can write out to an XML file. I've
gotta go through and look at whether I'll run into synchronization
problems if the database changes after loading data into the
datagridview... The user also has the option to filter/sort some of
the data, not sure if thats going to change which way the
implementation should go though.

 

[Nicholas Paldino [.NET/C# MVP]]

Yes, you will have to handle synchronization on your own. The dataset
is disconnected (that's the model in .NET), and you have to account for when
changes occur to the database when saving back.

 

[Jenden0]

In an effort to make this as generic as possible (I've got multiple
database versions that this may be connecting to) I'm looking at
creating the appropriate tableadapter using the
Activator.CreateInstance() method. I'm creating the object ok, but now
I can't figure out how to get to its fill method. There's no common
interface or parent with the method (they all derive from
System.ComponentModel.Component) so I can't cast it to something
generic, and I can't figure out how to create my own interface since
the fill method (the main one I need right now) takes a table-specific
dataTable...

 

[Mark Newmister]

I can't cast it to something generic...

You can't just cast this object returned by Activator.CreateInstance() to an
adapter object?

 

[Jenden0]

Nope, the adapter objects that are generated by VS inherit from
System.ComponentModel.Component. I ended up going through and putting
together a special interface that had a fill method that took a generic
DataTable (since the fill methods generated took specific tables), then
I had to implement this second fill method for all of the tables. Its
not as clean as I would have liked, but it seems to be working (for
now). All the added code was identicle except for the table names, so
copy/paste with some regular expression replacements made it go pretty
quick. If there's a way to force the compiler to wait till runtime to
check the object type on the adapter that would make things a lot
easier...

 

[Jenden0]

Ok, I figured out what I needed. I found I could use the
x.getType().getMethod("Fill").Invoke(x, table) to get it to work.

 

[Nicholas Paldino [.NET/C# MVP]]

I don't understand why you need this. After all, you can create any old
data adapter and assign the appropriate commands to the properties. You
don't need a typed one.

 

[Jenden0]

I may not, but if I understand how things work correctly to do it using
normal untyped adapters I'd have to write the appropriate SQL
statements every time I created the adapter. That would be all well
and fine if I only had a handful of instances where I need an adapter,
but I'm looking at something like 400 or 500 possible different
instances. Its much easier to let VS auto-generate the majority of
those adapters so that all I have to write are the ones that don't
correspond to a single complete database table.