Priview :


Introduction

 Hi,
    In this tutorial i will explain how to insert the multiple records into database from the front end using C# code. As I previously Posted How to create User-Defined-table Type in SQL Server and How to call User-Defined-table type to Stored procedure by using the above concepts we are passing the values of user defined table type from C#.
Steps to remember :
    1. Create Database table
    2. Create User-defined-Table type
    3. Call the Userdefined table type to the Store Procedure of inserting  records.
    4. Fill the records to DataTable and pass it to the User-defined-Table type
    5. Pass the User-defined-Table type as Parameter to the StoreProcedure
My Database table Structure as Below


UserDefined Table type

Create Type Type_StudentDetails as Table
(
StudentName varchar(300) null,
RegistrationNumber varchar(20) null,
Department varchar(500) null,
FatherName varchar(300) null
)

StoreProcedure

Create Procedure InsertStudentDetails
(
@DetailInsersion Type_StudentDetails Readonly
)
As
Begin
insert into   StudentsTable(StudentName,RegistrationNumber,Department,FatherName)
select StudentName,RegistrationNumber,Department,FatherName from @DetailInsersion
End
GO

ASPX Code :

<html>
<head runat="server">
    <title>Fourthbottle.com</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="text-align:center; width:60%">
    <table width="100%">
    <tr>
    <th>Student Name</th>
    <th>Registration Number</th>
    <th>Department</th>
    <th>FatherName</th>
    </tr>
   
    <tr>
    <td><asp:TextBox ID="name1" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="reg1" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="dep1" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="Fname1" runat="server"></asp:TextBox></td>
    </tr>

     <tr>
    <td><asp:TextBox ID="name2" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="reg2" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="dep2" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="Fname2" runat="server"></asp:TextBox></td>
    </tr>
   
     <tr>
    <td><asp:TextBox ID="name3" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="reg3" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="dep3" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="Fname3" runat="server"></asp:TextBox></td>
    </tr>
   
     <tr>
    <td><asp:TextBox ID="name4" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="reg4" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="dep4" runat="server"></asp:TextBox></td>
    <td><asp:TextBox ID="Fname4" runat="server"></asp:TextBox></td>
    </tr>
   
    </table>
    <br />
    <asp:Button ID="btnSubmit" runat="server" Text="SaveDetails"
            onclick="btnSubmit_Click" />
            <br />
            <asp:Label ID="lblStatus" runat="server"></asp:Label>
    </div>
    </form>
</body>
</html>


C# Code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace MultipleInserts
{
public partial class Page1 : System.Web.UI.Page
{
   protected void btnSubmit_Click(object sender, EventArgs e)
     {
       DataTable Dt = new DataTable();
       Dt.Columns.Add("StudentName");
       Dt.Columns.Add("RegistrationNumber");
       Dt.Columns.Add("Department");
       Dt.Columns.Add("FatherName");
       for (int i = 1; i <= 4; i++)
       {
        string StudentName =Request.Form["name"+i].ToString();
        string RegistrationNumber = Request.Form["reg"+i].ToString();
        string Department = Request.Form["dep" + i].ToString();
        string FatherName = Request.Form["Fname" + i].ToString();
       Dt.Rows.Add(StudentName,RegistrationNumber,Department,FatherName);
       }
       SqlConnection con = new SqlConnection("your connection String");
       SqlCommand cmd = new SqlCommand();
       cmd.CommandText = "InsertStudentDetails";
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Connection = con;
       cmd.Parameters.AddWithValue("@DetailInsersion", Dt);
        try
          {
           con.Open();
           cmd.ExecuteNonQuery();
           con.Close();
           lblStatus.Text = "Details Saved into Database";
          }
          catch (Exception es)
          {
              throw es;
          }
           
        }
    }
}