Easy way of Tracing open Data Reader

Finding DataReaders that are not being close in an .NET application can be something difficult.

Thats why I use this trick to detect which DataReaders where not close.

1. First add a new code file to your project.

2. Enter this code:

public static class SqlReaderTracer
	{
		static Dictionary<WeakReference,System.Diagnostics.StackTrace> traceInfo = new Dictionary<WeakReference,System.Diagnostics.StackTrace>();
		public static System.Data.SqlClient.SqlDataReader ExecuteReaderEx(this System.Data.SqlClient.SqlCommand command)
		{
			var stack = new System.Diagnostics.StackTrace(1, true);
			var reader = command.ExecuteReader();
			traceInfo.Add(new WeakReference(reader), stack);
			return reader;
		}
		public static void PrintTraceState()
		{
			Console.WriteLine("*******SQL Trace Results************");
			foreach (var reader in traceInfo.Keys)
			{
				if (reader.IsAlive)
				{
					var traceInfoFrame =  traceInfo[reader].GetFrame(0);
					var methodWhereItWasCreated =traceInfoFrame.GetMethod().DeclaringType.FullName + "." + traceInfoFrame.GetMethod();
					Console.Write("Reader alive created in " + methodWhereItWasCreated);
					if (traceInfoFrame.GetFileName() != null)
					{
						Console.Write(string.Format("at {0} ({1},{2})",traceInfoFrame.GetFileName(),traceInfoFrame.GetFileLineNumber(),traceInfoFrame.GetFileColumnNumber()));
					}
					Console.WriteLine();
						
				}
			}
		}

	}

And then to a Find /Replace All and change all the .ExecuteReader() for .ExecuteReaderEx(). 3. Ok Now. You are all set. To test it do something like this:

class Program
	{
		
		static void Main(string[] args)
		{
			var xxx = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyProjects\Phonebook.mdf;Integrated Security=True");
			xxx.Open();
			var ccc = xxx.CreateCommand();
			ccc.CommandText = "SELECT * from Phonebook";
			var reader = ccc.ExecuteReaderEx();
			xxx.Close();
			SqlReaderTracer.PrintTraceState();
			return;
		}
	}

And you will see an output like:

Starting a Project for the Cloud

If you dont have Windows Azure Tools installed

1. Open Visual Studio 2010

2. On the File Menu, Click new, and then click Project. This opens the New Project dialog

3. In the New Project dialog under Installed Templates, expand the Visual C# node

4. In project type click Cloud. A project that says Enable Windows Azure Tools appears

5. In Name type for example EnableTools and click OK

6. A project with a web page will appear. Click the Download Windows Azure Tools button. Close Visual Studio. Download and installed them.

Once you have Windows Azure Toolsl installed

1. Open Visual Studio 2010

2. On the File Menu, Click new, and then click Project. This opens the New Project dialog

3. In the New Project dialog under Installed Templates, expand the Visual C# node

4. In project type click Cloud. A project that says Windows Azure Cloud Service appears

5. In Name Type for example Phonebook  and click ok. The New Cloud Service Project is shown

If you are developing a Silverlight application with some services select the WCF Service Web Role. Rename it to something appropriate. In this example will be Phonebook.Web and Press OK

Your solution explorer will look like this:

Now you can developing your application. We already have an Silverlight application (that was migrated from VB6 to Silverlight) and some existing Silverlight Class libraries and Server Class Libraries (Class Libraries compiled with the full 4.0 Framework that will be used in the Server Project Phonebook.Web).

To add your Silverlight app. Right Click your server project (Phonebook.Web), then Properties and in Properties go to Silverlight Application and add Phonebook_Silverlight

When everything compiles Go to the Visual Studio Menu Bar, then Debug and then Start Without Debugging. That starts the local development environment on your computer.

You can now start testing and finish development of your application.

There is something you myst notice. Everytime you start the Cloud Service, the Azure test environment starts. By default it will start in http:\\localhost:81 but there was a previous Development Fabric it will start in http:\\localhost:81. This is important for Silverlight applications because you must configure your service end point to a particular port. Make sure that your ServiceReferences.ClientConfig file is in sync with the port that your DevelopmentFabric is using

Publishing your Application

1. Navigate to Azure : https://windows.azure.com/Cloud/Provisioning/Default.aspx

2. On the page that says My Projects. When you are starting you will only see one project line. Click on it

3. Click on New Service

4. Click on Hosted Services

5. In the Create Service page, specify the service properties. In this example we will use Phonebook.Web for service label and a short description and press next.

6. Now your hosted service needs a public url. You must check availability. In this example we will use phonebook. Hosted Affinity tries to group your services and storages in the same area. We have a SQL Azure database that is hosted in North Central US, so we will create a new AffinityGroup and call it PhonebookGroup. Press Create

7. After Creation your Service is ready, and you can start the deployment.

8. In Visual Studio 2010 Right click your Cloud Service. And Select Publish…

9. The Publish Cloud Service Dialog appears. From the Credentials drop down list select <Add>

10. From the Create or select an existing certificate select <Create>.
10.1 In our example we will enter a FriendlyName for the new certificate of PhoneBook.

10.2 Click the “Copy the full path” link in the dialog. Then Open your browser and navigate to https://windows.azure.com/cloud/provisioning/ and click on your project

10.3 Now click on account.And in the Account page click on Manage My API Certificates

10.4 Click the Choose file and paste the full path you had copied before and then click upload

the installed certificate will be shown below.

10.5 Now navigate to AppFabric by clicking AppFabric on the left hand side. This will show your subscription ID. Select it and copy it. You have to pasted on the dialog box in Visual Studio on the textbox that says above it “Copy the subscription ID”

and name those Credentials PhonebookAccount and press  OK

11 You need a storage account. If you have not created one. Then you have to follow these steps:

11.1 Open your browser and navigate to https://windows.azure.com/cloud/provisioning/ and click on your project.

11.2 Now click on New Service

11.3 Now click on Storage Account

11.4 The Create Service Page is show. Enter a Service label and Description and Click Next

11.5 Select a name for the public URL, check its availability. And select Affinity.And press Create

12. Press OK on the Publish Cloud Service Dialog

13. Visual studio will upload your application to the cloud

While your application is in staging the only problem might be that the published URL changed and that might be conflictive for your ServiceReferences.ClientConfig.

Once your application is on Staging you can switch it to Production and your are ready for business.

How to restore SQLSERVER .bak file to .mdf

If you have a .bak file, and all you want is to restore that file to a mdf. Then this is what you can do.

First you need an empty .mdf and .ldf files. The easier way to do that is to create a WebProject in Visual Studio and then In the App_Data folder right click and add new database. That will create an mdf and a .ldf.

Now copy those files to the data directory of your SQLSERVER Express. If it usually under the C:\Program Files\Microsoft SQL Server. In my case it is in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

So if your database is PhoneBook just copy:

copy PhoneBook.mdf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook.mdf”
copy PhoneBook_log.ldf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook_log.ldf”

Now attach your database:

open a Visual Studio 2008 Command Prompt or other prompt (Remember to right click the Run as Administrator, specially if it is Vista)

in the command prompt run:

sqlcmd –S ./SQLEXPRESS

and run the following commands:

CREATE DATABASE Phonebook ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf' )
FOR ATTACH ;
GO

copy your .BAK file to the data directory.

NOTE: Why the data directory: It seams that in some installation you can have permitions issues

RESTORE DATABASE PhoneBook
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'
WITH MOVE 'Phonebook' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf',
MOVE 'Phonebook_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf',
REPLACE

After that if everything runs ok then you can detach your database:

1> exec sp_detach_db 'Phonebook'

Now you can copy your mdf files back to where you wanted them

NOTE: If you dont know the name you have to put for Data and Log then Run commands like

RESTORE HEADERONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'

RESTORE FILELISTONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'

In my case it gives something like:

LogicalName
---------------------------------------------------------------------------------
Phonebook
Phonebook_log