How to Insert HTML Table Data into SQL Server in a Single Batch by Using JQuery Ajax and Structured Parameter

• Download source (RAR) - 651 KB
• Download source (ZIP) - 651 KB

Introduction

Recently, when I was working on a project, I had a Page in ASP.NET with master and details Data. Master data was present as textbox fields and Details Data was in a pure HTML Table.

What I wished then to pass my data (both Master and Details) to Web Method without much effort.

Here, I will be showing to save Details Data by converting it to a jagged array (array of an array) and pass it to Web Method and then Passing Details Data to database with the help of Structured Parameter without using StringBuilder or XML to send data to SQL Server in bulk.

Personally, I don't like stringbuilder or XML approach.

There may be several approaches. It's just one of them that I learnt.

What You Must Know?

Knowledge of C#, ASP.NET, Web Method (there may be Web Services, WCF, Web API, etc.), Jquery Ajax, basic SQL knowledge (Types, Stored Procedures) and Visual Studio 2012.

How Things Work?

1. We will first convert the HTML table to jagged array.
2. Pass this jagged array to WebMethod by Jquery Ajax request.
3. Fill a DataTable with jagged array data.
4. Pass this filled Datatable to stored procedure with Structure Parameter.
5. In USP Table-Valued Type will fetch the data passed with Structure Parameter.
6. Finally, insert data into database table from Table-Valued Type.

Let's go step by step.

Assume that you have an HTML table in your ASP.NET page. The HTML Table may have dropdowns, hidden fields, textarea, checkboxes, etc. Here, I am going through a very basic example of an HTML table.

<table id="tbldata" style="width: 100%;" border="1">
    <tr>
        <th>Name</th>
        <th>Last Name</th>
        <th>Age</th>
        <th>Address</th>
        <th>comments</th>
        <th>Course</th>
        <th>Is Eligible ?</th>
        <th>Hidden fields</th>
    </tr>
    <tr>
        <td>Amit</td>
        <td>Singh</td>
        <td>23</td>
        <td>
            <input type="text" value="Amit's Address" /></td>
        <td>
            <textarea rows="4" cols="50">your comments </textarea>
        </td>
        <td>
            <select>
                <option value="0">MBA</option>
                <option value="1">B.E.</option>
            </select>
            
        </td>
        <td>
            <input type="checkbox" />
        </td>
        <td>
            <input type="hidden" value="1" />
        </td>
    </tr>
    <tr>
        <td>Sakshi</td>
        <td>Singh</td>
        <td>20</td>
        <td>
            <input type="text" value="Sakshi's Address" /></td>
        <td>
            <textarea rows="4" cols="50">your comments</textarea>
        </td>
        <td>
            <select>
                <option value="0">MBA</option>
                <option value="1">B.E.</option>
            </select>
            
        </td>
        <td>
            <input type="checkbox" />
        </td>
        <td>
            <input type="hidden" value="2" />
        </td>
    </tr>
    <tr>
        <td>Ram</td>
        <td>Singh</td>
        <td>25</td>
        <td>
            <input type="text" value="Ram's Address" /></td>
        <td>
            <textarea rows="4" cols="50">your comments </textarea>
        </td>
        <td>
            <select>
                <option value="0">MBA</option>
                <option value="1">B.E.</option>
            </select>
         </td>
        <td>
            <input type="checkbox" />
        </td>
        <td>
            <input type="hidden" value="3" />
        </td>
    </tr>
    <tr>
        <td>Rahul</td>
        <td>Sharma</td>
        <td>23</td>
        <td>
            <input type="text" value="Rahul's Address" /></td>
        <td>
            <textarea rows="4" cols="50">your comments </textarea>
        </td>
        <td>
            <select>
                <option value="0">MBA</option>
                <option value="1">B.E.</option>
            </select>
            
        </td>
        <td>
            <input type="checkbox" />
        </td>
        <td>
            <input type="hidden" value="4" />
        </td>
    </tr>
</table>

So, we have got a table having some fields, inputbox, checkbox, hidden fields, dropdown and textarea too.

I have written a code that converts a table into a jagged array. Just reference Jquery in your page before using this code.

var HTMLtbl  =
  {
      getData: function (table) {
          var data = [];
          table.find('tr').not(':first').each(function (rowIndex, r) {
              var cols = [];
              $(this).find('td').each(function (colIndex, c) {

                  if ($(this).children(':text,:hidden,textarea,select').length > 0)
                      cols.push($(this).children('input,textarea,select').val().trim());

   // if dropdown text is needed then uncomment it and remove SELECT from above IF condition//
                      // else if ($(this).children('select').length > 0)
                      // cols.push($(this).find('option:selected').text());

                  else if ($(this).children(':checkbox').length > 0)
                  cols.push($(this).children(':checkbox').is(':checked') ? 1 : 0);
                  else
                      cols.push($(this).text().trim());
              });
              data.push(cols);
          });
          return data;
      }
  }

This function converts Table to a jagged array.

We call this function on a button's click event like this:

// event to fire on Save button click //
    $(document).on('click', '#btnSave', function () {
        var data = HTMLtbl.getData($('#tbldata'));  // passing that table's ID //
        var parameters = {};
        parameters.array = data;

        var request = $.ajax({
            async: true,
            cache: false,
            dataType: "json",
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "index.aspx/SaveData",
            data: JSON.stringify(parameters)
        });

        request.done(function (msg) {
            alert("Row saved " + msg.d);
        });

        request.fail(function (jqXHR, textStatus) {
            alert("Request failed: " + textStatus);
        });

    });

It executes an Ajax request. We will handle this request in WebMethod.

Though we are passing a Jagged array to WebMethod, parameter of the WebMethod must also be a jagged array.

[WebMethod]
public static string SaveData(string[][] array)

This is how we will be handling Ajax request and saving data to database.

We create a DataTable with the exact columns as we will create in Table-Value Type in the database.

Fill this DataTable with the data in jagged array with the help of Foreach.

Open the connection for our database.

Choose commandType to be StoredProcedure.

Pass the name of the SP, i.e., USPSaveDetails in my case.

Now, the most important one we pass the DataTable to a structured parameter of sqlCommand which is taken care by a Table-Valued Type in USP.

cmd.Parameters.Add("@TableType", SqlDbType.Structured).SqlValue = dt;

Here is the full code:

[WebMethod]
   public static string SaveData(string[][] array)
   {
       string result = string.Empty;
       try
       {
           //One thing to keep in mind Column Names of DataTable
           //must be same as Table-Valued Type parameters//
           //Please refer commented queries in the bottom.
           //Just execute all of them in the Database sequentially//
           //Then change Webconfig connectionstring according to you//

           DataTable dt=new DataTable();
           dt.Columns.Add("FirstName");
           dt.Columns.Add("LastName");
           dt.Columns.Add("Age");
           dt.Columns.Add("Address");
           dt.Columns.Add("comments");
           dt.Columns.Add("Course");
           dt.Columns.Add("Eligible");
           dt.Columns.Add("HFID");

           foreach (var arr in array)
           {
               DataRow dr = dt.NewRow();
               dr["FirstName"] = arr[0];
               dr["LastName"] = arr[1];
               dr["Age"] = arr[2];
               dr["Address"] = arr[3];
               dr["comments"] = arr[4];
               dr["Course"] = arr[5];
               dr["Eligible"] = arr[6];
               dr["HFId"] = arr[7];
               dt.Rows.Add(dr);
           }

           SqlConnection cnn = new SqlConnection();
           cnn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
           ["DefaultConnection"].ToString();
           cnn.Open();
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText="USPSaveDetails";
           cmd.Connection = cnn;
           cmd.Parameters.Add("@TableType", SqlDbType.Structured).SqlValue = dt;

           result= cmd.ExecuteNonQuery().ToString();
       }
       catch (Exception ex)
       {
           result = ex.Message;
       }
       return result;
   }

And in database:

We create a table into which we will insert data passed through Table-Valued type.

CREATE TABLE [dbo].[Table] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [FirstName] NVARCHAR (50) NULL,
    [LastName]  NVARCHAR (50) NULL,
    [Age]       INT           NULL,
    [Address]   NVARCHAR (50) NULL,
    [comments]  NVARCHAR (50) NULL,
    [Course]    INT           NULL,
    [Eligible]  BIT           NULL,
    [HFId]      INT           NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

And then after creating the table, we will create a Table-Valued Type.

CREATE TYPE TableType AS TABLE 
(
    [FirstName] NVARCHAR (50) NULL,
    [LastName]  NVARCHAR (50) NULL,
    [Age]       INT           NULL,
    [Address]   NVARCHAR (50) NULL,
    [comments]  NVARCHAR (50) NULL,
    [Course]    INT           NULL,
    [Eligible]  BIT           NULL,
    [HFId]      INT           NULL
 );

and then the Stored procedure with a parameter in it with type TableType (that we created above).

CREATE PROCEDURE [dbo].[USPSaveDetails]
(
	@TableType TableType READONLY
)
AS

begin
 INSERT INTO [Table](FirstName,LastName,Age,Address,comments,Course,Eligible,HFId)
 select FirstName,LastName,Age,Address,comments,Course,Eligible,HFId FROM @TableType 
end

• In this USP, we are selecting rows from TableType and insert them into Table table.

That's it. The task is done. We just inserted the rows in a single batch!!!!!

You can find a demo in the attached zip file.