Wednesday 29 May 2013

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

No comments:

Post a Comment