dynamic form based on stored proc

[coder91]

For the system I am creating which is a report generator, there is a form which is created based on the parameters required for a stored procedure, the code for this is:

 for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        string label = datatable.Rows[i]["label"].ToString();

                        Label mylabel = new Label();
                        mylabel.Text = label + ":";
                        reportParametersPlaceHolder.Controls.Add(mylabel);

                        if (datatable.Rows[i]["data_type"].ToString() == "datetime")
                        {
                            TextBox myTextBox = new TextBox();
                            myTextBox.CssClass = "form-control";
                            myTextBox.TextMode = TextBoxMode.Date;
                            reportParametersPlaceHolder.Controls.Add(myTextBox);
                        }
                        else
                        {
                            TextBox txtBox = new TextBox();
                            txtBox.CssClass = "form-control";
                            reportParametersPlaceHolder.Controls.Add(txtBox);
                        }
                    }

I then have a button which when selected should display a gridview with the results from the stored procedure. The problem I'm having is, because the website is dynamic - meaning it can work for any report without new pages being added and with the way the input fields are being generated how can I insert the parameters into the stored procedure when the number of parameters differs for each. the code for inserting into a stored procedure is:

SqlCommand command = new SqlCommand(reportName);                                                                                                                                                                                                                                                                                                               
            command.Parameters.AddWithValue("@auth_name", authName);
            command.Parameters.Add(new SqlParameter()
            {
                ParameterName = "RETURN_VALUE",
                Direction = ParameterDirection.ReturnValue
            });


SqlCommand command = new SqlCommand(reportName);                                                                                                                                                                                                                                                                                                               
            command.Parameters.AddWithValue("@param", param);
            command.Parameters.Add(new SqlParameter()
            {
                ParameterName = "RETURN_VALUE",
                Direction = ParameterDirection.ReturnValue
            });

Thanks in advance

[djjeavons]

Hi

I notice that when you are creating your dynamic controls you are not giving them an ID. This means that ASP.NET will assign an arbitary ID for you, something like ctr1, ctr2 etc. which is not going to help you in the next stage. Do you have something in your DataTable that you can use to determine what ID should be given to each control?

In terms of adding the data collected from the controls to your parameters collection, you can do this by looping through the controls collection of the place holder. In my previous post I alluded to using Attributes when creating the control to add some additional information such as the parameter name. This is demonstrated below. What you could do is loop through your controls, and for each control that contains an attribute name, then you know that this needs to be added to the parameters collection of your SqlCommand.

Finally, before getting into the code, you need to modify the way that you are creating the controls. As already mentioned, you need an ID. The next thing is the use of the Page.IsPostBack. You will need to create the controls each time the page is loaded otherwise they will not exist (and therefore not be accessible) when the parameters are submitted.

So, the following is my previous example of creating the controls, please note the ID and Attributes pieces, these are the additions you will need to make to your code. Also, remove the check for Page.IsPostBack that I showed you in your other post.

    protected void Page_Load(object sender, EventArgs e)
    {
        // Create your dynamic controls from the Data Table
        Label myLabel = new Label();
        myLabel.Text = "Parameter Label";
        myLabel.ID = "ParameterLabel";

        reportParametersPlaceHolder.Controls.Add(myLabel);

        TextBox myTextBox = new TextBox();
        myTextBox.ID = "ParameterTextBox";

        //Store additional information that can be used when submitted
        myTextBox.Attributes.Add("ParameterName", "@Param1");

        reportParametersPlaceHolder.Controls.Add(myTextBox);

        DropDownList myDropDownList = new DropDownList();
        myDropDownList.ID = "ParameterDropDownList";
        myDropDownList.Items.AddRange(new[] { new ListItem("Item 1", "Value 1"), new ListItem("Item 2", "Value 2"), new ListItem("Item 3", "Value 3") });
        myDropDownList.Attributes.Add("ParameterName", "@Param2");

        reportParametersPlaceHolder.Controls.Add(myDropDownList);
    }

Obviously you will get the names of the parameters and data types from your DataTable.

The next piece of code is surrounded with a check to see if the page has been posted back and should be placed directly under the above code (just splitting up here to make it more readable).

        //If it is a postback then the controls will have been recreated and populated via ViewState
        //Enumerate through them and read the attributes to determine what parameter they belong to.
        if (Page.IsPostBack)
        {
            PlaceHolder placeHolder = (PlaceHolder)this.FindControl("reportParametersPlaceHolder");

            foreach (var control in placeHolder.Controls)
            {
                //Determine control type if required. 
                if (control is TextBox)
                {
                    TextBox textBox = (TextBox)control;
                    Response.Write(String.Format("Text Box with value of {0} and parameter name of {1}<br />", Request.Form[textBox.ID], textBox.Attributes["ParameterName"]));
                }
                else if (control is DropDownList)
                {
                    DropDownList dropDownList = (DropDownList)control;
                    Response.Write(String.Format("Drop Down List with value of {0} and parameter name of {1}<br />", Request.Form[dropDownList.ID], dropDownList.Attributes["ParameterName"]));
                }
            }
        }

So at this point, we can write out the value of the control (Text if it is a TextBox or Value if it is a DropDown) and also the parameter name that was associated to the attributes collection. You will want to ensure that the control contains a ParameterName attribute before adding it to your SqlCommand.Parameters collection:

    if (dropDownList.Attributes["ParameterName"] != null)
        command.Parameters.AddWithValue(dropDownList.Attributes["ParameterName"], Request.Form[dropDownList.ID]).Direction = ParameterDirection.ReturnValue;

You will need to play around with these techniques to get it working fully but hopefully it will give you a start.

Just shout if you have any questions.

HTH