How to Use JSON for Data Insertion in ASP.NET

Json Insert list of Object into Database in Asp.net

Introduction

In the realm of ASP.NET development, leveraging JSON for data insertion can significantly enhance efficiency and flexibility. Building on my previous article on inserting data into a database using JSON, this post delves into the intricacies of inserting a collection of data objects. We'll explore the creation of database tables, HTML markup for the design page, and C# coding, coupled with jQuery for seamless JSON handling.

Creating Database Tables:

To begin, let's establish the foundation by creating two essential database tables - Student and Subject. The Student table encompasses information about individual students, while the Subject table accommodates multiple subjects associated with each student.

 Create Table Student(
  ID int,
  Name varchar(50),
  Gender varchar(50))

 Create Table Subject(
  studentID int,
  subjectName varchar(50))

HTML Markup for Design Page

In the design page, we incorporate a listbox featuring predefined subject names, allowing users to select multiple subjects when creating student records. The chosen subjects are then inserted into the Subject table.

<asp:TextBox ID="txtID" runat="server" placeholder="studentID"></asp:TextBox><br />
<asp:TextBox ID="txtName" runat="server" placeholder="student Name"></asp:TextBox><br />
<asp:TextBox ID="txtGender" runat="server" placeholder="Gender"></asp:TextBox><br />
<asp:Button ID="BtnInsert" runat="server" Text="Insert" />
   
<asp:ListBox ID="lbSubject" runat="server" SelectionMode="Multiple" Width="300px" Height="200px">
   <asp:ListItem Text="English" Value="1"></asp:ListItem>
   <asp:ListItem Text="Science" Value="2"></asp:ListItem>
   <asp:ListItem Text="Computer" Value="3"></asp:ListItem>
   <asp:ListItem Text="IT" Value="4"></asp:ListItem>
   <asp:ListItem Text="Mathematic" Value="5"></asp:ListItem>
</asp:ListBox>

C# Coding : Adding Student and Subject Class

Create two classes, Student and Subject, with automatic properties to facilitate data transfer and validation between classes. The Subject class encapsulates an array to store subjects associated with a student.

 public class Student
 {
  public int ID { get; set; }
  public string Name { get; set; }
  public string Gender { get; set; }
  public string[] Subject { get; set; }
 }
 public class Subject
 {
  public int ID { get; set; }
  public string Name { get; set; }
 }

C# Coding : Adding Namespace

C# Coding : Insert Method

Implement the Insert web method in C# to handle the insertion of student and subject data into the respective tables.

 [WebMethod]
 public static void Insert(Student stu)
 {
   SqlConnection con = new SqlConnection("Server=.; Database=master; User Id=sa; Password=tiger;");
   SqlCommand cmd = new SqlCommand("insert into Student(ID,Name, Gender) values('" + stu .ID+ "','" + stu.Name + "','" + stu.Gender + "')", con);
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
   if (stu.Subject != null)
   {
    foreach (string strSub in stu.Subject)
    {
     SqlCommand cmdov = new SqlCommand("Insert into Subject(studentID, subjectName) values('" + stu.ID + "','" + strSub + "')", con);
     con.Open();
     cmdov.ExecuteNonQuery();
     con.Close();
    }
   }
 }

jQuery : JSON Method

Integrate jQuery to handle JSON data and initiate an AJAX request to the Insert web method for seamless data insertion.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
    $('[id$=BtnInsert]').click(function () {
       var student = {};
       var subject = new Array();
       var i = 0;
         student.Name = $('[id$=txtName]').val();
         student.Gender = $('[id$=txtGender]').val();
         student.ID = $('[id$=txtID]').val();
                
         //Getting Selected List Items and stored in array
         $('[id$=lbSubject] option:selected').map(function () {
             var list = $(this).text();
             subject[i] = list;
             i++;
          });
         student.Subject = subject;
                
        $.ajax({
           contentType: 'application/json; charset=utf-8',
           type: 'post',
           url: 'WebForm1.aspx/Insert',
           dataType: 'json',
           data: JSON.stringify({ stu:student}),
           success: function (data) {
                    alert('success');
           },
           error: function (result) {
                    alert('error');
           }
         });
      });
   });
});
</script>

Conclusion

This comprehensive guide demonstrates the step-by-step process of inserting a list of objects using JSON in ASP.NET. By creating database tables, implementing C# coding for data insertion, and integrating jQuery for efficient JSON handling, developers can streamline the process and enhance the functionality of their web applications.