Archive for June, 2007

How To Use SqlParameterCache (Caching Sql Parameters)

This article describes how to cache your SqlParameters.  If you have code that is regularly executed, and that uses a stored procedure, you can benefit from this.  This information, as well as how to properly use this, is very hard to find, even on Google. (Don’t worry, you don’t need to spend all that time looking, the information is already here for you!)

There is a function that Microsoft built that will allow you to get the parameter information from the database stored procedure, and that doesn’t require you to re-build your parameter array every time.  The only thing you will need to do is set the values of the parameters.

What some people don’t realize, is that a similar approach can be taken with regular SQL queries that are not stored procedures!

The downside is that the first call will be slower, because it will need to cache the parameters, however, for each extra call, it will be faster because the parameters are already created.

How can you do this?  You first create your parameters, and store it in the call by calling SqlParameterCache.CacheParameterSet()

Then you call SqlParameterCache.GetCachedParameterSet() to get the cached result.  If the result is null, then you can store the parameters in the cache, otherwise you already have it cached!

Here is an example in VB.NET of how to use SqlParameterCache.CacheParameterSet()

Dim cachedParams as SqlParameter() = SqlHelperParameterCache.GetCachedParameterSet(Config.connstring,strSql)

if cachedParams is nothing then

Dim params as SqlParameter() = { _
    New SqlParameter("@userID", userID), _
    New SqlParameter("@timestamp", timestamp), _
    New SqlParameter("@email",email), _
    New SqlParameter("@timesLoaded",5), _
    New SqlParameter("@country",country), _
    New SqlParameter("@source",source) _

    cachedParams = params
    cachedParams(0).Value = userID
    cachedParams(1).Value = timestamp
    cachedParams(2).Value = email
    cachedParams(3).Value = 5
    cachedParams(4).Value = country
    cachedParams(5).Value = source
End If

SqlHelper.ExecuteNonQuery(Config.connstring, CommandType.Text, strSql, cachedParams)

Now the down side to this, is you lose your ability to dynamically create your SQL parameters (i.e. it depends on the order of your SQL parameters).

Related articles:

Encryption and Decryption for Dummies in .NET

I was trying to figure out the easiest way that I could encrypt some text for the application I was working on. I struggled for a bit to figure out what was the best way to perform encryption in .NET.  Having a piece of open source software that you can look at is very helpful, and I highly recommend it.  You will frequently run into problems that have been solved by others time and time again, and instead of trying to re-invent the wheel, it would be in your best interests to have this toolbelt of code that you are familiar with, and that you can look up.

The software that this site runs on is called Subtext.  You can download the source code or the compiled version and take a look inside at the source code to get some ideas and learn from the experience of those more experienced than you and I.

Encryption is a massive topic and I am not even going to scratch the surface of it, but the code that follows is a simple example of how you can use it.

This code demonstrates a very easy way to Encrypt and Decrypt using Rinjdael’s (AES) algorithm.

It first creates an instance of the algorithm, followed by a method that uses our instance to Encrypt, and another that uses our instance to Decrypt. 

        static SymmetricAlgorithm encryptionAlgorithm = InitializeEncryptionAlgorithm();
        static SymmetricAlgorithm InitializeEncryptionAlgorithm()
            SymmetricAlgorithm rijaendel = RijndaelManaged.Create();
            return rijaendel;

        /// <summary>
        /// Encrypts the string and returns a base64 encoded encrypted string.
        /// </summary>
        /// <param name="clearText">The clear text.</param>
        /// <returns></returns>
        public static string EncryptString(string clearText)
            byte[] clearTextBytes = Encoding.UTF8.GetBytes(clearText);
            byte[] encrypted = encryptionAlgorithm.CreateEncryptor().TransformFinalBlock(clearTextBytes, 0, clearTextBytes.Length);
            return Convert.ToBase64String(encrypted);

        /// <summary>
        /// Decrypts the base64 encrypted string and returns the cleartext.
        /// </summary>
        /// <param name="encryptedEncodedText">The clear text.</param>
        /// <exception type="System.Security.Cryptography.CryptographicException">Thrown the string to be decrypted 
        /// was encrypted using a different encryptor (for example, if we recompile and 
        /// receive an old string).</exception>
        /// <returns></returns>
        public static string DecryptString(string encryptedEncodedText)
                byte[] encryptedBytes = Convert.FromBase64String(encryptedEncodedText);
                byte[] decryptedBytes = encryptionAlgorithm.CreateDecryptor().TransformFinalBlock(encryptedBytes, 0, encryptedBytes.Length);
                return Encoding.UTF8.GetString(decryptedBytes);
            catch (FormatException fe)
                //throw new CaptchaExpiredException("Encrypted encoded text '" + encryptedEncodedText + "' was not valid.", fe);
            catch (CryptographicException e)
                //throw new CaptchaExpiredException("Captcha image expired, probably due to recompile making the key out of synch.", e);

As you can see, all you need is a few methods like this, and you have .NET encryption.  To encrypt data, simply call EncryptString("your string"), and to decrypt data, simply call DecryptString(encryptedString).  I commented out the exceptions, you will need to re-implement them as you like.  Or you can just leave it crashing 😉

Source from SubText-1.9.5-source\Subtext.Web.Controls\Captcha\CaptchaBase.cs

This code is licensed with the new BSD template – Open Source baby!

Update (July 10, 2007)
This method will create a random key every time the application restarts, which may not be what you want.  If this is the case, you can hardcode the key and IV values so that it will always be the same:

        rijaendel.Key = Convert.FromBase64String("HUfIj72qL4OnPu1OlMBKqoufdLSw/nOsIrJiSr+lRgg=");
        rijaendel.IV = Convert.FromBase64String("p1D5hkd4xa5kyX6O7ZXR2A==");

How did I get these values?  You don’t want to use these, but you can generate these values using the built in method above (InitializeEncryptionAlgorithm), and then store it as a string by running Convert.ToBase64String(keyValue)

Read "Better Captcha Through Encryption" for more details on how this code was implemented.

Another option is to set your machineKey value in your web.config

Update: Here is another slightly more optimized way to hardcore your Key and IV. Keep in mind this is not the most secure way, anyone can easily use Reflector on your DLL and get the Key and IV.

            rijaendel.IV = new Byte[] { 56, 151, 249, 160, 183, 47, 5, 42, 90, 5, 207, 241, 11, 166, 166, 173 };
            rijaendel.Key = new Byte[] { 214, 145, 104, 41, 148, 129, 139, 16, 224, 38, 40, 15, 5, 254, 217, 193, 146, 43, 187, 174, 132, 181, 220, 211, 228, 181, 153, 173, 239, 194, 45, 253 };

You shouldn’t use those exact values, but run the debugger and grab new values from GenerateKey() and GenerateIV(). This is more optimized because the values don’t have to be converted back into bytes.

Positive and Negative Testing in C#

In testing, there are two complementary concepts we need to understand–positive testing and negative testing.
For the sake of this example, positive testing refers to testing that things do what they should–for example, if you calculate the sum of two numbers, does it return the correct sum? Does sum(1, 2) return 3?
Negative testing refers to testing that things don’t do what they shouldn’t–for example, if you try to calculate sum("test", 4) you shouldn’t get a valid result!
The key to remember is positive and negative testing, together, give you comprehensive coverage of functionality–you’ve verified that the program does what it should do, and only what it should do, no more. There is no margin for ambiguity.
To clarify this, let’s jump to some code. Say you had a function that returns the greatest common denominator of two numbers:
public sealed class ExtendedMath
        /// Find the greatest common denominator of two numbers.
        /// Note this is based on the Wikipedia pseudocode.
        /// eg. findGcd(6, 8) will return 2
        ///<param name="a">The first number</param>
        ///<param name="b">The second number</param>
        ///<returns>The GCD of a and b</returns>
        public static int FindGcd(int a, int b)
            int t;
            while (b != 0)
                t = b;
                b = a % b;
                a = t;
            return a;
Now, to do some positive testing, you need to verify the function works. You might write the following:
            Debug.Assert(ExtendedMath.FindGcd(6, 8) == 2);
            Debug.Assert(ExtendedMath.FindGcd(3, 4) == 1);
            Debug.Assert(ExtendedMath.FindGcd(27, 27) == 27);
            Debug.Assert(ExtendedMath.FindGcd(3, 21) == 3);
(For the sake of berevity, we omitted writing actual NUnit test-cases.)
If you wrote those "tests", well and good–you’ve now tested that the GCD function returns what it should, given some sort of input. Note you have a few interesting cases–the second case, when the GCD is one; the third case, when the two numbers are equal; and the fourth case, when one of the numbers is the GCD.
In fact, by now, you’re probably feeling a little smug–only six lines of code to calculate the greatest common denominator of two numbers!
Now, let’s get a little fancy, and write a form that’ll take in two numbers in input-boxes (uxA and uxB) and a button (uxCalculateGcd), and show the GCD in a pop-up, like so:
And the code for the button:
public partial class GcdView : Form
        public GcdView()
        private void uxCalculateGcd_Click(object sender, EventArgs e)
            int firstNumber = int.Parse(this.uxA.Text);
            int secondNumber = int.Parse(this.uxB.Text);
            int result = ExtendedMath.FindGcd(firstNumber, secondNumber);
            MessageBox.Show(string.Format("The GCD of {0} and {1} is {2}", firstNumber, secondNumber, result));
If you try this with -8 and 12, we get the message "The GCD of -8 and 12 is 4", which seems correct. But what if we try this with 8 and -12? The function returns -4! Can -4 be the GREATEST common factor of 8 and -12? No, because 1 is always the common factor!
AHA! There, we’ve stumbled onto something–we’ve tested the function with input outside of its valid range, i.e. written a negative test!
To fix this, we should modify our function to return 1 if either of the numbers are negative, like so:
 public static int FindGcd(int a, int b)
            if (a < 0 || b < 0)
                // Not valid input
                return 0;
            int t;
            while (b != 0)
                t = b;
                b = a % b;
                a = t;
            return a;
(New code indicated in bold)
Now, running our function with these values gives us 0. (To be smarter about it, we could do some checking in the GUI and show an error-message if either number is negative; or we could apply some Debug.Assert statements into the beginning of the function to ensure that the input values are 0.)
What about if we enter 23 and "test"?   We get a FormatException, like so:
(To write this as a test case, we would have to set the text-box text, hit the run button, and catch a FormatException; if the exception wasn’t caught, then the test should fail.)
And that’s positive and negative testing in a nut-shell, the Sharp Developer way!


Search Trigger Text SQL Server 2005

If you want to search triggers for text in SQL Server 2005, here is how you can do it.  You would execute this in the case that you are looking for a trigger that updates a certain table, but you cannot find it, and you do not want to go through all the tables one by one, here is what you can execute:

FROM syscomments
WHERE [text] LIKE '%your_search_here%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1

And here is how you can search SQL server stored procedures (external link).

Guid Or Int Primary Key?

When designing a database, and creating the tables and schema, we have to choose carefully what we want our primary key to be. There are many different aspects to this. One aspect is do we want to use a natural key, or a surrogate key? An example of a natural key would be like a SIN or SSN number to represent a person. An example of a surrogate key would be an autonumber column in a database that starts with the value 1 and increases. Both of them have different pros and cons. The natural key if not chosen wisely can have duplicates and cause problems, and may also require you to join on multiple columns. For example, if your last name is your primary key, you will definitely run into cases where you will need a second column, say First Name, to differentiate between different records. In this case, you may even need to use a third one in the case of two people having the same first and last name. Surrogate keys do not suffer from this problem, but are meaningless in themselves (i.e. Record 4151 is much less meaningful than say Employee S Alibhai)
If we have decided to go with a surrogate key, we have a couple of choices, at the least. This article will discuss the benefits of using a GUID datatype over an Integer for your primary key.
First of all, what is a GUID? “A Globally Unique Identifier or GUID is a special type of identifier used in software applications in order to provide a reference number which is unique in the context for which it is used, for example, in defining the internal reference for a type of access point in a software application, or for creating unique keys in a database. While each generated GUID is not guaranteed to be unique, the total number of unique keys is so large that the probability of the same number being generated twice is very small.” (Wikipedia) This idea is so awesome, you can just keep generating GUIDs and never worry about a conflict. It’s not like phone numbers that we run out of them so quickly (especially since cell phones became so popular) that we have to add new area codes, you can just use a GUID and not worry about it!
GUIDs ensure global uniqueness.  Lets say you are one patent office out of 15,000 and you are issuing patents. If you issue a patent using Guids, you can be guaranteed that there is no other patent office that issued a patent with the same GUID as you!
GUIDs can be moved across databases nicely (no need to renumber existing keys). This means that even if you have a distributed database holding different sets of records from one table, you are garaunteed that when you merge them, you will not have any duplicate keys. For example, if you had one database for each sales office, and each sale had a unique key that was a GUID, you will never have two sales with the same GUID.
GUIDs reduce the number of joins required. If you have a hierarchal relationship in a database, say you have a county, that contains school, and a school contains a class, and a class contains students, here is two ways we can do this. 
            Select * from students where classID=5 and schoolID=3 and countyID=1
This is because each county can have a school numbered 1, and each school can have a class numbered 5.. So to ensure uniqueness, we have to add more to our where clause.

However, if we were doing this with GUID, we can just do the following instead:

            Select * from students where classID=’D59B58D1-4A46-4ED1-888F-00450556DA9A’
In this case, there is no possible way (well, maybe one in a quintillion) that there are two classes with the ID ’D59B58D1-4A46-4ED1-888F-00450556DA9A’
However, GUID has a larger data size, and you might want to take that into consideration. 
In conclusion, I feel that the benefits outweigh the costs. In one case, I have been able to pass a GUID and have much shorter queries rather than passing multiple sets of information that made my life much much easier. If I had a choice, I would go for GUID
Reference: Programming Microsoft ADO.NET 2.0 Applications Advanced Topics, Glenn Johnson (Free Chapter)

Mock Objects for Dependencies

By Ashiq Alibhai

Automated testing is a where developers write test-cases (via code, or some sort of action-recording device) which they can then run later at the click of a button. This code (test case) typically sets some things up, does some sort of action, then verifies the outcome against an expectation. Done en-mass, a proper selection of test-cases can help developers and quality-assurance test almost the entire application with little effort.

One question that often comes up with automated-testing is how to create independent tests—tests that don’t require more then one module. For example, if you have a login page, which pulls data from a database, how can you test if the login page is working without depending on the database working?

Enter "mock objects"–mock objects are used to mock expensive or hard-to-create resources. For example, you can create a "mock" database that always returns the same data, pretends it’s deadlocked, and so on—without the overhead of having a real database.

And this is one of the powers of mock objects—you can use them to eliminate dependences between different modules! If your application needs to connect to a database, grab XML from a remote server, and pull up a file from the file-system, then correlate the data—use mock-objects to eliminate the need of those modules to be working correctly. Simply return usable data, and test!

And of course, one of the drawbacks of mock objects is they’re not real—that cases may arise where the mock object is too fake, and although the test cases pass, the application fails on real data.

Mock objects are a tool, just like any other tool; and they can be used for good or for evil. Be wise, and make sure you understand what you’re mocking when you create your mock objects.

An Insider’s Look at Microsoft’s Software Testing

A lot of people dream when they think about working at Microsoft – You get free pop, right?  Well, not really.  More than that, you are building software that is used the world over, influences millions, and has huge worldwide impact.  Plus it doesn’t hurt that it pays well to work for Microsoft.  Imagine if you are building the .NET framework, you are building a tool that is used to build tools for the world.


Did you ever want to take a look inside Microsoft to see how they build and test these puppies?  Here is a brief glimpse at how important quality is to Microsoft, and how hardcore their testing is:


Microsoft supports all software for at least 10 years after the date of its release.  For testing ASP.NET 2.0 and Visual Web Developer, they have ~105,000 test cases and ~505,000 functional test scenarios covered Their team has approximately 1.4 testers for every 1 developer.  Why? Because, They take quality pretty seriously at Microsoft, and because they have a lot of very hard requirements that necessitate careful planning and work to ensure high quality.


Also, Microsoft built their own in house testing software called "MadDog".  MadDog runs on 1200 machines and will identify free machines in the lab, automatically format and re-image them with the appropriate operating system, install the right build on them, build and deploy the tests selected onto them, and then run the tests.  Wow!  Here is a picture


Now everyone knows there is a running joke about Microsoft software, how buggy it is, you know, blue screen of death, right?

Read more about it at  Testing ASP.NET 2.0 and Visual Web Developer


Are you really testing your code properly?

Creating Maintainable Database Queries In C# (with source code)

Creating Maintainable Database Queries In C#

By Ashiq Alibhai

Many applications (and websites, especially) rely on some sort of database to get their work done. As projects and teams grow beyond a one-person-working-in-his-garage model, you can’t rely on the fact that you, or someone who knows the query well, will be the one in charge of modifying it when the time comes. So what are some of the practises you can implement in order to create easily-maintainable and understandable queries?

Create a Data Object!

If you have a website, aim for three-tiered architecture and create objects in your App_Code folder (data/application tier) that call the database—don’t code the query directly in the class! Or, if you have an application, create some centralized folders/classes to perform database queries. The more you can abstract (eg. SomeObject.FetchData vs. SqlHelper.Execute…), the easier it will be if you have to switch data-sources later.

Avoid Stored Procedures!

Stored procedures–while offering a dubious performance upgrade–also come with the tag that they are very difficult to maintain. Not only will newbies to SQL become befuddled by the syntax, you need, but they require database-level permissions to modify it. And, in terms of releasing, it adds the extra maintenance of having to propagate stored procedures along with data—as opposed to a query embedded in a .cs file.

Build with StringBuilder!

C# string-concatenation requires creating a new buffer of the total length of both strings, then copying both into it. If you use concatenation in several places, especially if your code gets run frequently, it may become a bottleneck. Avoid it by using stringBuilder.Append(…) instead of string1 + string2.

Keep it Together!

As much as possible, keep your query in one piece and avoid breaking it up into parts as much as possible. This makes your query more readable, and also more efficient (in terms of string-concatenation, which is very slow because it requires allocating space for a new string and copying over the original). (You can even use the @ sign in front of your constant to allow you to break up the assignment over multiple lines without concatenating at each line – which, if you use proper indenting, also makes your query much easier to read.)

The only exception to keeping your query together is if you have if-conditions in a where clause, such as the following example:

            StringBuilder queryBuilder = new StringBuilder();
                    INNER JOIN ContactPurchases ON
                        Conacts.Id = ContactPurchases.ContactId
                    INNER JOIN Purchases ON
                        Purchases.Id = ContactPurchases.PurchaseId
                    Purchaser.Id = @purchaserId

            if (targetDate != DateTime.MinValue)
                queryBuilder.Append(" AND Purchases.Date = @targetDate");
            // ... similar checks and additions to the query

Of course, there are always reasons to break up query-building into more complicated logic—such as for-loops, cases, etc. as your application requires. The trade-off is yours to consider!

Code SQL over C#!

Wherever possible, code logic into the SQL query instead of the page—so functions like while-loops, cases, checking for null, etc. Should be done inside the query.

Why? Just like keeping your query in one piece, it allows anyone sufficiently skilled in SQL to read and understand the query without the control flow jumping all over the place. Plus, it makes it easy to copy-paste the query and execute it.

Use an SqlParameter Array!

If you have parameters to pass to your query, you should be using SqlParameter instances in order to avoid potential SQL injections. Additionally, instead of building your query line-by-line, like this:

            SqlParameter[] parameters = newSqlParameter[2];
            parameters[0] = newSqlParameter("@purchaserId", purchaserId);
            parameters[1] = newSqlParameter("@targetDate", targetDate);

Build them using the array initializer, like this:

            SqlParameter[] parameters = {
                new SqlParameter("@purchaserId", purchaserId),
                new SqlParameter("@targetDate", targetDate)

This has one main advantage over the array: it doesn’t require you to manually maintain the list (and count) of parameters, which can become a maintenance headache if the number goes over three or four parameters.  Read more about using SqlParameter arrays.

Trust Your Judgement!

Above all, trust your judgement and the judgement of your peers. Sometimes, past (or existing) coders may choose to write queries certain ways. Or maybe you feel that this one piece of logic really should be inside a C# for-loop.

It’s up to you! Ultimately, it’s all a trade-off, and the goal here is not to create the "ultimate" style of writing queries, but only to guide you towards building something that creates readable and maintainable queries.

Our .NET articles

Looking for the rest of our .NET C# articles?  Look no further – Our articles are here

Visual Source Safe Pinning Feature

 We have run into the situation before where we are developing code, but we don’t want other developers to get that code yet because it is not completely developed yet.  However, we still want to take advantage of Source Control and put it in the repository so we have incremental changes and we can rollback incase we do something dumb.  Our first solution to this was to use a “ready” label and then instead of getting latest version, we would do a “Get label ready”, and then the ‘ready’ label was updated when the code was more stable.  However, this didn’t work very well, and maybe it would have made more sense to use many different ‘ready’ labels, such as ready1, ready2, ready3, etc.. so that if we ever needed to revert to an older one, we could do that. 


However, there is a better way to accomplish this with sourcesafe, its called “Pinning”

I created a test.txt and here is the contents: blah blah version 1

Version 1 test.txt–

blah blah version 1

Version 2 test.txt–

blah blah version 2

Version 3 test.txt–

blah blah version 3 BROKENNNN!!!


SourceSafe Pinning Feature Screenshot

Now when you do a get latest version on the file or containing folder, it will give you version 2 (blah blah version 2), not version 3 – BROKENNNN!!!

Here are some more References:

Update (June 29, 2007) – If you pin a file, you CANNOT check it out and continue to work on it.  As usual, SourceSafe takes the cake!  Pinning could have been the perfect way to indicate "this version is stable, so get that one, while i continue to work on it", but it ended up being otherwise 🙁

Here is a blurb from that page:

Use of Pins in Version Control

Visual SourceSafe defines a pin as a marker that designates a specific version of a file as the version that is part of a project. The pin is represented by a pushpin icon, and is added through the Pin command in the History of <name> dialog box. For more information, see How to: Pin a Version.

You can pin any file, but you should do this only when you are not planning to change the marked file. Pinning is most useful when applied to shared files.



Note   When you pin a shared file, you cannot make changes to it until you unpin it using the Unpin command from the History of <name> dialog box.

If you share a pinned version of a file, the projects sharing the file cannot change it. However, if you share an unpinned file, and then pin it in one project, other projects can still change and update the file.

Let’s examine an example of using pins for version control. One developer has prepared a spell checker program. The first time the version of the program is stable, the developer uses pins to mark particular versions of the program code files. He then notifies a developer of a grammar checker program that uses the spell checker code that a good version is available. The second developer can share and branch the code and use the pinned file versions as the basis for his spell checking components of the grammar checker program. He only obtains the last known good code files for the spell checker, unless the first programmer unpins his file versions at some point.

Optimization WordPress Plugins & Solutions by W3 EDGE