locked
save gridivew data based on split RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my gridview data

    Process Item Qty
    AAA A 2
    AAA B 4
    AAA C 6
    BBB A 4
    BBB K 8
    CCC C 4
    DDD M 1
    DDD N 5

    I have two table Table1 & Table2

    Table1 I want to store like this

    Process  RefNo
    AAA 1
    BBB 2
    CCC 3
    DDD 4

    Table2 I Want to store like this below

    Process Item Qty RefNo
    AAA A 2 1
    AAA B 4 1
    AAA C 6 1
    BBB A 4 2
    BBB K 8 2
    CCC C 4 3
    DDD M 1 4
    DDD N 5 4

    How would be my insert query in c# code behind.

    Thanking You

    Thursday, May 28, 2020 6:47 PM

Answers

  • User-939850651 posted

    Hi, Gopi.MCA

    You could store the data in the collection after filtering, and then traverse them and insert it into the data table.

    More details, you could refer to the below codes:

    StoreTo2Page.aspx

    <body>
        <form id="StoreData" runat="server">
            <div>
                <asp:GridView runat="server" ID="TableData" AutoGenerateColumns="false">
                    <Columns>
                        <asp:TemplateField HeaderText="Process Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Process" Text='<%#Eval("Process") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Item Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Item" Text='<%#Eval("Item") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Qty Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Qty" Text='<%#Eval("Qty") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
                <asp:Button runat="server" OnClick="store_Click" ID="store" Text="StroeToTable" />
            </div>
        </form>
    </body>
    

    StoreTo2Page.aspx.cs

    public partial class StoreTo2Table : System.Web.UI.Page
        {
            String conStr = "data source=.; database=TestDB; integrated security=SSPI";
            protected void Page_Load(object sender, EventArgs e)
            {
                bindData();
            }
            private void bindData()
            {
                DataTable dt = new DataTable();
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    conn.Open();
                    String query = "select * from tb";
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        TableData.DataSource = dt;
                        TableData.DataBind();
                    }
                }
            }
    
            protected void store_Click(object sender, EventArgs e)
            {
                //store data to table1
                List<String> Pros = getProcess();
                for (int i = 0; i < Pros.Count; i++)
                {
                    try
                    {
                        using (SqlConnection conn = new SqlConnection(conStr))
                        {
                            conn.Open();
                            String insertQuery = "insert into table1 (Process,RefNo) values(@Process,@RefNo)";
                            using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@Process", Pros[i]);
                                cmd.Parameters.AddWithValue("@RefNo", i + 1);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception)
                    {
                        //do something here
                    }
                }
                //store data to table2
                List<Example> allData = getWholeGrid();
                for (int i = 0; i < allData.Count; i++)
                {
                    try
                    {
                        using (SqlConnection conn = new SqlConnection(conStr))
                        {
                            conn.Open();
                            String insertQuery = "insert into table2 (Process,Item,Qty,RefNo) values(@Process,@Item,@Qty,(select RefNo from table1 where Process=@Process))";
                            using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@Process", allData[i].Process);
                                cmd.Parameters.AddWithValue("@Item", allData[i].Item);
                                cmd.Parameters.AddWithValue("@Qty", allData[i].Qty);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception)
                    {
                        //do something here
                    }
                }
                Response.Write("<script>alert('stroe success')</script>");
            }
    
            private List<String> getProcess()
            {
                List<String> Pros = new List<string>();
                foreach (GridViewRow row in TableData.Rows)
                {
                    String Pro = ((Label)row.FindControl("Process")).Text;
                    if (!Pros.Contains(Pro))
                    {
                        Pros.Add(Pro);
                    }
                }
                return Pros;
            }
    
            private List<Example> getWholeGrid()
            {
                List<Example> Examples = new List<Example>();
                foreach (GridViewRow row in TableData.Rows)
                {
                    Example example = new Example();
                    example.Process = ((Label)row.FindControl("Process")).Text;
                    example.Item = ((Label)row.FindControl("Item")).Text;
                    example.Qty = int.Parse(((Label)row.FindControl("Qty")).Text);
                    Examples.Add(example);
                }
                return Examples;
            }
        }
    

    Example.cs

    public class Example
        {
            public String Process { set; get; }
            public String Item { set; get; }
            public int Qty { set; get; }
        }

    Result: In page is 

    When click the button, the data table is 

    Hope this can help you.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2020 3:40 AM

All replies

  • User-939850651 posted

    Hi, Gopi.MCA

    You could store the data in the collection after filtering, and then traverse them and insert it into the data table.

    More details, you could refer to the below codes:

    StoreTo2Page.aspx

    <body>
        <form id="StoreData" runat="server">
            <div>
                <asp:GridView runat="server" ID="TableData" AutoGenerateColumns="false">
                    <Columns>
                        <asp:TemplateField HeaderText="Process Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Process" Text='<%#Eval("Process") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Item Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Item" Text='<%#Eval("Item") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Qty Column">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Qty" Text='<%#Eval("Qty") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
                <asp:Button runat="server" OnClick="store_Click" ID="store" Text="StroeToTable" />
            </div>
        </form>
    </body>
    

    StoreTo2Page.aspx.cs

    public partial class StoreTo2Table : System.Web.UI.Page
        {
            String conStr = "data source=.; database=TestDB; integrated security=SSPI";
            protected void Page_Load(object sender, EventArgs e)
            {
                bindData();
            }
            private void bindData()
            {
                DataTable dt = new DataTable();
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    conn.Open();
                    String query = "select * from tb";
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        TableData.DataSource = dt;
                        TableData.DataBind();
                    }
                }
            }
    
            protected void store_Click(object sender, EventArgs e)
            {
                //store data to table1
                List<String> Pros = getProcess();
                for (int i = 0; i < Pros.Count; i++)
                {
                    try
                    {
                        using (SqlConnection conn = new SqlConnection(conStr))
                        {
                            conn.Open();
                            String insertQuery = "insert into table1 (Process,RefNo) values(@Process,@RefNo)";
                            using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@Process", Pros[i]);
                                cmd.Parameters.AddWithValue("@RefNo", i + 1);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception)
                    {
                        //do something here
                    }
                }
                //store data to table2
                List<Example> allData = getWholeGrid();
                for (int i = 0; i < allData.Count; i++)
                {
                    try
                    {
                        using (SqlConnection conn = new SqlConnection(conStr))
                        {
                            conn.Open();
                            String insertQuery = "insert into table2 (Process,Item,Qty,RefNo) values(@Process,@Item,@Qty,(select RefNo from table1 where Process=@Process))";
                            using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@Process", allData[i].Process);
                                cmd.Parameters.AddWithValue("@Item", allData[i].Item);
                                cmd.Parameters.AddWithValue("@Qty", allData[i].Qty);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception)
                    {
                        //do something here
                    }
                }
                Response.Write("<script>alert('stroe success')</script>");
            }
    
            private List<String> getProcess()
            {
                List<String> Pros = new List<string>();
                foreach (GridViewRow row in TableData.Rows)
                {
                    String Pro = ((Label)row.FindControl("Process")).Text;
                    if (!Pros.Contains(Pro))
                    {
                        Pros.Add(Pro);
                    }
                }
                return Pros;
            }
    
            private List<Example> getWholeGrid()
            {
                List<Example> Examples = new List<Example>();
                foreach (GridViewRow row in TableData.Rows)
                {
                    Example example = new Example();
                    example.Process = ((Label)row.FindControl("Process")).Text;
                    example.Item = ((Label)row.FindControl("Item")).Text;
                    example.Qty = int.Parse(((Label)row.FindControl("Qty")).Text);
                    Examples.Add(example);
                }
                return Examples;
            }
        }
    

    Example.cs

    public class Example
        {
            public String Process { set; get; }
            public String Item { set; get; }
            public int Qty { set; get; }
        }

    Result: In page is 

    When click the button, the data table is 

    Hope this can help you.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2020 3:40 AM
  • User-807418713 posted

    Thank You

    Friday, May 29, 2020 7:07 AM