Split single column into multiple columns

DECLARE @testvar VARCHAR(MAX)

SELECT @testvar = Coalesce(@testvar + ', ', '') + ColumnName
FROM YourTableName

DECLARE @pos int,@pos2 int, @curruntLocation char(20),@curruntLocation2 char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = @testvar
SELECT @input = @input + ','

CREATE TABLE #tempTable1 (temp1 varchar(100),temp2 varchar(100) )

WHILE CHARINDEX(',',@input) > 0
BEGIN
SELECT @pos=CHARINDEX(',',@input)
SELECT @curruntLocation = RTRIM(SUBSTRING(@input,1,@pos-1))

SELECT @input=SUBSTRING(@input,@pos+1,LEN(@input))
SELECT @pos2=CHARINDEX(',',@input)

SELECT @curruntLocation2 = RTRIM(SUBSTRING(@input,1,@pos2-1))
INSERT INTO #tempTable1 (temp1,temp2) VALUES (@curruntLocation,@curruntLocation2)
SELECT @input=SUBSTRING(@input,@pos2+1,LEN(@input))

END

SELECT * FROM #tempTable1

DROP TABLE #tempTable1

List of modified objects in SQL Server

-- tables modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='U'

-- stored procedures modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='P'

-- tables modified in last 7 days
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-7,GETDATE())
AND type='U'

Create DataTable and sort Programmatically in C#, ASP.NET

Create a DataTable instance

DataTable table = new DataTable();

Create 7 columns for this DataTable

DataColumn col1 = new DataColumn("ID");
DataColumn col2 = new DataColumn("Name");
DataColumn col3 = new DataColumn("Checked");
DataColumn col4 = new DataColumn("Description");
DataColumn col5 = new DataColumn("Price");
DataColumn col6 = new DataColumn("Brand");
DataColumn col7 = new DataColumn("Remarks");

Define DataType of the Columns

col1.DataType = System.Type.GetType("System.Int");
col2.DataType = System.Type.GetType("System.String");
col3.DataType = System.Type.GetType("System.Boolean");
col4.DataType = System.Type.GetType("System.String");
col5.DataType = System.Type.GetType("System.Double");
col6.DataType = System.Type.GetType("System.String");
col7.DataType = System.Type.GetType("System.String");

Add All These Columns into DataTable table

table.Columns.Add(col1);
table.Columns.Add(col2);
table.Columns.Add(col3);
table.Columns.Add(col4);
table.Columns.Add(col5);
table.Columns.Add(col6);
table.Columns.Add(col7);

Create a Row in the DataTable table

DataRow row = table.NewRow();

Fill All Columns with Data

row[col1] = 1100;
row[col2] = "Computer Set";
row[col3] = true;
row[col4] = "New computer set";
row[col5] = 32000.00
row[col6] = "NEW BRAND-1100";
row[col7] = "Purchased on July 30,2008";

Add the Row into DataTable

table.Rows.Add(row);


Sort DataTable

dt_table.DefaultView.Sort = "[" + dt_table.Columns[1].ColumnName + "] DESC";
table.AcceptChanges();
string PIdmax = table.DefaultView[0]["Name"].ToString();

Left Outer Join in LINQ

LINQ Query

var query = (from p in dc.GetTable<Person>()
join pa in dc.GetTable<PersonAddress>() on p.Id equals pa.PersonId into tempAddresses
from addresses in tempAddresses.DefaultIfEmpty()
select new { p.FirstName, p.LastName, addresses.State });


SQL Translation

SELECT [t0].[FirstName], [t0].[LastName], [t1].[State] AS [State]
FROM [dbo].[Person] AS [t0]
LEFT OUTER JOIN [dbo].[PersonAddress] AS [t1] ON [t0].[Id] = [t1].[PersonID]

Fill a DataSet or a DataTable from a LINQ query resultset

MyDataContext db = new MyDataContext();
IEnumerable<DataRow> query = 
         (from order in db.Orders.AsEnumerable()
          select new
          {
           order.Property,
           order.Property2
          }) as IEnumerable<DataRow>;
return query.CopyToDataTable<DataRow>();

Read excel data into dataset

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Data\\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""");
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet4$]", con);
DataSet ds = new DataSet();
da.Fill(ds);

for (Int32 i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Implement your logic here
}

Handle master page click events in content page

Step 1 : Define the property in the Master Page for the LinkButton lnkbtnFromMasterPage

Public LinkButton lnkbFromMasterPage(){

Get {

Return lnkbtnFromMasterPage;

}

}

Step 2 : Reference the Master Page as a casted object in the Content page.This can be done accessing the MasterPage object of the content page and casting it to the class for our Master Page. Here the class defined for the Master Page is CustomMasterPageCls.

CustomMasterPageCls cmp = ((CustomMasterPageCls)(Master));

Step 3 : Now we can access any Public properties, methods and members of our CustomMasterPageCls class. So here was the magic, would I be able to handle the button events in the Content Page? Of course I can!
You simply need to define the event handler in your Content Page, I do it in the PageLoad event handler.

If (!(IsNothing(cmp))) {

cmp.lnkbtnFromMasterPage.Click += New EventHandler(lnkbtnFromMasterPage_Click);

End If

Step 4 : Define the actual method to handle the event.

Protected void lnkbtnFromMasterPage_Click(Object sender, System.EventArgs e){

'Perform Business Logic Here

}