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