Send Email from SQL-Server Database

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

The following is the process for sending emails from database.

- Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.

- Write a script to send an e-mail. The following is the script.

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mathew@xyz.com; saadhya@xyz.com;anirudh@pqr.com’ 
@body = ' A warm wish for your future endeavor',
@subject = 'This mail was sent using Database Mail' ;
GO 

References:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Compiled By: Rajesh Rolen

Read More

How to get @@Error and @ROWCOUNT at the same time?

if @@Rewcount is checked after error checking statement then it will have '0' as the value of @@Recordcount as it would have been reset. and if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.

select @recCount = @@Rowcount, @Err = @@error

Compiled By: Rajesh Rolen

Read More

What is Table Scan?

Table Scan comes to picture when you search for data in a table and you table does't have any index created on it or your query does't take advantage of any existing index of table.
In normal condition Table Scan is not good but in some circumstances its good to use.

A table scan is the easiest and simplest operation that can be performed against a table. It sequentially processes all of the rows in the table to determine if they satisfy the selection criteria specified in the query. It does this in a way to maximize the I/O throughput for the table.

A table scan operation requests large I/Os to bring as many rows as possible into main memory for processing. It also asynchronously pre-fetches the data to make sure that the table scan operation is never waiting for rows to be paged into memory. Table scan however, has a disadvantage in it has to process all of the rows in order to satisfy the query. The scan operation itself is very efficient if it does not need to perform the I/O synchronously.

Now lets understand what exactly happens during Table Scan

Reads all of the rows from the table and applies the selection criteria to each of the rows within the table. The rows in the table are processed in no guaranteed order, but typically they are processed sequentially.

When Table Scan isn't Bad thing

If you are retrieving every row from a table, and plan on using it, it doesn't really matter that you are doing a table scan because there is really no other way to do it. Another time that it doesn't matter so much is when there is very little data in a table, like a type table or a small lookup table, because SQL Server can intelligently cache data when it needs to, and when there is very little data it can actually degrade performance some when it does have an index because of the overhead of maintaining the index. Striking a good balance of indexing vs. non-indexing is a skill that will come with time and experience.

Ex:

SELECT * FROM Employee
WHERE WorkDept BETWEEN 'A01'AND 'E01'
OPTIMIZE FOR ALL ROWS

References:
http://strangenut.com/blogs/dacrowlah/archive/2008/02/24/what-is-a-table-scan.aspx

Compiled By: Rajesh Rolen

Read More

ASP.NET Application and Page Life Cycle

Today we are going to learn complete application and page life cycle of asp.net
Beginning from request sent from browser to process and then response sent back to browser.

In broad way there are only 2 step process

- creation of an environment which can process the request
(creation of application object, request, response and context objects to process the request)

- Once the environment is created, the request is processed through a series of events which is processed by using modules, handlers and page objects. To keep it short, let's name this step as MHPM (Module, handler, page and Module event)

Creation of ASP.NET Environment

Step 1: The user sends a request to IIS. IIS first checks which ISAPI extension can serve this request. Depending on file extension the request is processed. For instance, if the page is an ‘.ASPX page’, then it will be passed to ‘aspnet_isapi.dll’ for processing.

Step 2: If this is the first request to the website, then a class called as ‘ApplicationManager’ creates an application domain where the website can run. As we all know, the application domain creates isolation between two web applications hosted on the same IIS. So in case there is an issue in one app domain, it does not affect the other app domain.

Step 3: The newly created application domain creates hosting environment, i.e. the ‘HttpRuntime’ object. Once the hosting environment is created, the necessary core ASP.NET objects like ‘HttpContext’ , ‘HttpRequest’ and ‘HttpResponse’ objects are created.

Step 4: Once all the core ASP.NET objects are created, ‘HttpApplication’ object is created to serve the request. In case you have a ‘global.asax’ file in your system, then the object of the ‘global.asax’ file will be created. Please note global.asax file inherits from ‘HttpApplication’ class.
Note: The first time an ASP.NET page is attached to an application, a new instance of ‘HttpApplication’ is created. Said and done to maximize performance, HttpApplication instances might be reused for multiple requests.

Step 5: The HttpApplication object is then assigned to the core ASP.NET objects to process the page.

Step 6: HttpApplication then starts processing the request by HTTP module events, handlers and page events. It fires the MHPM event for request processing.

The below image explains how the internal object model looks like for an ASP.NET request. At the top level is the ASP.NET runtime which creates an ‘Appdomain’ which in turn has ‘HttpRuntime’ with ‘request’, ‘response’ and ‘context’ objects.

Process Request using MHPM Events Fired

Once ‘HttpApplication’ is created, it starts processing requests. It goes through 3 different sections ‘HttpModule’ , ‘Page’ and ‘HttpHandler’. As it moves through these sections, it invokes different events which the developer can extend and add customize logic to the same.
Before we move ahead, let's understand what are ‘HttpModule’ and ‘HttpHandlers’. They help us to inject custom logic before and after the ASP.NET page is processed. The main differences between both of them are:

If you want to inject logic based in file extensions like ‘.ASPX’, ‘.HTML’, then you use ‘HttpHandler’. In other words, ‘HttpHandler’ is an extension based processor.

If you want to inject logic in the events of ASP.NET pipleline, then you use ‘HttpModule’. ASP.NET. In other words, ‘HttpModule’ is an event based processor.

Below is the logical flow of how the request is processed. There are 4 important steps MHPM as explained below:

Step 1(M: HttpModule): Client request processing starts. Before the ASP.NET engine goes and creates the ASP.NET HttpModule emits events which can be used to inject customized logic. There are 6 important events which you can utilize before your page object is created BeginRequest, AuthenticateRequest, AuthorizeRequest, ResolveRequestCache, AcquireRequestState and PreRequestHandlerExecute.

Step 2 (H: ‘HttpHandler’): Once the above 6 events are fired, ASP.NET engine will invoke ProcessRequest event if you have implemented HttpHandler in your project.

Step 3 (P: ASP.NET page): Once the HttpHandler logic executes, the ASP.NET page object is created. While the ASP.NET page object is created, many events are fired which can help us to write our custom logic inside those page events. There are 6 important events which provides us placeholder to write logic inside ASP.NET pages Init, Load, validate, event, render and unload. You can remember the word SILVER to remember the events S – Start (does not signify anything as such just forms the word) , I – (Init) , L (Load) , V (Validate), E (Event) and R (Render).

Step4 (M: HttpModule): Once the page object is executed and unloaded from memory, HttpModule provides post page execution events which can be used to inject custom post-processing logic. There are 4 important post-processing events PostRequestHandlerExecute, ReleaserequestState, UpdateRequestCache and EndRequest.
The below figure shows the same in a pictorial format.

In What Event Should We Do What?

Section	Event	Description
HttpModule	BeginRequest	This event signals a new request; it is guaranteed to be raised on each request.
HttpModule	AuthenticateRequest	This event signals that ASP.NET runtime is ready to authenticate the user. Any authentication code can be injected here.
HttpModule	AuthorizeRequest	This event signals that ASP.NET runtime is ready to authorize the user. Any authorization code can be injected here.
HttpModule	ResolveRequestCache	In ASP.NET, we normally use outputcache directive to do caching. In this event, ASP.NET runtime determines if the page can be served from the cache rather than loading the patch from scratch. Any caching specific activity can be injected here.
HttpModule	AcquireRequestState	This event signals that ASP.NET runtime is ready to acquire session variables. Any processing you would like to do on session variables.
HttpModule	PreRequestHandlerExecute	This event is raised just prior to handling control to the HttpHandler. Before you want the control to be handed over to the handler any pre-processing you would like to do.
HttpHandler	ProcessRequest	Httphandler logic is executed. In this section, we will write logic which needs to be executed as per page extensions.
Page	Init	This event happens in the ASP.NET page and can be used for: Creating controls dynamically, in case you have controls to be created on runtime. Any setting initialization. Master pages and the settings. In this section, we do not have access to viewstate, postedvalues and neither the controls are initialized.
Page	Load	In this section, the ASP.NET controls are fully loaded and you write UI manipulation logic or any other logic over here.
Page	Validate	If you have valuators on your page, you would like to check the same here.
	Render	It’s now time to send the output to the browser. If you would like to make some changes to the final HTML which is going out to the browser, you can enter your HTML logic here.
Page	Unload	Page object is unloaded from the memory.
HttpModule	PostRequestHandlerExecute	Any logic you would like to inject after the handlers are executed.
HttpModule	ReleaserequestState	If you would like to save update some state variables like session variables.
HttpModule	UpdateRequestCache	Before you end, if you want to update your cache.
HttpModule	EndRequest	This is the last stage before your output is sent to the client browser.

ASP.NET Page Events

Any ASP.NET page has 2 parts, one is the page which is displayed on the browser which has HTML tags, hidden values in form of viewstate and data on the HTML inputs. When the page is posted, these HTML tags are created in to ASP.NET controls with viewstate and form data tied up together on the server. Once you get these full server controls on the behind code, you can execute and write your own login on the same and render the page back to the browser.

Now between these HTML controls coming live on the server as ASP.NET controls, the ASP.NET page emits out lot of events which can be consumed to inject logic. Depending on what task / logic you want to perform, we need to put this logic appropriately in those events.
Note: Most of the developers directly use the page_load method for everything, which is not a good thought. So it’s either populating the controls, setting view state, applying themes, etc., everything happens on the page load. So if we can put logic in proper events as per the nature of the logic, that would really make your code clean.

Seq	Events	Controls Initialized	View state Available	Form data Available	What Logic can be written here?
1	Init	No	No	No	Note: You can access form data etc. by using ASP.NET request objects but not by Server controls.Creating controls dynamically, in case you have controls to be created on runtime. Any setting initialization.Master pages and them settings. In this section, we do not have access to viewstate , posted values and neither the controls are initialized.
2	Load view state	Not guaranteed	Yes	Not guaranteed	You can access view state and any synch logic where you want viewstate to be pushed to behind code variables can be done here.
3	PostBackdata	Not guaranteed	Yes	Yes	You can access form data. Any logic where you want the form data to be pushed to behind code variables can be done here.
4	Load	Yes	Yes	Yes	This is the place where you will put any logic you want to operate on the controls. Like flourishing a combobox from the database, sorting data on a grid, etc. In this event, we get access to all controls, viewstate and their posted values.
5	Validate	Yes	Yes	Yes	If your page has validators or you want to execute validation for your page, this is the right place to the same.
6	Event	Yes	Yes	Yes	If this is a post back by a button click or a dropdown change, then the relative events will be fired. Any kind of logic which is related to that event can be executed here.
7	Pre-render	Yes	Yes	Yes	If you want to make final changes to the UI objects like changing tree structure or property values, before these controls are saved in to view state.
8	Save view state	Yes	Yes	Yes	Once all changes to server controls are done, this event can be an opportunity to save control data in to view state.
9	Render	Yes	Yes	Yes	If you want to add some custom HTML to the output this is the place you can.
10	Unload	Yes	Yes	Yes	Any kind of clean up you would like to do here.

I wants to give lots of thanks to real author of this article Mr. Shivprasad koirala
References :http://www.codeproject.com/KB/aspnet/ASPDOTNETPageLifecycle.aspx

Read More

Will finally blocks be executed if returning from try or catch blocks

Yes, the finally block is executed however the flow leaves the try block - whether by reaching the end, returning, or throwing an exception.

The return value is determined before the finally block is executed though, so if you did this:

int Test()
{
    int result = 4;
    try
    {
        return result;
    }
    finally
    {
        // Attempt to change value result
        result = 1;
    }
}

the value 4 will still be returned, not 1 .

- the assignment in the finally block will have no effect the return result but it will be executed.

A finally block will always be executed and this will happen before returning from the method, so you can safely write code

Compiled By: Rajesh Rolen

Read More

Where is session stored if cookie is disabled on client’s machine

The session cookie is a special non-persistant cookie. It's only stored in memory, so in most cases even when cookies are disabled it still works fine.

It's also possible to enable something called cookieless sesssions where the sessionID is embedded in the URL, like this:

http://yourserver/folder/ (encrypted session ID here) /default.aspx

Here's a link to an MSDN article with more details:
http://msdn.microsoft.com/en-us/library/aa479314.aspx

Read More

Get all column names of datatable into string array

using Linq you can easily get list/array of all column names of a datatable:

string[] columnNames = dt.Columns.Cast< datacolumn > ().Select(x => x.ColumnName).ToArray();
//or in LINQ
string[] columnNames = (from dc in dt.Columns.Cast< datacolumn >() select dc.ColumnName).ToArray();

Compiled By: Rajesh Rolen

Read More

LINQ over Datatable

using below code we can use LINQ to query Datatables:

var res = from p in dt.AsEnumerable()
 where p.Field< string >("YourFieldName") == "Value" || p.Field < string > ("YourFieldName") == "Value"
 select p;

Compiled By: Rajesh Rolen

Read More

The query results cannot be enumerated more than once

While doing LINQ you will face this error to resolve it add a ToList() at end of your LINQ query. This way, the results are retrieved from the server, after which you can do with them whatever you want (since they are now loaded in-memory).

Compiled By: Rajesh Rolen

Read More

Fastest way to extract number from string

To extract numeric values from string you can use below function:

static string ExtractNumbers(string expr)
        {
            return string.Join(null, System.Text.RegularExpressions.Regex.Split(expr, "[^\\d]"));
        }

Compiled By: Rajesh Rolen

Read More