Query to Find First and Last Day of Current Month

Hello friends,
Following is a TSQL script that will find the 'Last day of previous month','First day of current month' and so on...
Hope so this will help you.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO

You can find main article on this url i.e. http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/


Regards,
Kinjal

XML Path Query

Hello Friends,
Here I provide you one link that will share you how to use xml in sql server.

http://beyondrelational.com/blogs/jacob/archive/2008/08/04/for-xml-path-how-to-remove-the-lt-row-gt-element-from-the-output-of-a-for-xml-path-query.aspx

Hopes it will help for beginners.

Regards,
Kinjal

MVC2

http://microsoftpdc.com/Sessions/FT59?type=wmv

Create MessageBox user control using ASP.NET and CSS

Hello friends,
I read one article that will shown how an importance of handling different message types properly.

So here I just put the link of that article.
http://www.jankoatwarpspeed.com/post/2008/05/28/Create-MessageBox-user-control-using-ASPNET-and-CSS.aspx

I hopes you are also interest in it.

Regards,
Kinjal

Embeded Script

<object width="610" height="420" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0"
classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000">
<param value="flvplayer.swf" name="movie" />
<param value="high" name="quality" />
<param value="never" name="AllowScriptAccess" />
<param value="opaque" name="wmode" />
<param value="file=610x420_700kbps_fulldvd.flv&image=../plumbing/pictures/preview.jpg"
name="FlashVars" />
<embed width="610" height="420" pluginspage="http://www.macromedia.com/go/getflashplayer"
allowscriptaccess="never" type="application/x-shockwave-flash" flashvars="file=540x380_400kbps_fulldvd.flv&image=../plumbing/pictures/preview.jpg"
wmode="opaque" src="flvplayer.swf" /></object>

Redirect from Http to Https in ASP.NET

Hello frnds,
In my one project, there is a requirement for redirect some page from HTTP to HTTPS.
So find one great article that will help me. So here I post the link of that article and code of how its work.

http://www.xdevsoftware.com/blog/post/Redirect-from-Http-to-Https-in-ASPNET.aspx

Code:-
-------------------------------------------------------
Class File Name: SecurePages.cs
-------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Collections.Specialized;
#region "SecurePage Class"
public class SecurePage
{
string _path = "";
string _pathType = "";

public string Path
{
get { return this._path; }
set { this._path = value; }
}

public string PathType
{
get { return this._pathType; }
set { this._pathType = value; }
}
}
#region "SecurePath Class"
public class SecurePath
{

public static bool IsSecure(string path)
{
List lstPages = new List();

bool isSecure = false;

try
{
//Fill the list of pages defined in web.config
NameValueCollection sectionPages = (NameValueCollection)ConfigurationManager.GetSection("SecurePages");

foreach (string key in sectionPages)
{
if ((!string.IsNullOrEmpty(key)) && (!string.IsNullOrEmpty(sectionPages.Get(key))))
{
lstPages.Add(new SecurePage { PathType = sectionPages.Get(key), Path = key });
}
}

//loop through the list to match the path with the value in the list item
foreach (SecurePage page in lstPages)
{
switch (page.PathType.ToLower().Trim())
{
case "directory":
if (path.Contains(page.Path))
{
isSecure = true;
}
break;
case "page":
if (path.ToLower().Trim() == page.Path.ToLower().Trim())
{
isSecure = true;
}
break;
default:
isSecure = false;
break;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}

return isSecure;
}
}
#endregion

#endregion


/* Add New custom section in web.config */

<configuration>
<configSections>
<section name="SecurePages" type="System.Configuration.NameValueFileSectionHandler, System, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<SecurePages>
<add key="/admin" value="directory" />
<add key="/WebForm2.aspx" value="page" />
<add key="/WebForm1.aspx" value="page" />
<add key="/myDir/myDirSec" value="directory" />
</SecurePages>
</configuration>

Generate N Level Product List Using CTE

Hello Friends,
Here I show you example which will generate navigation list for product through N level. Please check below query for generate N Level product list.

Query:-
-----------------------------------------------

DECLARE @Category TABLE
(
CategoryID INT
,Name VARCHAR(100)
,ParentID INT
)

INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 1,'Mobile',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 2,'Nokia',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 3,'Samsung',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 4,'Headphone',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 5,'Nokia N70',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 6,'Nokia N72',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 7,'IBall',4)

;WITH CTE(CategoryID,Name,ParentID,level,ParentName)
AS
(
SELECT CategoryID,Name,ParentID,0,cast('.' + Name + '.' AS VARCHAR(MAX)) FROM @Category WHERE ParentID=0
UNION ALL
SELECT C.CategoryID,C.Name,CT.ParentID,level + 1,CAST (CT.ParentName + '.' + C.Name AS VARCHAR(MAX)) FROM @Category C
INNER JOIN CTE CT ON CT.CategoryID=C.ParentID
)
SELECT SPACE(level * 4) + Name AS Products FROM CTE ORDER BY ParentName

Output
-------------------

Common Table Expression Example

Hello Friends,
Here I put the sample code example using CTE.

--Generate Fibonacii Series using CTE


;WITH FibonaciiSeries(N,F1,F2,F3)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT),CAST(0 AS BIGINT),CAST(1+0 AS BIGINT)
UNION ALL
SELECT N+1, F2,F3,F2+F3 FROM FibonaciiSeries WHERE N<92
)
SELECT * FROM FibonaciiSeries

--OUTPUT
-------------------------



--Find Factorial

;WITH Factorial(N,Number)
AS
(
SELECT CAST (1 AS BIGINT), CAST (1 AS BIGINT)
UNION ALL
SELECT N+1,CAST ((N+1)*(Number) AS BIGINT) FROM Factorial WHERE N<10
)

SELECT * FROM Factorial

--OUTPUT
-------------------------

Find the second highest salary for each department

Hello Friends,
Here I post one TSQL Challenge. I hopes it will help you.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

http://beyondrelational.com/blogs/tcb/archive/2009/10/13/tsql-beginner-s-challenge-1-find-the-second-highest-salary-for-each-department.aspx

TSQL Challenge Solved-

TSQL:-
SET NOCOUNT ON
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)


SELECT * FROM @Employees

;WITH SecondHighestSalary
AS
(
SELECT EmployeeID,EmployeeName,Department,SALARY AS SALARY,RANK() over (partition by Department order by salary) AS SalaryNumber FROM @Employees
)
SELECT EmployeeID,EmployeeName,Department,SALARY FROM SecondHighestSalary WHERE SalaryNumber=2



OUTPUT:-

Use of Sys.Net.WebRequest

Hello Friends,
I read on article which can access web service using Sys.Net.WebRequest. Below is a read article URL i.e
http://www.dotnetfunda.com/articles/article480-accessing-webservice-by-using-sysnetwebrequest-.aspx

So I need to access handler using Sys.Net.WebRequest.

So here is a sample code to access handler using Sys.Net.WebRequest.

WebRequest.Aspx Page
---------------------------------------------

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

<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<input type="text" id="text1" /><br />
<input type="button" id="Button1" value="Button" onclick="return Button1_onclick()" />
</div>
<div id="result">

</div>
</div>
<script language="javascript" type="text/javascript">
function Button1_onclick() {

var myRequest = new Sys.Net.WebRequest;
myRequest.set_url("sampleHandler.ashx");
myRequest.set_httpVerb("POST");
var tbvalue = document.getElementById("text1").value;
var body = "Param1=" + tbvalue;
myRequest.set_body(body);
myRequest.get_headers()["Content-Length"] = body.length;
myRequest.add_completed(myResultHandler);
myRequest.invoke();
}

function myResultHandler(executor, eventArgs) {
if (executor.get_responseAvailable) {
var result = document.getElementById("result");
result.innerHTML = executor.get_responseData();
}
}
</script>
</form>
</body>
</html>

Now Here is a code for 'sampleHandler.ashx'

<%@ WebHandler Language="C#" Class="sampleHandler" %>

using System;
using System.Web;

public class sampleHandler : IHttpHandler {

public void ProcessRequest (HttpContext context) {
string paramValue = string.Empty;
if (HttpContext.Current.Request.Form["Param1"]!="")
{
paramValue = HttpContext.Current.Request.Form["Param1"];
}
context.Response.ContentType = "text/plain";
context.Response.Write(paramValue);
}

public bool IsReusable {
get {
return false;
}
}

}

Happy Coding and Enjoy it...