SQL Server 2005 varchar(max) in C#

An interesting question at work today: How to properly define a parameter that's varchar(max) in Sql Server 2005 from within C#?

Sure, it should be possible to use the signature without adding the integer value. But what if a custom DataAdapter is being used that does require a value if you supply a string? We could use 4000 as a value, but that kind of defeats the purpose of using the new datatype. 8000 then? Same difference; you still possibly end up with truncated strings. Should we make it 2bn? That's 2GB of memory. Not really an otpion in my book.

After some googling, we found the answer: the size of the parameter should be set to -1. There's an example here, where the -1 value is not explained in detail, but just shown in the code. We tested, and it works like a charm.

Read more!

Humanized dates in C#

I've been looking for a way to make dates in the Forum sections of the site more user-friendly. Did some googling on how to accomplish this in either SQL or C#, but somehow that came up completely blank.

I did manage to find some scripts for some stuff I never heard about, and 1 javascript file. Just my luck. I seriously dislike javascript. Maxxim from the wrox forum will most likely be chuckling now, since he's been advising me to brush up my skills in Javascript ;)

So here's the translation in C#. Since I'm not sure about the copyright notice, I'll just slap a copy of the original in:


/**
* C# translation: Copyright (c) 2007 Peter Schmitz
* http://entropia-online.blogspot.com
*
* Original Javascript code: Copyright (c) 2007 Blake Householder
* http://www.blake8086.com/
*
* Permission is hereby granted, free of charge, to any person
* obtaining a copy of this software and associated documentation
* files (the "Software"), to deal in the Software without
* restriction, including without limitation the rights to use, copy,
* modify, merge, publish, distribute, sublicense, and/or sell copies
* of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
* NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
* BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
* ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
* CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*
*
*/

public string HumanizeDate(DateTime date)
{
DateTime dateNow = DateTime.Now;

// Determine the start of the date's week
DateTime startOfWeek = (date.Date - TimeSpan.FromDays((double)date.DayOfWeek));

// We'll use this variable for monthly comparison
int intMonthCompare = ((date.Year * 12 + date.Month) - (dateNow.Year * 12 + dateNow.Month));

// Do the monthly comparison first, as that's the biggest possible way to group
if (intMonthCompare > 1)
{
return "Beyond next month";
}

if (intMonthCompare < -1)
{
return "Older";
}

if (intMonthCompare == 1)
{
return "Next month";
}

if (intMonthCompare == -1)
{
return "Last month";
}

// Now do the same, but in weeks
TimeSpan ts = (startOfWeek - dateNow.Date);
if (ts.Days > 28)
{
return "Five weeks from now";
}

if (ts.Days > 21)
{
return "Four weeks from now";
}

if (ts.Days > 14)
{
return "Three weeks from now";
}

if (ts.Days > 7)
{
return "Two weeks from now";
}

if (ts.Days > 0)
{
return "Next week";
}

if (ts.Days < -35)
{
return "Five weeks ago";
}

if (ts.Days < -28)
{
return "Four weeks ago";
}

if (ts.Days < -21)
{
return "Three weeks ago";
}

if (ts.Days < -14)
{
return "Two weeks ago";
}

if (ts.Days < -7)
{
return "Last week";
}

// Nothing found so far. Let's see if it's tomorrow, today, or yesterday
ts = date.Date - dateNow.Date;

if (ts.Days == 1)
{
return "Tomorrow";
}

if (ts.Days == 0)
{
return "Today";
}

if (ts.Days == -1)
{
return "Yesterday";
}

// Still nothing? Must be a different day in this week then
return date.DayOfWeek.ToString();
}

Read more!

Reusing command parameters

A while ago, at that hobby I practise during the day (some call it work. I resent that. Work is what I do when I come home), I was writing a piece of VB.NET code (I know... I know...) that would fire off a bunch of stored procedures, all with the exact same parameters.

So I wondered if I really had to define the parameters once for every time I called the procedures, or whether I could actually just change the CommandText.

I created a little test app, and went away. Today, I rewrote the code to C#, and decided to put it up here, in order to show that reusing command parameters is perfectly legal in .NET (and to me, it actually sounds logical too, as it prevents the coder from having to rewrite the same code over and over again).


I created a page with one single button, and two labels (I left their names unchanged, as I was doing this during worktime, and I wanted to have the test take as little time as possible.

I added the code, with a few comments thrown in for clarity, and used the labels to post the ID's of the first records I got back from my stored procedures.

Next I fired off the code, while keeping an eye on my profiler, and sure enough, both the stored procedures were executed. I then changed the code a little and increased the value for the second stored procedure, in order to see if that would still work. It did:



Click here to download the sample project and test it for yourself. The project assumes you have the Adventureworks database installed. you will have to create the two stored procedures usp_Test1, and usp_Test2 as well (they're also included in the ZIP file).

Read more!

Welcome!

Hi there,

We'd like to take the time to welcome you to the official developer blog of http://www.entropiaonline.com/. This blog will be mostly used to describe some pieces of code that we found to be tremendously helpful while developing the site, discussion of programming techniques, and as a way for us to get feedback from our users as to what they would like us to work on next.

As some initial information, the techniques we will be using include ASP.NET (2.0), SQL Server 2005, and C#. Once we feel the content of the site is to our liking, we will most likely also look into AJAX to enhance the user experience.

Thanks for stopping by!

Read more!