Testing Linq2Sql
I have a test database and a production database. When developing, I work against this test database of course, and then when deploying I have to manually update the production database (by running a batch sql-script). This is usually fine, but there is the potential for errors when the deployed database does not match the test database.
For tables: Is there a way to automatically check all objects that I have mapped using linq2sql in the production database to see all properties, etc. existed?
source to share
I take a similar approach at design time, and I can ensure that synchronization between development and production databases can easily become difficult if you change too many tables at once.
I realized that the best approach was to forget about doing this synchronization manually, just too time consuming and error prone, and started using a tool to automate the process.
I am using RedGate SQlCompare and can say that I can no longer live without it. It compares the entire structure of databases, flawlessly pointing out changes and applying changes, even on tables with millions of records.
source to share
As far as I can tell, there is no way to automatically test before submitting. However, you can do this and check programmatically. I have a controller for each Linq object that I use to marshal a Linq object, and that controller has an IsValid method that goes through and checks the db rules using the 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 the modified version of the utility 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;
}
source to share
I would like to add my choice, Adept SQLDiff , it may not be as smooth as Redgate, but I found it more (I appreciated both, although I would say it was a few years ago). I have had free updates for years (SQL Server 7 - 2008 supported). At $ 320 with Data Diff, this is a very good value (much cheaper than Redgate). The developer responds to emails personally and quickly. And no, I don't work for them, just a happy user :)
source to share