How to insert multiple record as DataTable into SQL Server database in a single time without using looping?

How to insert multiple record as DataTable into SQL Server database in a single time without using looping?

My Gridview(Source Code):

<asp:GridView ID=”grdEducation” runat=”server” AutoGenerateColumns=”false”CssClass=”ShibashishMohantyStyleSheet” ShowFooter=”true”>

            <Columns>

                <asp:TemplateField>

                    <HeaderTemplate>

                        Examination Passed

                    </HeaderTemplate>

                    <ItemTemplate>

                        <asp:TextBox ID=”txtExamination” runat=”server”></asp:TextBox>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField>

                    <HeaderTemplate>

                        College/University

                    </HeaderTemplate>

                    <ItemTemplate>

                        <asp:TextBox ID=”txtCollege” runat=”server”></asp:TextBox>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField>

                    <HeaderTemplate>

                        Total Mark

                    </HeaderTemplate>

                    <ItemTemplate>

                        <asp:TextBox ID=”txtTotalMark” runat=”server”></asp:TextBox>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField>

                    <HeaderTemplate>

                        Marks Secured

                    </HeaderTemplate>

                    <ItemTemplate>

                        <asp:TextBox ID=”txtMarksSecured” runat=”server”></asp:TextBox>

                    </ItemTemplate>

                    <FooterTemplate><asp:Button ID=”btnSave” runat=”server”Text=”Save” /></FooterTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

My Gridview(Design View):

Code Behind method to set the default view of the Gridview : 

protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            FillGrid();

        }

    }

    public void FillGrid()

    {

        DataTable table = new DataTable();

        DataRow row;

        DataView view;

        for (int i = 0; i < 4; i++)

        {

            row = table.NewRow();

            table.Rows.Add(row);

        }

        view = new DataView(table);

        grdEducation.DataSource = view;

        grdEducation.DataBind();

    }

My Table (Education_Details):

 

After that add a Type in your Database.

CREATE TYPE Educations

    AS TABLE

    (

         Education_Name varchar(50),

         College_Name varchar(50),

         Total_Mark decimal(18,2),

         Marks_Secured decimal(18,2)

    )

Then create a stored procedure

CREATE PROCEDURE InsertEducationDetails

(

@Education as Educations READONLY

/* “Educations” is the Type you have created earlier */

/* “@Education” is a type of “Educations” like we are using int,varchar etc*/

)

AS

BEGIN

INSERT INTO Education_Details

(

       Education_Name,College_Name,Total_Mark,Marks_Secured

)

SELECT Education_Name,College_Name,Total_Mark,Marks_Secured FROM @Education;

END

Now write this code-behind method in your page and add this as a delegate for the button present inside the gridview.

protected void SaveButton_Click(object sender, EventArgs e)

    {

        DataTable dtEducation = new DataTable();

        DataColumn dcExam = new DataColumn(“Exam_Name”, typeof(string));

        DataColumn dcCollege = new DataColumn(“College_Name”, typeof(string));

        DataColumn dcTotalMark = new DataColumn(“Total_Mark”, typeof(decimal));

        DataColumn dcSecurdedMark = new DataColumn(“Secured_Mark”,typeof(decimal));

       

        dtEducation.Columns.Add(dcExam);

        dtEducation.Columns.Add(dcCollege);

        dtEducation.Columns.Add(dcTotalMark);

        dtEducation.Columns.Add(dcSecurdedMark);

        foreach (GridViewRow gr in grdEducation.Rows)

        {

            TextBox txtExamination = (TextBox)gr.FindControl(“txtExamination”);

            TextBox txtCollege = (TextBox)gr.FindControl(“txtCollege”);

            TextBox txtTotalMark = (TextBox)gr.FindControl(“txtTotalMark”);

            TextBox txtMarksSecured = (TextBox)gr.FindControl(“txtMarksSecured”);

            DataRow drNew = dtEducation.NewRow();

            drNew["Exam_Name"] = txtExamination.Text;

            drNew["College_Name"] = txtCollege.Text;

            drNew["Total_Mark"] = Decimal.Parse(txtTotalMark.Text);

            drNew["Secured_Mark"] = Decimal.Parse(txtMarksSecured.Text);

            dtEducation.Rows.Add(drNew);

        }

        SqlConnection con = new SqlConnection(“—My Connection String—”);

        SqlCommand insertEducations = new SqlCommand(“InsertEducationDetails”, con);

        insertEducations.CommandType = CommandType.StoredProcedure;

        insertEducations.Parameters.AddWithValue(“@Education”, dtEducation);

        con.Open();

        insertEducations.ExecuteNonQuery();

        con.Close();

    }

I have entered values to be inserted into database without using for loop.

After clicking the save button, the multiple values will be inserted into the database at a time.

Result:-

 

Thanks