Get only Date part for SQL Server DateTime value

There may be various scenarios wherein you need only Date part of an SQL Server DateTime value, rather than full DateTime value.

This is very much a requirement in case of comparing dates (only date and no datetime).

SQL Server 2008 offers a new datatype DATE which holds only Date. 

So you can simply use Convert(Date, GetDate()) to convert your full DateTime into Date. Its as simple as that. 

But if you are using a version prior to SQL Server 2008, you need to do a workaround. There are lot many ways to achieve that. Following is one of them which is being used too frequently.

Say your DateTime value is "2012-04-20 21:45:00.938"

First convert date into a varchar in format such as (dd/mm/yyyy or mm/dd/yyyy) depending upon the regional settings of the SQL Server's host environment.

SELECT CONVERT(varchar(10), GETDATE(), 101) -- 101 stands for mm/dd/yyyy (mostly in case of US)

SELECT CONVERT(varchar(10), GETDATE(), 103) -- 103 stands for dd/mm/yyyy

So, it will result into - "04/20/2012", "20/04/2012" respectively

But this is a varchar type, so you need to convert it back to DateTime type to make it able to be used in Date time operations.

Use either of this depending upon the settings in SQL server's host environment.

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101) )

or

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 103) )

This will now result to "2012-04-20 00:00:00.000"

Membership in ASP.NET MVC 4 (System.Web.Providers)

ASP.NET MVC 4 came with many changes from MVC 3, and one of them was the way membership was implemented.
Membership and profiles tables in MVC 4 are reduced to a few, and they appear to be quite clean. Names like "Memberships, Profiles, Applications, Roles, etc" are for quick to identify their purpose.
Also their structure is quite simplified.

ASP.NET MVC 4 membership uses Entity Framework as its ORM.

When you create a new MVC 4 website project (as Internet application), it gets created with controller, models, and views for Logon, Registration, and all other membership functionality. By default, connectionstring is set to a local SQL Express database (as it used to be in earlier versions).

If you create an empty MVC 4 website project, you will not be able to see any of these (as you only opted it!), but still you can use MVC 4's membership features by using "System.Web.Providers" library.
Make sure, its added as a reference in your solution. (it is there in most cases).
But If not, you can add it using NuGet package manager.
For that, Go to following command in Visual Studio menu.
Tools >> Library Package Manager >> Package Manager Console
This will open up command shell at the bottom. Type in following command and press enter, and this will do all necessary changes in your project and configurations

install-package "System.Web.Providers"

Once done, go to web.config, and configure a connectionstring named "DefaultConnection" to use your remote connection (instead of default SQL Express database).
But make sure your connectionstring includes "MultipleActiveResultSets=True" configuration, otherwise it will give you an error related to "Entity framework" while performing any membership operations.

Sample DataSource settings in connectionstring:
Data Source=myPC;Initial Catalog=myTestDB;Persist Security Info=True;User ID=nirman;Password=nothing*001;MultipleActiveResultSets=True;

Once done, your application is set for membership.
And as you run your MVC application for the first time, and attempts to register a new user, it automatically will create membership tables in SQL Server database. ASP.NET MVC 4 does not use Stored Procs, etc in background for Membership, as it has used EF as its ORM. So you will see only tables got created in database. Less complicated and easily manageable, isn't it!

Binding two or more types of objects (models) to single view in ASP.NET MVC

This article aims to explain how we can bind two or more different types of objects to single view in ASP.NET MVC application. (Please note, the code snippets are in razor view engine, but I know its not as such difficult for you to comprehend them in ASPX engine :))

As we know, its straight-forward bind a single type of object (or collection) to a view and we can pass it from Controller action to the View.

I mean, if your need to bind a View to an object of PersonEntity type(or its collection), you can bind, pass, and post it like below:

Binding in View: 

@model MVCTutorialApp.Models.PersonEntity

Passing it from controller action:

public ActionResult PersonEdit()

{

..................

return View("EditPerson", _person);

}

HttpPost action in your controller:

        [HttpPost]

        public ActionResult PersonEdit(PersonEntity  _person)

But sometimes we need to pass multiple different types of objects to a single view.

Say, for example, you have two different types "PersonEntity", and "ContactEntity", and you need to bind them to a single view.

There are various ways to achieve them:

But two of the most ideal approaches are Wrapper class, and System.Tuple

Approach A: Wrapper class

Create a class that can hold instance of both "PersonEntity" and "ContactEntity".

Like, 

public class PersonContactEntity

    {

        public PersonContactEntity()

        {

        }

        public PersonEntity Person { get; set; }

        public ContactEntity Contact { get; set; }

    }

Now in your corresponding action of controller -

Create an instance of the class "PersonContactEntity".

Assign "Person" and "Contact" properties with the their respective instances.

Pass instance of "PersonContactEntity" to the view. 

public ActionResult PersonContactEdit()

{

PersonContactEntity personContact = new PersonContactEntity();

..................

personContact.Person = person object;

PersonContact.Contact = contact object;

return View("EditPersonContact",  PersonContact );

}

Create your view as strongly typed view by adding following line at top:

@model MVCTutorialApp.Models.PersonContactEntity

Once done, when you write @Model followed by dot (.) the intellisense will show you "Person" and "Contact" both, and you can  now bind your controls, or use them in your view.

Example:

@Html.TextBoxFor(m => m.Person.FullName)

Also, @Model.Contact.Email

And, also corresponding HttpPost method (controller action) can also accept PersonContactEntity when a view post data to controller action:

        [HttpPost]

        public ActionResult PersonEdit(PersonContactEntity   _personContact)

Approach B: System.Tuple

A tuple is a data structure that has a specific number and sequence of elements (Ref: MSDN)

Please visit following MSDN link to know more about tuples

http://msdn.microsoft.com/en-us/library/system.tuple.aspx

Please note that, Tuple in our scenario should only be used if you do not need to do Post data from View to controller action. As tuple does not have a default constructor, it cannot be used as a parameter in HttpPost method, or you may need to find a workaround to make it possible (instead, Wrapper class (approach A) would be an easy to implement).

Coming back to using System.Tuple in our scenario:

Your controller action launching a view should be like:

public ActionResult PersonContactEdit()

{

..................

System.Tuple<PersonEntity, ContactEntity> personContactEntity = Tuple.Create(person object, contact object);

return View("EditPersonContact",   personContactEntity );

}

Create your view as strongly typed view by adding following line at top:

@model System.Tuple<LearnMVC4.Models.PersonEntity, LearnMVC4.Models.ContactEntity>

Once done, when you write @Model followed by dot (.) the intellisense will show you "Item1" and "Item2" both. Item1 corresponds to first type in tuple which is "PersonEntity", and Item2 corresponds to second type which is "ContactEntity". And you can now bind your controls, or use them in your view.

Example:

@Html.LabelFor(m => m.Item1.FullName)

Also, @Model.Item2.Email

So this is how we can pass different types of objects to single view, and can bind them.

Implement Custom Validation in ASP.NET MVC

This article aims to explain how we can implement Custom validation (server-side) in ASP.NET MVC.

System.ComponentModel.DataAnnotations namespace provides set of predefined validations which are common in almost all scenarios (like, Required, Range, Regular Expression, etc). Click here for more information.

This namespace contains "ValidationAttribute" class which is a base class for all validation attributes.
Following are the steps to be performed in order to implement a custom validation.
1. Inherit a new validation class from ValidationAttribute base class. (A new class can be created anywhere but should have System.Component.DataAnnotations referenced.)
2. Override "IsValid" method and write our own validation. (Here, we can get the value entered by user for the corresponding field/ property, so we can validate it).
3. Apply new attribute on the field/ property of a model wherever you need that custom validation to fire.

Sample Code:
In our "Person" model, we have a "BirthDate" field. We now want to validate if the Age of a person must be 18  or above.
1. A class inherited from ValidationAttribute, and overriding of IsValid method within:



[AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class BirthDateValidation : ValidationAttribute
    {
        public override bool IsValid(object value)
        {
            if (Convert.ToDateTime(value) >= DateTime.Now.AddYears(-18))
            {
                return false;
            }
            return true;
        }
    }


2. Applying "BirthDateValidation" attribute on Birthdate property.


[Display(Name="Birth Date")]
        [BirthDateValidation(ErrorMessage="Registration is only allowed for people above 18 years.")]
        public DateTime BirthDate { get; set; }


3. Last thing, as it is a server-side validation (just like, Required, Range, etc), dont forget to relaunch the view from corresponding HttpPost action in your controller, in case if ModelState.IsValid is False. (indicating, a validation failed)

[HttpPost] 

       public ActionResult PersonEdit(Person person)

        {

            if (ModelState.IsValid == false)

            {

                return View("PersonEdit",  person);

            }

            else

            {

             //Write code to define actions on success

            }            

        }

That's all we need to implement this validation:

Screenshot:

Simple client-side Validations using ASP.NET MVC

This article aims to demonstrate how we can apply simple validations at view (page) level in ASP.NET MVC.

Say, you have a "Person" model, that you pass in the view to and fro controller action.

A Person model is having many fields, one of them is "PersonName" (Label: Person Name) and you want to make it required when user inputs data in corresponding view of this model.

In order to achieve this, first decorate PersonName field in Person model with "Required" attribute.

"Required" attribute is a part of System.ComponentModel.DataAnnotations namespace. So this must be imported in your model (using System.ComponentModel.DataAnnotations)

System.ComponentModel.DataAnnotations contain various attributes that can be applied on a model field. Along with that it has all necessary validators (like, Required, Range, MaxLength, MinLength, Regular Expression, Custom validator, etc).

For our case, we should write - 

[Required(ErrorMessage="Please Enter Person Name!")]

[Display(Name="Person Name")]

public string PersonName { get; set; }

This will automatically handle required validation for Person Name field input.

Now, in the view (.aspx, or .cshtml), we need to put ValidationSummary in order to display error message in case validation fails.

@Html.ValidationSummary(false, "Please correct following errors and try again!")

The last thing is to add a condition in HttpPost method to relaunch the view with latest inputs and error messages, in case if validation(s) fail.

[HttpPost]

        public ActionResult PersonEdit(Person person)

        {

            if (ModelState.IsValid == false)

            {

                return View("PersonEdit",  person);

            }

            else

            {

             //Write code to define actions on success

            }            

        }

Now, when the view is launched with person model, it will have same inputs as well as validation errors, so that they will get displayed in ValidationSummary control in view to let users know about the errors.

NOTE: Also, remember to put @Html.ValidationSummary control before @Html.BeginForm block, otherwise it will always show you validation messages even before HttpPost.

Investigation: Values do not retain in model while posting it from view to controller action in MVC

Scenario: My controller action is passing a model to the view, and displaying the fields on my view.

There are few fields which are non-editable (like, ID, etc), and are only for display purpose, so I have placed them directly on view.

There are few fields in that model which are not required to be displayed at all on view, so I have not placed them on view.

The problem is that, when a user clicks Submit to post the view to controller action, the model does not contain their original value for those fields.

How to fix it?

Fix: When you bind a model by passing it from control action to view, you should bind each and every field of the model in view. This will retain their values during HttpPost.

So if there are some fields you want to display them as read-only, or if you do not want to display them at all, then you should still bind them within view. Its necessary if you are posting the view to a controller action (using HttpPost)

The best way is to use hidden tag, using @Html.HiddenFor helper, if you do not want to display the field.
Or use @Html.DisplayFor helper, if you want to display the field in read-only mode.

Data Binding to RadioButton in a View in MVC

This article is to provide information for how to do a data binding of a RadioButton (or group of them) in the View.

For example, you have Gender field in your model with two obvious options - Male, and Female.

What we really want to achieve is -
a. when a controller action invokes a view (with its model), it should correctly select the appropriate radio button from underlying model,
b. when a user changes the selection of radio button it should correctly update underlying model.

This is for what binding is needed.
For controls, such as TextBox, Label, etc, binding is quite straightforward using TextBoxFor, LabelFor, etc helpers.
But for controls such as RadioButton, CheckBox, etc, it's little bit different as below:

The below example is using RazorView engine.

@Html.RadioButtonFor(m => m.Gender, "Male", Model.Gender == "Male" ? new { Checked = "checked"} : null )
Male


@Html.RadioButtonFor(m => m.Gender, "Female", Model.Gender == "Female" ? new { Checked = "checked"} : null )
Female

Here, Checked is an HtmlAttribute for Checked state of RadioButton/ CheckBox
@Html.RadioButtonFor will show only RadioButton without its label, and for that purpose we have written Male, and Female after each Helpers.

Investigation: SelectedIndexChanged not firing for DropDownList or RadioButtonList

Issue: SelectedIndexChanged event not firing for a DropDownList or a RadioButtonList, CheckBoxList or any control that is inherited from "ListControl" abstract class.

Following can be the possible reasons:
1. AutoPostBack is not set to true for these controls.
Set this property to true.
2. Script execution is turned off for browser.
SelectedIndexChanged event is dependant on background JavaScript methods. So, if the Javascript is turned off for the browser then this event will not be fired.
So, in this case, either you need to enable javascript for the browser.
Or, if you absolutely cannot enable it (due to strange requirements of your users), you need to place a button besides the dropdown. You do not need to write Click event for this button, as Buttons are postback controls, so and clicking on that will invoke the postback, and that will automatically fireup all SelectedIndexChanged events which were pending since last postback.
3. If you are using Telerik's RadAjaxManager, make sure you are adding the controls properly in RadAjaxManager.
For example,
RadAjaxManager1.AjaxSettings.AddAjaxSetting("rbOptions","rbOptions",null);

Investigation: ExecuteNonQuery() always returns -1

Generally, ExecuteNonQuery() is used to return number of rows affected by INSERT/ UPDATE/ DELETE operations by the statement. (A statement can be a plain SQL Query, or it can be a Stored Procedure, etc)

If it returns 0 or more, then it indicates the number of rows affected by INSERT/ UPDATE/ DELETE. (And if any other rows are affected by underlying trigger, then it is also counted in the return value)

But in some cases, it returns -1, even if your INSERT/ UPDATE/ DELETE operation is performed successfully.

The most likely reason for this behavior is the use of "SET NOCOUNT ON" statement in SQL Statement (or stored procedure, etc).

SET NOCOUNT ON turns off row count and thus it does not count affected rows.

The easiest resolution is to remove SET NOCOUNT ON.

But, SET NOCOUNT ON is there to improve performance. If its not used, SQL Server keeps sending messages to client after executing each T-SQL statement. So its recommended to use NOCOUNT ON.

To deal with this issue, you should use @@ROWCOUNT in your stored procedure and assign it to the OUTPUT parameter.

@@ROWCOUNT also returns number of rows affected, in case of SET NOCOUNT ON

So, though your ExecuteNonQuery returns -1, you will have the count of affected rows in the OUTPUT parameter.

Development and Deployment - Troubleshooting, Investigations (Series of articles)

At times, we, developers or deployment people do come across some technical problems that eat up our hours (and days sometimes).. and when we find the resolution, or real cause of the issue, it makes us feel that such issues were not worth of the time and efforts we spent.

Here, I am making a collection of such issues/ troubleshooting notes that may help us to take extra care in order to avoid that issues raising while we develop or deploy.

There are each new post for each different issue/ troubleshooting note, and this post is an index that will keep growing as I add new post in this category.

Keep checking this place regularly.

INDEX:

ASP.NET

1. ExecuteNonQuery() always returns -1

2. SelectedIndexChanged not firing up

ASP.NET MVC

1. Values do not retain in model while posting it from view to controller action in MVC

Print Page Content to printer (or virtual print output) in ASP.NET using iFrame

Sometimes, there is a need to give user a facility to print the web page contents (or part of them) as they appear in their browser.

This article is to explain how we can achieve the same using iFrame, and javascript:

While designing your webpage, you should decide which contents should allowed to be printed, and can wrap them within DIV or any HTML container. (You can also print all contents of your page)

For this example, lets assume the DIV (with ID - "divFormContents") contains the contents you facilitate users to print.

Now, add an iFrame (with id = ifmContents, and with following style) in your webpage:

height: 0px; width: 0px; position: absolute

Write following javascript in your webpage.


function printPageContents() {
var content = document.getElementById("divFormContents");
var printContents = document.getElementById("ifmContents").contentWindow;
printContents.document.open();
printContents.document.write(content.innerHTML);
printContents.document.close();
printContents.focus();
printContents.print()
}

Finally, write a call to this javascript method (printPageContents())

That's all we need to do, and the users will now be able to print all the contents that reside within the container ("divFormContents") in our example

Show long text in Tooltip

As we all know, standard ToolTip property of ASP.NET server controls is having a limit of number of characters. Means if you have a long text of say, 10-12 lines, and some hundreds of words, the tooltip will be truncated in browser after a certain limit.

JQuery plugin is providing its own control to deal with this limit. But, in case, your project has not included JQuery libraries, you can still achieve this by writing some javascripts and attaching them in your controls.

For this, you first need to write javascript methods that will overwrite default behavior of tooltip. Technically speaking, you need to write methods for "onmouseover" and "onmouseout" respectively as follow:


function showCustomTooltip(hostControl) {
//hostControl = control where you want to override Tooltip
var tooltipText = hostControl.title;
var toolTipContainer = document.createElement('SPAN');
var textNode = document.createTextNode(tooltipText);
toolTipContainer.appendChild(textNode);
hostControl.parentNode.insertBefore(toolTipContainer, hostControl.nextSibling);
toolTipContainer.className = "customTooltipCss";
hostControl.title = "";
}
function hideCustomTooltip(hostControl) {
var controlText = hostControl.nextSibling.childNodes[0].nodeValue;
hostControl.parentNode.removeChild(hostControl.nextSibling);
hostControl.title = controlText;
}

next thing is to, attach this methods to the controls where you want to show tooltip. Say, the control name is "lblInfo"

protected void Page_Load(object sender, EventArgs e)
{
lblInfo.Attributes.Add("onmouseover", "showCustomTooltip(this)");
lblInfo.Attributes.Add("onmouseout", "hideCustomTooltip(this)");
}

Finally, as you can in the JavaScript code that I have assigned "className" property to "customTooltipCss" we need to add this class in CSS file (or whatever method you are using for accessing styles in your page.)


.customTooltipCss
{
position: absolute;
width: 400px;
margin: 1px;
padding: 2px;
background: #FFFFBB;
border: 1px solid lightgray;
}

That's all we need to do.
Please see screenshot to see how the tooltip appears while implemented using above method.

CodeProject

Auto login into Team system with your authorized credentials

If your local domain is different from the domain where Team Foundation Server (TFS) is hosted, it must be prompting you for credentials every time while accessing any resources of that TFS. Be it, accessing any project, source code, or anything in documents, reports and other folders.
This becomes annoying and time-consuming if you work with team system day in and day out.

This article is related to configure your Credentials (in your local machine) for auto login into Team Explorer. Means, once its been configured this way, you will not be prompted for Credentials (User name/ password) anymore while accessing any project or opening any document in team explorer. These settings are safe to do as they are user-specific, so it will not be automatically applied to other users on your local machine.

Step 1: Store your credentials in "Credentials Manager":

1. Open Control Panel >> User Accounts
2. Click "Manage Your Credentials". This will open up "Credentials Manager".
3. Here, you may see URL of your TFS in the list of Windows Credentials store. (This place gets updated whenever we click "Remember My Credentials" in windows login prompt)
>> If the entry for the site(s) already exist -
Click on the respective entry.
Click "Edit" link.
Enter your password of that TFS hosted domain's login.
Click "Save".
>> If entry does not exist for the site -
Click "Add a Windows Credential" link.
Enter URL of your TFS in the "Internet or network address" input box.
Enter your user name and password for TFS hosted domain.
Click "Save".
By "user of TFS hosted domain", I mean, the credentials you usually use to connect to your TFS.

Once the above settings are done, Team system will not prompt for Credentials while connecting to a project (in Visual Studio). However, if you try to open apart from anything under "Source control" (i.e. in Documents folder, etc), it will still prompt for the Credentials. Perform following changes to avoid this prompt.

Step 2: Add TFS site as a trusted site.

1. Launch Internet Explorer.
2. Go to Tools >> Internet Options >> Security Tab.
3. Select "Trusted Sites" as zone.
4. Click "Sites".
5. Add TFS URL (Complete URL)
6. Click "Close".
7. Click "Custom Level" to open "Security Settings" popup.
8. At the bottom of all options in list, there exists "User Authentication".
9. Select "Automatic logon with current user name and password".
10. Click OK, followed by Apply.

Now onwards, while opening any resource from Team Explorer, it should not prompt you for credentials.

This is not limited to TFS and Team system. You can also do the same for any of your intranet sites which is supporting Windows Authentication.

Also, take care to change your password in Credentials store, as and when you change/ reset password of your TFS hosted domain's login.

Hope this will save us a many keystrokes :)

Visual Studio .NET - Some handy shortcuts

We all know some standard keyboard shortcuts that are available in Visual Studio IDE.
Here, are some more shortcuts to use in Visual Studio IDE, which can come handy at times.

Ctrl + K + C = Comment In Code
Ctrl + K + U = Comment Out Code
Ctrl + Shift + A = Add New Item
Ctrl + Shift + B = Build Solution
Ctrl + Shift + C = Class View
Ctrl + Shift + R = Resource View
Ctrl + Shift + O = Open Project

F12 = Go to definition

Here are some more interesting:

1. Find all references
Shift + F12 = Find all references

Say, you have created a method which is called at many places within your project.
Use this shortcut key to know see all the places where this method has been referenced.

2. Paste from Copy History

Ctrl + Shift + V = Paste History
Every time you press "Ctrl + C" to copy contents in your IDE, the previously copied item(s) get stored in a buffer. You can access those contents by repeatedly pressing Ctrl + Shift + V.

Write custom events for User Controls in ASP.NET

Often we need some code to be executed in hosting page in response to some action in User control.
Say for example, your Page is having a Country dropdown, and also placed a user control having interface to allow users to add a new country in underlying data store. What you really require is to reload your Country dropdown as soon as user adds/edits a country through the user control.

This article will explain how to write custom events in user controls and to handle them in the hosting page in order to achieve this:

A. Changes to be done in Add/Edit Country User control.
- Declare an event handler in your Country user control.
public event System.EventHandler CountryAdded;
- Jump to the "Save" button code (or ItemInserted/ ItemUpdated event, if you are using ObjectDataSource).
Add following lines after completion of Save operation code.

if (this.CountryAdded != null)
{
this.CountryAdded(this, new EventArgs());
}

This is all you need to do in User control to make the control responsive to "CountryAdded" event.

B. The last step is a very straight forward - Just write event handler in your hosting page, and there you need to rebind your DropDownList.

Once done, you should now be able to see Country dropdown in your page gets reloaded as and when a Country is added/ edited through User control on that page.

SQL Query to return each date of month in each different row

Sometimes we need a resultset in SQL that should list all dates of a particular month.
For example, you want to return a resultset containing total of all expenses done against each date. But for that your expense table not necessary to contain entry of each and every date.
There may be some dates for which there is no expense, and simply that date will not be in the expense table.
In such scenarios you would always need to have one resultset (virtual table) containing list of all dates, which you can use to join (Left Join, ideally) with your expenses table by dates to get your desired result.

This is possible using CTE by performing recursive SELECT.

Following is the complete SQL Query to generate list of all dates for a particular month:



Image: Resultset after executing below SELECT Query
P.S. - There are actually 31 rows, its just due to screen truncate it showing 27 in image.


DECLARE @MonthName VARCHAR(3)
DECLARE @Year VARCHAR(4)
DECLARE @MaxDay INT

SET @MonthName = 'MAR'
SET @Year = '2012'
SET @MaxDay = 31;

WITH CTE_MonthDays AS (
SELECT 1 AS fldDay,
CONVERT(datetime, '1/' + @MonthName + '/' + @Year) AS fldDate
UNION ALL
SELECT fldDay + 1,
CONVERT(Datetime, CONVERT(varchar(2), fldDay + 1) + '/' + @MonthName + '/' + @Year) AS fldDate
FROM CTE_MonthDays
WHERE
fldDay + 1 <= @MaxDay
)

SELECT fldDate FROM CTE_MonthDays

How to debug ASP.NET Windows Service

Debugging a code at development or troubleshooting stage is a very basic need for any application.

However, debugging of .NET Windows Service is not as straight-forward as debugging a Windows Application.

Perform following steps to enable debugging of Windows Service in .NET. (Code sample in C#.Net)

1. Open Program.cs file.
2. Jump to "Main()" method in Program class. This is the entry point of Windows Service execution, so you must be having code to instantiate and run your service class in Main() method.
3. We need to tweak code of Main() method to include #if directive that executes code based on symbol (symbol means, Debug, Release, etc)
So your Main() method should have below structure after this change:


static void Main()
{

#if (!DEBUG)

Your Original code to instantiate and run Service class -- DO NOT ALTER IT

#else

Call to your service class method you wish to debug

#endif

}


4. Now run your application, and make sure you are running it in a Debug mode (and not in release mode) if you wish to debug it.
You should now be able to debug your Windows Service code.

5. Also there is no harm in keeping this code in your release to production server, because when you build a Windows Service in Release mode you will see the code you had written in #else block (in above sample) is grayed out (color of disabled text), indicating it will not be executed, rather the one written in #if block will be executed.

FIX: Add a 32-bit SQL Server 2000 as a Linked server in 64-bit SQL Server 2005

There are certain scenarios where we need to access an object (table, SP, etc) of remote server's database.
In such cases, we need to add that remote server as a Linked server in our SQL Server.

We can add a linked server by following path in Object Explorer of your SQL Server's Management Studio:

Server Objects >> Linked Servers; Right-click and add new linked server, and provide details of remote server you wish to link.

But if your remote server is a 32-bit SQL Server 2000, and your host server is 64-bit SQL Server 2005/2008, then generally you would be getting a following error while accessing any object (i.e. Table, SP, etc) of that remote server.

Query Execeution failed. Could not obtain the schema rowset "DBSCHEMA_TABLES_INFO" or similar message.

if you follow some articles over internet, they have suggested to execute some critical scripts to your remote server. This script file is of some 700KB, and performs lots of drop and create operation. This does not sound to be a good solution honestly.

The actual problem here is that, when you try to query an object of a remote sql server, it executes sp_tables_info_rowset in background. This happens without any issue if both the hosting and remote SQL server are of similar type.
But when you try to query an object of a remote sql server from a 64-bit connection, it attempts to execute sp_tables_info_rowset_64 sp, which obviously not a part of SQL Server 2000 installation.
So the simple workaround is to create this SP on remote SQL Server's master database, and this will simply a wrapper of sp_tables_info_rowset SP that already exist in remote SQL Server's master database.

Following is the script to create this SP:
Execute it in master database of remote sql server.

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
AS
DECLARE @Result int

SET @Result = 0

EXECUTE @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Once done, you should be able to query the database hosted on that remote server without any trouble.

Difference between integer Cast and Convert in C#

Technically speaking, Int64 and Long are equivalent in .NET
But if you convert one value into Int64, and cast the same value in Long the result is different.

Lets take one example:

(Long)27.5 returns 27
Whereas, Convert.ToInt64(27.5) returns 28
(Int64)27.5 returns 27

This is because, in C# (as well as in Java, and C++), the integer cast operation truncates - and so (Long)27.5 returns 27, whereas Convert has its own logic, and it rounds rather than truncating and so it returns 28.

Optimizing SQL Code by replacing IF..ELSE with CASE WHEN

Many times, we come across a need of writing different SQL SELECT queries in a stored procedure, just to seperate the WHERE clause logic depending upon if the parameter value is passed or not.

Lets consider following case -
Your stored procedure sp_GetCities has a parameter @CountryID.
Now, if the parameter is not null then you want to filter Cities based on passed CountryID.
But, if the parameter is Null then you need to return all cities irrespective of their country.

On many occasions, we write following logic in stored proc.


IF @CountryID is null // Or, IF ISNULL(@CountryID, 0) <= 0
BEGIN
SELECT ....
---No WHERE condition to filter countries-----
END
ELSE
BEGIN
SELECT ....
WHERE CountryID = @CountryID
END


There is one workaround to avoid writing multiple SELECT queries in similar situations.

Use of CASE..WHEN in SELECT statement


SELECT city_id, city_name
FROM tblCities
WHERE
1 = CASE WHEN ISNULL(@country_id, 0) <= 0 THEN 1 ELSE CASE WHEN @country_id = country_id THEN 1 ELSE 0 END END


Let us understand the WHERE clause -
If the right-hand side of the expression returns 1 then the city will be returned in resultset, otherwise it wont be.

If "@country_id" is not passed i.e. ISNULL(@country_id, 0), then obviously it will return 1. And in this case, all cities will be selected, as 1=1 is always true.
If "@country_id" is passed then if it matches with city's country_id (i.e., country_id = @country_id) then it will return 1, otherwise it will be 0.

We can use this upto any level, so we can definitely reduce number of SELECT queries in our code.

Check Authentication Mode in ASP.NET

At times you may require to check if the current authentication mode (that is, if windows or forms)

You can set the authentication mode using following your web.config file under System.Web section.

authentication mode="Windows"

To know the authentication mode within your page, you can use -
"HttpContext.User.Identity.AuthenticationType".

For Forms Authentication, it will return "Forms".
For Windows, it will return "NTLM" (that stands for NT LAN Manager)

How to validate a page using Java script

Lets consider a following scenario:

There is a web page which has many input control, and validators (required field validator, custom validator, etc).

Now, there is a link button which already has some javascript written for its "onclientclick" event.
So, generally when this button is clicked:
a. First,it will execute java scripts written in onClientClick
b. page-level validations (i.e. required field validators, etc) will be performed
c. if page-level validations are passed, server-side validations will be performed
d. if server-side validations are passed, server-side Click event will be executed.

So, even if the inputs are invalid, it will always execute, onClientClick.

But there are certain cases when you need your main code of OnClientClick javascript to be executed only if the page is valid.
In such cases, you can add a code to validate page in your onClientClick method, and to allow execution of further code only if page is valid. For that, we can use "Page_ClientValidate" and "Page_IsValid".

Write following java script in "onclientclick" event of that link button in order to achieve this -


OnClientClick = "if(typeof(Page_ClientValidate) == 'function')
{ Page_ClientValidate(); if (Page_IsValid == true) { JavaScriptFunctionForTargettedAction(); } } return false;"/>

Here JavaScriptFunctionForTargettedAction() is the javascript code/ method you have originally intended to execute in onclientclick evnet

CodeProject

SQL Query - To search for a column (by column name) in all tables/ views within a database

At times you need to know if a column of a specific name do exist in any table/ view of your SQL Server database.
In that case, we have certain options to browse throgh each table, or to generate script of all tables (in single go) and to do find operation. But its a time-consuming job.

As such, SQL Server stores information about any database object (i.e. column,index, table,SPs,etc) in system tables. So following SQL query can be very handy at times:


USE DatabaseName
select * from sys.objects where object_id in
(select object_id from sys.columns where name = @column_name)


This will return a resultset containing list of all tables which are having a column of the particular name. You can also perform wild-card search etc here.

C#.NET - Difference between Convert.ToString(), .ToString(), and (string) cast

Convert.ToString(), .ToString() and (string)
All these are used for converting an object into a string, but they differ the way they handle NULL values.

>> Convert.ToString() returns String.Empty while attempting to convert a NULL object into a string.
>> .ToString() throws an exception in such cases
>> While, (string) cast sets a string variable as "null" value, and do not throw any exception, but if you try to access any property, method it throws an exception.