Friday, November 12, 2010

Save Data Through XML


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

Mat Table Angular

Mat Table in Angular Mat table is used to display data . its a material designed styled data-table . For using Material Table MatTableMo...