ADO.NET – Avent Blog

if we have number of parameters. We can append all parameters to xml parameter and passing as a single parameter in xml. This if helpful for more performance to a stored procedure.

Follow the Below steps

Step-1: Create Table

Step-2: Create Procedure like below

CREATE PROCEDURE [dbo].[USP_XMLInsert]
(
@xmlx xml
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Tbl_Customer
SELECT
Customer.value(‘(Name/text())[1]’,’NVARCHAR(50)‘) as Name , –TAG
Customer.value(‘(City/text())[1]’,’NVARCHAR(50)‘) as City, –TAG
Customer.value(‘(IndustryType/text())[1]’,’NVARCHAR(50)‘) as IndustryType –TAG
FROM
@xmlx.nodes(‘/Customer‘)AS TEMPTABLE(Customer)

END

GO

Step 3: Create sample MVC application and create model class in models folder and create properties like below;

namespace Insert_XMLPROC.Models
{
  public class Customer
{
  public string Name { get; set; }
  public string City { get; set; }
  public string IndustryType { get; set; }
}
}

Step 4: Create controller in controller folder and write insert logic

  public class CustomerController : Controller
{
// GET: Customer
  public ActionResult Index()
{
  return View();
}

  [HttpPost]
  public ActionResult Index(Customer objcustomer)
{
  StringBuilder objstring = new StringBuilder();
objstring.Append(“<Customer>“);
objstring.Append(“<Name>“);
objstring.Append(objcustomer.Name);
objstring.Append(“</Name>“);
objstring.Append(“<City>“);
objstring.Append(objcustomer.City);
objstring.Append(“</City>“);
objstring.Append(“<IndustryType>“);
objstring.Append(objcustomer.IndustryType);
objstring.Append(“</IndustryType>“);
objstring.Append(“</Customer>“);

  XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(objstring.ToString());

  SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection“].ToString());
con.Open();
  SqlCommand cmd = new SqlCommand(“USP_XMLInsert“, con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@xmlx“, xmlDoc.InnerXml);
cmd.ExecuteNonQuery();
  return View();
}
}

Step 5: Create index view and write insert logic

@model Insert_XMLPROC.Models.Customer

@{
ViewBag.Title = “Index“;
}

<h2>Index</h2>

<script src=“~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js“></script>
<div>
<table>
<tr>
<td>Name</td>
<td>
<input type=”text” id=”txtName” />
</td>
</tr>
<tr>
<td>City</td>
<td>
<input type=”text” id=”txtCity” />
</td>
</tr>
<tr>
<td>IndustryType</td>
<td>
<input type=”text” id=”txtIndustryType” />
</td>
</tr>
<tr>
<td>
<input type=”button” id=”btnsubmit” value=”Save” />
</td>
</tr>
</table>

<script type=“text/javascript”>
$(document).ready(function () {
$(‘#btnsubmit‘).click(function () {
$.ajax(
{
type: “POST“,
url: ‘/Customer/Index‘,
data: {
Name: $(‘#txtName‘).val(),
City: $(‘#txtCity‘).val(),
IndustryType: $(‘#txtIndustryType‘).val(),
}

});

});
});
</script>
</div>

Follow below steps to Insert data into database using ajax call in MVC;

Step-1:

Create One table To Save data

Step-2:

Create procedure to insert data like below

CREATE PROCEDURE [dbo].[InsertData]
(
@Address nvarchar(50),
@Name nvarchar(50)
)

AS
BEGIN
INSERT INTO Tbl_Test
(
Address
,Name
)
Values
(
@Address
,@Name
)
END

Step-3:

Create an MVC sample application to insert data

Step-4:

Create Model class in Models folder

Write following code in model class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BundleConfigEx.Models
{
public class SaveData
{
public string Address { get; set; }
public string Name { get; set; }
}
}

Step-5:

Add controller class

paste below code in controller class

using BundleConfigEx.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace BundleConfigEx.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(SaveData objdata)
{
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection”].ToString());
con.Open();
SqlCommand cmd = new SqlCommand(“USP_InsertData”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@Address”, objdata.Address);
cmd.Parameters.AddWithValue(“@Name”, objdata.Name);
cmd.ExecuteNonQuery();
con.Close();

}
catch (Exception)
{

throw;
}
return View(“Index”);
}
}
}

Step-6:

Create a view and paste below code;

@model BundleConfigEx.Models.SaveData
@using System.Web.Optimization;

@{
ViewBag.Title = “Index”;
}

<h2>Index</h2>
<script src=”~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js”></script>
<div>
<table>
<tr>
<td>Address</td>
<td>
<input type=”text” id=”txtaddress” />
</td>
</tr>
<tr>
<td>Name</td>
<td>
<input type=”text” id=”txtname” />
</td>
</tr>
<tr>
<td>
<input type=”button” id=”btnsubmit” value=”Save” />
</td>
</tr>
</table>

});

});
});
</script>
</div>

All the best good luck 🙂

Category: ADO.NET

Passing XML as a parameter in stored procedure and inserting using ADO.NET code with Example In MVC

Insert data using Jquery Ajax Call in ASP.NET MVC