LINQ To SQL - CASE Statements

Switch functionality can be accomplished by using "Ternary Operator"

The ternary operator takes the "if" statement and turns it into an expression.  Here's an example:

The syntax is <condition>  ? <true value> : <false value>

Now, let's add a basic case statement.  This will return the text "This is poisonous!" for plants with a 0 in the edible field, and "Okay to eat" otherwise.  By looking at the generated SQL using the debug visualizer, we can see that a CASE statement is in fact being generated.

Now the question will come up, "what if I want to have more than just one WHEN and an ELSE".  In other words, how do I add more cases.  Here's the trick:

By replacing the "if false" value of the ternary expression with another ternary expression we logically create the same effect as a SQL CASE statement.  Unlike the switch statement, this is an expression, and can be used on the right had side of the assignment operator.  LINQ to SQL is smart about handling this too.  It creates additional WHEN clauses inside of one CASE statement.  Just like you would if you were writing the SQL yourself.  The C# syntax forces you to have an ELSE clause.  That's a good thing.

Some people might opt for writing in extra parenthesis to clearly show each ternary expression.  I think writing the statements without the parenthesis more clearly shows our intent, that is, making a CASE statement.

Types of LINQ syntax

There are 2 types of LINQ Syntax:

1. 1.Fluent syntax
2. 2.Query syntax

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };
IEnumerable<string> query =
names.Where (name => name.EndsWith ("y"));
query.Dump ("In fluent syntax");

query =
from n in names
where n.EndsWith ("y")
select n;
query.Dump ("In query syntax");

Combining interpreted and local queries.

void Main()
{
// This uses a custom 'Pair' extension method, defined below.

IEnumerable<string> q = Customers
.Select (c => c.Name.ToUpper())
.Pair() // Local from this point on.
.OrderBy (n => n);

q.Dump();
}

public static class MyExtensions
{
public static IEnumerable<string> Pair (this IEnumerable<string> source)
{
string firstHalf = null;
foreach (string element in source)
if (firstHalf == null)
firstHalf = element;
else
{
yield return firstHalf + ", " + element;
firstHalf = null;
}
}
}

SQL to LINQ converter

Try this tool : 

http://www.linqpad.net/

Using LINQPad to test your LINQ to XML queries

Sunday, 6 February 2011 12:55 by mha

If you’re a LINQPad user you’re probably used to query your DBML (LINQ to SQL), but did you know that you can also use it for LINQ to Objects, LINQ to XML etc.

A simple example of how to do a query against a XML file (make sure language is set to C# Statements):

var xml = XElement.Load (@"c:\\inetpub\\GWportal\\src\\wwwBackend\\App_Data\\axCache\\ 
   GWDK\productprice-mha.xml");

var query = 
  from e in xml.Descendants("unittransaction").Descendants() 
    where (e.Attribute("name").Value == "ItemRelation" && e.Value.Equals("10020")) 
  select e.Parent; 
  
query.Dump();

Which gives this result:

Get Query String Using Javascript

To get query string using Javascript:

temp = window.location.search.substring(1);

document.write(temp);

-We create a variable temp

-Built-in Javascript properties: window.location is the entire url. search.substring(1) is the part after the question mark.

-We write the value of temp to the browser.

Below is a function that will break the query string into pieces for you:

Copy and Paste Javascript Code:

<script type="text/javascript">
<!--

function querySt(ji) {
hu = window.location.search.substring(1);
gy = hu.split("&");
for (i=0;i<gy.length;i++) {
ft = gy[i].split("=");
if (ft[0] == ji) {
return ft[1];
}
}
}

var koko = querySt("koko");

document.write(koko);
document.write("<br>");
document.write(hu);

-->
</script>

Handy "SQL Queries"

Run the below SQL Query to get the procedure information in a database.

select * from INformation_schema.routines

jQuery : Expandable Sidebar Menu

jQuery Demo - Expandable Sidebar Menu from John Resig on Vimeo.

Free Screen Reader Software

Free web application called WebAnywhere for screen reader web accessibility tests to simulate how a person using a screen reader will interact with your content.

http://webanywhere.cs.washington.edu/ 

Should I Use the sp_ Prefix for Procedure Names?

Never might sound like a long time, but prefixing your procedure names with sp_ causes a performance penalty if the procedures exist in a database other than master. Don't do it.

The example that Listing 1 shows illustrates why you should never prefix procedures with sp_ if you intend to use them in a high-volume transaction-processing environment while maintaining the best possible performance. The code in Listing 1 creates two test procedures in tempdb. I named the first procedure Select1 and the second procedure sp_Select1. The procedures run an identical command, SELECT 1, which is the simplest SELECT statement imaginable.

Run each of the procedures once, as Listing 2 shows, to ensure that SQL Server has compiled the procedure plans for each procedure and has cached them in memory. Then, proceed through the following steps to see the performance implication of prefixing procedures with sp_.

1. Start SQL Server Profiler and connect to your server. Keep all the initial defaults, with one exception: Add the event class SP:CacheMiss. To do so, expand the Stored Procedures event class tree under the Available Event Classes group. On the Events tab, select the SP:CacheMiss event class.
2. Start the trace and press Crtl+Shift+Delete to clear all events in the Profiler window.
3. Run dbo.sp_Select1 from the open Query Analyzer connection in tempdb that you used to create the stored procedures.
4. Open the Profiler window and look at the events. If you created the SQL Trace as I defined above, you should see three new events in SQL Server 2000: two SP:CacheMiss events and one SQL:BatchCompleted event. In SQL Server 7.0, you'll see one SP:CacheMiss event and one SQL:BatchCompleted event. SP:CacheMiss means that SQL Server didn't find the procedure in the procedure cache. SQL:BatchCompleted reveals that the T-SQL batch has finished.
5. Press Crtl+Shift+Delete to clear all events in the Profiler window.
6. Run dbo.Select1 from the Query Analyzer connection you used in Step 3. If you created the SQL Trace as I defined earlier, you'll see two new events in SQL Server 2000: one SP:CacheMiss event and one SQL:BatchCompleted event. In SQL Server 7.0, you'll see one SQL:BatchCompleted event—however, you won't see the SP:CacheMiss event that Step 3 generated.

Why did sp_Select1 generate an SP:CacheMiss but Select1 didn't? SQL Server gives name-resolution preference to the master database for procedures that have the sp_ prefix. SQL Server looks for a compiled plan for the procedure associated with the master database and doesn't find it because, in this case, the sp_Select1 procedure exists in tempdb. SQL Server assumes the procedure isn't in cache (and thus must be recompiled) and acquires an exclusive compile lock on the stored procedure for a short time. However, the short time that the lock exists is enough to cause performance problems.

Why does SQL Server need an exclusive compile lock? According to the Microsoft article "INF: SQL Blocking Due to COMPILE Locks" (http://support.microsoft.com/support/kb/articles/q263/8/89.asp), "In SQL Server 7.0 and 2000, only one copy of a stored procedure plan is generally in cache at any given time. Enforcing this requires serialization of some parts of the compilation process, and this synchronization is accomplished in part through the use of compile locks." Exclusive locks, of course, block other exclusive locks, which can create a serialization point that causes blocking if many people are trying to run the procedure. This behavior is one of the reasons that you should not prefix a user stored procedure with sp_. For more information about preventing this kind of performance penalty, see the sidebar "Tip: Always Qualify Stored Procedure References."

Difference between DateTime and SmallDateTime - SQL Dates and Times Series

1. Range of Dates

A DateTime can range from January 1, 1753 to December 31, 9999.
A SmallDateTime can range from January 1, 1900 to June 6, 2079.

2. Accuracy

DateTime is accurate to three-hundredths of a second.
SmallDateTime is accurate to one minute.

3. Size

DateTime takes up 8 bytes of storage space.
SmallDateTime takes up 4 bytes of storage space.


Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.

SQL: COUNT Function:: What is the difference between count(1) and count(*) in a sql query

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.
For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Visual Studio Extensions

Improve your visual studio performance using the power tool.

Key features in Productivity Power Tool are:

1.Quick Find.

2.Enhanced Scroll-bar.

3.Solution Navigator.

4.Tab Well UI.

5.Searchable Add Reference Dialog.

6.Tools Options Support.

7.Quick Access.

8.Auto Brace Completion.

9.Triple Click.

10.Fix Mixed Tabs.

11.Ctrl + Click Go To Definition.

12.Align Assignments.

13.Move Line Up/Down Commands.

14.Column Guides.

15.Colorized Parameter Help.

Download the tool from the below mentioned path........

http://visualstudiogallery.msdn.microsoft.com/d0d33361-18e2-46c0-8ff2-4adea1e34fef?SRC=Home

 

       

WCF Useful links

WCF - http://msdn.microsoft.com/en-us/netframework/aa663324.aspx
(.net Framework Developer center, central resource for all things WCF)

WCF 3.5 samples - http://msdn.microsoft.com/en-us/library/ms751514.aspx
WCF 4.0 samples - http://msdn.microsoft.com/library/dd483346(VS.100).aspx
(good resource to learn the basics by playing with samples)

WCF screencasts - http://msdn.microsoft.com/en-us/netframework/wcf-screencasts.aspx

.Net endpoint blog - http://blogs.msdn.com/endpoint/
(Blog by the .NET and AppFabric teams about WCF and WF development, deployment, and management)

Bug report/ Feature request/ Feedback to the product team - https://connect.microsoft.com/wcf
(There are useful resources mentioned at the bottom of this page as well)

Detailed WCF security guidance - http://www.codeplex.com/WCFSecurity

Detailed debugging using WCF tracing - http://msdn.microsoft.com/en-us/library/ms733025.aspx

New to Windows Communication Foundation programming??

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

WCF Demos

Visual Studio Code Samples extension

http://visualstudiogallery.msdn.microsoft.com/4934b087-e6cc-44dd-b992-a71f00a2a6df

Deadlocks in .Net

Deadlocks

Before starting to use the thread pool in your applications you should know one additional concept: deadlocks. A bad implementation of asynchronous functions executed on the pool can make your entire application hang.
Imagine a method in your code that needs to connect via socket with a Web server. A possible implementation is opening the connection asynchronously with the Socket class'BeginConnect method and wait for the connection to be established with the EndConnect method. The code will be as follows:

class ConnectionSocket { public void Connect() { IPHostEntry ipHostEntry = Dns.Resolve(Dns.GetHostName()); IPEndPoint ipEndPoint = new IPEndPoint(ipHostEntry.AddressList[0], 80); Socket s = new Socket(ipEndPoint.AddressFamily, SocketType.Stream, ProtocolType.Tcp); IAsyncResult ar = s.BeginConnect(ipEndPoint, null, null); s.EndConnect(ar); } }

So far, so good—calling BeginConnect makes the asynchronous operation execute on the thread pool and EndConnect blocks waiting for the connection to be established.
What happens if we use this class from a function executed on the thread pool? Imagine that the size of the pool is just two threads and we launch two asynchronous functions that use our connection class. With both functions executing on the pool, there is no room for additional requests until the functions are finished. The problem is that these functions call our class' Connect method. This method launches again an asynchronous operation on the thread pool, but since the pool is full, the request is queued waiting any thread to be free. Unfortunately, this will never happen because the functions that are using the pool are waiting for the queued functions to finish. The conclusion: our application is blocked.
We can extrapolate this behavior for a pool of 25 threads. If 25 functions are waiting for an asynchronous operation to be finished, the situation becomes the same and the deadlock occurs again.
In the following fragment of code we have included a call to the last class to reproduce the problem:

class MainApp
{
   static void Main()
   {
      for(int i=0;i<30;i++)
      {
         ThreadPool.QueueUserWorkItem(new WaitCallback(PoolFunc));
      }
      Console.ReadLine();
   }

   static void PoolFunc(object state)
   {
      int workerThreads,completionPortThreads;
      ThreadPool.GetAvailableThreads(out workerThreads,
         out completionPortThreads);
      Console.WriteLine("WorkerThreads: {0}, CompletionPortThreads: {1}", 
         workerThreads, completionPortThreads);

      Thread.Sleep(15000);
      ConnectionSocket connection = new ConnectionSocket();
      connection.Connect();
   }
}

If you run the example, you see how the threads on the pool are decreasing until the available threads reach 0 and the application stops working. We have a deadlock.
In general, a deadlock can appear whenever a pool thread waits for an asynchronous function to finish. If we change the code so that we use the synchronous version of Connect, the problem will disappear:

class ConnectionSocket
{
   public void Connect()
   {
      IPHostEntry ipHostEntry = Dns.Resolve(Dns.GetHostName());
      IPEndPoint ipEndPoint = new IPEndPoint(ipHostEntry.AddressList[0], 80);
      Socket s = new Socket(ipEndPoint.AddressFamily, SocketType.Stream,
         ProtocolType.Tcp);
      s.Connect(ipEndPoint);
   }
}

If you want to avoid deadlocks in your applications, do not ever block a thread executed on the pool that is waiting for another function on the pool. This seems to be easy, but keep in mind that this rule implies two more:

• Do not create any class whose synchronous methods wait for asynchronous functions, since this class could be called from a thread on the pool.
• Do not use any class inside an asynchronous function if the class blocks waiting for asynchronous functions.

If you want to detect a deadlock in your application, check the available number of threads on the thread pool when your system is hung. The lack of available threads and CPU utilization near 0% are clear symptoms of a deadlock. You should monitor your code to identify where a function executed on the pool is waiting for an asynchronous operation and remove it.

Configuring WCF Service References

Change the default Visual Studio Test Client in "WCF"

Right Click project goto properties and in "debug" section change the "Command Line Arguments", by default is points out to visual studio default test client where we can point it to our own test client location.

Creating Your First WCF Client

Multiple Start-Up Projects in "Visual Studio"

Right Click on solution and select theSet StartUp projects as shown below.

Select the multiple projects radio button as shown below and set the active action to "start" for selected projects.

Self-hosting WCF Services

Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

If you install DotNet framework 4.0 on IIS server and then enable DotNet 3.0 or 3.5 WCF features, you might see following error when browse your application site made of ASP.NET 4.0 (or run on ASP.NET 4.0 application pool).

Resolution:
  To resolve this issue, run the following from Visual Studio command prompt:
  aspnet_regiis.exe -iru

Hosting WCF Services in IIS

Configuring Services with Endpoints

How to find the changed files using Visual Studio.

Step 1:Open the Visual Studio 2010 as shown below.

Step 2:Expand the Microsoft Windows SDK Tools and select "WinDiff" as shown below

Here we can load two different files or projects to compare.......................

Command to view all active "TCP" or "UDP" connections

Goto to command prompt and type "netstat"

Delete item from "Enumerable List"

While performing a delete operation on a "Enumerable List" always use "for" statement instead of "for each".
In for statement last from the last item in the list.
Example:

for(int index=tempList.count;index > 0;i--)

{

 //Delete logic here...................

}