从数据库加载数据在 Page_Load 事件中。

huangapple go评论62阅读模式
英文:

Get data loaded from sql on Page_Load

问题

I'm noobie to this 从数据库加载数据在 Page_Load 事件中。

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:

```csharp
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

英文:

I'm noobie to this 从数据库加载数据在 Page_Load 事件中。

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

答案1

得分: 0

当然,假设我们有一个下拉框(combo box),其中列出了一些城市,当您选择城市时,我们会填充一个酒店网格。

在选择索引更改时是否可以获取数据,还是需要再次查询 SQL Server?

在某些情况下,您可以“持久化”数据,但在大多数情况下,与再次查询数据库的成本一样高。

所以,是的,重新获取数据是标准的做法。

示例:

此标记:

<h3>选择酒店城市</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="名字" HeaderStyle-Width="100" />
    <asp:BoundField DataField="LastName" HeaderText="姓氏" HeaderStyle-Width="100" />
    <asp:BoundField DataField="HotelName" HeaderText="酒店名称" HeaderStyle-Width="120" />
    <asp:BoundField DataField="City" HeaderText="城市" />
    <asp:BoundField DataField="Description" HeaderText="省份" />
    <asp:TemplateField>
        <ItemTemplate>
            <button runat="server" id="cmdEditBooking"
                type="button" class="btn myshadow"
                onserverclick="cmdEditBooking_ServerClick">
                <span class="glyphicon glyphicon-home"></span>查看
            </button>
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

因此,我们有一个城市的下拉框,然后我们显示来自该城市的酒店。

代码后台:

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("选择城市", ""));
}

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();
    }
}

上面两个都使用这个方便的辅助程序:

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;
}

因此,结果如下:图片链接

英文:

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:

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

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 = &quot;SELECT City FROM City ORDER BY City&quot;;
        SqlCommand cmdSQL = new SqlCommand(strSQL);
        DropDownList1.DataSource = MyRstP(cmdSQL);
        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, new ListItem(&quot;Select City&quot;, &quot;&quot;));
    }


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

            SqlCommand cmdSQL = new SqlCommand(strSQL);
            cmdSQL.Parameters.Add(&quot;@City&quot;, 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:

从数据库加载数据在 Page_Load 事件中。

huangapple
  • 本文由 发表于 2023年2月6日 21:17:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75361809.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定