在由单个SqlDatasources填充的Gridview中查找多个下拉列表

Question

嗨，伙计们，我有4个下拉列表，每个下拉列表都分配给单独的SQL数据源，放在一个网格视图中。就像下面这样。

<asp:GridView ID="SupplierView" runat="server" OnRowDataBound="SupplierView_OnRowDataBound" OnPageIndexChanging="SupplierView_PageIndexChanging1" OnSorting="SupplierView_Sorting1" AllowPaging="True" AutoGenerateColumns="False" CssClass="GridCenter" CellPadding="4" ForeColor="#333333" GridLines="None" HorizontalAlign="Center" OnRowUpdated="SupplierView_RowUpdated" OnRowUpdating="SupplierView_RowUpdating" ViewStateMode="Enabled"  >
              <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <Columns>
                <asp:BoundField DataField="MaterialNumber" HeaderText="Material Number" SortExpression="MaterialNumber" />
                <asp:BoundField DataField="MaterialDecsription" HeaderText="Material Description" SortExpression="MaterialDecsription" />
                <asp:TemplateField HeaderText="CountryOfOrigin" SortExpression="CountryOfOrigin">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("CountryOfOrigin") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:DropDownList ID="CountryOfOriginDDL" runat="server"  SelectedValue='<%# Bind("CountryOfOrigin") %>' DataSourceID="SqlDataSource2COO" DataTextField="CountryOfOrigin" DataValueField="CountryOfOrigin" OnSelectedIndexChanged="CountryOfOriginDDL_OnSelectedIndexChanged" >
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ECCN" SortExpression="ECCN">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ECCN") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:DropDownList ID="ECCNDDL" runat="server" DataSourceID="SqlDataSourceECCN" DataTextField="ECCN" DataValueField="ECCN" OnSelectedIndexChanged="ECCNDDL_OnSelectedIndexChanged"  ViewStateMode="Enabled" AutoPostBack="True">
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ALCode" SortExpression="ALCode">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("ALCode") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:DropDownList ID="ALCODEDDL" runat="server" DataSourceID="SqlDataSourceALCODE" DataTextField="ALCode" DataValueField="ALCode" OnSelectedIndexChanged="ALCODEDDL_OnSelectedIndexChanged">
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Preference" SortExpression="Preference">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Preference") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:DropDownList ID="PrefDDL" runat="server" DataSourceID="SqlDataSourcePREF" DataTextField="Preference" DataValueField="Preference" OnSelectedIndexChanged="PrefDDL_OnSelectedIndexChanged" AutoPostBack="True">
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>

              <EditRowStyle BackColor="#999999" />
              <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
              <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
              <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
              <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
              <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
              <SortedAscendingCellStyle BackColor="#E9E7E2" />
              <SortedAscendingHeaderStyle BackColor="#506C8C" />
              <SortedDescendingCellStyle BackColor="#FFFDF8" />
              <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

        </asp:GridView>
                            <p>   <asp:Button ID="Updatebtn" runat="server" Text="Update" />-<asp:Button ID="Savebtn" runat="server" Text="Save"  />-<asp:Button ID="BtnLogOut" runat="server" Text="Log Out" OnClick="BtnLogOut_OnClick" />  </p>
                        </div>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SapMainServerDataPull %>" SelectCommand="SELECT [MaterialNumber], [MaterialDecsription], [CountryOfOrigin], [ECCN], [ALCode], [Preference] FROM [VendorDetailsRecordsForUpdate] WHERE ([VendorID] = @VendorID)">
            <SelectParameters>
                <asp:SessionParameter Name="VendorID" SessionField="VendorId" Type="String" />
            </SelectParameters>
                        </asp:SqlDataSource>


                        <asp:SqlDataSource ID="SqlDataSource2COO" runat="server" ConnectionString="<%$ ConnectionStrings:SapMainServerDataPull %>" SelectCommand="SELECT DISTINCT [CountryOfOrigin] FROM [VendorDetailsRecordsForUpdate] ORDER BY [CountryOfOrigin]" UpdateCommand="uspSupplierUpdateECCN" UpdateCommandType="StoredProcedure">
                            <UpdateParameters>
                                <asp:Parameter Name="ECCN" Type="String"></asp:Parameter>
                                <asp:Parameter Name="VendorId" Type="String"></asp:Parameter>
                            </UpdateParameters>
                        </asp:SqlDataSource>


        <asp:Label ID="ErrMsgUpdate" runat="server" Text=""></asp:Label>

                        <asp:SqlDataSource ID="SqlDataSourceECCN" runat="server" ConnectionString="<%$ ConnectionStrings:SapMainServerDataPull %>" SelectCommand="SELECT DISTINCT [ECCN] FROM [VendorDetailsRecordsForUpdate] ORDER BY [ECCN]" UpdateCommand="uspSupplierUpdateECCN" UpdateCommandType="StoredProcedure">
                            <UpdateParameters>
                                <asp:Parameter Name="ECCN" Type="String"></asp:Parameter>
                            </UpdateParameters>
                        </asp:SqlDataSource>
                        <asp:SqlDataSource ID="SqlDataSourceALCODE" runat="server" ConnectionString="<%$ ConnectionStrings:SapMainServerDataPull %>" SelectCommand="SELECT DISTINCT [ALCode] FROM [VendorDetailsRecordsForUpdate] ORDER BY [ALCode]"></asp:SqlDataSource>
                        <asp:SqlDataSource ID="SqlDataSourcePREF" runat="server" ConnectionString="<%$ ConnectionStrings:SapMainServerDataPull %>" SelectCommand="SELECT DISTINCT [Preference] FROM [VendorDetailsRecordsForUpdate] ORDER BY [Preference]"></asp:SqlDataSource>

我刚开始编码(还在我的学习阶段)抱歉，可能是一个简单的问题。

目前，下拉列表显示了我需要的数据。但是在回发之后，它们都重新回到了原来的值，我做了一些研究，我认为我需要在代码后面引用这些下拉列表。我不想使用sql适配器等，因为sql数据源似乎至少在显示数据。

我还需要更新用户选择的值上的应用程序，那么如何使我的下拉列表在选择后保存到数据库？

我还看到了一些关于在页面加载时在回发之外绑定网格视图的内容。对不起，我怀疑这是标准的东西，但我仍然找不到任何真正的解决我的具体问题。我看到了很多sqldata适配器和使用列表添加值的例子，但是我不想这样做，因为值可能会随着时间的推移而改变，所以直接从step资源调用它们似乎是一个逻辑步骤。

到目前为止，这是我的代码。它大部分都不起作用，而且我的东西也在上面。所以这可能没什么意义。

{
    public partial class Update : System.Web.UI.Page
    {
        public string Eccn { get; set; }
        public string CountryOfOrigin { get; set; }
        public string AlCode { get; set; }
        public string Pref { get; set; }

        public string VendorId;

        protected void Page_Load(object sender, EventArgs e)
        {

            if (Session["VendorId"] != null)
                VendorId = Session["VendorId"].ToString();
            RefreshGrid(VendorId);

            if (Session["VendorId"] == null)
            {
                Response.Redirect("~/Login.aspx");
            }

            if (!IsPostBack)
            {
                BindGrid();
            }
        }
          public void RefreshGrid(string vendorId)
        {
            try
            {
                //get list of records from vendorId 
                BizManager biz = new BizManager();

                DataTable dt = new DataTable();

                biz.GetMaterialAndDesc(VendorId);

                SupplierView.DataSource = SqlDataSource1;
                SupplierView.DataBind();
            }
            catch (Exception ex)
            {
                ErrMsg = App.App.HandleError(MethodBase.GetCurrentMethod(), ex,
                    "Application Failed adding products to the list");
            }

        }


        private string ErrMsg
        {
            get { return ErrMsgUpdate.Text; }
            set { ErrMsgUpdate.Text = value; }
        }

        private string ConvertSortDirectionToSql(SortDirection sortDirection)
        {
            string newSortDirection = String.Empty;

            switch (sortDirection)
            {
                case SortDirection.Ascending:
                    newSortDirection = "ASC";
                    break;

                case SortDirection.Descending:
                    newSortDirection = "DESC";
                    break;
            }

            return newSortDirection;
        }

        protected void SupplierView_PageIndexChanging1(object sender, GridViewPageEventArgs e)
        {
            SupplierView.PageIndex = e.NewPageIndex;
            SupplierView.DataBind();
        }

        protected void SupplierView_Sorting1(object sender, GridViewSortEventArgs e)
        {
            DataTable dataTable = SupplierView.DataSource as DataTable;

            if (dataTable != null)
            {
                DataView dataView = new DataView(dataTable);
                dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);

                SupplierView.DataSource = dataView;
                SupplierView.DataBind();
            }
        }

        private void RefreshData()
        {

        }

        protected void SupplierView_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {

        }

        protected void SupplierView_RowUpdated(object sender, GridViewUpdatedEventArgs e)
        {

        }

        protected void BtnLogOut_OnClick(object sender, EventArgs e)
        {
            Response.Redirect("~/Logout.aspx");
        }

        protected void CountryOfOriginDDL_OnSelectedIndexChanged(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }

        protected void ECCNDDL_OnSelectedIndexChanged(object sender, EventArgs e)
        {

            DropDownList ddl = sender as DropDownList;

            foreach (GridViewRow row in SupplierView.Rows)
            {
                Control ctrl = row.FindControl("ECCNDDL") as DropDownList;
                if (ctrl != null)
                {
                    DropDownList ddl1 = (DropDownList) ctrl;

                    if (ddl.ClientID == ddl1.ClientID)
                    {
                        SqlDataSourceECCN.UpdateParameters["VendorId"].DefaultValue = "0000107898";
                        SqlDataSourceECCN.UpdateParameters["ECCN"].DefaultValue = ddl1.SelectedValue;
                    }
                }
            }

            //Session["VendorId"] = VendorId;

            //SqlDataSourceECCN.UpdateParameters["VendorId"].DefaultValue = Session.ToString();
            //SqlDataSourceECCN.UpdateParameters["ECCN"].DefaultValue = Eccn;


        }

        protected void ALCODEDDL_OnSelectedIndexChanged(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }

        protected void PrefDDL_OnSelectedIndexChanged(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }

        protected void SupplierView_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.Header)
            {
                DropDownList dropDownLst = (DropDownList)e.Row.FindControl("ECCNDDL");

                dropDownLst.DataSource = SqlDataSourceECCN;

                dropDownLst.DataTextField = "ECCN";
                dropDownLst.DataValueField = "ECCN";
                dropDownLst.DataBind();



              }
            }
        }
    }

Answer 1

在webforms中，我发现简单地使用一些DataTables来保存数据。因此，我使用一种简单的方法从数据库中获取数据，并返回一个带有结果的DataTable。查询可以是select或存储过程。

public static DataTable LoadDataFromDB(string query)
{
    DataTable dt = new DataTable();

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        try
        {
            adapter.Fill(dt);
        }
        catch
        {
        }
    }

    return dt;
}

现在，您可以使用LoadDataFromDB everywhere来简单地用数据填充控件。

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack == false)
    {
        //as a query
        DataTable dt = LoadDataFromDB("select top 10 * from mytable");
        GridView1.DataSource = dt;
        GridView1.DataBind();

        //or stored procedure
        DropDownList1.DataSource = LoadDataFromDB("mysp 1, 2");
        DropDownList1.DataTextField = "columnA";
        DropDownList1.DataValueField = "columnB";
        DropDownList1.DataBind();
    }
}

更新

它们是否在GridView中并不重要。只需使用FindControl。

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DropDownList ddl = e.Row.FindControl("") as DropDownList;

        ddl.DataSource = LoadDataFromDB("select a from b order by c");
        ddl.DataTextField = "username";
        ddl.DataValueField = "fullwidth";
        ddl.DataBind();
    }
}