Dot.Net Seva: Insert bulk data using mvc with dynamic table

///////////////////// Create table

CREATE TABLE [dbo].[Customers](

[ID] [int] NOT NULL,

[Name] [varchar](3) NOT NULL,

[Country] [varchar](30) NULL

)

Database

I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.

Stored Procedure for inserting data into Database

The following Stored Procedure will be used to insert data into the SQL Server database table.

This Stored Procedure will be called using Entity Framework.

CREATE PROCEDURE [dbo].[Insert_Customer]

@Name VARCHAR(100) = NULL

,@Country VARCHAR(100) = NULL

BEGIN

SET NOCOUNT ON;

DECLARE @CustomerId INT

INSERT INTO Customers(Name, Country)

VALUES (@Name, @Country)

SET @CustomerId = SCOPE_IDENTITY()

SELECT @CustomerId [CustomerId]

END

Entity Framework Model

Once the Entity Framework is configured and connected to the database table, the Model will look as shown below.

Importing the Stored Procedure

Next step is to import the Stored Procedure by Right Clicking and selecting Update Model from Database option as shown below.

Then the Stored Procedure to be added needs to be selected and the Finish Button must be clicked.

Finally, in order to call the Stored Procedure using Entity Framework, the Stored Procedure needs to be imported as a Function. Thus, again Right Click and select Add and then click on Function Import option.

This will open the Add Function Import dialog window where the Function Name and the Stored Procedure to be imported as Function is selected.

Finally, the Scalar Radio Button is selected and the Int32 option is chosen from the Drop-Down.

Controller

The Controller consists of two Action methods.

Action method for handling GET operation

Inside this Action method, all the records from the Customers table is returned to the View as a Generic List collection.

Action method for inserting multiple records

Inside this Action method, the JSON array of Customers records is received as Generic List Collection of Customer Entity Class objects.

First all the records of the Customers Table are deleted by using the TRUNCATE command and then a loop is executed over the Generic List Collection of Customer EntityClass objects and one by one records are inserted into database by calling Stored Procedure using Entity Framework.

Finally the count of the records are returned back to the jQuery AJAX function.

Controller

public ActionResult Index()

{

CustomersEntities entities = new CustomersEntities();

return View(entities.Customers);

}

public JsonResult InsertCustomers(List<Customer> customers)

{

using (CustomersEntities entities = new CustomersEntities())

{

//Truncate Table to delete all old records.

entities.Database.ExecuteSqlCommand("TRUNCATE TABLE [Customers]");

//Check for NULL.

if (customers == null)

{

customers = new List<Customer>();

}

//Loop and insert records.

int insertedRecords = 0;

foreach (Customer customer in customers)

{

int customerId = entities.InsertCustomer(customer.Name, customer.Country).FirstOrDefault().Value;

insertedRecords++;

}

return Json(insertedRecords);

}

View

Inside the View, in the very first line the Entity Framework Customer Model class is declared as Model for the View.

Display

For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.

The HTML Table consists of the following elements:

1. thead– The Header row.

2. tbody– Left empty for dynamically adding (inserting) rows to the HTML Table.

3. tfoot– The footer row, consisting of two Text-Boxes and a Button for dynamically adding (inserting) rows to the HTML Table.

Adding a new row

When Add button in the Footer row of the HTML Table is clicked, the value of the Name and Country are fetched from their respective Text-Boxes and are added as a new row to the HTML Table.

Removing a row

When the Remove button in any row of the HTML Table is clicked, it calls the Remove method which removes the row from the HTML Table.

Inserting multiple rows to database using AJAX

When the Save All button is clicked, a loop is executed over all the rows of the HTML Table and a J-SON array of Customer objects is generated.

The J-SON array is then sent to the Controller using j-Query AJAX function and once the response is received it is displayed using JavaScript Alert Message Box.

HTML-View

@model IEnumerable<Insert_Stored_Proc_EF_MVC.Customer>

Layout = null;

}

<!DOCTYPE html>

<html>

<head>

<title>Index</title>

</head>

<body>

<thead>

<tr>

<th style="width:150px">Country</th>

</tr>

</thead>

<tbody>

@foreach (Customer customer in Model)

{

<tr>

<td>@customer.Name</td>

<td>@customer.Country</td>

</tr>

}

</tbody>

<tfoot>

<tr>

</tr>

</tfoot>

</table>

<br/>

$("body").on("click", "#btnAdd", function () {

//Reference the Name and Country TextBoxes.

var txtName = $("#txtName");

var txtCountry = $("#txtCountry");

//Get the reference of the Table's TBODY element.

var tBody = $("#tblCustomers > TBODY")[0];

//Add Row.

var row = tBody.insertRow(-1);

//Add Name cell.

var cell = $(row.insertCell(-1));

cell.html(txtName.val());

//Add Country cell.

cell = $(row.insertCell(-1));

cell.html(txtCountry.val());

//Add Button cell.

cell = $(row.insertCell(-1));

var btnRemove = $("<input />");

btnRemove.attr("type", "button");

btnRemove.attr("onclick", "Remove(this);");

btnRemove.val("Remove");

cell.append(btnRemove);

//Clear the TextBoxes.

txtName.val("");

txtCountry.val("");

});

function Remove(button) {

//Determine the reference of the Row using the Button.

var row = $(button).closest("TR");

var name = $("TD", row).eq(0).html();

if (confirm("Do you want to delete: " + name)) {

//Get the reference of the Table.

var table = $("#tblCustomers")[0];

//Delete the Table row using it's Index.

table.deleteRow(row[0].rowIndex);

}

};

$("body").on("click", "#btnSave", function () {

//Loop through the Table rows and build a JSON array.

var customers = new Array();

$("#tblCustomers TBODY TR").each(function () {

var row = $(this);

var customer = {};

customer.Name = row.find("TD").eq(0).html();

customer.Country = row.find("TD").eq(1).html();

customers.push(customer);

});

//Send the JSON array to Controller using AJAX.

$.ajax({

type: "POST",

url: "/Home/InsertCustomers",

data: JSON.stringify(customers),

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (r) {

alert(r + " record(s) inserted.");

}

});

</script>

</body>

</html>

Dot.Net Seva

Thursday 13 June 2019

Insert bulk data using mvc with dynamic table

No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN