468,136 Members | 1,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,136 developers. It's quick & easy.

Fetching data from SQL Server

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).

May 31 '06 #1
9 2550
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

<Je*****@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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).

May 31 '06 #2
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.

May 31 '06 #3
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

<Je*****@gmail.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...
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.

May 31 '06 #4
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...

May 31 '06 #5
>>I can't cast it to something generic...

You can't just cast this object returned by Activator.CreateInstance() to an
adapter object?
May 31 '06 #6
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...

May 31 '06 #7
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.

May 31 '06 #8
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

<Je*****@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...
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.

Jun 1 '06 #9
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.

Jun 1 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Srinivasan R | last post: by
reply views Thread by Shujun Huang | last post: by
1 post views Thread by nasirmajor | last post: by
22 posts views Thread by Sandman | last post: by
4 posts views Thread by tokcy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.