Brennan’s Blog

I wanted to give the QA team a way to clear the items in the cache during their testing so I justed added a button to the main QA test page with a button that runs the following code. I actually ripped this code from my book, Pro ASP.NET for SQL Server.

using System.Runtime.CompilerServices;
using System.Web;
using System.Web.Caching;

// ...

[MethodImpl(MethodImplOptions.Synchronized)]
public void ClearCache()
{
    Cache cache = HttpRuntime.Cache;
    List<string> keys = new List<string>();
    foreach (DictionaryEntry entry in cache)
    {
        keys.Add((string)entry.Key);
    }
    foreach (string key in keys)
    {
        cache.Remove(key);
    }
}

When I got home today I found a box full of books waiting for me. More books should be on shelves in bookstores everywhere starting Monday. I managed to put a lot of great content into the book that I could not find in other books, like a custom databound control and a photo album provider that integrates directly with the SiteMap Provider.

You can read more about it on the book website and see the table of contents for a full list of the covered topics. I plan to give away a few copies at the next local .NET User Group meeting.

Last week I presented Deeper in LINQ for the Wisconsin .NET User Group and concluded with a speed test. The speed test compared ADO.NET to LINQ. The ADO.NET method called a stored procedure while the LINQ method generated the query inline. The results showed that LINQ is not as fast. The overhead is due to the LINQ for SQL provider that has to parse the query within the CLR and then generate the SQL that it sends over to SQL Server. The generated SQL is identical to what is in the stored procedure used by the ADO.NET method. The difference in timing is purely from preparing the query. And since LINQ to SQL uses ADO.NET once it generates the SQL it cannot possibly run faster than a direct ADO.NET implementation. The speed test was simply to show the difference.

[ Download the sample project: DeeperInLINQ.zip ]

In the attached screenshot you can see three tests. I wanted to make a LINQ sample that runs much faster so I added the second test to show how long it would take to have LINQ call the stored procedure just like the ADO.NET method and avoid the overhead of preparing the query. It seems that even though the original LINQ method is running the same query over and over it still has to carry out the preparation for the query each time. This is clearly an area that could be tuned prior to the .NET 3.5 release. In the meantime, the call to the stored procedure allows the new LINQ method to perform nearly as well as the ADO.NET version. If this particular method was performing poorly in a real application, breaking out the work to a stored procedure would be a good tuning option. But most of the time you will want to continue with inline LINQ queries so you get the productivity benefits that it provides through Intellisense and other features.

(more...)

I had several tasks I wanted to complete on several of my personal projects which I had on hold while I was busy with other work the past few months. Over the break I was able to squeeze in time between driving around the state for family visits to knock of few of these tasks off my list.

(more...)

Over the last few days I have noticed a big difference in performance between SQL Server 2005 Standed Edition and SQL Express. It seems the Standard Edition uses more memory, but it also had some issues with permissions which was causing a major performance degredation.

I attempted to diagnose and resolve the problems myself with limited success, so I ventured over microsoft.public.sqlserver.server to post my problem. Here is what I did to get myself into this trouble.

First I had SQL Express running my development databases. What you need to know about SQL Express is that it is just like the big SQL Server but it is limited to a single processor and 1gb of memory. It also does not run other features like Reporting and Integration Services, but it can do Query Notifications. With those exceptions it is still just a glorified service to manage .mdf files which are compatible across SQL Express and SQL Server 2005 Standard Edition.

What I did was uninstall SQL Express while leaving the .mdf files in place. Then I installed SQL Server Standard Edition, the one I won at Deeper in .NET 2006. Since the .mdf files were still in place I simply attached to them. I fired up Visual Studio and I was back in business.

After a while I was noticing that SQL Server was chewing up a lot of processor time even when I was not hitting the database. I found it was generating some errors, such as...

An exception occurred while enqueueing a message in the target queue.
Error: 15517, State: 1. Cannot execute as the database principal
because the principal "dbo" does not exist, this type of principal
cannot be impersonated, or you do not have permission.

Through the discussion on the news group I found it was due to how the permissions are set up on the files SQL Server uses, such as the .mdf and .ldf files. In some cases the files only had my personal user account with access while others had access for the system and MSSQL User accounts. I added those permissions which helped resolve the problem, but I eventually chose to start from scratch and did it right.

I cleared out all of my databases, deleted the .mdf and .ldf files and then created an empty database for each instance with SQL Management Studio. I took the backups and restored on top of those empty instances. Creating the empty databases with SQL Management Studio ensured the permissions on the files were set up properly. Now things are generally back to normal.

However, I am seeing that SQL Server is much more resource intensive in terms of memory and disk usage. So on my virtual host I created a new virtual disk and installed SQL Server on it so that it has plenty of room to grow. Now I just need a couple of 1gb memory chips from Dell to double my memory.

Yesterday I spent a great deal of time preparing to display reports produced with SQL Server 2005 Reporting Services through a Sharepoint Web Part. It was a real chore, so I will now share what I have learned. Here are a few of the critical details.

• SQL Express 2005 does NOT include Reporting and Integration Services (Standard Edition is the baseline)
• IIS is required in order to activate Reporting Services (I regrettably had to enable IIS in my .NET 2.0 environment)
• Microsoft.Sharepoint.dll is required to create the Sharepoint Web Parts
• A Sharepoint Redistributable SDK is not available so you must extract the above assembly from an installation
• Sharepoint 2003 cannot be installed on WinXP (the installer prevents it)

I also learned that is a very time-consuming process to go through all of the steps necessary to get the environment ready to do any development. I guess that since I was in a bit of a rush to learn to do this as quickly as possible I skimmed quickly past some documentation which would have been helpful.

Previously I had to set up CruiseControl.NET to build some code for Crystal Reports. I was lucky and found a useful installer in the Visual Studio folder. This time I had to run the Sharepoint install on Windows Server 2003 so that I could get to the Microsoft.Sharepoint.dll assembly. Fortunately I am running Virtual Server on may laptop and have a Windows Server 2003 instance available.

Once I got past these initial requirements I was able to start making real progress. But there were still some problems I worked to overcome. Since I could not run a local Sharepoint installation on WinXP where I have Visual Studio I wanted to create a way to preview these reports. I used the ReportViewer control which I found is available in the Toolbox within the Data tab. My plan is to create a Server Control which programatically loads and sets the properties for the ReportViewer control which can then be dropped nicely into a Sharepoint Web Part for deployment. That way I can have a simple website project for Visual Studio to locally preview the web-based reports while also being easily integrated with Sharepoint.

Deploying to Sharepoint requires a few more steps and details. To get Sharepoint to recognize the new Web Part you must either put it into C:\inetpub\wwwroot\bin with the right trust level, or place it into the Global Assembly Cache (GAC). And to put it into the GAC you must sign the assembly. It seems that most bloggers and developers find dropping the signed assembly into the GAC to be the prefered approach. So I chose to stay with the pack. Once the assembly is deployed you must register your Web Part as a SafeControl and create a Web Part Definition file (.dwp).

To me it still feels like a lot of overhead to simply deploy a web-based report, but once it is done hopefully it will not demand any more hoop jumping. I would personally avoid Sharepoint for this purpose and just make these reports available with a basic ASP.NET 2.0 application using the ReportViewer control. It all depends on whether or not the features which Sharepoint provides are enough to justify the overhead.

Yesterday I was looking to feed rows from a query generated DataSet into a strongly typed DataSet. I had not attempted this before. Generally with .NET 2.0 I use a DataObject and stored procedures which conform to the Properties of the object so that I can use a clever routine using reflection to automatically map values. But yesterday I was dealing with existing code which was making use of typed DataSets.

I created a new stored procedure which generated a DataSet with a DataTable which had all of the columns for the typed DataSet along with a few extras and I wanted to add those rows to my empty typed DataTable. At first I attempted to map each value manually but that proved to be cumbersome and almost impossible because at times the Guid values from the database could be NULL so I would have to address each instance. Finally I discovered the method DataTable.ImportRow. It works great!

private void FillCustomerTable(CustomerDataSet.CustomerDataTable
customerDataTable, DataRow[] rows)         {
List customerKeys = new List();
foreach (DataRow row in rows)
{
if (row["ItemUID"] != null &&
!customerKeys.Contains((Guid)row["CustomerUID"]))
{
customerDataTable.ImportRow(row);
customerKeys.Add((Guid)row["CustomerUID"]);
}
}
}

I have been dealing with the trouble of running a remote MySQL server. At home and at work I could get a different IP address which can be troublesome since I firewall port 3306 on the MySQL server and only allow in specific IP addresses. I had to firewall that port because I found attempted exploits were taking place on the MySQL service. The firewall stops the abuse, but then requires me to log in as root each time I have a new IP address and update the allow list for port 3306. It was not a pretty solution, but I have now found away around it.

(more...)