How to insert HTML table data into SQL Server in a single batch by using jquery ajax and structured parameter with ASP.NET MVC controller

Question

How to make controller action method for this ajax function with SQL Server stored procedure:

<script type="text/javascript">
        // event to fire on Save button click //
        $(document).on('click', '#btnSave', function () {
            var data = HTMLtbl.getData($('#example-1'));
            var parameters = {};
            parameters.array = data;

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

Function to convert HTML table to jagged array and how to make action result in controller

        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) {

                            cols.push($(this).text().trim());                                // get td Value
                    });
                    data.push(cols);
                });
                return data;
            }
        }
    </script>

In aspx web form I make this to work with webmethod:

public static string SaveData(string[][] array)
{
    string result = string.Empty;

    try
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Month");
        dt.Columns.Add("Week");
        dt.Columns.Add("Area");

        foreach (var arr in array)
        {
            DataRow dr = dt.NewRow();
            dr["Month"] = arr[0];
            dr["Week"] = arr[1];
            dr["Area"] = arr[2];

            dt.Rows.Add(dr);
        }
    }

and then I use SQL Server stored procedure to insert at once :

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_one";
cmd.Connection = cnn;

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

result = cmd.ExecuteNonQuery().ToString();

now i get Request failed: parsererror with this code :

     [HttpPost]
    public JsonResult SaveData(string array)
    {


        using (MY_DB_1Entities dc = new MY_DB_1Entities())
        {
            if (array != null && array != "")
            {
                //Edit 
           Employee emp = JsonConvert.DeserializeObject<Employee>(array);
                dc.Employees.Add(emp);
                dc.SaveChanges();

            }

        }
        return Json(false, JsonRequestBehavior.DenyGet);

    }

when i turn off datatype:json from ajax i get rows save undefined

Transformer · Answer 1 · 2020-04-29T23:20:56.687

0

Since you are sending a JSON string back, just parse/deserialize that into your POCO object and save it.

[HttpPost]
public JsonResult SaveData(string json){  //take the JSON string
    dynamic myTableData = JObject.Parse(json);   // parse it or de-serialize it into your VM / POCO, or just use the dynamic myTableData  above

...
//var temp = myTableData.prop1;
return Json(false, JsonRequestBehavior.AllowGet);

}

then save it to your DB.. the rest is straight forward.

Updated based on additional question:

Get your type info and use Entity Frame work, then save

Employee emp = JsonConvert.DeserializeObject<Employee>(jsonString);
db.Employees.Add(emp);
db.SaveChanges(); this will save it and your are done.

edited Apr 29 '20 at 23:20

answered Apr 29 '20 at 03:00

Transformer

6,963
2
26
52

now to proceed like this: foreach (var arr in myTableData) { DataRow dr = dt.NewRow(); dr["Month"] = arr[0]; dr["Week"] = arr[1]; – LND Apr 29 '20 at 12:15
I don't understand what you are asking, what did you try, and what did you get, what failed, or simply post a separate question. Since you have the `POCO` object, your EF context can save it. For e.g 1) `Employee emp = JsonConvert.DeserializeObject(jsonString);` 2) `db.Employees.Add(emp);` 3) `db.SaveChanges();` this will save it and your are done. – Transformer Apr 29 '20 at 20:45
Hi transformer, i make same update with your sugestion but i got error, please see my update on post.Thank you, – LND Apr 29 '20 at 22:27
Thats because either your Ajax parser is not correctly setup or your return type is not returning a JSON object , please change the DenyGet to `AllowGet` and change the setup of your [Ajax call](https://stackoverflow.com/a/11507572/6085193). If this works for you please mark as answer! – Transformer Apr 29 '20 at 23:20

How to insert HTML table data into SQL Server in a single batch by using jquery ajax and structured parameter with ASP.NET MVC controller

1 Answers1