Config Files and Dlls.

Well - A request recently came up on CodeProject asking how to associate a config file with a DLL and then use that configuration file. Like me you would think that this would be an easy thing to do, and that it would be taken care of for you automatically. Unfortunately, it would seem that this isn’t the case - it’s easy enough to do, but you have to do it yourself. One way to do it would be to use XML to read the configuration file directly and then deal with it from there, but it turns out that there is another way that allows you to use the ConfigurationManager directly. To do this, you need to load the application into a separate application domain and then use remoting to access it. This does give you the ability to load plugins with their own config file and isolate them from the main process just in case they are a bit naughty.

Here’s a quick overview of the process - suppose you have a config file for your plugin Dll that contains the following settings:

<appSettings>
  <add key="PluginName" value="My Funky Plugin" />
  <add key="RequiredVersion" value="2.1" />
<appSettings>

Your Dll class may implement the following interface:

public interface IPluginConfiguration
{
  string PluginName { get ; }
  string RequiredVersion { get ; }
} public class MyPlugin : IPluginConfiguration
{
  public string PluginName
  {
    get
    {
      return ConfigurationManager.AppSettings["PluginName"];
    }
  }
  public string RequiredVersion
  {
    get
    {
      return ConfigurationManager.AppSettings["RequiredVersion"];
    }
  }
}

Now, in your calling application you could do the following:

private IPluginConfiguration _config;
public void ActivatePlugin(string configFile, string domainName)
{ 
  AppDomainSetup setup = new AppDomainSetup(); 
  setup.ConfigurationFile = configFile; 
  AppDomain domain = AppDomain.CreateDomain(domainName, null, setup); 
 
  _config = (IPluginConfiguration)domain.CreateInstanceAndUnwrap("MyDll", "MyDll.MyDllClass");
  Console.WriteLine("Name {0}", _config.PluginName);
}

There you go - you can now refer to the localised config file. It’s that simple.

Sql Server, GetDate and Linq to Sql.

OK, you’ve put a default value of GETDATE() on a column in your database and you’re inserting a record via InsertOnSubmit. Oh no, the current server date isn’t applied to your record. How can you get the server date and apply it?

Well, it turns out that there’s a really simple trick that just requires a little bit of skullduggery on your part. (I like that word). Create a partial class that corresponds to your data context. In that class, put the following code:

[Function(Name="GetDate", IsComposable=true)]
public DateTime GetSystemDate()
{
  MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
  return (DateTime)this.ExecuteMethodCall(this, mi, new object[]{}).ReturnValue;
}

Then, all your code needs to do is call GetSystemDate() whenever you need to the current date from SQL.

Note: You may wonder why I’ve gone to all of this trouble, rather than using DateTime.Now in the client code. By using this method, we get the date and time based on the database server, rather than on client connections which may be set to different times.

Refining queries in Linq.

The more I play around with Linq, the more it really appeals to me. It really is amazing how powerful it is, and how flexible it can actually make your applications. “Examples” I hear you cry, “give me examples”. Who am I to deny you when you ask so nicely?

Quite often, when an record is deleted in a database, the application design mandates that the record isn’t physically deleted. Instead, it is given a status of Deleted. This means that retrieval of live records needs to take this status into account. Now, in stored procedures you would add a check for the status in each query that you perform against one of these tables. As you can imagine, this quickly becomes very tedious.

So, how can Linq help to make this situation better? Well one of the tricks that you can employ with Linq is refining queries, so you start with a general query and then refine it until you get to a specific query. So, how does this work in practice? Well, take a look at the following query to get all of the records from a table (cunningly called MyTable in this example):

protected virtual IQueryable<MyTable> GetAll()
{
  IQueryable<MyTable> query = from myTable in context.MyTable // The context is the DataContext.
     select myTable;
  return query;
}

Now, as you can see, this query retrieves all of the records from MyTable regardless of status. However, we’ve already stated that we want a query that retrieves records that have been marked as Deleted (in MyTable, there’s a bit field called IsDeleted that states whether or not the record has been deleted). It turns out that it’s really easy to refine these queries. In the next example, we are going to provide methods that refine the original query further and further. We want a method that gets active products, and another method that gets active customers.

protected virtual IQueryable<MyTable> GetAllLiveItems()
{
  IQueryable<MyTable> query = GetAll();
  query = from myTable in query
          where myTable.IsDeleted == false
          select myTable;
}  public List<MyTable> GetActiveProducts(string name)
{
  IQueryable<MyTable> query = GetAllLiveItems();
  query = from myTable in query
          where myTable.ProductName == name
          select myTable;
  return query.ToList<MyTable>();
}  public List<MyTable> GetActiveCustomers(string name)
{
  IQueryable<MyTable> query = GetAllLiveItems();
  query = from myTable in query
          where myTable.CustomerName == name
          select myTable;
  return query.ToList<MyTable>();
}

I know, the table normalisation sucks big time, but this is intended to highlight how the queries have been refined. As you can see, the GetAllLiveItems method refines the original GetAll query so that it retrieves records that haven’t been deleted. The GetActiveCustomers and GetActiveProducts queries take the query from GetAllLiveItems and refine it further still to return the appropriate list. It’s this ability to refine and extend broad stroke queries that, in my mind, opens Linq up as a real rapid database application development tool.

Hmmm.

Why is SQL called Structured Query Language? You can do so much more with it than simply query. Sorry, just a thought.

CodeProject MVPs

Well, I’ve just been made a CodeProject MVP. This is a great honour for me as I’m among some illustrious company here. There are some truly amazing people in this list, and I guess I’m just there to make the coffee for them.

Let me offer my congratulations to all of the other MVPs and to all of the Microsoft MVPs. You guys and girls really are the best. Thanks also to Chris Maunder and his fantastic team for making CodeProject my second home, it’s really amazing what you’ve accomplished over the last year.

Finally, thanks to everybody who’s posted a question that I could answer or posted a piece of crap that I can pour scorn on. I appreciate it.

Extending Linq.

Sorry for the delay in continuing with the discussions on regular expressions, but I got a little sidetracked with the upgrade to Visual Studio 2008 (.NET 3.5), and all the “goodies” that it brings to you. For the moment I’m going to be putting the discussion on regular expressions to one side. I will get back to them but, for the moment, I want to take you on a journey into .NET 3.5.

Possibly the biggest headline in .NET 3.5 is the introduction of Linq. I have to admit that I was highly skeptical about how useful it will actually be in the “real world”, but having played around with it for a little while I have to admit that I’m actually quite impressed. It’s a well thought out area that actually does do a lot to increase productivity. Now, in this entry I’m not going to delve into the internals of Linq and how to use it. Instead, I’m going to talk about a feature of .NET 3.5 and how it can be used to “extend” the behaviour of Linq. Specifically, we’re going to look at how to use Extension Methods to add the ability to return a DataSet and DataTable from Linq in the same way you’d return a generic list.

The following code shows what you actually need to do to extend the IQueryable object in Linq. For the moment, don’t worry about what IQueryable actually does - once you’ve used Linq for a little while it becomes apparent what this interface is for, and where it fits into the big Linq picture.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient; namespace LinqTesting
{
 /// <summary>
 /// This class contains miscellaneous extension methods for Linq to Sql.
 /// </summary>
 public static class LinqDataExtensions
 {
  /// <summary>
  /// Add the ability to return a DataTable based on a particular query
  /// to a queryable object.
  /// </summary>
  /// <param name="extenderItem">The IQueryable object to extend.</param>
  /// <param name="query">The query to execute.</param>
  /// <param name="tableName">The name of the datatable to be added.</param>
  /// <returns>The populated DataTable.</returns>
  public static DataTable ToDataTable(this IQueryable extenderItem,
    DbCommand query,
    string tableName)
  {
    if (query == null)
    {       throw new ArgumentNullException("query");
    }
    SqlCommand cmd = (SqlCommand)query;
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = cmd;
    DataTable dt = new DataTable(tableName);
    try
    {
    cmd.Connection.Open();
    adapter.Fill(dt);
    }
    finally
    {
    cmd.Connection.Close();     }
    return dt;
  }
  /// <summary>
  /// Add the ability to return a DataSet based on a particular query
  /// to a queryable object.
  /// </summary>
  /// <param name="extenderItem">The IQueryable object to extend.</param>
  /// <param name="query">The query to execute.</param>
  /// <param name="tableName">The name of the DataTable to be added.</param>
  /// <returns>The populated dataset.</returns>
  public static DataSet ToDataSet(this IQueryable extenderItem,
    DbCommand query,
    string tableName)
  {
    if (query == null)
    {
    throw new ArgumentNullException("query");
    }
    return ToDataSet(extenderItem, query, null, tableName);
  }   /// <summary>
  /// Add the ability to return a dataset based on a particular query
  /// to a queryable object.
  /// </summary>
  /// <param name="extenderItem">The IQueryable object to extend.</param>
  /// <param name="query">A generic dictionary containing the
  /// query to execute along with the name of the table to add it to.</param>
  /// <returns>The populated dataset.</returns>
  public static DataSet ToDataSet(this IQueryable extenderItem,
    Dictionary<string, DbCommand> query)
  {
    if (query == null)
    {
    throw new ArgumentNullException("query");
    }
    if (query.Count == 0)
    {
    throw new ArgumentException("query");
    }
    return ToDataSet(extenderItem, query, null);
  }   /// <summary>
  /// Add the ability to return a dataset based on a particular query   /// to a queryable object.
  /// </summary>
  /// <param name="extenderItem">The IQueryable object to extend.</param>
  /// <param name="query">A generic dictionary containing the
  /// query to execute along with the name of the table to add it to.</param>
  /// <param name="dataSet">An optional DataSet. This allows application
  /// to add multiple tables to the dataset.</param>
  /// <returns>The populated dataset.</returns>
  public static DataSet ToDataSet(this IQueryable extenderItem,
    Dictionary<string,
    DbCommand> query,
    DataSet dataSet)
  {
    if (query == null)
    {
    throw new ArgumentNullException("query");
    }
    if (query.Count == 0)
    {
    throw new ArgumentException("query");
    }
    if (dataSet == null)       dataSet = new DataSet();  

    foreach (KeyValuePair<string, DbCommand> kvp in query)
    {
      dataSet = LinqDataExtensions.ToDataSet(extenderItem,
        kvp.Value,
        dataSet,
        kvp.Key);
    }
    return dataSet;
  }   /// <summary>
  /// Add the ability to return a dataset based on a particular
  /// query to a queryable object.
  /// </summary>
  /// <param name="extenderItem">The IQueryable object to extend.</param>
  /// <param name="query">The query to execute.</param>
  /// <param name="dataSet">An optional DataSet. This allows
  /// application to add multiple tables to the dataset.</param>
  /// <param name="tableName">The name of the DataTable to be added.</param>
  /// <returns>The populated dataset.</returns>
  public static DataSet ToDataSet(this IQueryable extenderItem,
    DbCommand query,
    DataSet dataSet,
    string tableName)
  {
    if (query == null)
    {
    throw new ArgumentNullException("query");
    }
    if (dataSet == null)
      dataSet = new DataSet();
    DataTable tbl = LinqDataExtensions.ToDataTable(extenderItem,
      query,
      tableName);
    if (tbl != null)
    {
      if (dataSet.Tables.Contains(tableName))
        dataSet.Tables.Remove(tableName);
      dataSet.Tables.Add(tbl);
    }
    return dataSet;
  }
 }
}

A couple of points to note about the class. Extension methods have to be in static classes, and they have to be static methods. In order to note what class is being extended, you use the “this” keyword before the first parameter. The code itself is fairly self explanatory and doesn’t do anything clever. The clever bit actually occurs in the Linq side. Here’s an example:

public DataSet GetDataSet()
{
    IQueryable q = GetAllQuery();
    return q.ToDataSet(context.GetCommand(GetAllQuery()), null, "MyTable");
} 

private IQueryable<MyTable> GetAllQuery()
{
    IQueryable<MyTable> q = from p in context.MyTables
                        select p;     return q;
}

The context is the DataContext item that you will create when you import the tables using Visual Studio. And that’s it. Using similar techniques you can extend other parts of Linq as you see fit.