Transaction Handling in C#

using System.Transactions;
Transaction on C# When handling only one Stored Procedure

using (TransactionScope transaction = new TransactionScope())
{
-- Write your code here it rolback if there is any issue in your query.

transaction.Complete();
}

Some time we need to update multiple table using multiple methods
at that time use

using (TransactionScope TranScope = new TransactionScope())
{
//Write your code here

TranScope.Complete();
}

SET vs SELECT when assigning variables

SET is the ANSI standard for variable assignment, SELECT is not.


SET can only assign one variable at a time, SELECT can make multiple assignments at once.

If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)

When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)

As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

SQL Server Join Hints

JOIN hints can be used in a query to specify the type of JOIN the Query Optimizer is to use for the execution plan. The JOIN options are:

• Loop
• Merge
• Hash

The syntax for a JOIN hint is (using an INNER JOIN as an example):

FROM table_one INNER [LOOP | MERGE | JOIN] JOIN table_two

Here's an example:

FROM header_table INNER LOOP JOIN detail_table

As you can see, the JOIN hint is between the type of JOIN (in this example, INNER) and the JOIN keyword. Only one JOIN hint can be used per JOIN in a query. In addition, JOIN hints can only be used when the ANSI JOIN syntax is used, not the older Microsoft JOIN syntax.

The syntax above is not the only option to add a JOIN hint to a query. In addition, you can also use OPTION clause. Using the OPTION clause specifies that the hint be used throughout all of the query. While multiple hints can be added to the OPTION clause, each query hint can be used only once. In addition, only one OPTION clause can be used per query.

Here's an example of using the OPTION clause:

OPTION (INNER) or OPTION (MERGE) or OPTION (HASH)

The Query Optimizer always tries to identify the fastest way to JOIN tables. The fastest JOIN method is the Loop JOIN, followed by the Merge and the Hash JOIN. While the Query Optimizer always tries to perform a Loop JOIN if possible, it is not always possible, and one of the other two types may have to be used.

Top with percent in Sql Server 2005

In Sql Server if you want Specific percent of rows from table then you can use percent keyword 

Query to select all rows   

Select * from Employee

Query to select Top 2 Rows

Select top 2 * from Employee

Query to select 40% rows of total rows

Select top 40 percent * from Employee

Verbatim String in C#

I used to wonder, if I coud somehow represent "\" as "\" instead of the escape sequence  for black slash ("\\") in string. I have precisely come across this feature today in C-sharp and I would like to share it with you all.Especially in my last articles when I used the paths I did not feel like using "\\".

Other than the regular string literals, C# supports what is called as Verbatim string literals.Verbatim string literals begin with @" and end with the matching quote. They do not have escape sequences.

string path = @"C:\Program Files\My Program"; //verbatim literal
string path2 = "C:\\Program Files\\My Program"; //regular literal

string msg = @"Hello,
                        This is a multi-line string"; //verbatim literal

string msg2 = "Hello,\nThis is multi-line string"; //regular literal

this Keyword in C#

The this keyword refers to the current instance of the class. Static member functions do not have a this pointer. The this keyword can be used to access members from within constructors, instance methods, and instance accessors.

Example

In this example, this is used to qualify the Employee class members, name and alias, which are hidden by similar names. It is also used to pass an object to the method CalcTax, which belongs to another class.

Output

Name: John M. Trainer
Alias: jtrainer
Taxes: $240.00

Now its time for desi Browser

If you are using Internet Explorer or Firefox or Chrome or Opera or Safari for browsing give it a Desi change with Epic – A New Browser from India. The Epic browser is developed by Bangalore based Hidden Reflex and is based on Mozilla and is available for free. What is very important is the news of Epic could give Moksha or liberation to millions of Indian netizens who are stuck with Internet Explorer 6.

Download - Epic browser here at the official website.

Use explicit casting instead of DataBinder.Eval

The DataBinder.Eval method uses .NET reflection to evaluate the arguments that are passed in and to return the results. Consider limiting the use of DataBinder.Eval during data binding operations in order to improve ASP.NET page performance.

Consider the following ItemTemplate element within a Repeater control using DataBinder.Eval:

Using explicit casting offers better performance by avoiding the cost of .NET reflection. Cast the Container.DataItem as a DataRowView:

SQL DATE Time Convert

During Sql Queries most of the time we need to manipulate the format of Date below I am trying to cover most of the format of date time in sql queries.

-- SQL Server T-SQL date and datetime formats - sql date / datetime format
-- Date time formats - mssql datetime - sql server date formats - sql dates format
-- MSSQL getdate returns current system date and time in standard internal format
-- SQL datetime formats with century (YYYY or CCYY format)- sql time format
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
-- Oct 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
-- Oct 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
-- yyyymmdd - ISO date format - international standard - works with any language setting
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
-- 02 Oct 2010 11:02:07:577
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
-- 2010-10-02T10:52:47.513
-- Without century (YY) date / datetime conversion - there are exceptions!
SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) -- mm/dd/yy
SELECT convert(varchar, getdate(), 2) -- yy.mm.dd
SELECT convert(varchar, getdate(), 3) -- dd/mm/yy
SELECT convert(varchar, getdate(), 4) -- dd.mm.yy
SELECT convert(varchar, getdate(), 5) -- dd-mm-yy
SELECT convert(varchar, getdate(), 6) -- dd mon yy
SELECT convert(varchar, getdate(), 7) -- mon dd, yy
SELECT convert(varchar, getdate(), 8) -- hh:mm:ss
SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) -- mm-dd-yy
SELECT convert(varchar, getdate(), 11) -- yy/mm/dd
SELECT convert(varchar, getdate(), 12) -- yymmdd
SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) -- hh:mm:ss
SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm

CTE (Common Table Expression)

The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name

Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1: Simple CTE

WITH ProductCTE
AS
(
SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
FROM Products
)
SELECT * FROM ProductCTE

Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.

This result set can be retrieved like table or view.

CTE2:Simple CTE with alias

WITH ProductCTE(ID,Name,Category,Price)
AS
(
SELECT ProductID,ProductName,CategoryID,UnitPrice
FROM Products
)
SELECT * FROM ProductCTE

Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.

WITH ProductCTE
AS
(
SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
FROM Products
)
SELECT * FROM ProductCTE

CTE 3: CTE joins with normal table

The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer
AS
(
SELECT DISTINCT CustomerID FROM Orders
)
SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROM Customers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.

CTE 4: Multiple resultsets in the CTE

WITH MyCTE1
AS
(
SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
),

MyCTE2
AS
(
SELECT DISTINCT ProductID FROM "Order Details"
)
SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID

Here, there are two result sets that will be filtered based on the join condition.

CTE 5: Union statements in the CTE

WITH PartProdCateSale
AS
(
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')
UNION ALL
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
)
SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" OD INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID

Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.

CTE 6: CTE with identity column

WITH MyCustomCTE
AS
(
SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
Customers
)
SELECT * FROM MyCustomCTE

TRY...CATCH (Transact-SQL)

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.



BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH






Retrieving Error Information



In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:
• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE() returns the line number inside the routine that caused the error.
• ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

How to resolve relative url's without ResolveUrl

Sometimes you need to resolve relative url's without ResolveUrl. If the code is executing outside a Control, for example in an IHttpHandler or business layer code somewhere that has no reference to a Control, you can't call Control.ResolveUrl.



The System.Web.VirtualPathUtility class has some very useful method for converting from an app relative path to an absolute path:



string absoluteUrl = VirtualPathUtility.ToAbsolute(relativeUrl);

How to view code that is covered by the IntelliSense pop-up

When working in Visual Studio 2008 and the IntelliSense pop-up is visible, but you would like to view the code that is covered by it, press and hold the Ctrl key, and the pop-up becomes transparent so that you can view the code that is below the pop-up.

Before pressing Ctrl Key



After pressing Ctrl Key

Maintain the position of the scrollbar on postbacks

In ASP.NET 2.0 you can simply add the MaintainScrollPostionOnPostBack attribute to the Page directive:



and if you want to maintain it for complete application then just add

this attribute in config file

How to loop through all rows of the DataTable?

I know this tip is very easy for 6 month experienced professionals but still some time few new professional are looking for this tip.

foreach (DataRow row in dTable.Rows)
{
yourvariable = row["ColumnName"].ToString();
}

//OR
for (int j = 0; j< dTable.Rows.Count; j++)
{
yourvariable = dTable.Rows[j]["ColumnName"].ToString()l
}

Two approaches to update database row if exists, insert if not

The biggest challenge with update/insert (so called upsert) is to minimize any kind of locks. Unfortunately there is no silver bullet for this yet. So let's review two the most commonly used methods:


1. Update, if @@ROWCOUNT = 0 then insert
UPDATE Table1 SET Column1 = @newValue WHERE Id = @id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)
END
This method is good if you know that in most of the cases a row will exist and update will be performed. Otherwise the second method should be used.

2. If row exists update, otherwise insert
IF EXISTS(SELECT * FROM Table1 WHERE Id = @id)
BEGIN
UPDATE Table1 SET Column1 = @newValue WHERE Id = @id
END
ELSE
BEGIN
INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)
END
This one is good if you know that in most of the cases a row will not exist and insert will be performed. For such cases it executes SELECT statement followed by INSERT statement. That results in less expensive lock comparing to UPDATE + INSERT in previous method.

Correct event invocation

Be aware that if there are no subscribers a .NET event will be null. Therefore when raising the event from C# test it for null first.
public event EventHandler SelectedNodeChanged;

protected virtual void OnSelectedNodeChanged(object sender, EventArgs e)
{
//Event will be null if there are no subscribers
if (SelectedNodeChanged != null)
{
SelectedNodeChanged(this, e);
}
}
However in multithreaded application the last subscriber can unsubscribe immediately after the null check and before the event is raised. To avoid a null reference exception make a temporary copy of the event.
public event EventHandler SelectedNodeChanged;

protected virtual void OnSelectedNodeChanged(object sender, EventArgs e)
{
//Make a temporary copy of the event to avoid possibility of
//a race condition if the last subscriber unsubscribes
//immediately after the null check and before the event is raised.
EventHandler handler = SelectedNodeChanged;

//Event will be null if there are no subscribers
if (handler != null)
{
handler(this, e);
}
}

Organize Usings

if you are on VS 2008, you can right-click anywhere inside your .cs source file and choose Organize Usings > Remove Unused Usings, Sort, or Remove and Sort.good way to clean up your usings and will speed up your build.

Debugger variable $exception

If your catch block do nothing with caught exception you may declare block argument without name (to avoid warning message "CS0168: The variable 'ex' is declared but never used"):
try
{
...
}
catch (Exception)
{
// deliberately suppressing all exceptions
}
But one day during debugging you may actually want to examine Exception. Since you don't have variable where exception is stored you can use debugger variable $exception provided by the Visual Studio.NET 2005 Debugger to examine the exception in a catch block. Just add it to Watch Window.

Label to a form input field

If you set a label to a form element set the AssociatedControlID property. This will force the label to render as label rather than span. A benefit of using the AssociatedControlID property is that clicking a label when this property is set automatically changes the form focus to the associated form input.

Turn off Session State if you're not using it

Since ASP.NET Session State is on by default, you pay the cost in memory even if you don't use it. If you're not using Session State, turn it off and save yourself the overhead. There are serveral ways to do this:
If you're not using Session State at all, turn it off completely via web.config file:



If you're using Session State, but it's required only for a few pages, then first turn it off for all pages:



then enable it for a specific page:


obviously such optimization makes sense only for high-traffic web sites.

Return read-only collection if you want to control its modification

Let's review the following example:
public class Order
{
private List _orderItems = new List();

public ReadOnlyCollection OrderItems
{
get { return _orderItems.AsReadOnly(); }
}

public void AddOrderItem(OrderItem orderItem)
{
...
_orderItems.Add(orderItem);
}
}

The class has a public property of a collection type and I want to have “controlled access “to this collection. What I mean is, that a consumer of these classes should not to add an OrderItem to the collection directly . since some additional actions need to be executed. He should always use the AddOrderItem method id he wants to add an OrderItem To the Order.

In Such cases keep the collection private, create the necessary methods to provide the necessary controlled access to the collection and create a public property which returns a read-only instance of the collection. So that you can iterate through the collection, change existing instances of objects within the collection, get the number of objects that are in the collection etc

Speed testing in .NET - System.Diagnostics.Stopwatch

System.Diagnostics.Stopwatch is a replacement for what most people probably do to identify the time spent on excecuting a method. The process usually goes something like: create a DateTime.Now value at the start and then subtract the ending DateTime.Now to get the TimeDuration value that elapsed.

As an alternative, the Stopwatch class was built using low-level API calls, with less overhead than other .NET methods. If the hardware and Windows version of the computer support a high-resolution performance counter, it will use this counter instead of the standard PC clock.

Here is a simple example:

System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();

watch.Start();



//do my stuff

...



watch.Stop();

MessageBox.Show("Time spent: " + watch.Elapsed.ToString());

Use Tab twice to get the code snippets that are available within code editor

Take for example of foreach loop, just press 2 tabs after typing "fore"... that's it you got the code snippet as mentioned below:

Other useful code snippets are:
•for - for loop
•while - for while loop
•equals - snippet for implementing Equals() according to quidelines
•prop - for public property and backing field
•tryf - for try finally
•indexer - for indexer

Tip Of the Day

?? operator (C#)
The ?? operator returns the left-hand operand if it is not null, or else it returns the right operand. For example:

int? x = null;

...

// y = x, unless x is null, in which case y = -1.

int y = x ?? -1;

The ?? operator also works with reference types:

//message = param, unless param is null

//in which case message = "No message"

string message = param ?? "No message";

My first day on blog

Hello

My name is Pawan Tiwari Currently working as a Senior Software engineer.
I am writing this blog to share my views and knowledge on Dot net technology and also want your feedback on my shared views.
This will help me to get time for my knowledge inhancement on regular basis.

Thanks