Get data loaded from sql on Page_Load

Question

I'm noobie to this :)

I'm getting data from SQL Server in the page_load event handler, and that works fine with SqlDataReader.

I also added a DataTable. I want to use the data when selecting a dropdownlist.

This is my code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack) 
    { 
        string mainconn1 = ConfigurationManager.ConnectionStrings["SqlConnection1"].ConnectionString;

        SqlConnection sqlconn1 = new SqlConnection(mainconn1);

        string Sqlquery1 = "SELECT p.[Name], m.machineid,md.MachineNumber, md.HostName FROM [db].[dbo].[Machine] m INNER JOIN MachineDevice md ON md.MachineID = m.MachineID INNER JOIN property p ON m.PropertyID = p.PropertyID WHERE ([status] = '1') AND (md.DateTimeRetired IS NULL) ORDER BY md.MachineNumber";

        SqlCommand sqlcomm1 = new SqlCommand(Sqlquery1, sqlconn1);
        sqlconn1.Open();

        SqlDataReader rd1 = sqlcomm1.ExecuteReader();

        DataTable dt = new DataTable();
        dt.Load(rd1);
   }
}

And this:

protected void Ort_SelectedIndexChanged1(object sender, EventArgs e)
{
    if (Ort.SelectedValue == "Stockholm")
    {
        // while (rd1.read)
        // {
        //    Machine.DataSource = rd1;
        //    Machine.DataTextField = "MachineNumber";
        //    Machine.DataValueField = "MachineNumber";
        //    Machine.DataBind();
        //    Machine.Items.Insert(0, new ListItem("-Select Machine-", "0"));
        // }
    }
}

Is it possible to get data when selectindexchanged, or do I need to ask SQL Server again?

Thanks

Answer 1

Sure, say we have a drop down (combo box) of some cities, and when you select the city, we fill out a gird of hotels.

Is it possible to get data when selectindexchanged, or do I need to ask SQL Server again?

you could in some cases "persist" the data, but the cost is just as high as in most cases hitting the database again.

so, yes, it is standard fair to re-pull that data.

example:

this markup:

<h3>Select Hotel city</h3>
<asp:DropDownList ID="DropDownList1" runat="server"
    Width="150px" Height="30px"
    DataTextField="City"
    AutoPostBack="true"
    OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
<br />
<br />
        
<asp:GridView ID="GVHotels" runat="server" CssClass="table table-hover" 
    DataKeyNames="ID" AutoGenerateColumns="false" Width="40%" OnRowDataBound="GVHotels_RowDataBound" >
<Columns>
    <asp:BoundField DataField="FirstName"    HeaderText="First Name"  HeaderStyle-Width="100" />
    <asp:BoundField DataField="LastName"     HeaderText="Last Name"   HeaderStyle-Width="100" />
    <asp:BoundField DataField="HotelName"    HeaderText="Hotel Name"  HeaderStyle-Width="120"/>
    <asp:BoundField DataField="City"         HeaderText="City" />
    <asp:BoundField DataField="Description"  HeaderText="Province" />
    <asp:TemplateField>
        <ItemTemplate>
            <button runat="server" id="cmdEditBooking"
                type="button" class="btn myshadow"
                onserverclick="cmdEditBooking_ServerClick">
                <span class="glyphicon glyphicon-home"></span>View
            </button>
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

So, we have a combo box (dropdown list) of city to select, and then we display hotels from that city.

Code behind:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadCombo();
    }

    void LoadCombo()
    {
        string strSQL = "SELECT City FROM City ORDER BY City";
        SqlCommand cmdSQL = new SqlCommand(strSQL);
        DropDownList1.DataSource = MyRstP(cmdSQL);
        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, new ListItem("Select City", ""));
    }


    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (DropDownList1.SelectedIndex >0)
        {
            string strSQL =
                @"SELECT * FROM tblHotelsA
                WHERE City = @City
                ORDER BY HotelName";

            SqlCommand cmdSQL = new SqlCommand(strSQL);
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = DropDownList1.Text;

            GVHotels.DataSource = MyRstP(cmdSQL); 
            GVHotels.DataBind();  
        }
    }

And both of above use this handy helper routine:

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

So, result: