Bee Eee Blog

Here’s a helpful, and perhaps dangerous, little tidbit of code for SQL Server 2005. It deletes all of the data from all of the tables. To be completely effective, the command may need to be run more than once because of foreign keys. Basically run it until there aren’t any errors. Then the db is clean and empty of all data.

exec sp_MSForeachTable "delete from ?"

the sp_MSForeachTable is a very useful system stored procedure.

There are four different string comparisons for handling string fields with linq to sql.  They are as follows:

The exact macth

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column == "Exact Match"
  select row;

Begins With:

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.StartsWith("Ex")
  select row;

produces an sql match string “Ex%”

Contains

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.Contains("Ex")
  select row;

Which is equivalent to “%Ex%”

Ends With

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.EndsWith("Ex")
  select row;

Which is equivalent to “%Ex”

I’ve recently been writing an application that builds a linq to sql query dynamically.  For instance:

MyDataContext dc() = new MyDataContext;
var query = dc.MyTables;
query = query.Where( t=>t.id > 10);

and so I want to order it by a certain column:

query = query.OrderBy( t=>t.id )

but the problem is the order direction isn’t know before hand so at first I was stuck with the following:

if ( descending )
    query = query.OrderByDescending( t=>t.id);
else
    query = query.OrderBy(t=>t.id);

Now if there is only a few possible columns to sort against it isn’t too bad.  But when you have 10 different possible columns it really makes a lot of extra work just to control to sort direction.  So I made a little helper class that allows the following.  It works with linq and linq to sql:

query = query.OrderByAscDec( t=>t.id, descending );

here’s the static class that implments the OrderByAscDec function:

static class VPLinqExtensions
{
	public static IOrderedQueryable<TSource> OrderByAscDes<TSource, TKey>(
		this IQueryable<TSource> source,
		System.Linq.Expressions.Expression<Func<TSource,
		TKey>> keySelector, bool IsDescending)
	{
		if (IsDescending)
			return source.OrderByDescending(keySelector);
		else
			return source.OrderBy(keySelector);
	}
}