Data Repeater & Joined Queries

[GregD]

Normally, when populating controls with data from a database I create a connection and then pass a SQL statement to create a DataReader. I then loop through the DataReader and populate the controls. Because I am creating the SQL statement on the fly I can query the database joining on multiple tables to get just the columns and rows I need.

Now I'm trying to use the Data Repeater control in VS 2010. The Data Repeater is part of the Visual Basic Power Pack add on. If you don't have it, it is similar to the Data Grid. I've never used the data grid because I found it lacking. I need more control on how to display the data. The data repeater seems as though it will work in this particular instance.

So, following the numerous on-line tutorials I created a new data source and a new dataset using the wizards. I pointed the dataset to a table in my data source and then bound the dataset to the data repeater. It works, but the dataset is bound to a table. I can think of very few instances where I would need to dump the data from a table on to form. I need to be able to query the table(s).

I need to be able to pass a SQL statement to the dataset at run-time. This could be a query that had multiple joins in it. It would only need to be read-only. It needs to be able to retain the bindings to the controls or I need to be able to bind the controls at run-time.

No property jumps out at me for changing the dataset SQL statement at run-time. I can't see how to bind controls on the data repeater atrun-time. I can bind a data set to a data repeater at run-time, but I can't figure out how to bind the controls to the dataset fields at run-time.

Any help is appreciated.

Greg

[jmcilhinney]

No property jumps out at me for changing the dataset SQL statement at run-time.

You don't, and that's the whole point. Everything is known at compile time so the system can type everything, i.e. create properties to expose the data as its actual type rather then using Object for everything because it has to be able to support anything. If you want to use any old SQL code at all at run time then you don't use a typed DataSet.

That said, do you really need to be able to execute any old SQL at all? The fat that you're using a DataRepeater suggests to me that you know what columns you want to display at least, and that's all you need to use a typed DataSet.

Your DataTables/TableAdapters each represent the schema of a result set. That result set often matches a table in the database but certainly not always. It can match a view or stored procedure, in which case the wizard can still generate them for you. You are also free to add your own in the designer, allowing you to use any SQL code you like to define the schema. You can also add multiple queries to a TableAdapter, each with different parameters. That allows you to populate a single DataTable using different filters and filter values at run time.

So, assuming that you know all the possible query schema you will want to return and all the possible filters you will want to use, you can use a typed DataSet OK. At run time, you decide which DataTable and TableAdapter you want based on the columns you want to return. You then decide which Fill/FillBy/GetData/GetDataBy method to call based on the filters you want to use. Finally, you call the method and pass the appropriate parameter values based on what rows you want to return.

If you want complete flexibility, i.e. you don't know what columns and rows you might want at design time, then you don't use a typed DataSet.

[GregD]

So you must make a view that displays all of the records and then filter the data set after it is filled? This seems like an inefficient way to work with the data. Isn't this kind of like if a friend wants to borrow my hammer I load every tool I own and take them over to his house so I could loan him one tool.

It is not that I want to change the types of data, and there fore the types of controls at run-time. I want to design the form with a predetermined set of controls for a predetermined number and types of fields. Then, at run time I would create a query on the fly with multiple joins on multiple tables where the only thing that could change would be the where clause. Then create a new dataset based on the query that is already bound to the data repeater.

One where clause might be WHERE UserID = 1001 AND StartDate >= 1/1/2011

Another might be WHERE UserID Between 1001 AND 1005 AND StartDate BETWEEN 1/1/2010 AND 12/31/2010 AND Region = 4

You are only returning the rows you need but the columns are always the same.

[GregD]

If anyone else is out there that is struggling with this like me the key is to use stored procedures not views or tables. Start with a view to create your dataset and bind that to the data repeater (drag and drop columns from the data source window to the data repeater). Then create one or more stored procedures that return the same columns as the view.

Once the SPs are created, open the dataset in the Designer (Edit Dataset with Designer). You will see 2 sections, the dataset on top with the columns listed and the table adapter on the bottom with the default Fill & GetData methods. Right click on the table adapter name and choose Add. When the wizard appears you select your stored procedure instead of making a query or choosing a view.

Give the new store procedure distinct names for the FillBy & GetData methods. When it is added you will see the parameters listed to the right of the Get and Fill names. Now, these new methods will be available to you in code and can be used like this…

Me.ViewTableAdapter.FillByChart(Me.Dataset.TableView, [parameter])
Me.ViewTableAdapter.GetDataByChart([parameter])