Calling a stored procedure on page-load in ASP.NET webforms using VS 2019

Question

Very new to this. I have the .aspx code basically done. I know the data source connection info, just trying to run and display output of a stored procedure on page load. This will be a report that simply displays the results upon selecting that item from a list and upon the page load. Apologies for the neandrethal'ish understanding of this...

I have been researching just the call of a stored procedure upon page load in the .aspx.cs file.

Answer 1

Ok, the general steps are:

First, setup a connection to SQL server.

So, in the project folder, you want to create a connection. This will thus result in ONE location for your connection, and thus you avoid multiple connections all over the place in the application.

This resulting database connection can thus be used over and over.

So, project - > properties.

Then choose the settings tab.

So, we create the database connection like this:

Note that I "often" use "." (dot) for the server name, as then you don't need to enter the database server name.

Ok, so now we have a valid connection.

So, now, here is our stored procedure, and it allows us to pass the city name to the procedure to return a list of hotels based on a given city parameter.

So, then this stored procedure:

CREATE PROCEDURE GetHotelsByCity 
    @City nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM tblHotelsA WHERE City = @City
    ORDER BY HotelName
END
GO

Ok, so now our markup for the web page. We will use a GridView to display the results.

        <asp:GridView ID="GridView1" runat="server"
            AutoGenerateColumns="False" DataKeyNames="ID"
            CssClass="table table-hover" Width="50%">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName"        />
                <asp:BoundField DataField="LastName" HeaderText="LastName"          />
                <asp:BoundField DataField="City" HeaderText="City"                  />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName"        />
                <asp:BoundField DataField="Description" HeaderText="Description"    />
            </Columns>
        </asp:GridView>

So, now our code in the page load event.

Do keep in mind that for any and all button clicks on a page, then the Page load event will fire each time. Thus, in near all cases, you want such loading code to only run on the real first page load.

Hence, we test/check the built in IsPostBack flag, and ONLY pull the data on the first real page load.

Hence this code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dtHotels = new DataTable();
        using (SqlConnection con = new SqlConnection(Properties.Settings.Default.MyDB))
        {
            using (SqlCommand cmd = new SqlCommand("GetHotelsByCity", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = "Edmonton";
                con.Open();
                dtHotels.Load(cmd.ExecuteReader()); 
            }
        }
        GridView1.DataSource = dtHotels;
        GridView1.DataBind();
    }
}

And the result is then this: