Linq2Sql Testing

Question

I have test database and a production database. When developing I of course work against that test database then when deploying I have to semi-manually update the production database (by running a batch-sql-script). This usually works fine but there's a chance for mistakes where the deployed database isn't the same as the test database.

For tables: Is there any way that I can automatically test all entities that I've mapped using linq2sql against a the production database so that all properties etc. exist?

Answer 1

As far as I can tell, there's no way to automatically test before doing a submit. You can however infer it and programmatically check it. I have a controller for each Linq object that I use to marshal the Linq object, and that controller has an IsValid method that goes through and checks the db rules using a technique I saw here: http://www.codeproject.com/KB/cs/LinqColumnAttributeTricks.aspx

I call it with the following code:

  if (address.City.Length > Utilities.LinqValidate.GetLengthLimit(address, "City"))
   throw new ArgumentOutOfRangeException("address.City Max Length Exceeded");

Here's a modified version of the utility that I'm using:

 public static int GetLengthLimit(object obj, string field)
 {
  int dblenint = 0;   // default value = we can't determine the length

  Type type = obj.GetType();
  PropertyInfo prop = type.GetProperty(field);
  // Find the Linq 'Column' attribute
  // e.g. [Column(Storage="_FileName", DbType="NChar(256) NOT NULL", CanBeNull=false)]
  object[] info = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
  // Assume there is just one
  if (info.Length == 1)
  {
   ColumnAttribute ca = (ColumnAttribute)info[0];
   string dbtype = ca.DbType;

   if (dbtype.StartsWith("NChar") || dbtype.StartsWith("NVarChar") ||
    dbtype.StartsWith("Char") || dbtype.StartsWith("VarChar")
    )
   {
    int index1 = dbtype.IndexOf("(");
    int index2 = dbtype.IndexOf(")");
    string dblen = dbtype.Substring(index1 + 1, index2 - index1 - 1);
    int.TryParse(dblen, out dblenint);
   }
  }
  return dblenint;
 }

 public static bool CanBeNull(object obj, string field)
 {
  bool canBeNull = false;

  Type type = obj.GetType();
  PropertyInfo prop = type.GetProperty(field); 
  object[] info = prop.GetCustomAttributes(typeof(ColumnAttribute), true); 
  if (info.Length == 1)
  {
   ColumnAttribute ca = (ColumnAttribute)info[0];
    canBeNull = ca.CanBeNull; 
  }
  return canBeNull;
 }

Answer 2

I use a similar approach during development and I can ensure that the synchronization between the development and production databases can easily become a daunting task if you modify too many tables at once.

I realized that the best approach would be to forget about doing this synchronization manually, it's simply too time consuming and prone to errors, and started using a tool to automate the process.

I've been using the RedGate SQlCompare and I can say that I couldn't live without it anymore. It compares all the structure of the databases, pointing the modifications and applying the changes flawlessly, even in tables with millions of records.

Link to Redgate SQL Compare

Answer 3

Check out sysobjects and syscolumns (and sysindexes). Since these tables don't change, you can write some LINQ to load them up and verify your expectations are met. Or you could do it in SQL scripts instead.

Answer 4

I would like to add my choice, Adept SQLDiff, it may not be as slick as Redgate, but I found it to be more reliable (I evaluated both, although I should say this was some years ago). I have had free updates for years (SQL Server 7 - 2008 are supported). At $320 with the Data Diff option it is very good value (considerably cheaper that Redgate). The developer answers emails personally and rapidly. And no, I don't work for them, just a happy user :)