How to Create Grid with input tag and save data in Xml format in to Sql Database(with EF)
####################################Home.js##########################################
#####################################################################################
/// <reference path="../Jquary/jquery-3.3.1.min.js" />
/// <reference path="../Jquary/jquery-ui.min.js" />
/// <reference path="Jcore.js" />
$(document).ready(function () {
$('#btnSave').click(function () {
var invHeader = {};
var invDetail = [];
invHeader.OrderId = $('#txtOrderNo').val();
invHeader.PackageName = $('#txtpackingName').val();
invHeader.OrerDate = $('#dtmorderDate').val();
invHeader.PaymentMethord = $('#cmbPayMethord').val();
$("#tblinvDetail tbody tr").each(function (index) {
var RowNo = $.trim($(this).find('.RowNo').find('input').val());
var OrdrNo = $.trim($(this).find('.OrdrNo').find('input').val());
var PackId = $.trim($(this).find('.PackName').find('select').val());
var PackName = $.trim($(this).find('.PackName').find('select option:selected').html());
var UnitPrice = $.trim($(this).find('.UnitPrice').find('input').val());
var QTY = $.trim($(this).find('.QTY').find('input').val());
invDetail.push({
RowNo: RowNo,
OrdrID: OrdrNo,
PackID: PackId,
PackageName: PackName,
UnitPrice: UnitPrice,
QTY: QTY
})
})
console.log(JSON.stringify(invDetail));
HomeClass.saveData(invHeader, invDetail);
});
HomeClass.fillCombo($("#tblinvDetail tbody tr"), '/Home/getPackage', '', -1);
HomeClass.fillCombo($("#cmbPayMethord"), '/Home/getPackage', '', -1);
});
var HomeClass = {
saveData: function (InvHeader, InvDetail) {
$.ajax({
url: '/Home/Save',
data: JSON.stringify({ 'oInvHeader': InvHeader, 'oInvDetail': InvDetail }),
type: 'POST',
contentType: 'application/json;charset=utf-8',
dataType: 'json',
success: function (data) {
alert('Success');
},
error: function (result) {
alert(result.toString());
}
});
},
addRow: function (data) {
var new_row = '<tr>' +
'<td class="RowNo"> <input type="text" class="" disabled> </td>' +
'<td class="OrdrNo"> <input type="text" class="" > </td>' +
'<td class="PackName">' +
'<select class="PackNamedropdown">' +
//'<option value="">Select one...</option>' +
//'<option value="1">1</option>' +
//'<option value="2">2</option>' +
//'<option value="3">3</option>' +
//'<option value="4">4</option>' +
'</select>' +
'</td>' +
'<td class="UnitPrice"> <input type="text" class="" > </td>' +
'<td class="QTY"> <input type="text" class="" > </td>' +
'<td class="addRow"><button type="button" onclick="HomeClass.addRow(this)" class="btn btn-primary">+</button></td>'+
'</tr>';
$("#tblinvDetail tbody").append(new_row);
this.fillCombo($("#tblinvDetail tbody tr"), '/Home/getPackage', '', -1);
},
fillCombo: function (data, cUrl, css, id) {
debugger;
$.ajax({
url: cUrl + '?id='+id,
//data: JSON.stringify({ 'oInvHeader': InvHeader, 'oInvDetail': InvDetail }),
type: 'GET',
contentType: 'application/json;charset=utf-8',
dataType: 'json',
success: function (a) {
debugger;
if (data.is('tr')) {//Table Drop Down
for (var i = 0; i < data.length; i++) {
i = data.length;
var cmb = data.find('.PackName').find('select')[data.length - 1];
for (var c = 0; c < a.length; c++) {
$(cmb).append('<option value="' + a[c].PacID + '">' + a[c].PackageName + '</option>');
}
}
}
else if(data.is('select')) {//Header Drop down
for (var c = 0; c < a.length; c++) {
$(data).append('<option value="' + a[c].PacID + '">' + a[c].PackageName + '</option>');
}
}
},
error: function (result) {
alert(result.toString());
}
});
}
}
####################################HomeController.cs################################
#####################################################################################
[HttpPost]
public JsonResult Save(tbl_OderHader oInvHeader, List<tbl_OrdrDetail> oInvDetail)
{
Exception msg = null;
try
{
AngularDBEntities mog = new AngularDBEntities();// Entity Object
var param1 = new SqlParameter("@Header", Common.ToXml(oInvHeader));
var param2 = new SqlParameter("@Detail", Common.ToXml(oInvDetail));
//or
/*var param = new SqlParameter() {
ParameterName = "@FirstName",
SqlDbType = System.Data.SqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input,
Size = 50,
Value = "Bill"
};*/
//Common.ToXml(oInvHeader);
var a = mog.Database.ExecuteSqlCommand("SaveSalesOrder @Header,@Detail", parameters: new[] { param1, param2 });
}
catch (Exception ex)
{
msg = ex;
throw;
}
finally
{
}
return Json(msg);
}
[HttpGet]
public JsonResult getPackage(int id)
{
AngularDBEntities mog = new AngularDBEntities();
var param = new SqlParameter("@PackID", 1);
//or
/*var param = new SqlParameter() {
ParameterName = "@FirstName",
SqlDbType = System.Data.SqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input,
Size = 50,
Value = "Bill"
};*/
var students = mog.Packages.SqlQuery("EXEC GetPackages @PackID",parameters: new[] { param});
return Json(students,JsonRequestBehavior.AllowGet);
}
####################################SaveSalesOrder.sql(SP)###########################
####################################################################################
ALTER PROCEDURE [dbo].[SaveSalesOrder]
@Header AS XML,
@Detail AS XML
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @hDoc INT
--#########Begin Header Data##########
EXEC sp_xml_PrepareDocument @hDoc OUT, @Header
INSERT INTO [dbo].[tbl_OderHader]
SELECT
InvHeader.value('(PackageName/text())[1]','VARCHAR(100)') AS PackageName,
InvHeader.value('(OrerDate/text())[1]','DATETIME') AS OrerDate,
InvHeader.value('(PaymentMethord/text())[1]','VARCHAR(100)') AS PaymentMethord,
'' AS Stock
FROM
@Header.nodes('/tbl_OderHader')AS TEMPTABLE(InvHeader)
--#########End Header Data##########
--#########Begin Detail Data##########
EXEC sp_xml_PrepareDocument @hDoc OUT, @Detail
INSERT INTO [dbo].[tbl_OrdrDetail]
SELECT
--InvDetail.value('(RowNo/text())[1]','VARCHAR(100)') AS RowNo,
InvDetail.value('(OrdrID/text())[1]','INT') AS OrdrID,
InvDetail.value('(PackID/text())[1]','INT') AS PackID,
InvDetail.value('(PackageName/text())[1]','VARCHAR(100)') AS PackageName,
InvDetail.value('(UnitPrice/text())[1]','DECIMAL(36,2)') AS UnitPrice ,
InvDetail.value('(QTY/text())[1]','DECIMAL(36,2)') AS QTY
FROM
@Detail.nodes('//ArrayOfTbl_OrdrDetail/tbl_OrdrDetail')AS TEMPTABLE(InvDetail)
--#########End Header Data##########
PRINT 'Sucess TranSaction '
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET ROWCOUNT 0
RAISERROR (15600,-1,-1, '[dbo].[SaveSalesOrder]');
RETURN 0
END CATCH
END
####################################Home.aspx#######################################
####################################################################################
<div id="header" class="row">
</div>
<div id="Mainheader" class="row alert alert-success">
<div class="col-sm-6 alert alert-success">
<div class="form-group">
<label for="lblOrderNo">OrderNo</label>
<input type="text" class="form-control" id="txtOrderNo">
</div>
<div class="form-group">
<label for="lblpackingName">Packing Name</label>
<input type="text" class="form-control" id="txtpackingName">
</div>
<div class="form-group">
<label for="lblorderDate">OrderDate</label>
<input type="date" class="form-control" id="dtmorderDate">
</div>
</div>
<div class="col-sm-6 alert alert-success">
<div class="form-group">
<label for="lblPayMethord">PaymentMethord</label>
<select id="cmbPayMethord" class="form-control">
<%-- <option value="">Select one...</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>--%>
</select>
</div>
<div class="form-group">
<label for="lblOrderNo">OrderNo</label>
<input type="text" class="form-control" id="Text4">
</div>
</div>
</div>
<div id="maindetail" class="row ">
<table class="table" id="tblinvDetail">
<thead>
<tr>
<td>Row No</td>
<td>Ordr No</td>
<td>Pack Name</td>
<td>Unit Price</td>
<td>QTY</td>
</tr>
</thead>
<tbody>
<tr>
<td class="RowNo">
<input type="text" class="" disabled>
</td>
<td class="OrdrNo">
<input type="text" class="">
</td>
<td class="PackName">
<select class="PackNamedropdown">
<%-- <option value="">Select one...</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>--%>
</select>
</td>
<td class="UnitPrice">
<input type="number" class="">
</td>
<td class="QTY">
<input type="number" class="">
</td>
<td class="addRow">
<button type="button" onclick="HomeClass.addRow(this)" class="btn btn-primary">+</button>
</td>
</tr>
</tbody>
</table>
</div>
<div id="buttonAria" class="row alert alert-success">
<div class="col-sm-11"></div>
<div class="col-sm-1">
<button type="button" id="btnSave" class="btn btn-primary">Save</button>
</div>
</div>
<div id="footer" class="row alert alert-success">
</div>
####################################Convert List Object To XML######################
#####################################################################################
public static string ToXml<T>(T obj)
{
using (StringWriter stringWriter = new StringWriter(new StringBuilder(), CultureInfo.InvariantCulture))
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
xmlSerializer.Serialize(stringWriter, obj);
return stringWriter.ToString();
}
}
s
Comments
Post a Comment