/* ------------------------ My Meta Content Here SEO ------------------------ */


Main Menu

Thursday, December 22, 2011

Using Datediff() Function in SQL SERVER

The DATEDIFF() function returns the time between two dates.

Simple example using Datediff()

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS [Difference Date]

Calculating Intervals using Datediff() Function

USE test

CREATE TABLE [dbo].[MyTable](
    [CustomerId] [int] NULL,
    [DateTime] [datetime] NULL,
    [interval] [int] NULL

CREATE PROC CalculateInterval
    DECLARE @InsertionTime DATETIME
    --SELECT @InsertionTime =  [datetime] FROM MyTable WHERE customerid=6
    SET @InsertionTime =  (SELECT TOP(1) datetime FROM MyTable ORDER BY datetime DESC)
    INSERT INTO MyTable(customerid,datetime,Interval)

EXEC CalculateInterval

Convert Seconds to hours:minutes:seconds

SET @sec=7612
SELECT CONVERT(VARCHAR(5),@sec/3600)+':'+CONVERT(VARCHAR(5),@sec%3600/60)+':'+CONVERT(VARCHAR(5),(@sec%60))

Find total seconds of time in sql server. You can get the Total seconds of the current time in

SELECT [Total Seconds] =
(DATEPART(hh, GETDATE()) * 3600) +

time (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb677243.aspx
Date and Time Type
Date and Time Functions
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
minutemi, n
secondss, s
Read More »

Wednesday, December 21, 2011

Classic ASP and ASP.NET Security Image (CAPTCHA) Generator

Create and download your own CAPTCHA

Read More »

Tuesday, December 20, 2011

Send Mail in ASP.Net 2.0

Send Email in ASP.Net 2.0 - Feed back Form

using System.Web.Mail;
using System.Net.Mail;

The Send mail functionality is similar to Dotnet 1.1 except for few changes
System.Net.Mail.SmtpClient is used instead of System.Web.Mail.SmtpMail (obsolete in Dotnet 2.0).
System.Net.MailMessage Class is used instead of System.Web.Mail.MailMessage (obsolete in Dotnet 2.0)
The System.Net.MailMessage class collects From address as MailAddress object.
The System.Net.MailMessage class collects To, CC, Bcc addresses as MailAddressCollection.
MailMessage Body Format is replaced by IsBodyHtml

The Code is Self explanatory by itself.

public void SendMailUsingSmtp()
SmtpClient smtpclient = new SmtpClient();
MailMessage message = new MailMessage();
//SMTP Server Details
string smtp = "mail.emailsrvr.com";
string smtpuserid = "smtptest@magnonsolutions.com";
string smtppassword = "smtptest";
string port = "25";

//smtpclient.Host = "localhost";
//smtpclient.Port = 25;
//smtpclient.Host = "smtp.gmail.com";
//smtpclient.Port = 465;

//FROM E-Mail Address
MailAddress fromaddress = new MailAddress(txtsendmail.Text, string.Concat(txtfname.Text, txtlname.Text));
message.From = fromaddress;

//TO E-Mail Address

//CC E-Mail Address

//BCC E-Mail Address

//Subject Here
message.Subject = "Feedback";
message.SubjectEncoding = System.Text.Encoding.UTF8;

//Body can be Html or text format
//Specify true if it is html message
message.IsBodyHtml = false;

// Message body content
message.Body = txtfname.Text;
message.BodyEncoding = System.Text.Encoding.UTF8;

//Checkout the Credentitial for smtp mail server
smtpclient.Credentials = new System.Net.NetworkCredential(smtpuserid, smtppassword);
smtpclient.Port = Convert.ToInt32(port);
smtpclient.Host = smtp;
smtpclient.EnableSsl = false;
lblStatus.Visible = true;
lblStatus.Text = "Messgae has been sent successfully.";
catch (Exception ex)
lblStatus.Visible = true;
lblStatus.Text = "Send Email Failed." + ex.Message;

Below is a C# and VB.NET class that demonstrates using System.Net.Mail to send an email.
Calling the function from code
MailHelper.SendMailMessage("fromAddress@yourdomain.com", "toAddress@yourdomain.com", "bccAddress@yourdomain.com", "ccAddress@yourdomain.com", "Sample Subject", "Sample body of text for mail message")

using System.Net.Mail;
public class MailHelper
/// Sends an mail message
Sender address
Recepient address
Bcc recepient
Cc recepient
Subject of mail message
Body of mail message

public static void SendMailMessage(string from, string to, string bcc, string cc, string subject, string body)
// Instantiate a new instance of MailMessage
MailMessage mMailMessage = new MailMessage();

// Set the sender address of the mail message
mMailMessage.From = new MailAddress(from);

// Set the recepient address of the mail message
mMailMessage.To.Add(new MailAddress(to));

// Check if the bcc value is null or an empty string
if ((bcc != null) && (bcc != string.Empty))
// Set the Bcc address of the mail message
mMailMessage.Bcc.Add(new MailAddress(bcc));
} // Check if the cc value is null or an empty value
if ((cc != null) && (cc != string.Empty))
// Set the CC address of the mail message
mMailMessage.CC.Add(new MailAddress(cc));
} // Set the subject of the mail message
mMailMessage.Subject = subject;
// Set the body of the mail message
mMailMessage.Body = body;

// Set the format of the mail message body as HTML
mMailMessage.IsBodyHtml = true;
// Set the priority of the mail message to normal
mMailMessage.Priority = MailPriority.Normal;

// Instantiate a new instance of SmtpClient
SmtpClient mSmtpClient = new SmtpClient();
// Send the mail message

Imports System.Net.Mail
Public Class MailHelper
''' ''' Sends an mail message
''' ''' Sender address
''' Recepient address
''' Bcc recepient
''' Cc recepient
''' Subject of mail message
''' Body of mail message

Public Shared Sub SendMailMessage(ByVal from As String, ByVal recepient As String, ByVal bcc As String, ByVal cc As String, ByVal subject As String, ByVal body As String)

' Instantiate a new instance of MailMessage
Dim mMailMessage As New MailMessage()

' Set the sender address of the mail message
mMailMessage.From = New MailAddress(from)
' Set the recepient address of the mail message
mMailMessage.To.Add(New MailAddress(recepient))

' Check if the bcc value is nothing or an empty string
If Not bcc Is Nothing And bcc <> String.Empty Then
' Set the Bcc address of the mail message
mMailMessage.Bcc.Add(New MailAddress(bcc))
End If

' Check if the cc value is nothing or an empty value
If Not cc Is Nothing And cc <> String.Empty Then
' Set the CC address of the mail message
mMailMessage.CC.Add(New MailAddress(cc))
End If

' Set the subject of the mail message
mMailMessage.Subject = subject
' Set the body of the mail message
mMailMessage.Body = body

' Set the format of the mail message body as HTML
mMailMessage.IsBodyHtml = True
' Set the priority of the mail message to normal
mMailMessage.Priority = MailPriority.Normal

' Instantiate a new instance of SmtpClient
Dim mSmtpClient As New SmtpClient()
' Send the mail message
End Sub
End Class

Web.config xml version="1.0"?>
<smtp from="defaultEmail@yourdomain.com">
<network host="smtp.yourdomain.com" port="25" userName="yourUserName" password="yourPassword"/>
Read More »

Friday, December 16, 2011

SQL SERVER 2005 Ranking Functions (Transact-SQL) – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL Server 2005 ranking functions

SQL Server 2005 has total of 4 Ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.


Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.It Returns Bigint type Value

RANK () OVER ([] )
Returns the rank of each row within the partition of a result set.

Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([] )
Distributes the rows in an ordered partition into a specified number of groups.


CREATE TABLE OrderRanking (OrderID INT IDENTITY(1,1) NOT NULL, CustomerID INT, OrderTotal decimal(15,2))

************************Inserting Rows Into the table *****************************

INSERT OrderRanking (CustomerID, OrderTotal)
SELECT 1, 1000
SELECT 1, 500
SELECT 1, 650
SELECT 1, 3000
SELECT 2, 1000
SELECT 2, 2000
SELECT 2, 500
SELECT 2, 500
SELECT 3, 500
SELECT 4, 100

*******************Query for the Ranking Function One by One **********************


ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS [Row No],    
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [RowNo Partitionwise],

RANK() OVER (ORDER BY OrderTotal DESC) AS [Rank],

RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Rank Partitionwise],

DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS [Dense Rank],

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Dense Rank Partitionwise],


FROM OrderRanking ORDER BY OrderTotal DESC

Query Results:

==================== For more information ===================
- NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }
- RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }
- DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }
- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }
- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }
- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }
- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }
- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }
Read More »

Wednesday, December 7, 2011

Sql Server Connection Module in VB 6.0

Global conn As New adodb.connection
Global con As New adodb.connection
Global pconn As New adodb.connection
Global rs As New adodb.Recordset
Global app_path As String
Global server_name As String
Global server_username As String
Global server_password As String
Global userName As String

Public Sub connect()  
    If conn.State = 1 Then conn.Close
    app_path = "C:\Program Files\Microsoft Visual Studio\VB98\"
    server_name = "your server name data source"
    server_username = "dbusername"
    server_password = "dbpassword"

    Call OpenConnection
    ''******* connection ***********
    If pconn.State = adStateOpen Then pconn.Close
    pconn.CursorLocation = adUseClient
    pconn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & server_username & ";pwd=" & server_password & ";Initial Catalog=Your DataBase Name;Data Source= " & server_name
End Sub
Read More »

Tuesday, December 6, 2011

Tuesday, November 29, 2011

Managing Connection Strings for Web Farms in ASP.NET 2.0


What do I do with my connection strings? How do I encrypt them to protect my logins? Where should I store them? How can I share them across servers? These are questions that have plagued developers for ages. With .NET 1.0/1.1, a consensus has formed on storing this information in the web.config file. Encryption is fairly simple using the framework encryption libraries and letting Windows internally manage the encryption key.
But Web Farms are more problematic. The encryption algorithm is often shared with other encryption needs (e.g., user passwords), so all machines must share a common encryption key. Key management then becomes a problem, because if someone finds your key, there are only a handful of algorithms to test before your data is compromised. Microsoft recommended storing the key or the connection string in a protected registry key, but in practice I found this to be cumbersome because it required execution of your ASP.NET application as a specially privileged user. Storing the key in the web.config file is easier, but then you had the encrypted data and the decryption key living in the same file - bad idea. Machine config is a better choice, but occasionally causes problems with other applications on the same box, like SharePoint. So there are solutions but no perfect answers.

Simple Example: Unencrypted Connection Strings in Web.Config

We'll start with some simple test code to check our connection. It is the computer classic, Hello World, using the database to say hello.
Create a new website using IIS to host:
  1. File > New > Website >
  2. In dialog, choose the following settings:
    • Template = ASP.NET Web Site
    • Location dropdown = HTTP
    • URL = http://localhost/connectionTest
Next, add the code listed below. The connection string assumes SQL Server 2005 to be installed as a named instance on myserver. For SQLExpress, the server will look like "localhost\SQLExpress".

Test Code Listings

Here is some simple code written as it would be for .NET 1.1. We'll use this as a starting point.


Here, we define our connection string. You can see I added a SQL user for AdventureWorks named test with the password test.
        <add key="myconnection" 
           value="server=myserver\sql2005;initial cata
                  log=AdventureWorks;user id=test;pwd=test;"/>


This is the snippet of code that opens a connection and has the database say hello. There is an ASP Labelcontrol named "Label1" and an ASP Button control named "Button1" on the page.
using System.Data.SqlClient;

protected void Button1_Click(object sender, EventArgs e)
    string constr = ConfigurationSettings.AppSettings["myconnection"];
    SqlConnection sqlConn = new SqlConnection(constr);
    SqlCommand cmd = sqlConn.CreateCommand();
    cmd.CommandText = "SELECT 'Hello World'";
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
            this.Label1.Text = reader.GetString(0);
            this.Label1.Text = "No Result";
    catch (Exception ex)
        this.Label1.Text = "Error: " + ex.Message;
        if(sqlConn.State != ConnectionState.Closed)

Test Code Updated to ASP.NET 2.0


What happened to my <appSettings> config section? It's still there and still usable, but Microsoft has decided to promote connection strings to their own section. The updated code looks like this:
        <add name="myconnection" 
                            user id=test;pwd=test;"/>


The way configuration settings are accessed has changed slightly as well. The oldSystem.Configuration.AppSettings object has been deprecated in favor of the newSystem.Configuration.ConfigurationManager. In the case of connection strings, we access theConnectionStrings collection to obtain a ConnectionStringSettings object.
protected void Button1_Click(object sender, EventArgs e)
    ConnectionStringSettings conn = 
    string constr = conn.ConnectionString;
So that's the simple, unencrypted way to do connection strings. It would run just fine for web farms as is, but you have exposed SQL credentials which is not particularly safe. What about encrypting for web farms?
Help! My code isn't working!
If you get the following error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
you may have to modify your SQL Server (2005) to allow external connections via the SQL Server Surface Area Configuration tool (under Start > Programs > Microsoft SQL Server 2005 > Configuration Tools). Either that or your connection string is incorrect.

Encrypting Connection Strings

Encryption of the connectionStrings section of the web.config is easy and fun! Just follow the few easy steps outlined in MSDN (click on the "Configuration" link). You utilize the aspnet_regiis utility to encrypt a section in the application's web.config (hence we use IIS to host our development site instead of the VS2005 built-in web server).
  1. Open a command prompt in the .NET 2.0 framework directory. On most computers, this will be:
  2. Per MSDN instructions, type the following command:
    aspnet_regiis.exe -pe "connectionStrings" -app "/connectionTest"
This uses the default RSAProtectedConfigurationProvider to encrypt the data. This is also the recommended provider to use when setting up a web farm. After this task has been performed, the<connectionStrings> section in your web.config will be rewritten to look something like this:
  <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
     Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
   <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
    <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
        Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
     <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
      <KeyName>Rsa Key</KeyName>
Now, everything is encrypted and protected and safe. Try to execute your page to prove it.
  • Parser Error Message: Failed to decrypt using provider 'RsaProtectedConfigurationProvider'. Error message from the provider: The RSA key container could not be opened.
Whoops! Seems the MSDN article left out an important step. The application user needs to have access granted to the key container. What access? What key container? Well, let's find out.

Decrypting the Web.Config

First, let's get back to the point where we started. While it is a good idea to maintain backups of yourweb.config file before doing this encryption, it is reversible. Open your command prompt and go to the .NET Framework installation as before. Issue the following command to decrypt and restore your web.config to its previous state:
aspnet_regiis.exe -pd "connectionStrings" -app "/connectionTest"
The -pd is the decryption flag for the utility. Your config file should be back to its previous state.

Re-Encrypting your Web.Config Correctly for Web Farms

For encryption to work, the key must be accessible both during encryption and during decryption. Our prior example failed because it was not available during decryption. As it turns out, the simple examples out there of how to encrypt your connectionStrings are misleading at best. It is a three step process.
  1. Add a <configProtectedData> section to your web.config to identify the encryption provider and your key container.
  2. Create your own key container on the server.
  3. Grant access to this key container to all interested accounts.
  4. Encrypt the <connectionStrings> section of web.config.
  5. Export the key container for use on other servers in the farm.
So let's do it, step by step.

Step 1: Modify web.config to identify the key container

Edit your web.config file to contain a provider definition that identifies which key container to use for encryption/decryption. Under the <configuration> section, add the following:
<configProtectedData >
        <add name="ConnectionTestProvider"
                System.Configuration, Version=,
                Culture=neutral, processorArchitecture=MSIL"

You may also wish to modify the configuration tag to identify the proper XML namespace, to avoid XML validation errors. It's not necessary for compilation and execution, but it is annoying to have the dev environment tell you the attributes required are not valid. Modify the <configuration> tag to add the xmlnsattribute as below.
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

Step 2: Create the key container on the server

Use aspnet_regiis at the command prompt to generate a new RSA cryptographic key container calledconnectionTestKey.
> aspnet_regiis -pc "connectionTestKey" -exp
This will create a new machine level key container in the following location:
C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys
You are highly advised to not manually manipulate these files (delete, change permissions, rename, etc.). I hosed my IIS installation doing this, and had to do a ground-up reinstall of IIS and both the 1.1 and 2.0 .NET Frameworks. My VS2005 installation is still not quite right as a result of this.

Step 3: Grant access to the key container

> aspnet_regiis -pa "connectionTestKey" "ASPNET"
This grants read access on the crypto key file to the indicated user; in this case, the ASPNET built-in account is granted access. Be warned: you may also have to grant read access on the directory C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys to the ASPNET account. I had endless problems with this and it is an undocumented "feature" of the security model.

Step 4: Encrypt the section of your web.config file

> aspnet_regiis -pe "connectionStrings" -app "/connectionTest"
This will encrypt and rewrite your web.config file. You are now good to go (on this server). If you execute the code, your "Hello World" app should work fine.

Step 5: Export the key container for later use on farm servers

> aspnet_regiis -px "connectionTestKey" \temp\mykeyfile.xml
This writes the key to an XML file for use on other machines in your farm. The contents of the key file will look something like this:

Step 6: Import the key container on farm servers

> aspnet_regiis -pi "connectionTestKey" mykeyfile.xml
When you deploy your application, you will need to import the key as part of the installation process. Place the key file in an accessible location on the server and run the command above to import the key. You are now good to go!

Pitfalls to avoid

Most problems, predictably, revolve around proper configuration of access privileges to the key container. Make sure you add access rights for the account your application is running under, to both the key container and the folder holding it.
If problems occur, do not delete your key containers willy-nilly; use the aspnet_regiis utility. I did this (manually deleted the container) to remove a key container I had mugged up by removing everyone's privilege to. In the process, I deleted some other key containers that were important. Upon restarting IIS, I discovered I could not restart IIS. Instead, I got the following error for the IISADMIN service:
System error 8 - Not enough storage is available to process this command
This required me to uninstall and reinstall IIS to fix the problem. And both the .NET frameworks too. And it's still not fixed. I continue to have the following error:
Safe handle has been closed
when I try to do anything other than create keys. Comments/clues are appreciated. I just hope it doesn't require a full OS install to fix. I'm guessing that the IIS metabase also gets touched when doing things with crypto keys, but I'm still investigating the matter.


To summarize, utilize the new <connectionStrings> section in the web.config along with the newSystem.Configuration.ConfigurationManager.ConnectionStrings collection to obtain your connection string from the configuration file. Create a key container using the aspnet_regiis utility, and identify the proper container in you web.config by adding a <configProtectedData> section. Encrypt with theaspnet_regiis utility, and export your key for use on other servers in the web farm.
I'm sorry, why is this so complex again? While I laud Microsoft's recognition of the need for security and consistency when handling connection strings, this multi-step, thinly documented process makes me wonder a little about their sanity. In some ways, I appreciate the justification for people like me to continue to make the big bucks. In other ways, I wonder why a simple add-in or installer option wasn't written for VS2005 to handle web farm connection strings. It's almost like they hired some UNIX geek or a Java developer to make this crypto utility stuff work. I smell an opportunity for some intrepid developer.
Also, why was aspnet_regiis appropriated for so many non-ASP.NET-framework-registration-with-IIS functions? Instead of making a security key configuration utility, aspnet_regiis became a dumping ground for this functionality. A poorly thought out utility decision in an otherwise impressively designed framework and SDK (IMnsHO).

Resources Used in Writing this Article

Read More »

Recent Posts

My Blog List