[Solved] Point to another sql table at runtime

[Felice]

I have many sql tables that are all structured in the same identical way (headers and type).
I would like to use one radgrid to work on all these tables (insert, update, delete) selecting the table from a drop down list.
The code here below works only partially, I can select a different table from the drop down list and it is immediately loaded but I cannot perform insert delete update and also sorting and filtering are not working.
What should I do to get all the above working? Is there a demo for such situation?
Moreover, I have added manually the "Edit" and "Delete" columns because I like to have the image buttons for these operations but in this context they both appear on the left side of the grid. I understand the reasons but I would like to get them on the sides (left and right) of the columns.

<div>
          <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged1">

              <asp:ListItem Value="Table_321">Table_321</asp:ListItem>
              <asp:ListItem Value="Table_621">Table_621</asp:ListItem>
          </asp:DropDownList>
          <br />
          <br />
          <br />
          <telerik:RadGrid ID="RadGrid1" runat="server" Culture="it-IT" OnNeedDataSource="RadGrid1_NeedDataSource" AllowPaging="True" AllowSorting="True" AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True">
              <ExportSettings ExportOnlyData="True">
                  <Pdf PageWidth="">
                  </Pdf>
              </ExportSettings>
              <ClientSettings>
                  <Scrolling AllowScroll="True" UseStaticHeaders="True" />
              </ClientSettings>
              <MasterTableView CommandItemDisplay="Top">
                  <CommandItemSettings ShowExportToExcelButton="True" />
                  <Columns>
                      <telerik:GridEditCommandColumn ButtonType="ImageButton">
                          <HeaderStyle Width="30px" />
                      </telerik:GridEditCommandColumn>
                      <telerik:GridButtonColumn ButtonType="ImageButton" Text="Delete" CommandName="Delete" FilterControlAltText="Filter column1 column" ConfirmDialogType="RadWindow" ConfirmText="Do you really want to delete this project and all its content?" UniqueName="Cancel">
                          <HeaderStyle Width="30px" />
                      </telerik:GridButtonColumn>
                  </Columns>

                  <EditFormSettings>
                      <EditColumn UniqueName="EditCommandColumn1" FilterControlAltText="Filter EditCommandColumn1 column"></EditColumn>
                  </EditFormSettings>
                  <PagerStyle AlwaysVisible="True" />
              </MasterTableView>
              <PagerStyle AlwaysVisible="True" />
          </telerik:RadGrid>
          <br />
          <br />

      </div>

      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BOMConnectionString %>">
          <DeleteParameters>
              <asp:Parameter Name="Id" Type="Int32" />
          </DeleteParameters>
          <InsertParameters>
              <asp:Parameter Name="Code" Type="Double" />
              <asp:Parameter Name="Description" Type="String" />
              <asp:Parameter Name="Bkgroup" Type="Double" />
              <asp:Parameter Name="Stgroup" Type="Double" />
              <asp:Parameter Name="Quantity" Type="Double" />
              <asp:Parameter Name="BomNote" Type="String" />
              <asp:Parameter Name="UnitEng" Type="String" />
              <asp:Parameter Name="AlternCost" Type="Double" />
          </InsertParameters>
          <UpdateParameters>
              <asp:Parameter Name="Code" Type="Double" />
              <asp:Parameter Name="Description" Type="String" />
              <asp:Parameter Name="Bkgroup" Type="Double" />
              <asp:Parameter Name="Stgroup" Type="Double" />
              <asp:Parameter Name="Quantity" Type="Double" />
              <asp:Parameter Name="BomNote" Type="String" />
              <asp:Parameter Name="UnitEng" Type="String" />
              <asp:Parameter Name="AlternCost" Type="Double" />
              <asp:Parameter Name="Id" Type="Int32" />
          </UpdateParameters>
      </asp:SqlDataSource>

  
and this is the code behind:

protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
    {
        if (DropDownList1.SelectedIndex > -1)
        {
            RadGrid1.DataSource=null;
            RadGrid1.Rebind();
        }
    }

    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        string tableInUse = DropDownList1.SelectedItem.Text;
        RadGrid1.DataSource = SqlDataSource1;
        SqlDataSource1.DeleteCommand = "DELETE FROM [" + tableInUse + "] WHERE [Id] = @Id";
        SqlDataSource1.InsertCommand = "INSERT INTO [" + tableInUse + "] ([Code], [Description], [Bkgroup], [Stgroup], [Quantity], [BomNote], [UnitEng], [AlternCost]) VALUES (@Code, @Description, @Bkgroup, @Stgroup, @Quantity, @BomNote, @UnitEng, @AlternCost)";
        SqlDataSource1.SelectCommand = "SELECT * FROM [" + tableInUse + "] ORDER BY [Code]";
        SqlDataSource1.UpdateCommand = "UPDATE [" + tableInUse + "] SET [Code] = @Code, [Description] = @Description, [Bkgroup] = @Bkgroup, [Stgroup] = @Stgroup, [Quantity] = @Quantity, [BomNote] = @BomNote, [UnitEng] = @UnitEng, [AlternCost] = @AlternCost WHERE [Id] = @Id";
    }

Thank you for supporting,
Felice

[Konstantin Dikov]

Hello Felice,

The approach that you are trying to implement, by setting the SqlDataSource controls as a DataSource control for the grid will not work, since that event will not fire when you initiate an insert or update operation and the grid will lose those settings.

For achieving the desired behavior you should apply those settings within the Page's Load event handler for example and on the SelectedIndexChanged event of the DropDownList control.

For your convenience, following is an example with such functionality, which works correctly on my end:

protected void Page_Load(object sender, EventArgs e)
{
    SetDataSource();
}

protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
{
    if (DropDownList1.SelectedIndex > -1)
    {
        SetDataSource();
        RadGrid1.Rebind();
    }
}

private void SetDataSource()
{
    string tableInUse = DropDownList1.SelectedItem.Text;
    RadGrid1.DataSourceID = SqlDataSource1.ID;
    SqlDataSource1.DeleteCommand = "DELETE FROM [" + tableInUse + "] WHERE [Id] = @Id";
    SqlDataSource1.InsertCommand = "INSERT INTO [" + tableInUse + "] ([Code], [Description]) VALUES (@Code, @Description)";
    SqlDataSource1.SelectCommand = "SELECT * FROM [" + tableInUse + "] ORDER BY [Code]";
    SqlDataSource1.UpdateCommand = "UPDATE [" + tableInUse + "] SET [Code] = @Code, [Description] = @Description [Id] = @Id";
}

You could also find the entire page attached to this post.

Hope this helps.


Regards,
Konstantin Dikov
Telerik

 

 

[Felice]

Thats perfect.
Thanks for your support.

Felice