When using a Repeater to create multiple GridViews, how to use an ObjectDataSource for each Gridview and add a SelectParameter to it?

Question

So, I'm trying to use a Repeater to create multiple Gridviews. (And I'm not all that well versed in ASP.NET.) But in all of the examples I've seen, within the ItemDataRowBound, individual SQL select statements are being used to provide the datasource.

I'm trying to stay within the existing framework of the application, as it has other UserControls/pages that are built to create similar-looking and functioning Gridviews. The Gridviews are all built as a result of user text search results of the datasources. And they all use ObjectDataSources defined in their controls. So it would be helpful if I could use ObjectDataSources instead, to source my dynamic Gridviews.

I tried creating each ObjectDataSource dynamically within the ItemDataRowBound, but when you'd click on any pagination control, pagination basically didn't happen. And I thought about it overnight and concluded that this must be because we're creating the ObjectDataSources all over again with the postback / repeater rebinding.

So, I now have a single ObjectDataSource defined within the actual UserControl / .ascx file.

<asp:ObjectDataSource ID="DynamicDocumentsDataSource" runat="server"
    TypeName="ConnectIT.Bll.Search" SelectMethod="GetPagedDataSet"
    EnablePaging="true" SelectCountMethod="GetRowCount"
    StartRowIndexParameterName="startRow"
    MaximumRowsParameterName="pageSize" SortParameterName="sortColumns"
    OnObjectCreated="dynamicDocuments_ObjectCreated">
    <SelectParameters>
        <asp:Parameter Name="storedProc" DefaultValue="[uspGetDynamicDocuments]" />
    </SelectParameters>
</asp:ObjectDataSource>

I'm trying to figure out how to tell the ItemDataBound method to access that control/object and add a SelectParameter to it, so that it selects slightly-different data for that Gridview. (But something tells me that with each successive loop... as we're grabbing that same static object...I need to clear the select parameter from the previous loop iteration?? Or will it overwrite ??)

It's not inside the repeater, but I'm wondering if it needs to be? If so, would having the same ID value for each one potentially cause problems?

Thanks!

Answer 1

Well, I would still consider dumping the SQL data source that been dumped into the aspx page. In most cases, they are can be "great" for simple things, but the instant code and WHEN the data source fires and is used? Well, we throw out those training wheels and flip to using code behind to feed the grid view.

In other words, dumping SQL data sources into that page tend over time to not only make the markup messy, but such data operations really don't belong in the page markup anyway. (Separation of UI and code logic is a good design approach).

The main issue with those SQL data sources in the markup, you have VERY little control when they are fired, and worse is trying to feed a particular parameter to the SQL data is "messy" at best, and at worst you simple can't control very well when they are used and fire.

I also dumped the use of the built in in-line editing for grid view, since there are far nicer UI choices you can make in that area also.

I am comfortable stating that instant you need code and fancy pants things (like say a repeating grid view), then that is the VERY same instant I suggest dropping the use of those SqlDatasouces.

SQL data sources are nice, since they are often wizard created. However, as a result, they are limited in use, and REALLY make a mess of the markup in the page anyway.

I mean, we can have this simple markup:

<h3>Enter City(s) to view</h3>
<asp:TextBox ID="txtCity" runat="server" Width="400">
</asp:TextBox>
<asp:Button ID="cmdShow" runat="server" Text="Show Hotels" CssClass="btn"
    OnClick="cmdShow_Click" />
<br />

<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
    <ItemTemplate>
    <h3>Hotels for city = <%# Eval("City") %></h3>
        <asp:GridView ID="GridView1" runat="server" 
            CssClass="table table-striped"
            width="40%" >
        </asp:GridView>
    </ItemTemplate>
</asp:Repeater>

Note how clean the markup is.

And our code behind is thus this:

    protected void cmdShow_Click(object sender, EventArgs e)
    {
        DataTable rstCities = new DataTable();
        rstCities.Columns.Add("City");
        string[] MyCities = txtCity.Text.ToString().Split(',');
        foreach (string OneCity in MyCities)
        {
            DataRow OneRow = rstCities.NewRow();
            OneRow["City"] = OneCity;
            rstCities.Rows.Add(OneRow);
        }
        Repeater1.DataSource = rstCities;
        Repeater1.DataBind();
    }

    protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item 
            || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            DataRowView rowData = (DataRowView)e.Item.DataItem;
            GridView GV = (GridView)e.Item.FindControl("GridView1");

            SqlCommand cmdSQL =
                new SqlCommand(@"SELECT FirstName, LastName, HotelName, Description
                                FROM tblHotelsC WHERE City = @City");

            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = rowData["City"];
            GV.DataSource = General.MyRstP(cmdSQL);
            GV.DataBind();
        }
    }

Not a lot of code, but then again, note how easy it is to control the data source for the GV. (And more so if you build just 2-3 helper routines for this).

So, the result is now this when I type in a few cities, and display a grid view for each city showing the hotels.

So, even from a speed of development, efforts, and code written?

Not a lot of code, and the markup page is now OH so nice and clean.

You might be able to hit the Sqldatasouce with a frying pan, and "maybe" get it to trigger each time for each repeater row, and "maybe" get the correct parameter(s) to be passed.

But life is too short to "fight" the system, and the path of least resistance is the best advice I can give here.

In the above, many moons ago, I became rather tired of having to setup and type in connection strings. (To be fair, that is ONE advantage of Sqldatasources, it sets that stuff up for you).

However, I have this "helper" routine in my general code library of routines, and thus that code is this:

public static DataTable MyRstP(SqlCommand cmdSQL, string sCon = "")
{
    DataTable rstData = new DataTable();
    if (sCon == "")
        sCon = Properties.Settings.Default.TEST4;

    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
    {
        using (cmdSQL)
        {
            cmdSQL.Connection = conn;
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
        }
    }
    return rstData;
}

So, I use above over and over all over the place in my code.