Remove From My Forums

save gridivew data based on split

Question
0

Sign in to vote

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