Digging Technology...

Schedule daily backup for sql server 2005 database using sql agent

2009-04-28T01:50:00.004-04:00

Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.

Open sql server 2005 Management Studio and connect a server
Expand the tree (left navigation) ”Management->Maintenance Plans”
Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following

6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.

7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.
8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish step.
12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.
13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).

Sql user defined function to replace HTML text without disturbing HTML elements

2011-05-19T01:41:00.008-04:00

You may find many articles on how to strip HTML in SQL. But we rarely find articles on doing replace functionality without disturbing the HTML tags in SQL. This is a very useful requirement where we store content in HTML format in the database.

When I did Google search on this I found some articles on how to strip HTML from SQL and one of those articles inspired me to write a user defined function which does replace within the text and preserves the HTML as it is.

The below is the user defined function written by me as inspired from the article

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

CREATE FUNCTION [dbo].[udf_ReplaceHTMLContent]
(
@HTMLText VARCHAR(MAX),
@Find VARCHAR(MAX),
@Replace VARCHAR(MAX)
)

RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
DECLARE @UpdatedHTMLText VARCHAR(MAX)

SET @UpdatedHTMLText = ''
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1


WHILE @Start > 0
AND @End > 0
AND @Length > 0

BEGIN

SET @UpdatedHTMLText = @UpdatedHTMLText + Replace(SUBSTRING(@HTMLText, 0, @Start), @Find, @Replace)

SET @UpdatedHTMLText = @UpdatedHTMLText + SUBSTRING(@HTMLText, @Start,@Length)

SET @HTMLText = STUFF(@HTMLText,1, @Start + @Length - 1,'')

SET @Start = CHARINDEX('<',@HTMLText)

SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

END


if @UpdatedHTMLText = ''

SET @UpdatedHTMLText = @UpdatedHTMLText + Replace(@HTMLText, @Find, @Replace)


RETURN LTRIM(RTRIM(@UpdatedHTMLText))

END

Jquery language translation plugin

2010-12-01T19:19:00.008-05:00

Many of us know that Google is providing an online tool to translate from one language to another language. Of course this translation may not be accurate but the percentage of accuracy varies from language to language based on the language structure. Basically it translates word by word. This tool is really helpful to understand statements in different languages.

But if we have a our own website which is completely in English and there are users which don't know English and they want to know about our site then it is a problem. In this case they may not know that there is a tool which translates text in English to their native language or they don't have time to translate each and every page or they may not be interested to translate at all. Then they may simply skip reading and ignore your site.

To avoid these kind of risks Google also provided us translator API which lets us develop our website to translate from one language to another language. To know more about Google translator please go through the link http://code.google.com/apis/language/translate/v1/getting_started.html. It is very easy to integrate with our website by following this link.

But the major problem with the Google translate API is its limitation with the length of the text we pass to translator. It does not translate if we pass text with more than 5000 characters. It is only helpful where we need to translate a portion of our web page and that too with less than 5000 characters. If we have to translate the complete web page then we need to write complex logic to split the html code into 5000 character sets and translate thru API and then merge the results. This is really paining.

To solve these problems we have Jquery plugin for translator. You can download this at http://jquery-translate.googlecode.com/files/jquery.translate-1.3.9.min.js. It internally uses Google translator API and applies the above discussed logic and gives us the complete translated html. This takes the complex part and leave us a simplest part to translate complete web page.

The below code will do complete translation of your web page from source language to destination language

$('body').translate( srcLang, destLang );

Eg: $('body').translate( "en", "es"); //translates text from English to Spanish

But to get it working you also need to make sure you have Jquery along with this plugin.

You can also try playing with this plugin and see how it works. Please take a look at http://jsbin.com/emufo/edit and make any changes you want and preview the behavior by clicking Preview link on top left corner.

SQL Server 2008 - MERGE statement

2010-12-04T19:10:00.008-05:00

SQL server 2008 has introduced a new feature to merge two tables. It lets you write a single SQL statement to insert, update and delete the records in the target table based on some conditions. It is very useful where you need to sync two tables. It avoids writing separate statements and logic for insert, update and delete.

It's syntax is very simple and straight forward:


MERGE <target_table> [AS table_alias]

USING <table_source> [AS table_alias]

ON <search_condition>

[WHEN MATCHED [AND clause_search_condition]

THEN <merge_matched> ]

[WHEN NOT MATCHED [BY TARGET] [AND clause_search_condition]

THEN <merge_not_matched> ]

[WHEN NOT MATCHED BY SOURCE [AND clause_search_condition]

THEN <merge_ matched> ];

Let me write an example and explain how MERGE works.

Let's say we have two tables named Books_Source and Books_Target which need to be synced periodically. And at one instance

The table Books_Source contains:


BookID          BookName                         Quantity

1              "Learn ASP.Net"                      3

3              "SQL tips"                           5

4          "Complete Reference of JAVA"             2

The table Books_Target contains:


BookID     BookName                      Quantity

1         "Learn ASP.Net"                   2

2          "Learn XML"                      4

4      "Complete Reference of JAVA"         4

To merge the above tables our MERGE statement goes like this:


MERGE Books_Target AS t

USING Books_Source AS s

ON t.BookID = s.BookID

WHEN MATCHED AND s.Quantity != t.Qunatity

THEN UPDATE SET t.Quantity = s.Quantity

WHEN NOT MATCHED BY TARGET

THEN INSERT(BookID, BookName, Quantity) VALUES(s.BookID, s.BookName, s.Quantity)

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

As you can see in the above code, insert, update and delete actions happen in a single MERGE statement. It lets us add our own conditions to merge conflicts and our own merge actions to take place.
In this example we have written update statement where the records exist in both tables but the column Quantity is different. In this case I want to update only when quantity gets changed in Order_Source table.
Similarly we have written insert statement where the records exist in source table but not in target table. Here we have not added any additional condition because we wanted to insert all those don't exist in target table.
In the same way we have written DELETE statement where the records exist in target table but not in the source table. In this case we don't want the records those are not in source table.
MERGE statement is more efficient than separate statements for insert, update and delete operation to merge tables

The final result after the execution of above statement is:


BookID     BookName                      Quantity
1         "Learn ASP.Net"                   3
3              "SQL tips"                   5
4      "Complete Reference of JAVA"         2

Online tool Url Decoder / Encoder

2010-10-13T02:23:00.004-04:00

I have come across a need for url decoding as I got a link which was url encoded. And I have found a useful online tool which lets me enter a url encoded text and get the url decoded text. This also has functionality to url encode the given text. You may url encode as many times you want, but you need to decode the encoded text as number of times it was encoded.

I have found this as very useful for my purpose. And this works completely on client side. Please take a look at it.

http://meyerweb.com/eric/tools/dencoder/

Changing cookie expiration time

2010-07-09T05:21:00.010-04:00

Changing cookie expiration time is not straight forward. It is not as easy as setting the http cookie's property Expires to required value as shown below.

HttpContxt.Response.Cookies["UserID"].Expires = DateTime.Now.AddMinutes(20);

I was using the above line of code in one of my projects as I wanted to make sure the cookie's expiration time gets updated for every user action. I added that line of code in the method where I check for authentication in each page request. But I was wondering with it's strange behavior as it is crashing the web page to load. I did spend some time on fixing it but got no luck.

After a while I got the solution which is explained in an MSDN article http://msdn.microsoft.com/en-us/library/ms178194.aspx. As explained the article, we must recreate the cookie with value and expiration time as we normally do when adding a cookie. So changing cookie is not at all different from creating a cookie in the browser. Finally I changed my code to recreate the cookie where updating cookie expiration time is needed. And it is perfectly working fine.

The correct code it worked was:

HttpContxt.Response.Cookies["UserID"].Value= UserID;
HttpContxt.Response.Cookies["UserID"].Expires = DateTime.Now.AddMinutes(20);

Audio recorder in website

2010-06-01T20:57:00.011-04:00

One of our clients had a requirement having an audio recorder to be integrated with his website. He wanted record and playback features. This was challenging job to me and I did lot of research in this and finaly found one third party tool which serves exactly. The tool was FLV Audio Recorder developed by AVChat Software. You can get more details in it's website http://flvar.com/.

But this needs one the below media servers. Media servers are same as conventional web servers but they only deal with storing and streaming of videos, audios and images. Media servers use a different protocal called RTMP to accept the video/audio requests same as HTTP is used to accept various kinds of requests.

Red5
FMIS
Wowza

The 1st media server listed above, Red5 is available for free and rest are expensive. I was interested to use Red5 as it was freely available.

To integrate the audio recorder in website I did the following:

I got the 30-day trial version of FLV audio recorder. We need to request for trial version by filling a small form and they will email us with the download details of trial product. This will include a license key which needs to be entered in the product.
I unzipped the archive and placed in some folder. And I created a virtual directory in IIS pointing to this folder. So I have verified all the files in audio recorder are accessed by webserver.
I installed the Red5 media server as explained in the post http://cherupally.blogspot.com/2010/06/installing-red5-on-windows.html.
I followed the installation instructions provided in http://flvar.com/documentation.
I entered my license key in audiorecorder.properties file as instructed.
I changed the avc_settings.php file to point to my red5 server.
That's it. I am done with the integrating stuff. When I opened the audiorecorder-api.html file in browser I have noticed the audio recorder rendered in flash and functioning well.
After completing all steps you may not get the audio recorder working until you reboot the machine. I had to reboot my machine to see the recorder working.

Sitefinity performance factor - script manager

2010-10-05T06:09:00.004-04:00

If you are using sitefinity to build any website then while adding sitefinity inbuilt controls such as menu you will be forced to write Script Manager in the template (master file). If you use script manager anywhere in the website it will load lot of unwanted javascript files along with the target page. It is better we don’t write script manager in any website unless we are building a complex website which needs ajax framework and other javascript libraries. I recommend you manually add menu and menu items in code rather than using sitefinity navigation controls and for ajax calls please use jquery to make server requests. Not having script manager will reduce the amount of javascript resources to a siginificant number.

And on our current project waypoint, we were not using any of the sitefinity inbuilt controls and not ajax framework too. But somehow we were having script manager written in the master file. And due to this it was loading around 350 KB javascript files which were never used by the site. And after I realised, I have removed the script manager tag and noticed a big surprise. The site is perfectly working without any issues and the overall page size was reduced from 500KB to 150KB (Avoiding 350KB for unwanted javascript files).
So I would strongly recommend you to avoid writing Script Manager in your websites and find the alternate solution for that.

C# 4.0 new features: Named and Optional arguments

2009-11-05T23:50:00.009-05:00

Microsoft has introduced few new features in C# 4.0. I would like to discuss the below listed new features in this post.

1. Optional arguments

2. Named arguments

Optional arguments: This feature allows you to omit arguments when calling methods. This is done by defining a method with assigning default values to parameters. For better understanding, let's take a look at the below example.

We define a method called "SomeMethod" by proving default values to two of its parameters as shown.

public void SomeMethod(int a, int b = 50, int c = 100);

Now this method can be called in different ways as shown below.

SomeMethod(10, 30, 50); // This is a normal call as 3 arguments were passed

SomeMethod(10, 30); // This call is omitting parameter "c". This call is equalant to SomeMethod(10, 30, 100)

SomeMethod(10); // This call is omitting both "b" and "c". This call is equalant to SomeMethod(10, 50, 100)

As you see, we can omit any number of consecutive parameters from right to left. In the above examples we omitted parameter "c" alone and parameters "b" and "c" together. Do you see a way to omit the parameter "b" in the above example? If you call the method "SomeMethod" by passing one argument as shown below, the compiler assumes that the argument passed was for the first parameter i.e, "a". So we need a way to tell the compiler that this argument was passed to a particular parameter, in this case it is "b". This requirement was fulfilled by c# 4.0's another new feature called "Named Arguments". Let's take a closer look at this feature.

Named Arguments: This feature allows you to pass the arguments by the corresponding parameter names.

For example you can pass the arguments by name as shown below.

SomeMethod(a:10, b:30, c:50);

By this feature, we don't need to pass the arguments in the order of parameters defined in the method. We can rewrite the above method call as shown below.

SomeMethod(c:50, b:30, a:10);

And you can solve the above discussed problem (omitting the middle parameter "b") as shown below.

SomeMethod(a:10, c:50); // This call Omits the parameter "b".

Optional and Named arguments features can also be applied to constructors. This feature is mainly useful where you have methods that have many number of parameters. Because it is very difficult to call a method by passing the parameters in same order as the method was defined. There are many possibilities to make mistakes in the order. So, having named parameters and the ability to pass parameters in any order by explicitly referring the parameter names will make our life easier.

Installing Red5 on windows

2010-06-01T23:16:00.005-04:00

Installing Red5 is easy as explained below.

Before installing Red5 we need to make sure java is installed on the target machine. Because Red5 needs to java.
If java is not installed then download the latest version of java and install it.
Make sure the JAVA_HOME environment variable is set to the java installed root directory.
To set the environment variable, right click "My Computer" in start menu/desktop and go to properties. Go to "Advanced" tab and there you will see a button named "environment variables" at bottom. Clicking it will open a dialog which will let you add your own variable. Click "New" button in the top section i.e., User variables and int the popup eneter JAVA_HOME for variable name and root directory of the java for variabl value as shown below
Now you are ready to install the Red5. Download the latest version of Red5 from http://code.google.com/p/red5/ and install it.
During the installation you may be asked to enter your ip address and port number (for version Red5 0.9.0 Final) to run the http protocol for Red5. You can enter your ip address or the dns name. But you must remember this as this is the one you need to use always. If you enter ip address during installation and use dns name in application then it will not work. You should use the one what you entered during installation. I don't know the reason but I experienced this. But if you are not asked to enter IP address (for version Red5 0.8.0 Final) then there will be no issues with the dns name to access the server. In this case the default port number is 8080.
After Red5 installation is complete then you need to start the service.
You can verify whether the Red5 is running and working fine by browsing to http://ip_address:port_number in any browser. The ip_address and port_numbers should be same as the ones entered during installation. Or the dns name and default port number 8080 will work work in the second case explained in the above step. If the server is working fine then you will be able to the server's home page which will talk about some demos, etc.

Bubbling up events from user control to parent page/control

2010-02-12T02:06:00.010-05:00

It is a good practice to use user controls when same controls are repeated in many pages. But there are some cases where the user control has some controls which generate events and those need to be handled by the containing page/user control. For eample there is a button control in the user control and you want to do something in parent page/control when the button in user control is clicked. In this case

1.You need to define an event handler in user conrol as shown below.


public partial class UC_Pagination : System.Web.UI.UserControl
{
...
...

public event EventHandler PageIndexChanged;
...
...
...
}

2. You need to call the event explicitly in the user control's implementation of the event handler of button click as shown here


public partial class UC_Pagination : System.Web.UI.UserControl
{
...
...
    protected void Button1_Click(object sender, EventArgs e)
    {
        ...
        ...
        PageIndexChanged(sender, e);
        ...
        ...
    }
...
...
...
}

3. Implement the event handler bubbled up by user control in the parent page/control in its own way as usual (shown below).


public partial class Coaches : System.Web.UI.Page
{
...
...
    protected void ucPaginationPageIndexChanged(object sender, EventArgs e)
    {
        ...
        ...
        ...
    }

...
...
}

How to implement paging with LINQ

2010-02-12T00:25:00.011-05:00

Before going into how paging is implemented with LINQ, Let's discuss the need for implementing paging.

With large amounts of data, it is not a good practice to pull all records from database when you are showing a fraction of them in one page. It is always recommended to use data on demand approach. When you want to show first 20 records out of the search results then you must get the first 20 records from database and discard the rest. Similarly when you want to show next 20 records of the search results then you need to get the next 20 records from database and discard the rest. This is nothing but called paging.

LINQ has made the paging solution very simple as shown below example.


public List<Client> 
GetAllClients(bool? isActive, int pageNumber, int pageSize, out int totalPages)
{
//Actual query which returns large data
var query = dataContext.Clients.Where(p => isActive == null || p.IsActive == isActive);

//Calculating total number of pages by taking ceiling number of the fractional value
totalPages = (int)Math.Ceiling((decimal)query.Count() / (decimal)pageSize);

//Paging logic goes here
return query.Skip((pageNumber - 1)*pageSize).Take(pageSize).ToList();
}

The parameters which play major role in paging are page number and page size. The page number is to identify the page of which the records to be returned. And the page size to identify the number of records to be returned. And there is another out parameter totalPages which is used to hold the total number of pages available within the data returned. This is needed to show the number of pages to the user and also useful in the logic which enables/disables page navigation.

Links for 2010-02-23 [del.icio.us]

2010-02-24T00:00:00-08:00

How to implement paging with LINQ
This is a nice post which explains custom paging with c# linq.

How to get Sql server 2008 intellisense working for schema changes

2009-12-19T08:36:00.011-05:00

The new feature "intellisense" added in sql server 2008 is making my life easier. And I am enjoying this feature a lot. But I noticed many times that when we make any changes to DB schema like creating / modifying database objects (tables, sprocs, ...) we don't notice the schema changes in intellisense in the same session. This made me too bad and many times I cursed Microsoft for this inclomplete feature. But after making some research on this, I have found the solution to get intellisense for new schema changes in current session.

Actually what happens when you connect to sql server 2008 using sql server management studio is, it queries for the current database schema and saves it somewhere. And It uses this schema information for showing the intellisense when the user is writing queries. But when we create/update the sql objects (sprocs, tables, views, ...) these won't be updated and the intellisense is pulled from old schema. That's why we don't notice the schema changes in intellisense unless we explicitly refresh the cache. In order to get new chema changes we must clear the intellisense cache.

To clear the intellisense cache we need to click Edit -> Intellisense -> RefreshLocalCache in sql server 2008 management studio as shown in below screen.

How to create temporary table in sql server 2005?

2009-05-06T07:07:00.001-04:00

The syntax to create a temporary table is exactly same as the syntax to create a normal table. But the difference is the table name is prefixed with '#' (pound). By looking at the prefix '#' the sql server understands that it is a temporary table. The following code demonstrates how to create a temporary variable.

CREATE TABLE #Customer
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)

Important points about temporary tables:

It is almost similar to normal table with very few exceptions.
The scope of the temp table is the current connection session. It can be accessed from any where in same connection session.
This table is automatically dropped when the connection session is closed.
Different connection sessions creating temporary table with same name will create different copies of the temporary table with the same name. Actually internally the table name is appended with a unique number to support many connection sessions creating tables with same name. You can notice how the table name is uniquely maintained by sql server by running this query. select * from information_schema.tables where table_name like '%customer%'
foreign key relations cannot be applied to temp tables.
Optionally you may drop the table at the end of it's use. It is a good practice to drop any temporary table after use. drop table #Customer

When you create a temporary table it will be created in tempdb. At the time of table creation the tempdb is locked and hence there is some overhead involved using this. If there are less than 100 records then using temporary tables is not recommended. In such cases you can use table variables which are stored in memory rather than in database(on disk). To learn more about table variables please read this
http://cherupally.blogspot.com/2009/05/table-variables-in-sql-server-2005.html

table variables in sql server 2005

2009-05-06T08:48:00.001-04:00

Table variable is almost similar to the temporary table in sql server 2005. Table variables are replacement to temporary tables. The following syntax shows how to define a table variable.

declare @Customer table
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)

You can notice the syntax to define a table variable is similar to the syntax to normal table definition. declare keyword is used in place of create keyword. And table name is prefixed with '@' as all tsql variables do.

Table variables are stored in memory rather than in database.
Querying table variables is very fast as there are no disk reads needed.
The scope the table variables is same as other tsql variables. i.e, within statements batch or sproc
Table variables cannot be dropped as they are automatically disappeared when they reach out scope
As explained above all the data in table variables is stored in server's memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.
If the number of records is more than 100 then it is recommended to use temporary tables. To learn more about temporary tables please readi this blog post http://cherupally.blogspot.com/2009/05/how-to-create-temporary-table-in-sql.html

Implementing Custom Paging, Sorting and Dynamic query in sql

2009-05-04T07:13:00.001-04:00

This post explains and demonstrates how to implement following things

Custom Paging: Returning a set of records for the given page number and page size
Dynamic query: Querying based on the where string passed thru parameter
Dynamic sorting: Sorting based on the sort expression passed thru parameter
SP_ExecuteSql: Executing sql statements included within parameters
Common Table Expression: Similar to table variable
function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int

set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end

Occasionally Connected Systems Sync Framework

2009-06-16T03:42:00.005-04:00

Every one of us must have realized that there should be a system which allows us to work offline and online. Nowadays most of the people started using laptops and working remotely. So we got a need of a framework which lets the developers build applications to sync the offline changes with the server.

Fortunately Microsoft has released this kind of framework which is called Microsoft Sync Framework. This enables the developers to build applications with the ability to sync the files in file system, table records in the database between two machines.

For more details on microsoft sync framework please read the following.

Problems in configuring sql server for session mode sql server

2009-08-28T00:58:00.007-04:00

Before going to details let me explain my experience on this. I was working on a project which pushed us to use bulky data in session. So we thought that it wouldn't be reliable to use the session in "InProc" mode as the asp.net worker process consumes lot of RAM and leads to crash the service. To avoid this we decided to use the session state in sql server mode. Telling the asp.net application to use sql server for session state is easy as shown below.

But to make the above to work, we need to setup the sql server which is mentioned in connection string above. To do this, I did the following steps mentioned in some article found in Google search.

Find the file 'aspnet_regsql.exe' in the location 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
Run the aspnet_regsql.exe.
Follow the wizard with all default values till the wizard finishes.
Verify that a new database is created in the sql server.

It all looked perfect for me and when I tested my application I got an error saying "Cannot open database requested in login 'ASPState'. The asp.net framework was look for the database 'ASPState' but when I looked at the sql server I have noticed that the setup has created 'aspnetdb' instead of 'ASPState'. Then I have changed the config to use the database 'aspnetdb' as show below.

This time I got a different exception.
"Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above."

To solve these problems I did a long research and found a way.

Open command prompt
Change the current directory to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
Run aspnet_regsql.exe -S SqlServer -E -ssadd -sstype p
You will notice the database 'ASPState' created in the sql server give
That's it. Your application is ready to use the sql server to store the session state.

Easy way to strip time part in sql date time

2009-09-25T02:53:00.005-04:00

Most of the times we wouldn't want the time part of the dates to be stored or retrieved when dealing with only days. But the data type "datetime" in sql always includes the time even though you don't want it. There is no other data type in sql to store or retrieve the date without time. In this case we need to strip the time part from the date and store the remaining in database. To do this favor, I have come up with the below sql code which strips the time part from the date time.

declare @date datetime;
set @date = getdate();
select cast(convert(varchar, @date, 101) as datetime) --Strips time element from the date time

The above code will work like this:
If you have the date time 25/09/2009 12:36:40:654 then this will be converted as 25/09/2009 00:00:00:000

How to create a windows service in dot net framework

2009-09-10T01:23:00.015-04:00

Windows service is an application which always runs in background. We can view all the installed windows services in ControlPanel/Administrativetools/Services. Sql server, Oracle, ... are the good examples of windows service.

Using visual studio it is very easy to create windows service. Please follow the below steps to create a windows service.

Open visual studio and create a new project of template "Windows Service" as shown below
After you click OK in the above step you will see a new solution named "WindowsService1" created as shown below.
Right click on the above screen and click "Add Installer". This will create an installer component which helps us in installing this service.
You can notice a new component "ProjectInstaller.cs" added and opened as shown below. It has 2 controls "ServiceProcessInstaller1" and "ServiceInstaller1".
You can set whether this service is manual, automatic, or disabled by changing the StartType property of "ServiceInstaller1" (Right click -> Properties).
Change the "ServiceProcessInstaller1"'s property "Account" to "LocalSystem"
Right click on Service1.cs in solution explorer and click "View Code"
In code you will see the service event handlers "OnStart", "OnStop".
Write code that initiates a thread in OnStart event handler.
Write code that stops execution of the thread in OnStop event handler.
Please see below for sample code
Build the solution. You have the windows service built ready.
But until we install this service we cannot see it is running or not.
To install this service you need to open the Visual Studio 2005/2008 Command prompt in Start->Visual Studio 2005/2008->Visual Studio Tools
Run the command : InstallUtil.exe Windows_Service_EXE_Full_Path
Here Windows_Service_EXE_Full_Path is the exe generated for the WindowsService1 project. Let's say the full path of the exe is C:/WindowsService1/Bin/Debug/WindowsService1.exe then our command to install it would be like this.
InstallUtil.exe C:/WindowsService1/Bin/Debug/WindowsService1.exe
If the above command is successful then you will notice a new service named "Service1" is shown in Control Panel->Administrative Tools/Services
To uninstall a service, InstallUtil.exe -u C:/WindowsService1/Bin/Debug/WindowsService1.exe
To make the life easier to install/uninstall windows services we can create a setup project which will let people easily install/uninstall with a wizard Please read this link. http://cherupally.blogspot.com/2009/09/how-to-create-setup-project-to-install.html

How to create a setup project to install windows services

2009-09-10T00:34:00.013-04:00

This post assumes that you have knowledge on windows services and how to create a windows service using visual studio. People who don't know how to create windows service please learn here.

Please follow below steps to create a setup project
1. Create a visual studio solution with a project of type Windows Service as explained in http://cherupally.blogspot.com/2009/09/how-to-create-windows-service-in-dot.html

2. Add a new project of type "Setup Project" to the solution as shown below

2. Once you click on OK button in the above step, you will see the screen below. There you notice a new project named "Setup1" is added.

3. Now right click on Setup1 project in the solution and click "Add Project Output" as shown below.

4. In "Add Project Output Group" dialog box shown below, select the windows service project in the drop down and click OK.

5. The above 4 steps are only to copy all the assemblies to specifies installation folder. Setup will not install the services in the system. To tell the setup to install services we must add custom actions. To open the custom actions window right click "Setup1" project in solution explorer and click View->Custom Actions as shown below.

6. You will see the Custom Actions tab opened like this.

7. Right click on "Custom Actions" and click "Add Custom Action".

8. That will open a dialog box which lets you choose the items from "Application Folder". Select "Primary Output From WindowsService1" in "ApplicationFolder" and click OK.

9. The above step will add this custom action to all sub folders "Install", "Commit", "Rollback", "Uninstall". Finally you will see the screen like this.

10. Build the "Setup1" project. Now you are ready to use this setup project to install/uninstall windows services. Take the setup build from WindowsService1\Setup1\Debug and use it to install/uninstall when needed.

How to find an element in c# Generic List

2009-09-22T05:07:00.009-04:00

The following sample code demonstrates you how to find a matching element in the object list.

Let's say we have an object called Person with public properties PersonID, Name, Age, Gender as defined below.

using System.Collections.Generic.List;

public class Person
{
public int PersonID {get; set;}
public string Name {get; set;}
public int Age {get; set;}
public bool Gender {get; set;}
}

class program
{
public static void Main(string[] args)
{
// Let's say we have a method defined as dbAccess.GetPersons() to
//return all the records from Person table and populates the
//list object "persons" as shown below.
List persons = dbAccess.GetPersons();

int personIDToFind = 1234;
//Find person in persons list by PersonID using predicate
//"p=>p.PersonID == personIDToFind"
Person personByFindOperation = persons.Find( p=>p.PersonID == personIDToFind );

string personName = "Kiran"
//Find person in persons list by Name using predicate
//"p=>p.Name == personName"
Person personByFindOperation2 = persons.Find( p=>p.Name == personName );

}
}

Note: The Find method of List returns the first occurrence of the exact match within the entire System.Collections.Generic.List.

Installing IIS 7.0 on Windows Server 2008

2009-11-08T01:45:00.005-05:00

Let me explain my experience on installing IIS 7.0 on windows server 2008. I had a tough time to find a way to install IIS 7.0 on windows server 2008. I thought that it would be same as installing IIS 6.0 on windows server 2003 by going to "control panel" -> "Add or Remove Programs" -> "Add/Remove Windows Components" and install IIS. But I couldn't find this in windows server 2008. After doing a long research I found a link which helped me.

I followed the below steps to install IIS 7.0 on windows server 2008.

Go to "Start" -> "Administrative tools" -> "Service Manager".
Right click the node "Roles" in the "Service Manager" window and click on "Add Roles".
Follow the wizard.
Select web server(IIS) in 2nd step of the wizard.
You might get the following popup immediately. If you get this please click "Add Required Role Services".
You follow the wizard until you get the below step.
Follow the wizard until the final step.
That's it. You are done with installing IIS 7.0 on windows server 2008.
If you face any problems please follow the below link. It has detailed step by step guidelines to install the IIS 7.0 on windows server 2008. http://learn.iis.net/page.aspx/29/installing-iis-70-on-windows-server-2008/

Links for 2009-11-06 [del.icio.us]

2009-11-07T00:00:00-08:00

C# 4.0 new features: Named and Optional arguments
This post explains the usage of c# 4.0's new feature "named and optional arguments". This feature is mostly useful when we have methods defined with large number of parameters. It is hard to remember the list of parameters in order. So by using named arguments we can pass the arguments in any order. And also many times it is possible that, we don't want to pass all arguments but use default values. This requirement is replaced by "optional parameters" feature.

Links for 2009-10-20 [del.icio.us]

2009-10-21T00:00:00-07:00

Use of "for" attribute of label control in html
This post explains the actual use of "for" attribute of label control in html language

How to post html code in blogspot / blogger

2009-05-04T04:47:00.002-04:00

I had problem when posting an article on blogspot. I was trying to show some sample html/xml code in that article. But I faced problems when I wrote the core html code. The problem was that the html code was rendering as html controls and breaking the post content. I tried many times and got frustration. Finally I searched in google for this problem and I got the below useful link.

http://www.simplebits.com/cgi-bin/simplecode.pl

If you follow the above link you will notice 2 textareas in which the first one is for input the html code and second one is for output of the changed html code which fits into the blogspot post and renders as expected.

Whoever wants to post the html code in blogger, please go to the link and paste your html code in first text box control and click the process button. Then you will notice the second textbox updated with the processed html code. Then copy the complete code from second text box and paste it in your blogspot post. It works not only in blogspot but also in all blogs which don't allow direct html code in posts.

Use of "for" attribute of html label

2009-10-21T01:58:00.006-04:00

I have noticed many of the html programmers that, they use an attribute called "for" when writing a label tag. And the attribute value is exactly same as the corresponding html input control such as text box, check box, drop down,... But I never tried to know the actual use of this attribute.

Very recently, I have searched in Google to know the use of it. Now I have come to know that, the "for" attribute of label binds the label with the corresponding control. Binding in the sense, when the user clicks on the label it toggles the corresponding control (control id passed as value for "for" attribute). This way the "for" attribute is used for the label. Now onwards I am going to make a good practice of using "for" attribute of the label control.

Let's see an example of the usage of the "for" attribute of label control


<label for="FirstName">First Name:</label>

<input id="FirstName" type="text">

The above code is rendered as shown below. You can test this behavior by clicking on the label "First Name:". You can notice the text box focused.

First Name:

Links for 2009-09-22 [del.icio.us]

2009-09-23T00:00:00-07:00

How to find an element in c# Generic List

Links for 2009-09-18 [del.icio.us]

2009-09-19T00:00:00-07:00

How to create a windows service in dot net framework
Explains step by step process to create a windows service in visual studio.
How to create a setup project to install windows services
This demonstrates how to create a setup project to easily install windows services and other console and winform applications
Problems in configuring sql server for session mode sql server
This discusses the problems faced in setting up sql server to work as storage media to store session data.
Occasionally Connected Systems Sync Framework
This give you a brief introduction of Microsoft Sync framework.
Web page loading twice problem
This discusses and solves the problem with web page loading twice
Code to Serialize Object in C# .Net
This explains how to write code to serialize any object in c# language
How to access Remote sql server’s database table
This teaches you to learn how to make sql queries from one sql server's pane to other sql server
How to invoke a process/application from parent application using C#
Explains how to invoke a third party application from the current application using c# language
Learn How HyperText Tranfer Protocol works
This explains how HTTP protocol works in detail
Impose maxlength on textarea controls to avoid "String or binary data would be truncated" error
This is useful to put length constraint on html text area controls or multiline text box controls
Return multiple result sets using IMultipleResults in linq c#
This demonstrates how to query multiple result sets from sproc and populate the objects using LINQ IMultipleResult
Schedule daily backup for sql server 2005 datbase using sql agent
This post very well explains and demonstrates how to schedule a database backup daily or weekly using sql server agent or maintenance plan wizard
How to perform search within the stored procedures
This is helpful to query for the key words used within the sproc's code