XSD DataSets and ignoring foreign keys

Question: I have a pretty standard table set-up in a current application using the .NET XSD DataSet and TableAdapter features. My contracts table consists of some standard contract information, with a column for the primary department. This column is a foreign key to my Departments table, where I store the basic department name, id, notes. This is all setup and functioning in my SQL Server.
When I use the XSD tool, I can drag both tables in at once and it auto detects/creates the foreign key I have between these two tables. This works great when I'm on my main page and am viewing contract data.
However, when I go to my administrative page to modify the department data. I typically do something like this:

Dim dtDepartment As New DepartmentDataTable()
Dim taDepartment As New DepartmentTableAdapter()

taDepartment.Fill(dtDepartment)

However, at this point an exception is thrown saying to the effect that there is a foreign key reference broken here, I'm guessing since I don't have the Contract DataTable filled.
How can I fix this problem? I know I can simply remove the foreign key from the XSD to make things work fine, but having the additional integrity check there and having the XSD schema match the SQL schema in the database is nice.

Solution:  You can try turning Check-constraints off on the DataSet (it's in its properties), or altering the properties of that relationship, and change the key to a simple reference - up to you.

ASP.NET Site Maps

Question: Has anyone got experience creating SQL-based ASP.NET site-map providers?

I've got the default XML file web.sitemap working properly with my Menu and SiteMapPath controls, but I'll need a way for the users of my site to create and modify pages dynamically.
I need to tie page viewing permissions into the standard ASP.NET membership system as well.

Solution:  Here's my fix in BuildSiteMap():

SiteMapNode node = GetSiteMapNodeFromReader(reader);
string url = node.Url;
if (url.Contains(":"))
{
    string garbage = Guid.NewGuid().ToString();  // SiteMapNode needs unique URLs
    node.Url = "~/dummy_" + garbage + ".aspx";
    AddNode(node, _root);
    node.Url = url;
}
else
{
    AddNode(node, _root);
}

SQLDependency caching is cool, but if you don't want to make a trip to the DB everytime your menu loads (to check to see if the dependency has changed) and your menus don't change very often, then why not use HttpRuntime.Cache instead?

public override SiteMapNode RootNode
{
    get
    {
        SiteMapNode temp = (SiteMapNode)HttpRuntime.Cache["SomeKeyName"];
        if (temp == null)
        {
            temp = BuildSiteMap();
            HttpRuntime.Cache.Insert("SomeKeyName", temp, null, DateTime.Now.AddHours(1), Cache.NoSlidingExpiration);
        }
        return temp;
    }
}

Decoding T-SQL CAST in C#/VB.NET

Question: Recently our site has been deluged with the resurgence of the Asprox botnet SQL injection attack. Without going into details, the attack attempts to execute SQL code by encoding the T-SQL commands in an ASCII encoded BINARY string. It looks something like this:

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500...06F007200%20AS%20NVARCHAR(4000));EXEC(@S);--

I was able to decode this in SQL, but I was a little wary of doing this since I didn't know exactly what was happening at the time.
I tried to write a simple decode tool, so I could decode this type of text without even touching SQL Server. The main part I need decoded is:

CAST(0x44004500...06F007200 AS
NVARCHAR(4000))

I've tried all of the following commands with no luck:

txtDecodedText.Text =
    System.Web.HttpUtility.UrlDecode(txtURLText.Text);
txtDecodedText.Text =
    Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.Unicode.GetString(Encoding.Unicode.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.ASCII.GetString(Encoding.Unicode.GetBytes(txtURLText.Text));
txtDecodedText.Text =
    Encoding.Unicode.GetString(Convert.FromBase64String(txtURLText.Text));

What is the proper way to translate this encoding without using SQL Server? Is it possible? I'll take VB.NET code since I'm familiar with that too.

Okay, I'm sure I'm missing something here, so here's where I'm at.
Since my input is a basic string, I started with just a snippet of the encoded portion - 4445434C41 (which translates to DECLA) - and the first attempt was to do this...

txtDecodedText.Text = Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(txtURL.Text));

...and all it did was return the exact same thing that I put in, since it converted each character into is byte.
I realized that I need to parse each two characters into a byte manually since I don't know of any methods yet that will do that, so now my little decoder looks something like this:

while (!boolIsDone)
{
    bytURLChar = byte.Parse(txtURLText.Text.Substring(intParseIndex, 2));
    bytURL[intURLIndex] = bytURLChar;
    intParseIndex += 2;
    intURLIndex++;

    if (txtURLText.Text.Length - intParseIndex < 2)
    {
        boolIsDone = true;
    }
}

txtDecodedText.Text = Encoding.UTF8.GetString(bytURL);

Things look good for the first couple of pairs, but then the loop balks when it gets to the "4C" pair and says that the string is in the incorrect format.
Interestingly enough, when I step through the debugger and to the GetString method on the byte array that I was able to parse up to that point, I get ",-+" as the result.
How do I figure out what I'm missing - do I need to do a "direct cast" for each byte instead of attempting to parse it?

Solution: did some more poking and realized that I did need to do a double conversion, and eventually worked out this little nugget:

Convert.ToString(Convert.ToChar(Int32.Parse(EncodedString.Substring(intParseIndex, 2), System.Globalization.NumberStyles.HexNumber)));

From there I simply made a loop to go through all the characters 2 by 2 and get them "hexified" and then translated to a string.

Anatomy of a “Memory Leak”

Question: In .NET perspective:

• What is a Memory Leak?
• How can you determine whether your application leaks? What are the effects?
• How can you prevent a memory leak?
• If your application has memory leak, does it go away when the process exits or is killed? Or do memory leaks in your application affect other processes on the system even after process completion?
• And what about unmanaged code accessed via COM Interop and/or P/Invoke?

I have some answers for these questions myself, but they are incomplete. What do you think?

Solution: The best explanation I've seen is in Chapter 7 of the free Foundations of Programming ebook.

Basically, in .NET a memory leak occurs when referenced objects are rooted and thus cannot be garbage collected. This occurs accidentally when you hold on to references beyond the intended scope.
You'll know that you have leaks when you start getting outofmemoryexceptions or your memory usage goes up beyond what you'd expect (perfmon has nice memory counters).
Understanding .NET's memory model is your best way of avoiding it. Specifically, understanding how the garbage collector works and how references work (again, I refer you to chapter 7 of the ebook). Also, be mindful of common pitfalls, probably the most common being events. If object A registered to an event on object B, then object A will stick around until object B disappears because B holds a reference to A. The solution is to unregister your events when you're done.
Of course, a good memory profile will let you see your object graphs and explore the nesting/referencing of your objects to see where references are coming from and what root object is responsible (red-gate ants profile, JetBrains dotMemory, memprofiler are really good choices, or you can use the text-only windbg and sos, but I'd strongly recommend a commercial/visual product unless you're a real guru).
I believe unmanaged code is subject to typical memory leaks of unamanged code, except that references shared between the two are managed by the garbage collector. Could be wrong about this last point.

Paging a collection with LINQ

Question: How do you page through a collection in LINQ given that you have a startIndex and a count?

Solution:  Following natural way of paginating a LINQ collection.

var query = from i in ideas
            select i;
var pagedCollection = query.InPagesOf(10);
var pageOfIdeas = pagedCollection.Page(2);

How do I get a distinct, ordered list of names from a DataTable using LINQ?

Question: I have a DataTable with a Name column. I want to have a collection of the unique names ordered alphabetically. The following query ignores the order by clause.

var names =
    (from DataRow dr in dataTable.Rows
    orderby (string)dr["Name"]
    select (string)dr["Name"]).Distinct();

Why does the orderby not get enforced?

Solution:  To make it more readable and maintainable, you can also split it up into multiple LINQ statements.

1. First, select your data into a new list, let's call it x1, do a projection if desired
2. Next, create a distinct list, from x1 into x2, using whatever distinction you require
3. Finally, create an ordered list, from x2 into x3, sorting by whatever you desire

Reliable timer in a console application

Question: I am aware that in .NET there are three timer types (see Comparing the Timer Classes in the .NET Framework Class Library). I have chosen a threaded timer as the other types can drift if the main thread is busy, and I need this to be reliable.

The way this timer works in the control of the timer is put on another thread so it can always tick along with the work begin completed on the parent thread when it is not busy.
The issue with this timer in a console application is that while the timer is ticking along on another thread the main thread is not doing anything so the application closes.
I tried adding a while true loop, but then the main thread is too busy when the timer does go off.

Solution:

You can use something like Console.ReadLine() to block the main thread, so other background threads (like timer threads) will still work. You may also use an AutoResetEvent to block the execution, then (when you need to) you can call Set() method on that AutoResetEvent object to release the main thread. Also ensure that your reference to Timer object doesn't go out of scope and garbage collected.

Check for changes to an SQL Server table?

Question: How can I monitor an SQL Server database for changes to a table without using triggers or modifying the structure of the database in any way? My preferred programming environment is .NET and C#.
I'd like to be able to support any SQL Server 2000 SP4 or newer. My application is a bolt-on data visualization for another company's product. Our customer base is in the thousands, so I don't want to have to put in requirements that we modify the third-party vendor's table at every installation.
By "changes to a table" I mean changes to table data, not changes to table structure.
Ultimately, I would like the change to trigger an event in my application, instead of having to check for changes at an interval.

The best course of action given my requirements (no triggers or schema modification, SQL Server 2000 and 2005) seems to be to use the BINARY_CHECKSUM function in T-SQL. The way I plan to implement is this:
Every X seconds run the following query:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);

And compare that against the stored value. If the value has changed, go through the table row by row using the query:

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);

And compare the returned checksums against stored values.

Solution:

 Take a look at the CHECKSUM command:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

That will return the same number each time it's run as long as the table contents haven't changed. 

Filling a DataSet or DataTable from a LINQ query result set

Question: How do you expose a LINQ query as an ASMX web service? Usually, from the business tier, I can return a typed DataSet or DataTable which can be serialized for transport over ASMX.

public static MyDataTable CallMySproc()
{
    string conn = "...";

    MyDatabaseDataContext db = new MyDatabaseDataContext(conn);
    MyDataTable dt = new MyDataTable();

    // execute a sproc via LINQ
    var query = from dr
                in db.MySproc().AsEnumerable
                select dr;

    // copy LINQ query resultset into a DataTable -this does not work !    
    dt = query.CopyToDataTable();

    return dt;
}

How can I do the same for a LINQ query? Is there a way to populate a typed DataSet or DataTable via a LINQ query?

Solution: 

As mentioned in the question, IEnumerable has a CopyToDataTable method:

IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
    select order;

// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();

Note: To everyone wondering why CopyToDataTable() doesn't work on their machine: This function is not part of .NET 3.5 SP1 nor will it be of .NET 4.0 and onwards.

Calculate relative time in C#

Question: Given a specific DateTime value, how do I display relative time, like:

• 2 hours ago
• 3 days ago
• a month ago

Solution:

const int SECOND = 1;
const int MINUTE = 60 * SECOND;
const int HOUR = 60 * MINUTE;
const int DAY = 24 * HOUR;
const int MONTH = 30 * DAY;

var ts = new TimeSpan(DateTime.UtcNow.Ticks - yourDate.Ticks);
double delta = Math.Abs(ts.TotalSeconds);

if (delta < 1 * MINUTE)
  return ts.Seconds == 1 ? "one second ago" : ts.Seconds + " seconds ago";

if (delta < 2 * MINUTE)
  return "a minute ago";

if (delta < 45 * MINUTE)
  return ts.Minutes + " minutes ago";

if (delta < 90 * MINUTE)
  return "an hour ago";

if (delta < 24 * HOUR)
  return ts.Hours + " hours ago";

if (delta < 48 * HOUR)
  return "yesterday";

if (delta < 30 * DAY)
  return ts.Days + " days ago";

if (delta < 12 * MONTH)
{
  int months = Convert.ToInt32(Math.Floor((double)ts.Days / 30));
  return months <= 1 ? "one month ago" : months + " months ago";
}
else
{
  int years = Convert.ToInt32(Math.Floor((double)ts.Days / 365));
  return years <= 1 ? "one year ago" : years + " years ago";
}

Calculate age in C#

Question: Given a DateTime representing a person's birthday, how do I calculate their age in years?

Solution:

// Save today's date.
var today = DateTime.Today;
// Calculate the age.
var age = today.Year - birthdate.Year;
// Go back to the year the person was born in case of a leap year
if (birthdate > today.AddYears(-age)) age--;

While applying opacity to a form should we use a decimal or double value?

Question: I want to use a track-bar to change a form's opacity.
This is my code:

decimal trans = trackBar1.Value / 5000;
this.Opacity = trans;

When I build the application, it gives the following error:

Cannot implicitly convert type 'decimal' to 'double'.

I tried using trans and double but then the control doesn't work. This code worked fine in a past VB.NET project.

Solution:

An explicit cast to double isn't necessary.

double trans = (double)trackBar1.Value / 5000.0;

Identifying the constant as 5000.0 (or as 5000d) is sufficient:

double trans = trackBar1.Value / 5000.0;
double trans = trackBar1.Value / 5000d;