Programmatically Speaking ...

Get the Monday date of a given week in a given year

I have not done much sophisticated SQL server programming, generally I profer ad-hoc queries, select-insert-update-delete, and leave other calculations in .net programming. (When I do do heavy-lifting database programming, I use foxpro. I like quick and dirty stuff. Really have to try to pay more attention to style and comments).

However, a user question in DNS forum really got me to look at SQL programmabilities, especially writing native sql functions.  

Quoting the user question:

"I'm pretty new to all the SQL Server things, but I was wondering if the following is possible:

@Week=26 and @Year=2008, based on these two variables, is it possible to let SQL Server calculate the date of the monday in week 26?"

Some quick thinking and quick search led me to the following solution of mine.

CREATE FUNCTION [dbo].[GetFirstMondayOfAWeekInYear]
( @InputYear   int,
  @InputWeekNo  int
)
RETURNS DATETIME
BEGIN
declare @firstDayOfYear as datetime, @firstMondayOfYear as datetime;

--get the first day of year

set @firstDayOfYear = convert(VARCHAR(10),str(@InputYear)+ '-1-1',111)

set @firstMondayOfYear = DATEADD(DD, 1 - DATEPART(DW, @firstDayOfYear)+1,
               @firstDayOfYear);

RETURN DATEADD(DD, (@InputWeekNo -1) * 7, @firstMondayOfYear)

END
GO

 To use the function, you just call:

select

I tested, it worked like charm.

Action steps in the function

1) Get the date of the first day of the given year. Easy, the first date of any year would be Year + "-1-1".

2) Find out the date of the monday of this week. For example, for 2008, the first monday date would be 12-31-2007.

3) Now, add #weeks * 7 to the very first monday, there we get it.

CompareValidator in three ways

 1. Data type validation

Syntax:

<asp:CompareValidator id="CompareFieldValidatorDOB" runat="server"

ControlToValidate="xxx"

Type="xxx"

Operator="DataTypeCheck"

ErrorMessage="…">

</asp:CompareValidator >

allowable types:

String

Integer

Double

Date

Currency

Example:

Birthdate

<asp:TextBox ID="txtDOB" runat="server" ></asp:TextBox>

<asp:CompareValidator id="CompareFieldValidatorDOB" runat="server"

ForeColor="Red"

ControlToValidate="txtDOB"

Type="Date"

Operator="DataTypeCheck"

ErrorMessage="Please enter an valid date">

</asp:CompareValidator >

2.  Comparison against a value

Syntax:

<asp:CompareValidator id=" " runat="server"
ControlToValidate=" "
ValueToCompare=
Type=""
Operator=" "
ErrorMessage=" ">

Available Operators:

Example:

Age: <asp:textbox id="txtAgeYrs" runat="server"/>

<asp:CompareValidator id="CompareFieldValidator1" runat="server"

ForeColor="Red"

ControlToValidate="txtAgeYrs"

ValueToCompare=0

Type="Integer"

Operator="GreaterThan"

ErrorMessage="Please enter a whole number greater than zero.">

</asp:CompareValidator >

3. Compare the user entry against the value of another control

Syntax:

<asp:CompareValidator id=" " runat="server"

ForeColor="Red"

ControlToValidate=""

ControlToCompare=""

Type="String"

Operator="Equal"

ErrorMessage="Confirm password must be the same.">

</asp:CompareValidator >

Example:

Password: <asp:textbox id="txtPassword" runat="server" TextMode="Password"/><br/>

Confirm Password:<asp:textbox id="cfmPassword" runat="server"  TextMode="Password"/>

<asp:CompareValidator id="CompareValidator1" runat="server"

ForeColor="Red"

ControlToValidate="cfmPassword"

ControlToCompare="txtPassword"

Type="String"

Operator="Equal"

ErrorMessage="Confirm password must be the same.">

</asp:CompareValidator >

Sending Email Asynchronously

A DNS user in the forum asked about how to quickly respond to users while sending bulk email in the background. This got me to write a test function to see how it works to send emails asynchronously, which resulted the following code (modified from some existing code from somewhere).

Web Form Directive (Please note that to use asynchronous mode, set Async="true") :

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

Send Email Code:

public void Send(string from, string

[] to, string subject, string messageBody,

bool isBodyHtml, bool Async,

string[] attachments)

{

SmtpClient emailClient;

try

{

emailClient = new SmtpClient();

emailClient.Host = //”Your host”;

emailClient.UseDefaultCredentials = true;

emailClient.DeliveryMethod = SmtpDeliveryMethod.Network;

emailClient.Port = 25;

//prepare the MailMessage contents

MailMessage message = new MailMessage ();

message.From = new MailAddress (from);

message.Subject = subject;

message.Body = messageBody;

// message.Sender = new MailAddress(from);

//set the receiver' address

foreach (string address in to)

message.To.Add(new MailAddress (address));

//set to indicate whether to use html format

message.IsBodyHtml = isBodyHtml;

message.BodyEncoding = System.Text.Encoding.UTF8;

Attachment a = new Attachment(@"D:\backup\index.html");

if (attachments.Length != null)

{

for (int i = 0; i < attachments.Length; i++)

message.Attachments.Add(new Attachment (attachments));}

if (Async == true)

{

//wire up the event for when the Async send is completed

//smtp.SendCompleted += new SendCompletedEventHandler(SmtpClient_OnCompleted);

// emailClient.SendCompleted +=

emailClient.SendAsync(message, null);

// sendDelegate(emailClient, message);

}

else

emailClient.Send(message);

message.Dispose();

}

catch (Exceptionex)

{

Response.Write(ex.Message);

}

}

Note:

I have done some mini-scale test, there is no significant performance advantage of async over sync. However, if sending thousands of emails would be a differnt matter.

...

Blogging is a buiness that needs a lot of discipline, which I sorely lack, as a result, my blog has a whole month of gapping hole. Too bad. I hope I can do better from now on.