Dynamic Database Binding in C#

Question

I have a project here that connects to an in-production database and grabs tables and views. The code I've inherited connects to a SQL Server database via a SqlConnection, with the user's provided credentials and the database location they provide. When data is required, it uses the connection and a SQL string to create a SqlDataAdapter, and fills a brand new DataSet. That data then gets displayed after manipulating it, replacing table column names with appropriate display names, that sort of thing.

Problem is, the entire process is slow, and the icing on the cake is that it displays large amounts of data in ListViews, which do not take kindly to being given ten thousand rows of data. For design reasons, we're not going to be breaking into pages - there is a search control - and I can implement a virtual ListView at great effort to simply get back to where I was. I simply think this is the wrong application for ListViews - I'm connecting to a database, and I'm displaying records. Sounds like a job for a DataGridView.

Sadly, it simply won't work. I'm trying to bind the DataGridView to the DataSet I've gotten from the connection code via a DataBinder, but when I fire it up to have a look the data's sitting in the DataSet while the DataGridView is completely empty.

However, if I use the GUI binding on my test database, taking the current database schema and my credentials, lo and behold it works a treat. But I can't use this stuff, because it's simply not flexible enough - I don't want to define a schema in the code that I have to change every time we update the database, and I need access to the connection string, and I don't seem to get that from the TableAdapter it creates.

Am I missing something simple here to make my DataSet/BindingSource solution work? Am I barking up the wrong tree?

Is it even worth fiddling around with binding anyway? All of the binding stuff I can see seems to get me 90% of the way there, but then I can't modify the connection string or sort particular columns the way I want, and it seems to want me to give it a defined schema which is going to break as soon as the database changes - whereas the handwritten code is at least defensively designed and quite flexible. I'm not cutting features, and the slow solution already works - if I have to give up on some of my requirements in order to get it to work, we'll just deal with what we've got.

Answer 1

I am unsure if you cannot change the Query at all, or just in the context of the situation you mentioned at the end of you post.

But I would suggest implementing some sort of paging if you can, and only retreive the rows of the particular page the user of the data is on. This would make a HUGE difference in performance, especially if the result set is as big as you say it is. This would probably be the biggest performance increasing change you could make in my opinion. And you can keep the currently working listview implementation. Besides, even if you had a grid with your own query populating it, you would still need some sort of paging strategy, or that would be slow too.

i just grabbed a random article about implementing but you can find many others

EDIT: In regards to your updated Question, the whole point of my answer was, if it aint broke, dont fix it. If the listview works right now, why change it?

Answer 2

open your dataset in the designer, click on the adapter, and change the connection to public (it defaults to internal), then you can access it

as for the rest of your issues, show us the codez - databinding on a datagridview works fine AFAIK...

Answer 3

It should work fine as long as you have specified the table name (else IIRC the first table is used). Of course, you can simplify things by giving the appropriate DataTable (rather than the DataSet) to the DGV. You might also want to check that auto column-generation is enabled on the DGV. But binding to an ad-hoc DataTable works fine; I use it all the time for examples etc.