Add Row Number in SQL SERVER

Select Row_number()  over (order by [Name]) 'SNO', ID, Name from emp

Validate GridCell with Decimal Value in C#

Validate a Cell only Decimal No is Input , Alphabet should not be  enter in that Cell only no and one point(.) should be enter and Backspace shoul be allowed
To do this you have to make a Method Named validate(char Key) and after write code on keyPress Event

      #region Decimal Validation

        public static bool validate(char Key)
        {
            byte i;
            bool parsed = byte.TryParse(Key.ToString(), out i);
            bool val = true;

            if (parsed)
            {
                val = false;
            }
          
            else if (parsed == false)
            {
                switch (Convert.ToInt32(Key))
                {
                    default:
                        val = true;
                        break;
                    case 8:
                        val = false; // If Backspace, allow char.
                        break;
                    case 46:
                        val = false; // If Delete, allow char.
                        break;
                }
            }
            return val;
        }

        #endregion

This is the method you have to write

Write Code on Key Press Event

void datagrigview1_KeyPress(object sender, KeyPressEventArgs e)
{
#region ValidateDecimal

if (datagrigview1.CurrentCell.OwningColumn.Name == "Quantity")

{
e.Handled = true;
int m = e.KeyChar;

if (e.KeyChar == 46)
{
//if (dGVTrans.CurrentCell.Value != null)
{

int n =datagrigview1 .CurrentCell.EditedFormattedValue.ToString().IndexOf(".");
if (n > 0)
{
return;
}
}
}

e.Handled = validate(e.KeyChar);
}


#endregion

How To Make Composite Key in SQL SERVER

CREATE TABLE Product
(
  ProductId int,
  ProductName varchar(30),
  Price int,
  Remark Varchar(300),
  PRIMARY KEY (ProductId, ProductName)
)

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;

            }

        }

Check Existence of Column In Table SQL SERVER

SELECT Name FROM sys.all_columns WHERE
[object_id] =OBJECT_ID(N'dbo.TableName') AND NAME = 'ColumnName'


SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
AND COLUMN_NAME = 'YourColumnName'