Techniques for adding the numbers

1.Techniques for adding the numbers 1 to 100

Sum from 1 to n =


Sum from 1 to 100 =

2.Let's prove the second statement.

1 + 2² + 3² + ... + n² =

3.The sum of cubes

  

print a triangle of stars using SQL Server

DECLARE @count INT,@num INT,@num1 INT, @space INT, @str varchar(50)
SET @count =10 SET @num = 1
WHILE(@num<=@count)
BEGIN
    SET @num1 = 0 SET @space = @count-@num
    WHILE (@num1<@num)
    BEGIN
        if @str is null
            SET @str = '* '
        ELSE
            SET @str = @str+'* '   SET @num1 = @num1+1
    END
    PRINT (space(@space)+@str)
    SET @num = @num+1   SET @str = null
END

Find Nth column of the Particular Table – Query to Retrieve the Nth value

SELECT TOP 1 *
    FROM (
            SELECT TOP 3 *,ROW_NUMBER() OVER ( ORDER BY ColumnNameBasedOnWhichOrder DESC) AS RowNum
            FROM TableName
        )a 
ORDER BY
    a.RowNum ASC

SELECT TOP 1 ColumnDetailID
FROM (
SELECT DISTINCT TOP 6 ColumnDetailID
FROM TableName
ORDER BY ColumnDetailID DESC) a
ORDER BY ColumnDetailID


SELECT TOP 1 ColumnDetailID
FROM
(
    SELECT TOP 3 ColumnDetailID
    FROM TableName
    ORDER BY ColumnDetailID DESC
) SalarySubquery
ORDER BY ColumnDetailID ASC

Diff between SQL Server 2005 and SQL Server 2008

"What is the diff. between Sqlserver 2005 and Sqlserver 2008"?

Here I am going to describe only 3 differences which is related to Declare and Set Keywords of Sql.

Differences 

1. You can declare variable and assign the value without using Set keyword in sqlserver 2008. / but not in 2005

2. You can increment the value like c# @i+=1 in sqlserver 2008. / but not in sqlserver 2005

3. You can put the break point for debug query  in sqlserver 2008. / but not in sqlserver 2005

...many other diff but here is only 3 we describing 

Declare @I int = 1

while(@I<=10)

begin

print @i

set @i+=1

end

More Details:
http://www.c-sharpcorner.com/blogs/5451/difference-between-sql-server-2005-and-2008.aspx

Computed Column – PERSISTED and Storage

If we are creating one computed column in the database it won't take more size of that computed column until we give the PERSISTED keyword

It does not increase the data size of the original table as well as it does not turn the column into persisted. When we create an index on the column of the table, there is an additional space occupied by that index.

More Details:
http://blog.sqlauthority.com/2010/08/21/sql-server-computed-column-persisted-and-storage-part-2/

Simple DataSet Sorting in C#

           Simple DataSet Sorting in C# 

            DataSet dsTest = new DataSet();
            DataTable dtSample = new DataTable("SampleTable");
            DataColumn dcName = new DataColumn("Name",typeof(string));
            dtSample.Columns.Add(dcName);
            dtSample.Columns.Add("Age", (typeof(Int32)));
            dtSample.Rows.Add("Lawrence", 25);
            dtSample.Rows.Add("Dinesh", 30);
            dtSample.Rows.Add("Aruna", 24);
            dtSample.Rows.Add("Deb", 35);
            dtSample.Rows.Add("Satish", 42);
            DataView dvSample = new DataView(dtSample);
            dvSample.RowFilter = "Age <> 30";
            dvSample.Sort = "Age Asc";
            dsTest.Tables.Add(dtSample);

Adding One more column with existing DataTable

               object objCheckAmount;
                if (ReceivableDT.Columns.Contains("CheckAmountWithTax") == false)
                {
                    ReceivableDT.Columns.Add("CheckAmountWithTax", typeof(System.Double), "Paid + TaxPaid");
                }
                objCheckAmount = ReceivableDT.Compute("Sum(CheckAmountWithTax)", "Select = 'true' and ReceivableHeaderID='" + ReceivableHeaderID + "'");
                if (objCheckAmount != DBNull.Value)
                {
                    CheckAmount = Convert.ToDouble(objCheckAmount);
                }

Rounding Functions IN SQL Server, C# and JavaScript

/// SQL Query to Round the values in different format

 DECLARE @value decimal(18,5)
SET @value = 6.001
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)


/// C# code to Round the values in different format

decimal Value = Convert.ToDecimal(Console.ReadLine());
System.Console.WriteLine("RoundValue={0}\nCeilingValue={1}\nFloorValue={2}", Math.Round(Value),Math.Ceiling(Value),Math.Floor(Value));

/// JavaScript code to Round the values in different format

var ceilValue = Math.ceil(Value); 
var floorValue = Math.floor(Value);
var roundValue = Math.round(Value);
 

Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

More Details:

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/ 

Difference between On clause and Where clause when used with left join in SQL Server

 Below Link has the Elaborate explanation. Please go through it.
http://www.codeproject.com/Articles/231132/Difference-between-On-clause-and-Where-clause-when

Use of PARSEONLY Keyword in SQL Server

Syntax:

SET PARSEONLY { ON | OFF }

* When SET PARSEONLY is ON, SQL Server only parses the statement.
* When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.
The setting of SET PARSEONLY is set at parse time and not at execute or run time.
Do not use PARSEONLY in a stored procedure or a trigger. SET PARSEONLY returns offsets if the OFFSETS option is ON and no errors occur.

Examples


 Eg 1:   SET PARSEONLY ON;
            SELECT * FROM Lawrence_Setup
//This will parse the query. It wont compile and execute. It will check the syntax error only.


  Eg 2:  SET PARSEONLY OFF;
            SELECT * FROM Lawrence_Setup  
//This will compile and execute the query and also It will return the output vales based on the query

Using NOLOCK and READPAST table hints in SQL Server

//About Lock in SQL Server
URL-1:http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

URL-2:http://blog.sqlauthority.com/2011/05/08/sql-server-what-kind-of-lock-with-nolock-hint-takes-on-object/

To filter the value from the Data Table

            DataSet DSDocumentationFee = SelectLeaseDocumentationFeeSlabDetails();
            DataTable DTDocumentationFee = DSDocumentationFee.Tables[0];
            DataRow[] rows = DTDocumentationFee.Select("LeaseCostFrom <= " + LeaseCost + " AND  LeaseCostTo >=" + LeaseCost);
            DocumentationFee = Convert.ToDouble(rows[0]["DocumentationFeeAmount"]);

To get the Date format in SQL Server

 Method:1
//SET DATEFORMAT mdy
Declare @DueDay varchar(10)           
Declare @Month varchar(10)
Declare @Year varchar(10)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(11) 

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select @CommenceDate=CONVERT(varchar(11), Convert(varchar(2),@DueDay)+'/'+Convert(varchar(2),@Month)+'/' + Convert(varchar(4),@Year), 104)
print @CommenceDate

Method:2
CREATE FUNCTION [dbo].[FnDateTime]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
    DECLARE @Dateformat INT
    DECLARE @ReturnedDate VARCHAR(80)
    SELECT @DateFormat=CASE @format
    WHEN 'mm/dd/yyyy' THEN 101
    WHEN 'dd/mm/yyyy' THEN 103
    WHEN 'yyyy/mm/dd' THEN 111
    END
    SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
RETURN @ReturnedDate
END

SELECT [dbo].[FnDateTime] ('8/7/2008', 'dd/mm/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'mm/dd/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

Declare @DueDay varchar(5)           
Declare @Month varchar(5)
Declare @Year varchar(5)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(50)  

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104)
set @CommenceDate= [dbo].[FnDateTime] (CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104),'dd/mm/yyyy')

print @CommenceDate

//More Detail you can use the below link
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

http://sandeep-tada.blogspot.in/2012/01/format-date-with-sql-server-function-in.html

To Export the Crystal Report from Dataset through the C# Codes

// To create the DataSet by adding Add->new Item..

        private void btnReport_Click(object sender, EventArgs e)
        {
            ReportDocument cryRpt;
            ExportOptions CrExportOptions;
            DiskFileDestinationOptions CrDiskFileDestinationOptions;
            PdfRtfWordFormatOptions CrFormatTypeOptions;
                cryRpt = new ReportDocument();
                cryRpt.Load("C:\\LawrenceSoft\\Sample.rpt");
                DataSet DataNew = new DataSet();
                DataNew.ReadXml("F:\\LawrenceSoft\\VatDetails.xml");
                DataTable dtReportSource = (DataTable)DataNew.Tables[0];
                cryRpt.SetDataSource(dtReportSource);

                CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
                CrFormatTypeOptions = new PdfRtfWordFormatOptions();
                SaveFileDialog sfdSavePDF = new SaveFileDialog();
                sfdSavePDF.OverwritePrompt = true;
                sfdSavePDF.SupportMultiDottedExtensions = true;
                sfdSavePDF.Filter = "PDF files (*.pdf)|*.pdf";
                sfdSavePDF.ShowDialog(this);
                CrDiskFileDestinationOptions.DiskFileName = sfdSavePDF.FileName;//@"c:\\test.pdf";
                if (string.IsNullOrEmpty(CrDiskFileDestinationOptions.DiskFileName) == false)
                {
                    CrExportOptions = cryRpt.ExportOptions;
                    {
                        CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                        CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                        CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                        CrExportOptions.FormatOptions = CrFormatTypeOptions;
                    }
                    cryRpt.Export();
                }

        }
//To use the Report Viewer for showing the Crystal Report..

            DataSet DataNew = new DataSet();
            DataNew.ReadXml("F:\\LawrenceSoft\\VatDetails.xml");
            DataTable dtReportSource = (DataTable)DataNew.Tables[0];
            ReportDocument rdSample = new ReportDocument();

            rdSample.Load("C:\\LawrenceSoft\\Sample.rpt");
            rdSample.SetDataSource(dtReportSource);
            crystalReportViewer1.Visible = true;
            crystalReportViewer1.ReportSource = rdSample;
            crystalReportViewer1.Refresh();

To Kill the Database transaction using the query

//Below query for getting system process id in the master database
select  kpid,* from sysprocesses where dbid=DB_ID('Training')


//Based on the process id we can kill the process of Database
kill kpid[processId] eg:- kill 53

Disable CUT,COPY,PASTE in your ASP.NET textbox

 Here I will explain how to disable copy, cut and paste functionality (Ctrl + c/Ctrl + v/ Ctrl+ x) in asp.net textbox using JavaScript.

            To achieve this we have two methods first method is directly set copy, cut and paste options return false in textbox to disable and second one we can use JavaScript functionality to disable copy, cut and paste options.

First Method

<asp:TextBox ID="TextBox1" runat="server" oncopy="return false" oncut="return false" onpaste="return false"></asp:TextBox>

Second Method

JavaScript Function:

         function DisableControlKey(e) {

                  // Message to display

                  var message = "Cntrl key/ Right Click Option disabled";

                  // Condition to check mouse right click / Ctrl key press

                  if (e.which == 17 || e.button == 2) {

                  alert(message);

                  return false;

                     }

                  }

            

In .aspx page

<asp:TextBox ID="txtUser" runat="server" onKeyDown="return DisableControlKey(event)"

onMouseDown="return DisableControlKey(event)"></asp:TextBox>

Expiring a page on clicking browser back button in Asp.net?

It is most common in some scenarios for us to expire a page as soon as the user clicks the back button in the web browser. This will be needed in scenarios like, when registering a new user or company in our web application.


For all who need this, I thought of sharing the code which will accomplish the same.


Response.Expires = -1;
Response.Cache.SetNoServerCaching();
Response.Cache.SetAllowResponseInBrowserHistory(false);
Response.CacheControl ="no-cache";
Response.Cache.SetNoStore();


Just paste the above code in the Page_Load event of your asp.net web page for which you want to expire when user clicks browser back button.

How to convert the table values to XML file and XSD file

SqlConnection Con = new SqlConnection("server=.;uid=.;Pwd=.;database=.");
SqlDataAdapter da = new SqlDataAdapter("Select top 10 * from SoftLawrence", Con);
DataSet DS = new DataSet();
da.Fill(DS);
DS.WriteXml("F:\\SoftLawrence\\Sample.XML");
DS.WriteXmlSchema("F:\\SoftLawrence\\Test.XSD");

//To convert the Xml file to Dataset
DataSet dsVat = new DataSet();
string appPath =  GetExecutingPath();
dsVat.ReadXml(appPath + "Sample.xml");
 return dsVat;

To give Bold in the particular word of the Asp label control text

<asp:Label ID="lblText" runat ="server" Text="To attach a file, type the path of the file in the field below or click on the &lt;b&gt;Browse&lt;/b&gt; button to locate it in your computer." />

Use of COALESCE in the SQL Server

DECLARE @RegistrationNo VARCHAR(1000);    

                    SELECT  @RegistrationNo = COALESCE( CASE WHEN CAST(@RegistrationNo AS VARCHAR(1000)) IS NOT NULL AND @RegistrationNo<>'' then CAST(@RegistrationNo AS VARCHAR(1000)) + ', ' end, '') +''+ CAST(RegistrationNo AS VARCHAR(100))   

                    FROM Table_Name

                    SELECT '[' + @RegistrationNo + ']'

To convert the DataSet to the JsonString

[AjaxPro.AjaxMethod]
    public string GetContractJsonString()
    {
var dataSource = ContractServiceInstance.SelectContractList(cultureName, lessorName, customerId);
        return dataSource.ToJsonString();
}

 /// <summary>
    /// ToJSONString
    /// </summary>
    /// <param name="ds"></param>
    /// <returns></returns>
    public static string ToJsonString(this DataSet ds)
    {
        return ToJsonString(ds.Tables[0]);
    }

    public static string  ToJsonString(this DataTable dataTable)
    {
        var strDc = new string[dataTable.Columns.Count];
        var headStr = string.Empty;
        if (dataTable.Rows.Count == 0)
        {
            return "";
        }
        for (var i = 0; i < dataTable.Columns.Count; i++)
        {
            strDc[i] = dataTable.Columns[i].Caption;
            headStr += "\"" + strDc[i] + "\" : \"" + strDc[i] + i + "¾" + "\",";
        }

        headStr = headStr.Substring(0, headStr.Length - 1);
        var builder = new StringBuilder();
        builder.Append("[");
        for (var i = 0; i < dataTable.Rows.Count; i++)
        {
            var tempStr = headStr;
            builder.Append("{");
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                tempStr = tempStr.Replace(dataTable.Columns[j] + j.ToString() + "¾",
                                          dataTable.Rows[i][j].ToString());
            }
            builder.Append(tempStr + "},");
        }
        builder = new StringBuilder(builder.ToString().Substring(0, builder.ToString().Length - 1));
        builder.Append("]");
        return builder.ToString();
    }

How to know the number of rows in all the tables in a SQL server 2005 database?

The below query will show the number of rows in all the tables in a SQL server 2005 database


select distinct sysobjects.name,sysindexes.rows from sysobjects
inner join sysindexes on sysindexes.id=sysobjects.id
where sysobjects.xtype='u'
order by sysindexes.rows desc

or you can use the below querry to the same details

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count

FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

To create the business objects based on the SQL table columns

//SQL Query to create entity
select 'public '+
case when system_type_id=56 then ' int '+sys.columns.name+' { get; set; }'
when system_type_id=167 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=239 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=231 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=104 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=61 then ' DateTime '+sys.columns.name+' { get; set; }'
when system_type_id=108 then ' double '+sys.columns.name+' { get; set; }'
when system_type_id=175 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=34 then ' byte[] '+sys.columns.name+' { get; set; }'
when system_type_id=99 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=48 then ' byte '+sys.columns.name+' { get; set; }'
when system_type_id=52 then ' short '+sys.columns.name+' { get; set; }'
when system_type_id=127 then ' long '+sys.columns.name+' { get; set; }'
when system_type_id=106 then ' decimal '+sys.columns.name+' { get; set; }'
end AS 'Class Properties'
from sys.columns
inner join sys.tables on sys.columns.object_id=sys.tables.object_id and sys.tables.name='CustomerInquiry_AssetDetails'

How to get the machine and windows user name of a logged in user in c#?

The below code will help you to find the Logged in windows user

System.Security.Principal.WindowsIdentity.GetCurrent().Name;

For getting the Machine Name you can use the below code.

WindowsIdentity.GetCurrent().Name;

The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>).

var objCmbValidateMe = document.getElementById('<%= ComboBox1.ClientID %>’)

When I sued the above code in my aspx page I was getting the eroor as .

“The Controls collection cannot be modified because the control contains code blocks (i.e. ).”
This error can be solved by just replacing the code as below .

Other wise pass the control’s clientId as a parameter to the javascript function from thes server side code.

Replace your code as below will solve the issue. 

var objCmbValidateMe = document.getElementById('<#= ComboBox1.ClientID #>’)

You can also find other solution in the bleow link.
http://www.west-wind.com/weblog/posts/5758.aspx

How to get the selected text of a microsoft ajax combobox using javascript ?

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<%@ Register assembly="System.Web.Ajax" namespace="System.Web.UI" tagprefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
    <script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        TextBox textBox = ComboBox1.FindControl("TextBox") as TextBox;
        if (textBox != null)
        {
            textBox.Attributes.Add("onBlur", "cmb_OnBlur(this);");
        }

    }
    </script>
    <script language="javascript" type="text/javascript">
    function cmb_OnBlur(Text)
    {
         alert(Text.value  )
    }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:AjaxScriptManager ID="AjaxScriptManager1" runat="server">
    </asp:AjaxScriptManager>
    <div>
    </div>
    <asp:ComboBox ID="ComboBox1" runat="server">
        <asp:ListItem Value="R">Rajesh</asp:ListItem>
        <asp:ListItem Value="V">Vrinda</asp:ListItem>
    </asp:ComboBox>
    </form>
</body>
</html>

To change the particular row color of the grid in ASP.Net

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            if (DataBinder.Eval(e.Row.DataItem, "StaffName").ToString() == "Ram")
            {
                // color the background of the row yellow
                e.Row.BackColor = Color.Yellow;
                e.Row.ForeColor = Color.Green;
            }
 
        }
    }

To create the custom date[April 3rd 2012]- Crystal Report

//First Parameter -TitleDate
NumberVar DayIn := Day (PrintDate);
Totext (DayIn , 0 )
& (if DayIn in 4 to 20 then 'th' else
if remainder (DayIn , 10) = 1 then 'st' else
if remainder (DayIn , 10) = 2 then 'nd' else
if remainder (DayIn , 10) = 3 then 'rd' else 'th')

//Final Custom Date format like-April 3rd 2012
ToText(MonthName (Month(PrintDate))) + " " + ToText({@TitleDate}) + " "+ Cstr(Year(PrintDate),0,"")

To show the currency value in a words format -Crystal Reports

UCASE("Rupees " + {@tt1} + 
IIF ((Round({?test},2) - Int({?test})) * 100 = 0 , " Only" , " and " + ToWords ((Round({?test},2) - Int({?test})) * 100, 0) + " Cents Only" ))

 Note:
Instead of giving this parameter{@tt1} we can give ToWords (({?test}),0)

To create columns in run time to bind the grid in Windows C#

 try
            {
                DataGridViewTextBoxColumn dgvtbcTerm = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn dgvtbcDownPaymentAmount2 = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn dgvtbcDownPaymentAmount3 = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn dgvtbcDownPaymentAmount4 = new DataGridViewTextBoxColumn();
                DataGridViewCellStyle style = new DataGridViewCellStyle();
                style.BackColor = Color.Beige;
                DataGridViewCellStyle dgvcHeaderStyle = new DataGridViewCellStyle();
                dgvcHeaderStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                DataGridViewCellStyle dgvcStyle = new DataGridViewCellStyle();
                dgvcStyle.Alignment = DataGridViewContentAlignment.BottomRight;

                dgvtbcTerm.Name = "Term";
                dgvtbcTerm.HeaderText = "Term";
                dgvtbcTerm.DataPropertyName = "Term";
                dgvtbcTerm.ReadOnly = true;
                dgvtbcTerm.Width = 115;
                dgvtbcTerm.HeaderCell.Style = dgvcHeaderStyle;
                dgvtbcTerm.CellTemplate = new DataGridViewTextBoxCell();

                dgvtbcDownPaymentAmount2.Name = "DownPaymentAmount2";
                dgvtbcDownPaymentAmount2.HeaderText = "Down Payment Amount * 2";
                dgvtbcDownPaymentAmount2.DataPropertyName = "DownPaymentAmount2";
                dgvtbcDownPaymentAmount2.ReadOnly = true;
                dgvtbcDownPaymentAmount2.Width = 200;
                dgvtbcDownPaymentAmount2.ValueType = typeof(decimal);
                dgvtbcDownPaymentAmount2.HeaderCell.Style = dgvcHeaderStyle;
                dgvtbcDownPaymentAmount2.DefaultCellStyle = dgvcStyle;
                dgvtbcDownPaymentAmount2.CellTemplate = new DataGridViewTextBoxCell();

                dgvtbcDownPaymentAmount3.Name = "DownPaymentAmount3";
                dgvtbcDownPaymentAmount3.HeaderText = "Down Payment Amount * 3";
                dgvtbcDownPaymentAmount3.DataPropertyName = "DownPaymentAmount3";
                dgvtbcDownPaymentAmount3.ReadOnly = true;
                dgvtbcDownPaymentAmount3.Width = 200;
                dgvtbcDownPaymentAmount3.ValueType = typeof(decimal);
                dgvtbcDownPaymentAmount3.HeaderCell.Style = dgvcHeaderStyle;
                dgvtbcDownPaymentAmount3.DefaultCellStyle = dgvcStyle;
                dgvtbcDownPaymentAmount3.CellTemplate = new DataGridViewTextBoxCell();


                dgvtbcDownPaymentAmount4.Name = "DownPaymentAmount4";
                dgvtbcDownPaymentAmount4.HeaderText = "Down Payment Amount * 4";
                dgvtbcDownPaymentAmount4.DataPropertyName = "DownPaymentAmount4";
                dgvtbcDownPaymentAmount4.ReadOnly = true;
                dgvtbcDownPaymentAmount4.Width = 200;
                dgvtbcDownPaymentAmount4.ValueType = typeof(decimal);
                dgvtbcDownPaymentAmount4.HeaderCell.Style = dgvcHeaderStyle;
                dgvtbcDownPaymentAmount4.DefaultCellStyle = dgvcStyle;
                dgvtbcDownPaymentAmount4.CellTemplate = new DataGridViewTextBoxCell();

                dgvLeaseRentalSuggestions.AutoGenerateColumns = false;
                dgvLeaseRentalSuggestions.Enabled = false;
                dgvLeaseRentalSuggestions.DefaultCellStyle = style;
                dgvLeaseRentalSuggestions.Columns.AddRange(new DataGridViewColumn[] { dgvtbcTerm, dgvtbcDownPaymentAmount2, dgvtbcDownPaymentAmount3, dgvtbcDownPaymentAmount4 });
                dgvLeaseRentalSuggestions.BackgroundColor = Color.Beige;
                dgvLeaseRentalSuggestions.Columns["DownPaymentAmount2"].DefaultCellStyle.Format = "#,0.00";
                dgvLeaseRentalSuggestions.Columns["DownPaymentAmount3"].DefaultCellStyle.Format = "#,0.00";
                dgvLeaseRentalSuggestions.Columns["DownPaymentAmount4"].DefaultCellStyle.Format = "#,0.00";


            }

private void SetDetfaultValuesToLeaseRentalSuggestionsGrid()
        {
            _dsLeaseRentalSuggestions = new DataSet();
            DataTable dtLeaseRentalSuggestions = new DataTable();
            dtLeaseRentalSuggestions.Columns.Add("Term", typeof(Int32));
            dtLeaseRentalSuggestions.Columns.Add("DownPaymentAmount2", typeof(Decimal));
            dtLeaseRentalSuggestions.Columns.Add("DownPaymentAmount3", typeof(Decimal));
            dtLeaseRentalSuggestions.Columns.Add("DownPaymentAmount4", typeof(Decimal));
            dtLeaseRentalSuggestions.TableName = "LeaseRentalSuggestions";
            _dsLeaseRentalSuggestions.Tables.Add(dtLeaseRentalSuggestions);

            DataRow drLeaseRentalSuggestions = dtLeaseRentalSuggestions.NewRow();
            drLeaseRentalSuggestions["Term"] = 60;
            dtLeaseRentalSuggestions.Rows.Add(drLeaseRentalSuggestions);
            drLeaseRentalSuggestions = dtLeaseRentalSuggestions.NewRow();
            drLeaseRentalSuggestions["Term"] = 48;
            dtLeaseRentalSuggestions.Rows.Add(drLeaseRentalSuggestions);
            drLeaseRentalSuggestions = dtLeaseRentalSuggestions.NewRow();
            drLeaseRentalSuggestions["Term"] = 36;
            dtLeaseRentalSuggestions.Rows.Add(drLeaseRentalSuggestions);
            dgvLeaseRentalSuggestions.DataSource = _dsLeaseRentalSuggestions.Tables["LeaseRentalSuggestions"];
            _dsLeaseRentalSuggestions.Tables["LeaseRentalSuggestions"].DefaultView.AllowNew = false;
        }

To fill the combobox from the XML file

             //Presentation Layer
            DataSet dsVatDetails = tc.GetVatDetails();
            cboVatCode.DataSource = dsVatDetails.Tables["Vat"];
            cboVatCode.DisplayMember = "VatCode";
            cboVatCode.ValueMember = "Id";
            dsVatDetails.Tables["Vat"].DefaultView.Sort = "VatCode";

        public DataSet GetVatDetails()
        {
            DataSet dsVat = new DataSet();
            string appPath =  GetExecutingPath();
            dsVat.ReadXml(appPath + "VatDetails.xml");
            return dsVat;
        }

public string GetExecutingPath()
        {

            string appPath = string.Empty;// Assembly.GetAssembly(typeof(TrialCalculation)).Location;
            //System.Windows.Forms.MessageBox.Show(appPath);
            string[] appPathArray = GetExecutingAssemblyDirectory().Split("\\//".ToCharArray());          
            if (appPathArray.Contains("bin") == true)
            {
                appPath = string.Empty;
                for (int cnt = 0; cnt < appPathArray.Length - 4; cnt++)
                {
                    appPath = appPath + appPathArray[cnt] + "\\";
                }
            }
            return appPath;
        }

private  string GetExecutingAssemblyDirectory()
        {

            string currentAssembly = Assembly.GetExecutingAssembly().Location; // Get current assembly with filename
            int nbCharToKeep = currentAssembly.Length; // Initialize length

            // Decrease length until an escape caracter is found
            while (currentAssembly[nbCharToKeep - 1] != '\\')
            {
                nbCharToKeep--;
                System.Diagnostics.Debug.Assert(nbCharToKeep >= 0, "GetExecutingAssemblyDirectory() - nbCharToKeep < 0");
            }

            // Return the dir including the last escape
            return currentAssembly.Substring(0, nbCharToKeep);

        }

Basic Queries...

--IF THE TABLE IS Exist
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
--------------------------------
--IF THE TABLE IS NOT Exist
SELECT *
INTO DISHSEGMENTDETAIL
FROM TEMPDISHSEGMENTDETAIL
-------------------------------
SP_RENAME 'NEWTABLENAME','OLDTABLENAME'
-------------------------------------
--Rename the column
SP_RENAME 'DISHSEGMENTDETAIL.SEG_DETAIL_DATETIME','Seg_Detail_UpdateTime'
-------------------------------------
EXEC sp_renamedb 'adminv2_lawrence', 'newName'
alter database newName modify name=adminv2_lawrenc
---------------------------------------------------------
IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Employee' and COLUMN_NAME='Name')
BEGIN
ALTER TABLE Employee
ALTER COLUMN Name varchar (30)
END
---------------------------------------------------------

To insert the table values from other machine in SQL Server

--Port the table values from destination machine to local machine
insert into fb_shopdishdetails
select * from openrowset('sqloledb','192.168.0.13';'sa';'development','select * from adminv2.dbo.fb_shopdishdetails')a

To create the button in java script

  var ButtonIns = document.createElement('BUTTON');
                    ButtonIns.innerText = "Remove";

To show the alert message and call the java script from the .cs page in ASP.Net

           ClientScript.RegisterStartupScript(this.GetType(), "ShowMessage", "confirmLogin();", true);
-------------------------------------------------
            //ClientScript.RegisterStartupScript(typeof(Page), "MessagePopUp", "alert('Registration successfully'); window.location.href ='frmContentPlaceHolder.aspx';", true);
-------------------------------------------------
<script language="javascript" type="text/javascript">
        function confirmLogin() {
            var answer = confirm("Registration successful.Do you want to login now?");
            if (answer)
            {
                window.open("frmContentPlaceHolder.aspx", "_parent", "", "");
            }
            else
             {
                 return false;
             }

             }
        </script>

To take the connection string from config file in ASP.Net

 static string connStr =ConfigurationManager.ConnectionStrings["SQLConnection"].ToString();

        SqlConnection objconn = new SqlConnection(connStr);

To create the Database using quries in SQL Server

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='Test_WCF_3',
@physname=N'F:\LAWRENCE\Personal\WCF\WCF.mdf'
GO
-- Method 2:
CREATE DATABASE Test_WCF_2 ON
(FILENAME = N'F:\LAWRENCE\Personal\WCF\WCF.mdf')
FOR ATTACH_REBUILD_LOG

to take the backup from the database in SQL Server

EXEC sp_addumpdevice 'disk', 'Daily_backup', 'D:\Adminv2'
BACKUP DATABASE hmsv2 TO Daily_backup

To filter the Date in SQL Server

select * from losposfandbheader
where cast(left(poshBillDate,11) as datetime) = '2010-10-01'

To encrypt and decrypt the password in SQL Server

VALUES(@User_Id,@User_Name,EncryptByPassPhrase('User_Password', @User_Password ))
 
string strSQL = "select * from User_Login where User_Name ='" + uname + "' AND decryptbypassphrase('User_Password',User_Password) ='" + password + "'";

To get the string length with the space...

SELECT DATALENGTH(rtrim(ltrim('Lawre nce '))),LEN('Lawre nce '),DATALENGTH('Lawre nce '),LEN('Lawrence ')

To Update the column without using Inner join in SQL Server

UPDATE accounts.dbo.customermaster    
SET accounts.dbo.customermaster.Address1 = reservation.dbo.TourOperator.Address 
from reservation.dbo.TourOperator
where  accounts.dbo.customermaster.TOID = reservation.dbo.TourOperator.TOID
and accounts.dbo.customermaster.CompanyID = reservation.dbo.TourOperator.CompanyID

Bulk Insert from Text file

CREATE TABLE TestCSV
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)

BULK
INSERT TestCSV
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

SSRS report creation

set fmtonly off
exec sp_TillEndCreditSales 105
set fmtonly on