Monday, December 6, 2010

DateTimePicker Format in C#

Here are Some Different Types of Format of  DateTimePicker













dateTimePicker2.Format = System.Windows.Forms.DateTimePickerFormat.Short;

dateTimePicker3.Format = System.Windows.Forms.DateTimePickerFormat.Time;

dateTimePicker4.CustomFormat = "yyyy-mm-dd";

dateTimePicker4.Format = System.Windows.Forms.DateTimePickerFormat.Custom;

Saturday, December 4, 2010

Select Record between Months in SQL

select ID, Name, Month from (
    select ID, Name, Month,
        case Month
            when 'January' then 1
            when 'February' then 2
            when 'March' then 3
            when 'April' then 4
            when 'May' then 5
            when 'June' then 6
            when 'July' then 7
            when 'August' then 8
            when 'September' then 9
            when 'October' then 10
            when 'November' then 11
            when 'December' then 12
           
        end as MonthNo
    from
        Emp
 ) as TempTable
 where
    MonthNo between 1 and 12 order by [Month]

Wednesday, December 1, 2010

Convert String In Title Case C#


string s = "This is a test";
s = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(s.ToLower())

Convert ArrayList to String

ArrayList ar =new ArrayList();
string str=string.Empty;
str = String.Join(',', (string []) ar.ToArray(typeof(string)))


Tuesday, November 16, 2010

Add Row Number in SQL SERVER

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

Saturday, November 13, 2010

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)
)

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;

            }
        }











Thursday, November 11, 2010

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'

Friday, October 29, 2010

Add a DataGridViewColumn in DataGridView , C#

DataGridViewColumn dgvc = new DataGridViewColumn();


dgvc.AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;

dgvc.HeaderText = st;

dgvc.Name = st;

DataGridViewCell newcell = new DataGridViewTextBoxCell();

dgvc.CellTemplate = newcell;

dgvc.Visible = true;

datagridview1.Columns.Add(dgvc);

datagridview1.Rows[0].Cells[st].Value ="500";

Split a String Seperated With \ , C#

string sSuppName = "A/B/C/D/E";
string[] str1 = sSuppName.Split('/');
  
output     A
              B
              C
              D

string sSuppName = "A\B\C\D\E";
string[] str1 = sSuppName.Split('\\');

output    A
             B
             C
             D

Followers

Visitor No

click for free hit counter
Download a free hit counter here.