Image from a sharepoint location

string url = "URL";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Credentials = new NetworkCredential("test", "test", "test");
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
BinaryReader br = new BinaryReader(stream);
byte[] content = br.ReadBytes(500000);
br.Close();

string[] imagetype = url.Split('.');
Response.ContentType = "image/" + imagetype[imagetype.Length-1];
//Response.ContentType = "image/jpeg";
Response.BinaryWrite(content);
response.Close();

//string file_name = Server.MapPath(".") + "\\logo.jpg";
//FileStream fs = new FileStream(file_name, FileMode.Create);
//BinaryWriter bw = new BinaryWriter(fs);
//try
//{
// bw.Write(content);
//}

//finally
//{
// fs.Close();
// bw.Close();
//}

CTE

CTE (Common Table Expression):


WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name


CTE 1: Simple CTE

WITH ProductCTE
AS
(
SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
FROM Products
)
SELECT * FROM ProductCTE

Update group of records

SqlDataAdapter daAuthors = new SqlDataAdapter("Select Id,Name From ttt", DA.Data.SqlConnectionString.Conn);
DataSet ds = new DataSet("Pubs");
daAuthors.Fill(ds, "ttt");
ds.Tables[0].TableName = "ttt";
DataRow dr=ds.Tables[0].NewRow();
dr["Name"]="Arun";
ds.Tables[0].Rows.Add(dr);

SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daAuthors);
daAuthors.InsertCommand = cmdBuilder.GetInsertCommand(true);
daAuthors.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
daAuthors.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
daAuthors.Update(ds, "ttt");

Mobile call when click a link

brokerPro.append(CreateBrokerElements('Email: ', '' + result.Items[i].Email + ''));

Pivot table eg: (SQL)

Create Table #TempResult(Sno int,AllocationMonthYear varchar(100),Efforts numeric(18,2),
Location varchar(100),Release nvarchar(100), Category nvarchar(100),
Name nvarchar(50),Mon Int, Ye int)
Insert Into #TempResult
SELECT
Sno
,cast(DateName(mm,DATEADD(mm,ActualAllocationInMonthsDtl.Month,-1)) as varchar) +' '+
cast(ActualAllocationInMonthsDtl.Year as varchar) AllocationMonthYear
,Sum(ActualAllocationInMonthsDtl.Efforts)Efforts
,ActualAllocationInMonthsDtl.Location
,PlannedAllocationHdr.Release
,PlannedAllocationHdr.Category
,Designation.Name AS Designation
,ActualAllocationInMonthsDtl.Month
,ActualAllocationInMonthsDtl.Year
FROM
ActualAllocationInMonthsDtl
INNER JOIN
ActualAllocationInMonthsHdr ON ActualAllocationInMonthsDtl.AllocationHdrUid = ActualAllocationInMonthsHdr.Uid
INNER JOIN
ActualVsPlanned ON ActualAllocationInMonthsHdr.Uid = ActualVsPlanned.ActualAllocationId
INNER JOIN
Designation ON ActualVsPlanned.DesignationUid = Designation.Uid
INNER JOIN
PlannedAllocationHdr ON ActualVsPlanned.PlannedAllocationId = PlannedAllocationHdr.Uid WHERE ActualAllocationInMonthsHdr.ProjectId=@projectUid
GROUP By
Sno,ActualAllocationInMonthsDtl.Month,
ActualAllocationInMonthsDtl.Year,
ActualAllocationInMonthsDtl.Location,
PlannedAllocationHdr.Release,
PlannedAllocationHdr.Category,
Designation.Name
Order By ActualAllocationInMonthsDtl.Year,ActualAllocationInMonthsDtl.Month

Declare @TblMonthYear Table(Mon int,Yea int)
Insert Into @TblMonthYear Select Distinct Mon,Ye From #TempResult Order By Ye,Mon

DECLARE @PivotColumnHeaders VARCHAR(MAX)

SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(DateName(mm,DATEADD(mm,Mon,-1)) as varchar) +' '+ cast(Yea as varchar) + ']',
'[' + cast(DateName(mm,DATEADD(mm,Mon,-1)) as varchar) +' '+ cast(Yea as varchar) + ']'
)
FROM (Select Mon,Yea From @TblMonthYear) as test

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM
(SELECT Sno,AllocationMonthYear,Efforts,Release,Category,Location,Name AS Designation
FROM #TempResult) AS DataTable
PIVOT
(
Max(Efforts)
FOR AllocationMonthYear IN ('+ @PivotColumnHeaders +')
) AS PivotTable;
'
EXECUTE(@PivotTableSQL)

Reset Identity Field in SQL Table

DBCC CHECKIDENT (TableName, RESEED, 0)

Reset Identity Field in SQL Table

DBCC CHECKIDENT (TableName, RESEED, 0)