SQL Server Connections Leaks

I've came across with an exam question on MCTS data access dump and found this topic, I just googled and thought of writing a blog with what I found.

Connection leak basically happens when we open a connection to the database from our application and forget to close it, or due to some reasons it doesn't get closed.

Symptoms of Connection Leaks :

1. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
2. SQL Server Does Not Exist Or Access Denied 
3. General Network Error 

If your application is leaking connections to Microsoft SQL Server, you can find the last SQL statement that was run for a connection by running this query

SQL SERVER 2008 R2
SELECT recent.text AS 'Last SQL Statement',
               connection.client_net_address AS 'client IP',
               connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

SQL SERVER 2008
SELECT recent.text AS 'Last SQL Statement',
               connection.client_ip_address AS 'client IP',
              connection.connect_time AS 'connected since'
FROM sys.dm_exec_connections AS connection
CROSS APPLY sys.dm_exec_sql_text(connection.most_recent_sql_handle) AS recent
ORDER BY connection.connect_time ASC

You can use the below code to find out whether your application is explicitly closing or disposing the SQL connections.

 string instanceName = Assembly.GetEntryAssembly().FullName;  
 PerformanceCounter pCount = new PerformanceCounter(".NET Data Provider for Sqlserver",  
                                             "NumberOfReclaimedConnections",  
                                             instanceName,  
                                             true );  
 int leakedConnetionsCount = (int) instanceName.NextValue();  

Read more at this MSDN article

Free Source Version Control Facility from Microsoft for Small Teams

They have introduced Visual Studio Online with the capabilities of Team Foundation Server and additional cloud services, Where we can create our source code repository on the cloud and work in the same way as what we do now.

This online user plan includes everything from the hosted GIT repos, project tracking tools, and continuous integration tools. These user plans are described in
Visual Studio Online User Plans

The Visual Studio online basic is given free for up to five users. In this blog, I’ll elaborate on each and every step of how you can get your projects migrated to online TFS and work as a small team.

 ** You need Visual Studio 2013 Professional or Ultimate installation to use this feature.

Steps :
1. You must create a Live account, and verify the given email and phone number. Go to http://www.visualstudio.com/en-us/products/what-is-visual-studio-online-vs.aspx​ and logged in using your credentials.

 If all the validations and verification are completed, you must see your own name as below.

3. Then click on Create a free account now to create a Visual Studio online account.

4. Now you should be landed on this page.

5. Now type your project name, chose the version control system TFVC / Git, Process Control Template, and click on Create project. Read more on the SVC system supported by Microsoft Visual Studio : Use version control

6. Now you must see this page.

7. Now click on "Open with Visual Studio to connect". You might be asked for your credentials, please provide them. Then you will be connected to the online TFS account that you have just created.

8. Now you can add your solution to the online TFS.

Type Sharing Issue with Multiple WCF Service References

I've encountered with a strange issue where the WCF test client could not generate the WSDL for the WCF service. No solutions have been available when googling with the error message we've got. So I split the service contract into three separate services depending on their functionality of them with the intention of adding them as 3 separate service references.

Then I've added the 3 service references to the front-end projects with the type-sharing option. But still had to re-factor a considerable number of lines of codes as the previous service client splits into 3 now. I've opened the files (except the xsds) generated by the service reference, then found this interesting behaviour in a file called "Reference.svcmap"

I've listed all my other mex endpoints as below and incremented the souceId, then I've regenerated the service reference.

Bingo..... Now using the ServiceAClient object I could invoke all the service methods available in ServiceB, ServiceC and ServiceD.

Fix : The "FindRibbons" task could not be loaded VSTO

I've recently encountered with an error in a VSTO development (an Excel Add-in) under Vs 2010 environment.   The error message I got when compiling the solution is as follows.

"The "FindRibbons" task could not be loaded from the assembly, Microsoft.VisualStudio.Tools.Office.BuildTasks, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a. Confirm that the <UsingTask> declaration is correct, that the assembly and all its dependencies are available, and that the task contains a public class that implements Microsoft.Build.Framework.ITask"

I have installed the VS 2012 together with VS2010 recently and I did the following process to get this resolved.

1. Open "Microsoft.VisualStudio.Tools.Office.targets" File from below Location
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\OfficeTools
2.  Press Ctrl+H and replace all 10.0.0.0 text with 11.0.0.0
3. Save the file to the desktop with the same & extension.
4. Delete the original file
5. Cut and paste the saved file to the above location.
6. Close and restart VS 2010 (If Opened)

WCF (Windows Communication Foundation) First Step

Prerequisite : Microsoft Visual Studio 2010 (.net 4.0)

The purpose of this WCF article is to give a basic idea about how to create a simple WCF service.

To begin this tutorial, First, open up Visual Studio 2010 and create an empty Visual Studio solution. In My case, "WCF All in One Place".

Then add a WCF Service Library Project as Follows.

Now, There should be 3 files appear in the WCFServiceLibray project. The basic purpose of those files is as follows.
- Service.cs : Contains the service implement ion code
- IService.cs : Contains the service contract/ Service Interface class
- App.config : Contains the service configurations


You should be able to build and run the solution. If you do, the WCF Test Client should automatically host your WCF service and show what are the methods in your service and will give you a chance to play with those methods. If you have noticed your service metadata is exposed via the following mex end point.

 "http://localhost:8732/Design_Time_Addresses/WcfServiceLibrary/Service1/mex" 

"The metadata that the mex endpoint exposes describes the service, the various operations it has, parameters the operations require, and the return types. In other words, the metadata exposes the contract. With this information, the client can create a proxy to interact with the service. Clients use the mex endpoint to access the metadata."

Let's customize the project as we need. I suggest you to detached (decouple) the Service from its service contact, since I'm going to deploy this solution in several ways.

So first add another class library project to the solution called "ServiceContract" and delete the automatically create class1.cs.

Then add a new Interface class called "IServiceContratc.cs" to the ServiceContract project and place the following code inside the interface class.

Then delete the IService1.cs and Service1.cs classes from the WcfServiceLibrary project and add a new class called SampleService.cs to the same project and place the following code.

 

 Change the following attributes in the app.config file located in the WcfServiceLibrary project as follows.

- service name="WcfServiceLibrary.SampleService"

- baseAddress = "http://localhost:8732/WcfServiceLibrary/SampleService/"

- contract="ServiceContract.IServiceContract"

Debug the WcfServiceLibrary project, and the WCF Test Client window should list your new methods. Click on the .. method and enter the two values for the required two parameters.

and click on the invoke button. A pop-up dialogue box may appear and click OK after clicking on the tick (Don't show this....).  Then observe the Response.

Click on the XML tab located in the bottom center of the window and observe the request message sent by the WCF Test Client.

Password is in the plain sight !!. This is why you are required to think of securing your WCF services.