onDevelopment+=1;

Avoiding DataTables for Table Valued Parameters by using IEnumerable<SqlDataRecord>.

jcallico@callicode.com — Sun, 19 Feb 2012 16:15:54 GMT

A little intro

The Table Valued Parameters were introduced in SQL Server 2008 as a solution to allow multiple rows of structured data to be passed as parameters to stored procedures. For a brief introduction to this subject please refer to this article.

This feature immediately caught my attention but I’ve avoided it’s usage because I didn’t like the fact that on the examples I saw there was always the need to convert your in memory data structure to a DataTable which is in my opinion such a heavy class to instantiate just to pass a parameter, so I continued using comma separated lists of identifiers or XML for more complex data.

It wasn’t until recently that I had to integrate with existing stored procedures which already expected Table Valued Parameters that I had to dig a little deeper on this subject.

As it turns out the SqlClient supports populating Table Valued Parameters not only from DataTable but also from DbDataReader or IEnumerable<SqlDataRecord>. From these three options I prefer the latter since it’s relatively easy to extent one of the native collections to support IEnumerable<SqlDataRecord> which can then latter be reused if needed and avoiding the need to using the more heavy DataTable.

A simple example using IEnumerable instead of DataTable.

Let’s say that we want to pass a collection of keys and values to a given stored procedure, so let’s start by defining the following SQL objects:

- A User-Defined table representing a dictionary:

CREATE TYPE dbo.DictionaryTable AS TABLE 
(
      [Key] VARCHAR(20),
      [Value] VARCHAR(20)
)

- A Stored Procedure expecting this table as parameter:

CREATE PROCEDURE p_Sample_Stored_Procedure
(
      @Dictionary dbo.DictionaryTable READONLY
)
AS
 
      SET NOCOUNT ON
      
      SELECT * FROM @Dictionary
      
GO

So assuming that in our application we use a IDictionary to store the data:

var dictionary = new Dictionary()
                {
                    {"Key 1", "Value 1"},
                    {"Key 2", "Value 2"}
                };

In our data access layer we can create a class which inherits from Dictionary and implements IEnumerable.

        public class DictionaryDataRecord : Dictionary, IEnumerable
        {
            private string _keyColumnName = "Key";
            private string _valueColumnName = "Value";

            public DictionaryDataRecord()
            {
                
            }

            public DictionaryDataRecord(IEnumerable> dictionary)
            {
                foreach (var item in dictionary)
                {
                    this.Add(item.Key, Convert.ToString(item.Value));
                }
            }

            public virtual string KeyColumnName
            {
                get { return this._keyColumnName; }
                set { this._keyColumnName = value; }
            }

            public virtual string ValueColumnName
            {
                get { return this._valueColumnName; }
                set { this._valueColumnName = value; }
            }

            #region Implementation of IEnumerable

            IEnumerator IEnumerable.GetEnumerator()
            {
                var record = new SqlDataRecord(
                    new SqlMetaData(this.KeyColumnName, SqlDbType.VarChar, 255),
                    new SqlMetaData(this.ValueColumnName, SqlDbType.VarChar, 255));

                foreach (var item in this)
                {
                    record.SetString(0, item.Key);
                    record.SetString(1, item.Value);

                    yield return record;
                }
            }

            #endregion
        }

Note how we need to specify the name of the columns of the target table which can be changed if needed. Also we have a constructor expecting a dictionary so it’s easier to create a new instance of this class using an existing dictionary as source.

In order to pass our in-memory dictionary as a parameter to the stored procedure we do this:

	SqlParameter param = command.Parameters.AddWithValue("@Dictionary", new DictionaryDataRecord(dictionary));
	param.SqlDbType = SqlDbType.Structured;
	param.TypeName = "dbo.DictionaryTable";

Notice that we need to specify the type name of the User-Defined table in the same way we specify VARCHAR or DATETIME types.

So this is how a simple program which executes the stored procedure and then writes the same data bounced back to console will look like:

using (var connection = new SqlConnection(connectionString))
{
	connection.Open();

	var command = new SqlCommand("p_Sample_Stored_Procedure", connection)
	{
		CommandType = CommandType.StoredProcedure,
	};

	SqlParameter param = command.Parameters.AddWithValue("@Dictionary", new DictionaryDataRecord(dictionary));
	param.SqlDbType = SqlDbType.Structured;
	param.TypeName = "dbo.DictionaryTable";

	using (var reader = command.ExecuteReader())
	{
		while (reader.Read())
		{
			Console.WriteLine(string.Format("Key: {0}, Value: {1}", reader["Key"], reader["value"]));
		}
	}
}

In the end

Given the options supported by the SqlClient I personally like this approach better. It may appear more complicated upfront but I think it’s worth it. I’ve always tried to avoid working with DataSet and DataTable instances unless there are no other options available and wanted to do the same here.

More ...

Up with HTML5 but not running yet

jcallico@callicode.com — Sat, 09 Jul 2011 02:40:35 GMT

A couple of days ago I finished reading “HTML5: Up and Running” and I wanted to share my thoughts about this book.

This is one of these books that you can read in one sitting, it’s very well written and full of examples.

If you are a programmer, web designer or somehow familiar with the HTML markup language this book will help you discover the new features in HTML5 but not only that, it will also provide useful examples about how to detect HTML5 features in today’s browsers and will enable you to start taking advantage of these features while remaining compatible with other (should I say older) browsers which are far behind when it comes to HTML5 support.

If, on the other hand, you are a technology enthusiast and have heard everybody using HTML5 as a buzzword this book will show you what the fuzz is all about.

One of the things that became really clear to me after reading this book was that one of the most highlighted features in HTML5, video support, is far from solved, and the battle between the different video containers and codecs for web supremacy is far from over. I was also not fully aware of all the licensing issues surrounding the popular H.264 (MPEG-4) video format.

HTML5 features like Local Storage, Canvas and Web Workers are going to forever change the way we write and use browser based application nowadays not to mention the fact that by using the new semantic HTML elements and microdata our documents are going to be better read and interpreted by other readers and applications allowing us to further act on the information contained on them.

HTML5 is finally here, you better be prepared, but don’t expect that by the end of this book you will be an expert, this is just an introduction to the new world of possibilities that HTML5 is bringing to us.

More ...

Maintaining indexes in MS-SQL

jcallico@callicode.com — Wed, 01 Jun 2011 18:23:14 GMT

More than half of the times I’m asked to troubleshoot performance issues on MS-SQL I end up finding a missing index as the cause of the problem.

I’m sure that as part of the initial database design process the most important indexes are created, but as the application evolves new queries are introduced and verifying if there is an index backing up the new query is usually an afterthought.

How to create an instance of a generic type passing arguments to it’s constructor.

jcallico@callicode.com — Mon, 02 May 2011 19:25:42 GMT

This is one of these things that I keep forgetting how to do and end up having to search my code library every time.

Beware when mapping enumerations using NHibernate

jcallico@callicode.com — Thu, 21 Apr 2011 17:48:00 GMT

I’m almost positive that everybody who has used NHibernate beyond a simple Hello World application has been bitten by this. Today it was my turn.

How to restore a SQL Server 7.0 database backup into SQL Server 2008

jcallico@callicode.com — Sat, 19 Feb 2011 17:01:55 GMT

The Problem

I got the following error when trying to restore an old database backup into my local 2008 SQLEXPRESS instance:

According to the SQL Server 2008 documentation:

The database being restored must be at least version 80 (SQL Server 2000) to restore to SQL Server 2008 R2. SQL Server 2000 or SQL Server 2005 databases that have a compatibility level less than 80 will be set to compatibility 80 when restored.

The tedious workaround

Luckily SQL Server 2005 is able to restore backups taken with version 7. A workaround is to install it as a new instance, restore the old backup file into an empty database, backup this database and restore it into SQL 2008.

SQL Server 2005 Express can be downloaded from here.

The installation process is straightforward, only two steps are worth mentioning:

1- When selecting the instance name and since SQLEXPRESS is already in use by SQL 2008 something else needs to be selected, I chose SQLEXPRESS2005 but anything else will also do the job.

2- If Windows Authentication was selected (default mode) then check the option to add your windows user to the SQL Administrator Role since this option is not checked by default. I missed it the first time and wasn’t able to do much since only had access to the “public” role.

SQL Management Studio 2008 is able to connect to SQL 2005 instances so there is no need to install the Management Studio 2005.

Now that we are able to connect to both instances…

… we simply restore the SQL 7.0 backup into SQL 2005, take a backup of the same database and restore it in 2008.

After the database was restored into 2008 I uninstalled the SQL 2005 instance and was done.

This solution worked but installing an extra instance of SQL just for restoring a database didn’t seem to me like the easier way.

Again this is a very rare situation but I’ll update this article if I find an easier way.

More ...

Sometimes the best way to keep a client is to stop doing business with them

jcallico@callicode.com — Tue, 18 Jan 2011 05:00:00 GMT

… at least for a while.

This is the story of my relationship with a long-time client. I’ve been doing business with them for more than 10 years, but a few months ago I decided that by stopping our relationship I’d be doing them a favor - here is why:

I started working for this company as a full time employee and because it was a small company, I had to do be proficient on a number of different levels, such as software development, network administration, support for existing applications, documentation specialist, meeting with clients, etc. It was a great learning experience for me, but eventually I wanted to concentrate on what really drives me – software development.

So, after working there for 5 years, I decided to find another job. When I informed them of my decision, they asked me to continue maintaining a small server farm that hosted all their applications and services. I accepted, because I wanted to keep myself current with that subject, and more importantly, I considered it fair payback for the years that they had employed me.

After a few months, I started to see the task of remote administration more as a hobby and less as a business. I tried to save this company as much money as I could, even under-billing for hours spent troubleshooting an issue. Due to this mindset, my support-style became reactionary – something I had promised never to do. In my opinion, this evolution occurred over time and resulted from my losing my objectivity and trying to be all things to this client – friend first, and solution provider, second.
The consequences are obsolete infrastructure, and a growing list of support tickets that needed to be resolved.

In the end, I chose to terminate my relationship, because we had both grown beyond what the current situation described. I emailed and called them, detailing my reasons and though it was an initial shock, they understood, and I believe, appreciated my honesty.

What have I learned from all this?

1- Always keep yourself honest and be realistic about the quality of your work. If you don’t have the time, resources, or motivation to provide the best possible service to your clients, don’t keep them as clients.

2- When you are ending a business relationship with a client, always try to make their transition process to the new service provider as smooth as possible – this benefits both the client, and you.

3- Keep your focus on what you really want to do and remove distractions even if you will be impacted financially.

More ...

Extending WCF tracing to support trace file rotation

jcallico@callicode.com — Thu, 30 Dec 2010 05:00:00 GMT

The native support for tracing in WCF is very good but as sometimes is the case is missing a functionality that I happen to need right now: The ability to rotate the trace files or in other words automatically switching to a new trace file based on a time based criteria.

Why do I need this?

I’ve been working on a project that has come to rely heavily in the trace files as a way to keep track of all the calls made by partners. If something goes wrong the trace files are the place to go to find out what really happened. Tracing, which in my opinion should only be enabled to troubleshoot a given problem, is now always on and the trace file gets big, really big, so finding a given message comes with the price of downloading this trace file and then searching for a given text.

The Service Trace Viewer Tool does a decent job opening these big trace files and even lets you define what segment of the trace file gets loaded based on a given time frame but still trying to restrict the size of the trace files has become a necessity.

There is built in support for Circular Tracing which basically switches between two files back and forth when a given size threshold is reached but this is not exactly what I need.

How I would like trace file rotation to be configured

Configuring tracing is a very straight forward process which involves adding the following section to your configuration file.

Ideally I would like to use standard string formatting to timestamp the trace file. For example in order to get a daily trace file I would like to only have to add the following placeholders to the file name:

Extending XmlWriterTraceListener class

If you pay attention to the configuration section above you will notice that the XmlWriterTraceListener class is used to generate the trace file. After taking a look at how this class is implemented I came with the simple idea of inheriting from this class, intercepting the calls to Flash(), Write() and WriteLine() and generating a new file name when it’s needed.

Here is all the code you need. You can copy this class in the App_Code folder in your project.

namespace Callicode.System.Diagnostics
{
    using global::System;
    using global::System.Diagnostics;
    using global::System.IO;
    using global::System.Text;

    /// 
    /// Extends the System.Diagnostics.XmlWriterTraceListener class to include 
    /// datetime formating as part of the trace filename.
    /// Caution:
    /// Because System.Diagnostics.XmlWriterTraceListener is not thread-safe, 
    /// the trace source may lock resources exclusively when outputting traces.
    /// 
    public class TimeStampedXmlWriterTraceListener : XmlWriterTraceListener
    {
        protected string originalFilename = string.Empty;
        protected string formatedFilename = string.Empty;

        public TimeStampedXmlWriterTraceListener(Stream stream)
            : base(stream)
        {
        }

        public TimeStampedXmlWriterTraceListener(Stream stream, string name)
            : base(stream, name)
        {
        }

        public TimeStampedXmlWriterTraceListener(TextWriter writer)
            : base(writer)
        {
        }

        public TimeStampedXmlWriterTraceListener(TextWriter writer, string name)
            : base(writer, name)
        {
        }

        public TimeStampedXmlWriterTraceListener(string filename)
            : base(GetFormatedFileName(filename))
        {
            originalFilename = filename;
            formatedFilename = GetFormatedFileName(filename);
        }

        public TimeStampedXmlWriterTraceListener(string filename, string name)
            : base(GetFormatedFileName(filename), name)
        {
            originalFilename = filename;
            formatedFilename = GetFormatedFileName(filename);
        }

        public override void Flush()
        {
            RecreateWriterIfObsolete();
            base.Flush();
        }

        public override void Write(string message)
        {
            RecreateWriterIfObsolete();
            base.Write(message);
        }

        public override void WriteLine(string message)
        {
            RecreateWriterIfObsolete();
            base.WriteLine(message);
        }

        protected virtual void RecreateWriterIfObsolete()
        {
            string newFormatedFileName;

            if (this.Writer == null || string.IsNullOrEmpty(originalFilename) || string.IsNullOrEmpty(formatedFilename) ||
                formatedFilename.Equals(
                    newFormatedFileName = GetFormatedFileName(originalFilename), StringComparison.InvariantCultureIgnoreCase))
            {
                return;
            }

            // closing existing writer
            this.Writer.Close();

            // creating a new writer using the new filename
            // this is pretty much what the base EnsureWriter method does but since it's internal can't be used here
            Encoding encodingWithFallback = GetEncodingWithFallback(new UTF8Encoding(false));
            string fullPath = Path.GetFullPath(newFormatedFileName);
            for (int i = 0; i < 2; i++)
            {
                try
                {
                    this.Writer = new StreamWriter(fullPath, true, encodingWithFallback, 0x1000);
                    break;
                }
                catch (IOException)
                {
                    newFormatedFileName = Guid.NewGuid() + newFormatedFileName;
                    fullPath = Path.GetFullPath(newFormatedFileName);
                }
                catch (UnauthorizedAccessException)
                {
                    break;
                }
                catch (Exception)
                {
                    break;
                }
            }

            // updating field with new filename
            this.formatedFilename = newFormatedFileName;
        }

        protected static Encoding GetEncodingWithFallback(Encoding encoding)
        {
            // Exactly the base GetEncodingWithFallback was declared private.
            Encoding encoding2 = (Encoding)encoding.Clone();
            encoding2.EncoderFallback = EncoderFallback.ReplacementFallback;
            encoding2.DecoderFallback = DecoderFallback.ReplacementFallback;
            return encoding2;
        }

        protected static string GetFormatedFileName(string filename)
        {
            return string.Format(filename, DateTime.Now);
        }
    }
}

In order to use the new class simply modify the line containing the reference to the XmlWriterTraceListener class.

Caveats

- The XmlWriterTraceListener class was not written with inheritance in mind and some key methods like EnsureWrite were declared private or internal making necessary to duplicate some code that could have been reused otherwise.
- Having to check for the filename every time Flush(), Write() or WriteLine() are invoked adds more latency to the process but it was the only safe way I found to make sure that the data was written to the right file.

More ...

Tags: WCF

Error 128 "ResGen.exe" exited with code -532459699.

jcallico@callicode.com — Mon, 30 Aug 2010 05:00:00 GMT

This weekend I got the following error message when trying to build a VS 2010 solution that I was able to build without any problems 2 days before:

Error 128 "ResGen.exe" exited with code -532459699.

It was obvious to me that the problem was related to the resource files.

After checking the properties for some of the resource files on my solution I noticed that some of them didn't have a the "Custom Tool" property set.

Setting the value of "Custom Tool" to "GlobalResourceProxyGenerator" allowed me to build my solution again.

If you Google this error, the first link is a Microsoft Connect entry created 5 days ago without any recommended workaround yet.

More ...

Tags: VS2010

SEO and removing the www prefix from your site URL

jcallico@callicode.com — Fri, 06 Aug 2010 05:00:00 GMT

I’ve been working on making callicode.com more friendly to search engines. One of the first things to do when trying to improve you site’s ranking is to make sure that your content is only available from a unique URL otherwise it can be considered duplicate content and your site penalized (more details here and here). Since most websites are configured to work with and without the www prefix you have to decide which one is going to be your site’s main domain and redirect all other requests accordingly preferable using a 301 redirection.

I decided that callicode.com was going to be the main domain and started looking for ways to get it done with minimal effort.

First I tried to find a possible built in functionality on DotNetNuke, which is the engine powering callicode.com. Up until version 05.04.04 this functionality is not supported. There are some commercial plug-ins specialised in SEO advertising such functionality but I did not wanted to follow that path since I was sure there was a free alternative out there.

Next I downloaded and configured UrlRewriter.Net. After trying for a while I gave up since couldn’t get the proper redirection to work.

My next option was Managed Fusion URL Rewriter a URL manipulation engine based on the Apache mod_rewrite extension which works as a HTTP Module on your existing .NET site, was easy to configure and got the job done.

Only the following steps are required:

1- Download and unzip the file provided.

2- Copy ManagedFusion.Rewriter.dll to your site’s bin folder.

3- Modify your site’s web.config to include the following sections:

http:http:http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/callicode.comhttp:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/callicode.comhttp:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/managedfusion.comhttp:http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/callicode.comhttp:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/xsdhttp:http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/callicode.comhttp:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/managedFusionhttp:http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/http:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/callicode.comhttp:http://callicode.com/http://callicode.com/callicode.comhttp://callicode.com/rewriter">

4- Create a file named “ManagedFusion.Rewriter.txt” on the root of your site with the following content: