Running ASP.Net Web Server manually

Syntax: WebDev.WebServer.EXE /path: /port: /vpath:

path: location of your file to be exposed to internet
port: port to access your application/web
vpath: virtul path (e.g. http://localhost:1010/testing)


Example
WebDev.WebServer.EXE /path:D:\Web\Testing /port:2529 /vpath:/testing

Accessing this: http://localhost:2529/testing

[C# 2.0] How To Debug, Install, Uninstall, Run, Stop A Windows Service

using System.Diagnostics;

protected override void OnStart(string[] args)
{

#if (DEBUG)
Debugger.Launch();
#endif

}


Tips to Install/Unistall/Run/Stop Windows Service using Visual Studio
From the menu, select Tool then External Tools, and add this item.


Title: Install Service
Command: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe
Arguments:
Initial directory: $(ProjectDir)/bin/debug

Title: Uninstall Service
Command: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe
Arguments: /u
Initial directory: $(ProjectDir)/bin/debug

Title: Run Service
Command: NET.exe
Arguments: START ""
Initial directory:

Title: Stop Service
Command: NET.exe
Arguments: STOP ""
Initial directory:

[C#.Net] Read xml from string variable

DataSet myDS = new DataSet();

DataTable myTable = new DataTable("table1");

myTable.Columns.Add("col1", typeof(string));

myDS.Tables.Add(myTable);

string xmlData = "<XmlDS><table1><col1>Value1</col1></table1><table1><col1>Value2</col1></table1></XmlDS>";

System.IO.StringReader xmlSR = new System.IO.StringReader(xmlData);

myDS.ReadXml(xmlSR, XmlReadMode.IgnoreSchema);

[C#.Net] Get Response Data from 3rd Party Web Service

WebRequest request;
WebResponse response;
string url = "https://www.nbdgateway.net/xmlgateway/affiliatelogin.do";

string postdata = "affiliatecode=backcheck&username=sa&password=pass&ssn=1&product=AIM";
request = WebRequest.Create(url);
request.Method = "POST";
//request.Referer = url;
request.ContentLength = postdata.Length;
request.ContentType = "application/x-www-form-urlencoded";
StreamWriter sw;
StreamReader sr;
// post data
sw = new StreamWriter(request.GetRequestStream());
sw.Write(postdata);
sw.Close();
//get result
response = request.GetResponse();
sr = new StreamReader(response.GetResponseStream());
string result = sr.ReadToEnd();
sr.Close();
textBox1.Text = result;

[SQL] Date Functions

Syntax
DATEPART ( datepart , date )

Day of the Week
SELECT DATEPART(dw,GETDATE())

Year
SELECT DATEPART(yy,GETDATE())
SELECT DATEPART(yyyy,GETDATE())

Quarter
SELECT DATEPART(q,GETDATE())
SELECT DATEPART(qq,GETDATE())

Month
SELECT DATEPART(m,GETDATE())
SELECT DATEPART(mm,GETDATE())

Day of the Year
SELECT DATEPART(y,GETDATE())
SELECT DATEPART(dy,GETDATE())

Month Day
SELECT DATEPART(d,GETDATE())
SELECT DATEPART(dd,GETDATE())

Week
SELECT DATEPART(wk,GETDATE())
SELECT DATEPART(ww,GETDATE())

Hour
SELECT DATEPART(hh,GETDATE())

Minute
SELECT DATEPART(n,GETDATE())
SELECT DATEPART(mi,GETDATE())

Second
SELECT DATEPART(s,GETDATE())
SELECT DATEPART(ss,GETDATE())

Millisecond
SELECT DATEPART(ms,GETDATE())

[SQL] Convert VARCHAR To VARBINARY

Usefull for hinding a value.

DECLARE @Password varchar(50)

SET @Password = 'MLAV'

SELECT CONVERT(varbinary(50), @Password)

[ASP.Net] Get Object Value from User Control

[C#]

UserControl.FindControl method

TextBox firstNameTextBox = (TextBox) uc1.FindControl("firstNameTextBox");

[SQL] Get Number of Days in a Month Function

REATE FUNCTION [dbo].[GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN

   RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
               WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
               ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                               YEAR(@pDate) % 100 != 0) OR
                              (YEAR(@pDate) % 400  = 0)
                         THEN 29
                         ELSE 28
                    END
          END

END
GO

[SQL] Measuring the time span between two dates

SELECT DATEDIFF(dd, '1/1/01', GETDATE())
GO

[SQL] Retrieves the current date

SELECT GETDATE()
GO

[C#.Net] Code: Determining the Span Between Two Dates

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cscon/html/vctskcodedeterminingspanbetweentwodatesvisualc.asp

DateTime oldDate = new DateTime(2002,7,15);
DateTime newDate = DateTime.Now;

// Difference in days, hours, and minutes.
TimeSpan ts = newDate - oldDate;
// Difference in days.
int differenceInDays = ts.Days;

Console.WriteLine("Difference in days: {0} ", differenceInDays);

Download Compiled Code : http://ayette.net/ComputeHoursWork.rar

Download Sample Code: http://ayette.net/ComputeHoursWork.rar

[VB.Net] Get Last Day of the Month

Function LastOfThisMonth() As Date
LastOfThisMonth = DateAdd("d", -1, FirstOfNextMonth)
End Function

Function FirstOfNextMonth() As Date
Dim dtm As Date
dtm = Date
FirstOfNextMonth = DateSerial(Year(dtm),
Month(dtm) + 1, 1)
End Function

[JavaScript] Prevent Previous Button/ALT-LEFT

<script language="javascript">

history.forward(history.length - 1)

script>

[General] MSDE: login failed for user '(null)'. Reason: Not associated with a trusted SQL server

How To Verify and Change the MSDE System Administrator Password

http://support.microsoft.com/default.aspx?scid=kb;en-us;322336

[SQL] Get UniqueIdentifier (Stored Procedure)

CREATE PROCEDURE
(
@OrderID uniqueidentifier out
)

AS

SET @OrderID = NewID()

GO

[ASP.Net] Export To EXCEL file without using Office Object

Response.Clear()

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader("Content-Disposition", "attachment;filename= sample.xls")

Dim sbldTable As New StringBuilder

sbldTable.Append("

")

sbldTable.Append("

")

sbldTable.Append("

")

sbldTable.Append("

")

sbldTable.Append("

").Append("Sample Data") sbldTable.Append("

")

Response.Write(sbldTable.ToString)

sbldTable = Nothing

Response.End()

[C#.Net] Convert String data type to GUID data type

Guid myGUID = new Guid("");

[C#.Net] Random Numbers

Random rndNumber = new Random(9999);

int genRandom = rndNumber.Next(1,9999);

[ASP.Net] Prevent caching of page by the browser

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.Cache.SetAllowResponseInBrowserHistory(false);

[SQL] Sample Complex Search - StoredProcedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Service.CPIC.GetFiles]
(
@PoliceDeptId int = NULL,
@FileType int = NULL,
@PoliceOfficerId int = NULL,
@FirstName nvarchar(50) = NULL,
@LastName nvarchar(50) = NULL,
@StartFileCreationDate datetime = NULL,
@EndFileCreationDate datetime = NULL,
@StartFileCompletionDate datetime = NULL,
@EndFileCompletionDate datetime = NULL
)
AS

DECLARE @MainQuery nvarchar(MAX)
DECLARE @Condition nvarchar(MAX)

SET NOCOUNT ON;

SET @Condition = ''
-- Main Query
SET @MainQuery = '
SELECT
[CPICId],
[File_ServiceId],
[CandidateId],
[ResultsId],
[IsRushJob],
[PositionOfTrust],
[AccurateRepresentation],
[CriminalRecords],
[FirstName],
[LastName],
[MiddleName],
[MaidenName],
[Aliases],
[Gender],
[DateOfBirth],
[BirthCity],
[Height],
[Weight],
[EyeColor],
[HairColor],
[Address],
[PreviousAddress],
[Status],
[Owner],
[PoliceDeptId],
[CheckedDateTime],
[CheckedByPoliceOfficerId],
[ConfirmedDateTime],
[ConfirmedByPoliceOfficerId],
[CheckOutById],
[FileType],
[DateTimeCreated],
[DateTimeCompleted]
FROM [dbo].[Service.CPIC.GetAllFiles]()
'


-- @PoliceDeptId
IF (@PoliceDeptId IS NOT NULL)
BEGIN
SET @Condition = 'WHERE [PoliceDeptId] = ' + CAST(@PoliceDeptId as nvarchar(max)) + ' '
END

-- @FileType
IF (@FileType IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
END

-- @PoliceOfficerId
IF (@PoliceOfficerId IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
END

-- @FirstName
IF (@FirstName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FirstName] LIKE ''' + @FirstName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FirstName] LIKE ''' + @FirstName + '%'' '
END
END

-- @LastName
IF (@LastName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [LastName] LIKE ''' + @LastName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [LastName] LIKE ''' + @LastName + '%'' '
END
END

-- @StartFileCreationDate AND @EndFileCreationDate
IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
END

-- @StartFileCompletionDate AND @EndFileCompletionDate
IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
END

PRINT @MainQuery + ' ' + @Condition
EXEC (@MainQuery + ' ' + @Condition)


/*
SELECT *
FROM [dbo].[Service.CPIC.GetAllFiles]()

-- @PoliceDeptId
WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)

-- @StartFileCreationDate
AND [


-- @EndFileCreationDate
*/

[SQL] Conversion DateTime DataType

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example:

Format Date to mm/dd/yyyy
CONVERT(nvarchar(11),[FieldName],101)

From: '2007-05-04 16:35:52.140'
To: '05/04/2007'

Reseting Time Value to Zero
CAST(CONVERT(nvarchar(11),[FieldName],101) as datetime)

From: '2007-05-04 16:35:52.140'
To: '2007-05-04 00:00:00.000'

[SQL] Common Catalog Stored Procedures

1. sp_columns
Returns column information for the specified tables or views that can be queried in the current environment.

Example:

USE AdventureWorks
GO
EXEC sp_columns @table_name = N'Department',
@table_owner = N'HumanResources'

2. sp_tables
Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause.

Example:
USE AdventureWorks;GOEXEC sp_tables @table_name = '%', @table_owner = 'Person', @table_qualifier = 'AdventureWorks'

3. sp_databases
Lists databases that either reside in an instance of the SQL Server 2005 Database Engine or are accessible through a database gateway.

Example:
USE master;
GO
EXEC sp_databases;

[C#.Net] Session Class

public class SessionHelper
{
private const string POLICE_PROFILE = "POLICE_PROFILE";

public static PoliceOfficerDS PoliceProfile
{
get
{
if (HttpContext.Current.Session[POLICE_PROFILE] == null)
{
return new PoliceOfficerDS();
}
else return (PoliceOfficerDS)HttpContext.Current.Session[POLICE_PROFILE];
}
set
{
HttpContext.Current.Session[POLICE_PROFILE] = value;
}
}
}

[SQL] Complex WHERE Condition

WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)