SQL Query Stuff

1.By using this query can search any string contain within stored procedure, function or view

SELECT object_name(id)
FROM sys.syscomments
WHERE text like '%YourFunctionName%'

2.By using this query can search any 'columnName' contain within tables.

SELECT table_name,column_name 
FROM information_schema.columns
WHERE column_name like '%ShiftTypeID%'

3. Case statement within where clause

SELECT * 
FROM ORDERS
WHERE
(
CASE
WHEN @VAR1 = 'Customers' AND CustomerID = @VAR2
THEN 1
WHEN @VAR1 = 'Employee' AND EmployeeID = @VAR2
THEN 1
ELSE 0
END
) = 1
4. Date Comparison in SQL

1. WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
2. WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
3. WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
4. WHERE OrderDate LIKE '01/01/2006%'
5. WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'

5. Table name with row counts in sql using "sysobjects "

SELECT [TableName] = so.name,[RowCount] = MAX(si.rows)
FROM sysobjects so,sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

6. Get Column name from sql

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS  c_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%
ORDER BY schema_name, table_name

7. Check the table existence in SQL

IF OBJECT_ID ('DATABASE_NAME.dbo.TABLE_NAME','U') IS NOT NULL
BEGIN
--do any thing if the table exists
ELSE
--do any thing if the table dose not exists
END


'U':- Is a symbol for identifying a table in SQL
 

Sql PIVOT Sample

SELECT a.AttendancesID,a.EmployeeNo,MONTH(a.AttendancesDate) As AttMonth
FROM Attendances a
WHERE YEAR(a.AttendancesDate) = '2010'
GROUP BY a.AttendancesID, a.EmployeeNo, MONTH(a.AttendancesDate)
ORDER BY a.AttendancesID,a.EmployeeNo,AttMonth




SELECT *
FROM (
SELECT a.EmployeeNo,
CASE MONTH(a.AttendancesDate)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS AttMonth,a.AttendancesID AS AttCount
FROM Attendances a
WHERE YEAR(a.AttendancesDate) = '2010'
GROUP BY a.EmployeeNo, MONTH(a.AttendancesDate), a.AttendancesID
) DataTable

PIVOT(
COUNT(AttCount)
FOR AttMonth
IN ([January], [February], [March], [April], [May], [June], [July],[August], [September], [October],  [November], [December])
)
PivotTable 
ORDER BY EmployeeNo

Star works In SQL[Use of while loop]

Code	Output
DECLARE @count INT DECLARE @ct INT DECLARE @print varchar(10) SELECT @count =10 WHILE @count > 0 BEGIN SELECT @print= '' SELECT @ct = @count WHILE @ct > 0 BEGIN SELECT @print = @print + '*' SELECT @ct = @ct -1 END PRINT @print SELECT @count=@count-1 END	********** ********* ******** ******* ****** ***** **** *** ** *
DECLARE @count INT DECLARE @ct INT DECLARE @print varchar(10) SELECT @count = 0 WHILE @count < print=" ''" ct =" 0" print =" @print" ct =" @ct" count="@count+1">	* ** *** **** ***** ****** ******* ******** ********* **********

Dynamically bind any # of checkbox/ textbox or dropdownlist columns to gridview using C# 3.5

In the Following code i am going to generate a Grid view with dynamic check box columns.
as u can seen on the picture below.



//When Post back take place firstly have to clear the gridview
//So following code doing that thing


DataTable dtANull = new DataTable();
gvwStudentSubject.DataSource = dtANull;
gvwStudentSubject.DataBind();


//I am going to bind a data table to a grid view
//So first have to create the data table

DataTable dt = new DataTable();
DataColumn dcol;


// following columns are hidden columns.
dcol = new DataColumn("ID", typeof(System.String));
dt.Columns.Add(dcol);

dcol = new DataColumn("ExamEligibleID", typeof(System.String));
dt.Columns.Add(dcol);

//1st  Column that appears on the grid view (As shown in the Image above)

dcol = new DataColumn("Name\\Subject", typeof(System.String));
dt.Columns.Add(dcol);



///Creating the Headings(Columns)
///dtExamSubject :-Data Table Contains the list of Subjects

for (int i = 0; i < dcol =" new" i =" 0;" drow =" dt.NewRow();" bfield =" new" datafield =" col.ColumnName;" headertext =" col.ColumnName;" showcheckbox =" Convert.ToBoolean(col.ExtendedProperties[" datasource =" dt;" visible =" false;" visible =" false;">

/// Summary description for CustomBoundField
///
public class CustomBoundField : DataControlField
{
public CustomBoundField()
{
//
// TODO: Add constructor logic here
//
}

#region Public Properties


///
/// This property describe weather the column should be an editable column or non editable column.
///
public bool Editable
{
get
{
object value = base.ViewState["Editable"];
if (value != null)
{
return Convert.ToBoolean(value);
}
else
{
return true;
}
}
set
{
base.ViewState["Editable"] = value;
this.OnFieldChanged();
}
}

///
/// This property is to describe weather to display a check box or not.
/// This property works in association with Editable.
///
public bool ShowCheckBox
{
get
{
object value = base.ViewState["ShowCheckBox"];
if (value != null)
{
return Convert.ToBoolean(value);
}
else
{
return false;
}
}
set
{
base.ViewState["ShowCheckBox"] = value;
this.OnFieldChanged();
}
}

//Add By Damith ON 2010-03-18
///
/// This property is to describe weather to display a Text Box box or not.
/// This property works in association with Editable.
///
public bool ShowTextBox
{
get
{
object value = base.ViewState["ShowTextBox"];
if (value != null)
{
return Convert.ToBoolean(value);
}
else
{
return false;
}
}
set
{
base.ViewState["ShowTextBox"] = value;
this.OnFieldChanged();
}
}

public bool ShowDropDownList
{
get
{
object value = base.ViewState["ShowDropDownList"];
if (value != null)
{
return Convert.ToBoolean(value);
}
else
{
return false;
}
}
set
{
base.ViewState["ShowDropDownList"] = value;
this.OnFieldChanged();
}
}

//*Added Code Ended

///
/// This property describe column name, which acts as the primary data source for the column.
/// The data that is displayed in the column will be retreived from the given column name.
///
public string DataField
{
get
{
object value = base.ViewState["DataField"];
if (value != null)
{
return value.ToString();
}
else
{
return string.Empty;
}
}
set
{
base.ViewState["DataField"] = value;
this.OnFieldChanged();
}
}
#endregion

#region Overriden Life Cycle Methods
///
/// Overriding the CreateField method is mandatory if you derive from the DataControlField.
///
///
protected override DataControlField CreateField()
{
return new BoundField();
}

///
/// Adds text controls to a cell's controls collection. Base method of DataControlField is
/// called to import much of the logic that deals with header and footer rendering.
///
/// A reference to the cell
/// The type of the cell
/// State of the row being rendered
/// Index of the row being rendered
public override void InitializeCell(DataControlFieldCell cell, DataControlCellType cellType, DataControlRowState rowState, int rowIndex)
{
//Call the base method.
base.InitializeCell(cell, cellType, rowState, rowIndex);

switch (cellType)
{
case DataControlCellType.DataCell:
this.InitializeDataCell(cell, rowState);
break;
case DataControlCellType.Footer:
this.InitializeFooterCell(cell, rowState);
break;
case DataControlCellType.Header:
this.InitializeHeaderCell(cell, rowState);
break;
}
}
#endregion

#region Custom Protected Methods

///
/// Determines which control to bind to data. In this a hyperlink control is bound regardless
/// of the row state. The hyperlink control is then attached to a DataBinding event handler
/// to actually retrieve and display data.
///
/// Note: This control was built with the assumption that it will not be used in a gridview
/// control that uses inline editing. If you are building a custom data control field and
/// using this code for reference purposes key in mind that if your control needs to support
/// inline editing you must determine which control to bind to data based on the row state.
///
/// A reference to the cell
/// State of the row being rendered
protected void InitializeDataCell(DataControlFieldCell cell, DataControlRowState rowState)
{
//Check to see if the column is a editable and does not show the checkboxes.
if (Editable & !ShowCheckBox & !ShowTextBox & !ShowDropDownList)
{
Label lblText = new Label();
lblText.DataBinding += new EventHandler(lblText_DataBinding);
cell.Controls.Add(lblText);

}
else
{
if (ShowCheckBox)
{
CheckBox chkBox = new CheckBox();
cell.Controls.Add(chkBox);

Label lblText = new Label();
cell.Controls.Add(lblText);
}

//Code Added By Damith On 2010-03-18
else if (ShowTextBox)
{
TextBox txtBox = new TextBox();
txtBox.Width = 35;
txtBox.MaxLength = 5;
cell.Controls.Add(txtBox);

Label lblText = new Label();
cell.Controls.Add(lblText);
}
else if (ShowDropDownList)
{
DropDownList ddlList = new DropDownList();
Gcs.Sol.Common.Util.FillDropDown(ddlList, DropDownitems());
cell.Controls.Add(ddlList);

Label lblText = new Label();
cell.Controls.Add(lblText);
}
//Added Code Ended

else
{
Label lblText = new Label();
lblText.DataBinding += new EventHandler(lblText_DataBinding);
cell.Controls.Add(lblText);
}
}
}

private SortedList DropDownitems()
{
SortedList ddlList = new SortedList();

ddlList.Add(0, "#");

for (int i = 1; i <= 10; i++)
{
ddlList.Add(i,i.ToString());
}

return ddlList;

}

void lblText_DataBinding(object sender, EventArgs e)
{
// get a reference to the control that raised the event
Label target = (Label)sender;
Control container = target.NamingContainer;

// get a reference to the row object
object dataItem = DataBinder.GetDataItem(container);

// get the row's value for the named data field only use Eval when it is neccessary
// to access child object values, otherwise use GetPropertyValue. GetPropertyValue
// is faster because it does not use reflection
object dataFieldValue = null;

if (this.DataField.Contains("."))
{
dataFieldValue = DataBinder.Eval(dataItem, this.DataField);
}
else
{
dataFieldValue = DataBinder.GetPropertyValue(dataItem, this.DataField);
}

// set the table cell's text. check for null values to prevent ToString errors
if (dataFieldValue != null)
{
target.Text = dataFieldValue.ToString();
}
}

protected void InitializeFooterCell(DataControlFieldCell cell, DataControlRowState rowState)
{
CheckBox chkBox = new CheckBox();
cell.Controls.Add(chkBox);
}

protected void InitializeHeaderCell(DataControlFieldCell cell, DataControlRowState rowState)
{
Label lbl = new Label();
lbl.Text = this.DataField;
cell.Controls.Add(lbl);
}

void txtBox_DataBinding(object sender, EventArgs e)
{
// get a reference to the control that raised the event
TextBox target = (TextBox)sender;
Control container = target.NamingContainer;

// get a reference to the row object
object dataItem = DataBinder.GetDataItem(container);

// get the row's value for the named data field only use Eval when it is neccessary
// to access child object values, otherwise use GetPropertyValue. GetPropertyValue
// is faster because it does not use reflection
object dataFieldValue = null;

if (this.DataField.Contains("."))
{
dataFieldValue = DataBinder.Eval(dataItem, this.DataField);
}
else
{
dataFieldValue = DataBinder.GetPropertyValue(dataItem, this.DataField);
}

// set the table cell's text. check for null values to prevent ToString errors
if (dataFieldValue != null)
{
target.Text = dataFieldValue.ToString();
}
}

#endregion
}

connectionStrings

****>
**add name="ConToSOLDB" connectionString="Data Source=db-svr\MSSQL2008; Database=SOLV3; User Id=sa; password=sqladmin;" providerName="System.Data.SqlClient"***
****connectionStrings>

A statement by Anagarika Dharmapala Said In 1926

ඇල්ලේපොල මහ නිලමේ පැවසූ අවසන් වදන

"සිංහලේ ස්වාධීනත්වය යළිත් ළඟාකර දීමට සිංහලේ අනෙක් නායකයන් සමඟ එක් වී සටන් බිමට බැස්සෙමි. මවූබිම මූදා ලීමත් එමඟින් ජාතියේ නිදහස ලබා ගැනීමත් සඳහා අපි පොරොන්දු ගිවිස ගත්තෙමූ. අපේ පොරොන්දුව පරිදි මා හට පැවැරුණූ ඒ භාරදූර කාර්යය නියම වශයෙන් කිරීමට මම අපොහොසත් වීමි. ඒ නිසා මා තව ටික වේලාවකින් මැරුම් කන්නේ පොරොන්දු කඩකළ කෙනකු වශයනි. පොරොන්දු කඩකළ කෙනකුට, එමෙන්ම ජාතිය වෙනූවෙන් කළ යූතුව තිබූණූ කාර්ය භාරය ඉටූ කිරීමට නොහැකි වූ කෙනෙකුට මිනී වළක් හිමිකර ගැනීමේ ගෞරවය අත්නොවිය යූතුමය. ඒ නිසා මා මිය ගිය විගස මගේ මළකුණ කැබැලිවලට කපා බලු කපූටන්ගේ ආහාරය පිනිස මහා මාර්ගය අසලට දමනූ මැනවි"

                                                                          

උපුටා ගෑනීම දිවයින 2008/10/22