Guru's .NET Blog

How to resolve "Cannot drop database because it is currently in use" error

2015-11-25T14:49:00.003-08:00

Run a ALTER DATABASE command like below before dropping the database:

USE [master]
GO
ALTER DATABASE [databaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]
GO

DROP DATABASE [databaseName]
GO

Visual Studio Online

2015-07-21T16:27:00.003-07:00

Features included with free Visual Studio Online account:

5 FREE Basic user licenses
Unlimited stakeholders
Unlimited eligible MSDN subscribers
Unlimited team projects and private code repos
FREE work item tracking for all users
FREE 60 minutes/month of build
FREE 20K virtual user minutes/month of load testing
PREVIEW application monitoring and analytics

Visual Studio 2015

2015-07-21T15:51:00.003-07:00

Some new features in Visual Studio 2015:

Visual Studio 2015 RC Downloads
.Net in GitHub
Vsual Studio Code
Visual Studio Online
Visual Studio 2015 includes Xamarin integrated for mobile development
Cordovo tooling in Visual Studio 2015
.Net and microservices
Don't have to build to run a web app
All source code are in Git hub (example Asp.Net MVC source code). If we get source code, source reference will override nuget package.
Dev and Test in Cloud - use virtual machines
Debugging
- Performance step (no more debug trace lines)
- Applying linq queries with lamda expression in the output window while debugging
- Editing the linq query real time while debugging

Entity Framework Power Tools

2014-11-11T15:51:00.000-08:00

Found this cool entity framework power tool to do reverse engineering for creating code first entity framework data models from an existing database.

Install the Entity Framework Power Tools Beta 4 from here:
https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d

Once the tool is installed, you should be able to access the entity framework reverse engineer plugin from Visual Studio.

Right click on your solution in Visual Studio and you should see menu option for reverse engineer:
"Entity Framework" -> "Reverse Engineer Code First"

Create a class library for your data layer and click "Reverse Engineer Code First" menu option. Folowwing the instructions to create entity framework data models for an existing database.

Memory gates checking failed because the free memory is less than 5% of total memory

2014-11-03T16:14:00.000-08:00

I got this exception when running a service on my localhost:

"Memory gates checking failed because the free memory (180146176 bytes) is less than 5% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element."

I checked memory usage in task manager and the free memory was indeed less than 5%.

Easy fix for this issue is to set "minFreeMemoryPercentageToActivateService" value in "serviceHostingEnvironment" configuration to 0:

<system.serviceModel>

Cannot drop database because it is currently in use error

2014-06-02T16:25:00.001-07:00

We might get following error when we try to drop a database in SQL Server:

"Cannot drop database 'MyDatabaseName' because it is currently in user".

To resolve this error, alter the database first before deleting the database like below..

USE [master]

ALTER DATABASE [MyDatabaseName] set single_user with rollback immediate

DROP DATABASE [MyDatabaseName]

Visual Studio solution file opens in notepad!

2014-03-06T14:21:00.001-08:00

If you edit your visual studio solution (.sln) file in notepad and after that if the solution file always opens in notepad by default, then this is what you need to make the solution file open back in visual studio again:

1. Right click on the solution file, select "Properties"
2. Look for "Opens with:" option and change it to your Visual Studio version.

The Principles of Open Space Conference

2012-11-17T16:00:00.002-08:00

Today I attended a technical open space conference and I really liked the concept.

In brief these are the principles of open space conference:

1. Whoever shows up are the right people.
2. Whenever it starts, it starts.
3. Whenever it's over, it's over.
4. Wherever it happens is the right place.
5. Whatever happens is the only thing that could

Visual Studio Database (VSDB) Project issue with UDF and Views

2012-02-21T16:29:00.000-08:00

In Visual Studio 2010, there seems to be a dependency problem with User Defined Functions (UDF) and Views. The deployment script created by the VSDB project always puts UDF before database Views. Consider this scenario, we have a database function which depends on a database view and we have altered both the function and the view. Now, when a deployment script is created for this database project, the altered function is always placed before the alter view statement. Running this script will fail at alter function statement because the dependent view was not altered yet. I could not find any settings in VSDB project to change the sequence order for the build, meaning build my database View first and then the UDF. The only workaround I found was to alter my database View in the pre-deployment script.

Entity Framework Error: Resource not found for segment 'Property'

2011-08-08T16:49:00.000-07:00

I was getting "Resource not found for segment 'Property'" error with the below code. I am loading some data from the database using .Net Entity Framework.

private void LoadItem(int specId)
{
var query = DataContext.Items.Where( x => x.SpecId == specID );

DataServiceQuery dsq = DataServiceQuery)query;

dsq.BeginExecute( ar =>
{
this.items = dsq.EndExecute( ar ).SingleOrDefault();
},
null );
}
}

The above code threw an exception when the data query returned no records. I was expecting SingleOrDefault() to return default value (which is NULL) if no matching records were found. But, instead I was getting "Resource not found for segment 'Property'" exception.

After doing some investigation I found out that the exception was due to the fact that I was using primary key field in my data service query.

var query = DataContext.Items.Where( x => x.SpecId == specID );

Here the field SpecId was a primary key. In Entity Framework if your data service query is performed on a primary key, then SingleOrDefault() and FirstOrDefault() methods will throw an exception if no matching records were found.

To resolve this issue, I set the following property to TRUE on the data service DataContext. This line will suppress the "Resource Not Found" exception.

DataConext.IgnoreResourceNotFoundException = true;

SQL: Deleting duplicate records from table

2011-07-13T14:13:00.000-07:00

Here is one way to delete duplicate records from a table:

Consider we have some duplicate records in TempTable which has a unique identifier column called "RowId".

delete from TempTable
where RowId not in
(
select min(ROWID)
from TempTable
group by column1, column2,...
)

For each duplicate set of duplicate records, the above query retains one row with minimum RowId and deletes all other duplicate records. If you want to retain row with maximum RowId and delete all other duplicate records, then just replace the min() method with max().

SQL Error: None of the result expressions in a CASE specification can be NULL

2011-06-07T16:48:00.000-07:00

I got this error when my CASE statement in SQL returned NULL and did not have any ELSE section in the CASE statement: "None of the result expressions in a CASE specification can be NULL"

The CASE statement which resulted in error was like this:

SELECT

CASE WHEN StudentId = 0 THEN null END

I wanted to return NULL when StudentId = 0; else return the StudentId.

The above query did not work without the ELSE part. After adding the ELSE section like below the query executed without error:

SELECT

CASE WHEN StudentId = 0 THEN null ELSE StudentId END

Creating DataGridTemplateColumn from code behind

2011-05-05T22:45:00.000-07:00

We can create DataGridTemplateColumn from code behind by defining a DataTemplate in the ResourceDictionary xaml and then use the DataTemplate to create the DataGridTemplateColumn.

In the below sample code, I am creating a data grid column with checkbox.

In the ResourceDictionary xaml, add DataTemplate for the checkbox:

<DataTemplate x:Key="CheckboxTemplate">
<CheckBox IsChecked="{Binding IsSelected, Mode=TwoWay}" />
</DataTemplate>

From the code behind create the DataGridTemplateColumn for the checkbox:

DataGridTemplateColumn checkboxColumn = new DataGridTemplateColumn()
{
CellTemplate = App.Current.Resources["CheckboxTemplate"] as DataTemplate
};

Double click issue with DataGridCheckbox column

2011-05-05T16:14:00.000-07:00

DataGridCheckboxColumn in DataGrid seems to have double click issue. One has to click twice to check or uncheck, first click to select the row and the second click for the checkbox. We can use DataGridTemplateColumn instead for the checkbox which does not have double click issue.

sample code:

<controlsData:DataGridTemplateColumn.CellTemplate>

</DataTemplate>

</controlsData:DataGridTemplateColumn.CellTemplat>

</DataGridTemplateColumn>

IIS version on Windows versions

2011-02-28T13:56:00.000-08:00

Every Windows version has it's own IIS version. Here are some Windows versions:

Windows Server 2000 - IIS version 5.0
Windows XP Professional - IIS version 5.1
Windows Server 2003 - IIS version 6.0
Windows Vista - IIS version 7.0
Windows Server 2008 - IIS version 7.0

Using WebGet and WebInvoke

2011-02-22T14:34:00.000-08:00

nice article on WebGet and WebInvoke attributes: Using WebGet and WebInvoke

LINQ to Entities does not support Contains() method

2011-02-17T09:41:00.000-08:00

We are using .NET Entity Framework in our project and today I wrote a LINQ to Entities query to fetch records from table A if corresponding records are not found in table B. Let us take example of customer orders scenario. I want to get all the customers who don't have a order in the Orders table. What I needed was "NOT IN" or "NOT EXISTS" SQL equivalent in LINQ to Entities. I knew that we have Contains() method in LINQ to SQL which is equivalent to NOT EXISTS in SQL. So, I tried following LINQ to Entities query to return all the costumers who don't have a order in the Orders table:

from c in context.Customers
where !( from o in context.Orders select o.CustomerId ).Contains( c.CustomerId )
select c

I was confident this would work, but no the above query did not work!!. I got an error that LINQ to Entities does not support Contains() method. It is frustrating to know that the same Contains() method works in LINQ to SQL but not in LINQ to Entities. Why not support such basic functionality.

I had other frustrating moments with LINQ to Entities too. I prefer to just use LINQ to SQL and stay away from LINQ to Entities.

The NOT EXISTS equivalent in LINQ to SQL

2011-02-16T14:11:00.000-08:00

Here is a sample LINQ to SQL query to return all the customers who don't have an order in the Orders table:

var query =
from c in Customers
where !( from o in Orders select o.CustomerId ).Contains( c.CustomerId )
select c;

Useful Keyboard Shortcuts for Microsoft Visual Studio

2011-02-08T15:42:00.000-08:00

Here are some useful keyboard shortcuts for Microsoft Visual Studio:

F5 => Build
CTRL+F5 => Execute
F9 => Breakpoint for debugging
F10 => Single step during debugging
F11 => Get into function or method during debugging

CTRL+K+K => Book Mark
CTRL+K+N => Go to next book mark
CTRL+K+P => Go to previous book mark

CTRL-C => Copy
CTRL-V => Paste
CTRL-X => Cut
CTRL-A => Select All
CTRL-Z => Undo
ALT-TAB => Switch between open programs
CTRL-TAB => Switch between windows in the current program
CTRL-END => Go to the end of the document
CTRL-HOME => Go to the start of the document
END => Go to the end of the line
HOME => Go to the start of the line
SHIFT-END => Select from the current cursor position to the end of the line
SHIFT-HOME => Select from the current cursor position to the start of the line

F4 => Repeat the last action (only works in most Microsoft Office
applications, such as Excel/Visio/ Word, but very powerful in those)

CTRL-SHIFT-RightArrow => Select from the current cursor position to the end of the current word (including space)

CTRL-SHIFT-LeftArrow => Select from the current cursor position to the start of the prior word

CTRL-F => Find (open the Find dialog in many applications)

CTRL-H => Find and Replace (open the Find and Replace dialog in many
applications)

CTRL-SHIFT-UpArrow => Select from the current cursor position to the start of the current line of next line up

CTRL-SHIFT-DownArrow => Select from the current cursor position to the end of the current or next line down

CTRL-G => Go to a specific line number (works in notepad, Visual Studio,
other editing programs)

Firebug for Firefox

2011-02-08T15:33:00.000-08:00

Firebug integrates with Firefox to put a wealth of web development tools at your fingertips while you browse. You can edit, debug, and monitor CSS, HTML, and JavaScript live in any web page.

Download Firebug from here: http://getfirebug.com/

Internet Explorer Developer Toolbar

2011-02-08T15:03:00.001-08:00

The Microsoft Internet Explorer Developer Toolbar provides a variety of tools for quickly creating, understanding, and troubleshooting Web pages.

Download the IE Developer Toolbar here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=95e06cbe-4940-4218-b75d-b8856fced535

Silverlight error: The debugger cannot continue running the process.

2011-01-25T10:49:00.001-08:00

Today I got this error while debugging my silverlight application and the debugger just crashed without any useful error information. I got this error when the debugger crashed:

"The debugger cannot continue running the process. Process was terminated."

After doing some debugging and research I found this thread online where in many people had the same issue because of recursive call in class property:

http://social.msdn.microsoft.com/Forums/en-US/vsdebug/thread/f9b08dd4-8d20-4104-9a24-9f002caceb48/

I looked at my code and indeed I had a recursive call in one of my property by mistake.

private bool isUser;
public bool IsUser
{
get { return IsUser; }
set
{
IsUser = value;
OnPropertyChanged( "IsUser" );
}
}

As you can see in the above code my getter was recursively referring to IsUser property itself instead of the field isUser. This was just coding error but it took some time to figure out the root cause as the debugger was crashing without proper error information.

Date format in WPF/Silverlight

2010-10-26T16:38:00.000-07:00

Here is an example for formatting date in WPF (Format: MM/DD/YYYY):

<TextBlock Text="{Binding CreationDate, StringFormat=\{0:MM/dd/yyyy\}}" />

Error when referencing assembly from network share - Silverlight 4 (.NET Framework 4.0)

2010-09-22T11:00:00.000-07:00

I got this error today when I added reference to an assembly from network share:

"Error 3 Could not load the assembly file://\\CM\libraries\Microsoft\Rx\Reactive Extensions v1.0.2677.0\SL4\System.Observable.dll. This assembly may have been downloaded from the Web. If an assembly has been downloaded from the Web, it is flagged by Windows as being a Web file, even if it resides on the local computer. This may prevent it from being used in your project. You can change this designation by changing the file properties. Only unblock assemblies that you trust".

I am using Silverlight 4 (.NET Framework 4.0).

To fix this issue I added following entry in the Visual Studio 2010 devenv.config file.

< loadFromRemoteSources enabled="true" />
To the runtime section of:
C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe.config

SQL Script to Create a Database

2010-08-18T10:33:00.000-07:00

Note: You will need to modify lines 2 and 4 to adjust the file paths for the data and log files to land the database on your files system wherever you want it. That should be the only modifications needed.

----------------------------------------

CREATE DATABASE [MyDatabase] ON PRIMARY
( NAME = N'MyDatabase_data', FILENAME = N'C:\SQL\Data\MyDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:\SQL\Log\MyDatabase.ldf' , SIZE = 220160KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MyDatabase', @new_cmptlevel=90
GO

ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDatabase] SET READ_WRITE
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM
GO
USE [MyDatabase]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO