Here tblSalesBritanniaHead is Master Table and tblSalesBritanniaDetails is Transaction Table
Here is the UI we will use this to save data and Update
Here We will save data and update data through XML
Here is the Procedure that we will use to update data and save data
Create PROCEDURE USP_SaveSalesBritannia
@VoucherId int=null,
@VoucherNo varchar(15),
@VoucherDate smalldatetime,
@CustomerId int,
@SalesManId int,
@xml_data xml
as
BEGIN
DECLARE @VoucherIdToUpdate INT
IF( COALESCE(@VoucherId,0)=0)
BEGIN
INSERT INTO tblSalesBritaniaHead(VoucherNo,CustomerId,SalesmanId,VoucherDate)
VALUES (@VoucherNo,@CustomerId,@SalesManId,@VoucherDate)
--Get the generated ID
SELECT @VoucherIdToUpdate=SCOPE_IDENTITY()
INSERT INTO tblSalesBritaniaDetail
(VoucherId,ItemId,Packing,Rate,Cases,Pcs,DiscountPer,DiscountValue,TaxPer,TaxValue,Amount,SpecialDiscount,NetAmount)
SELECT
@VoucherIdToUpdate,
t.ref.value('ItemId[1]', 'bigint'),
t.ref.value('Packing[1]', 'bigint'),
t.ref.value('Rate[1]', 'float'),
t.ref.value('Cases[1]', 'float'),
t.ref.value('Pcs[1]', 'float'),
t.ref.value('DiscountPer[1]', 'float'),
t.ref.value('DiscountValue[1]', 'float'),
t.ref.value('TaxPer[1]', 'float'),
t.ref.value('TaxValue[1]', 'float'),
t.ref.value('Amount[1]', 'float'),
t.ref.value('SpecialDiscount[1]', 'float'),
t.ref.value('NetAmount[1]', 'float')
FROM @xml_data.nodes('/NewDataSet/Record') t(ref)
END
ELSE
BEGIN
UPDATE tblSalesBritaniaHead
SET
SalesManId=@SalesManId,
VoucherDate=@VoucherDate,
CustomerId=@CustomerId
WHERE
VoucherId=@VoucherId
SET @VoucherIdToUpdate=@VoucherId
DELETE FROM tblSalesBritaniaDetail
WHERE VoucherId=@VoucherIdToUpdate
INSERT INTO tblSalesBritaniaDetail
(VoucherId,ItemId,Packing,Rate,Cases,Pcs,DiscountPer,DiscountValue,TaxPer,TaxValue,Amount,SpecialDiscount,NetAmount)
SELECT
@VoucherIdToUpdate,
t.ref.value('ItemId[1]', 'bigint'),
t.ref.value('Packing[1]', 'bigint'),
t.ref.value('Rate[1]', 'float'),
t.ref.value('Cases[1]', 'float'),
t.ref.value('Pcs[1]', 'float'),
t.ref.value('DiscountPer[1]', 'float'),
t.ref.value('DiscountValue[1]', 'float'),
t.ref.value('TaxPer[1]', 'float'),
t.ref.value('TaxValue[1]', 'float'),
t.ref.value('Amount[1]', 'float'),
t.ref.value('SpecialDiscount[1]', 'float'),
t.ref.value('NetAmount[1]', 'float')
FROM @xml_data.nodes('/NewDataSet/Record') t(ref)
END
END
Here is the Save Button Coding
private void ultbtnSave_Click(object sender, EventArgs e)
{
DataTable NewSaveTable = (DataTable)ultraGridBritania.DataSource;
NewSaveTable.TableName = "Record";
System.IO.StringWriter writerXMLDetail = new System.IO.StringWriter();
NewSaveTable.WriteXml(writerXMLDetail, true);
string SXMLDetail = writerXMLDetail.ToString();
string SVoucherNo = Convert.ToString (cmbVoucherNo.Text);
DateTime SVoucherDate = DateTime.Now;
int SCustomerId=0;
int SSalesmanId = 0;
SVoucherDate = Convert.ToDateTime(VoucherDate.Value);
SCustomerId = Convert.ToInt32(cmbCustomeName.Value);
SSalesmanId = Convert.ToInt32(cmbSalesman.Value);
int i = DataLayer.DAL.SaveSalesBritenniaDetails(0, SVoucherNo, SVoucherDate, SCustomerId, SSalesmanId, SXMLDetail);
if (i > 0)
{
UltraDesktopAlert alert = new UltraDesktopAlert { AnimationStyleShow = AnimationStyle.FadeAndScroll, AutoCloseDelay = 5000, Style = DesktopAlertStyle.Office2007 };
alert.Show("SalesBritannia", "Sales Britannia " + " Saved Successfully");
}
else
MessageBox.Show("Error in Data Saving........");
}
Here is the code for Update Button
DataSet dsUpdate;
private void ultbtnUpdate_Click(object sender, EventArgs e)
{
if (cmbVoucherNo.SelectedRow != null)
{
DataTable NewUpdateTable = (DataTable)ultraGridBritania.DataSource;
NewUpdateTable.TableName = "Record";
string UVoucherNo = Convert.ToString(cmbVoucherNo.Value);
DateTime UVoucherDate = Convert.ToDateTime(VoucherDate.Value);
int UCustomerId = Convert.ToInt32(cmbCustomeName.Value);
int USalesmanId = Convert.ToInt32(cmbSalesman.Value);
int VoucherId = Convert.ToInt32(cmbVoucherNo.SelectedRow.Cells["VoucherId"].Value);
System.IO.StringWriter writerXMLDetail = new System.IO.StringWriter();
NewUpdateTable.WriteXml(writerXMLDetail, true);
String UXMLDetail = writerXMLDetail.ToString();
int i = DataLayer.DAL.SaveSalesBritenniaDetails(VoucherId, UVoucherNo, UVoucherDate, UCustomerId, USalesmanId, UXMLDetail);
if (i > 0)
{
UltraDesktopAlert alert = new UltraDesktopAlert { AnimationStyleShow = AnimationStyle.FadeAndScroll, AutoCloseDelay = 5000, Style = DesktopAlertStyle.Office2007 };
alert.Show("SalesBritannia", "SalesBritannia " + " Saved successfully");
}
else
MessageBox.Show("Error in Data Updation........");
}
}
public static int SaveSalesBritenniaDetails(int VoucherId, String VoucherNo, DateTime VoucherDate, int CustomerId, int SalesManId, string DetailXML)
{
try
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("USP_SaveSalesBritannia");
db.AddInParameter(dbCommand, "@VoucherId", DbType.Int32, VoucherId);
db.AddInParameter(dbCommand, "@VoucherNo", DbType.String, VoucherNo);
db.AddInParameter(dbCommand, "@VoucherDate", DbType.DateTime, VoucherDate);
db.AddInParameter(dbCommand, "@CustomerId", DbType.Int32, CustomerId);
db.AddInParameter(dbCommand, "@SalesManId", DbType.Int32, SalesManId);
db.AddInParameter(dbCommand, "@xml_data", DbType.Xml, DetailXML);
dbCommand.CommandType = CommandType.StoredProcedure;
int i = db.ExecuteNonQuery(dbCommand);
//if (i>0)
return i;
}
catch (Exception ex)
{
return -1;
}
}
No comments:
Post a Comment