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

XML_Stored_Proc

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

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>


Insert data using Jquery Ajax Call in ASP.NET MVC

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 type=”text/javascript”>
$(document).ready(function () {
$(‘#btnsubmit’).click(function () {
$.ajax(
{
type: “POST”,
url: ‘Home/Index’,
data: {
Address: $(‘#txtaddress’).val(),
Name: $(‘#txtname’).val(),
}

});

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

All the best good luck 🙂