Thanks a lot for sharing this long awaited sample!
Once in a while, one spends painfull ours debugging Transact-SQL scripts and we do know that many times things just take much longer than they should, just because SQL Server developers/decisors never paid due attention to make SQL Error messages more
precise; unfortunately, this kind of stuff does not seem to be valuable enough to get their attention and to save our neuroniums from burning.
In my opinion, your solution would be perfect if it was actived only when target exception occurs. Therefore, I suggest
base.SubmitChanges(failureMode) method should be called, leaving your code to be activated whenever an SqlConnectionException occurs.
Hope this helps someone
namespace YourNameSpace
{
partial class YourLinqDBContext
{
public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
try // because performance matters, offending column should only be search when corresponding exception occurs.
{
base.SubmitChanges(failureMode);
}
catch (System.Data.SqlClient.SqlException sqlex)
{
if (sqlex.Number == 8152) // String or binary data would be truncated
LocateOffendingColumn();
else
throw sqlex;
}
}//override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
private void LocateOffendingColumn()
{
//get hold of the change set
ChangeSet cs = this.GetChangeSet();
//types that we will check for maxlength
Type[] typesToCheck = new Type[] { typeof(string), typeof(System.Data.Linq.Binary) };
//get pending inserts and updates along with the members that can have a max length
var insertsUpdates = (
from i in cs.Inserts.Union(cs.Updates)
join m in this.Mapping.GetTables() on i.GetType() equals m.RowType.Type
select new { Entity = i, Members = m.RowType.DataMembers.Where(dm => typesToCheck.Contains(dm.Type)).ToList() }
).Where(m => m.Members.Any()).ToList();
//check all pending inserts and updates for members containing a value longer than the allowed max length for the db column
foreach (var ins in insertsUpdates)
{
foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
{
int maxLength = GetMaxLength(mm.DbType);
if (mm.MemberAccessor.HasValue(ins.Entity))
{
int memberValueLength = GetMemberValueLength(mm.MemberAccessor.GetBoxedValue(ins.Entity));
if (maxLength > 0 && memberValueLength > maxLength)
{
string ErrorMessage="Member '" + mm.Name + "' in '" + mm.DeclaringType.Name + "' has a value that will not fit into column '" + mm.MappedName + "' (" + mm.DbType + ")";
object OffendingRow = ins;
InvalidOperationException iex = new InvalidOperationException(ErrorMessage);
throw iex;
}//if (maxLength > 0 && memberValueLength > maxLength)
}//if (mm.MemberAccessor.HasValue(ins.Entity))
}//foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
}//foreach (var ins in insertsUpdates)
}
private int GetMaxLength(string dbType)
{
int maxLength = 0;
if (dbType.Contains("(")) { dbType = dbType.Substring(dbType.IndexOf("(") + 1); }
if (dbType.Contains(")")) { dbType = dbType.Substring(0, dbType.IndexOf(")")); }
int.TryParse(dbType, out maxLength);
return maxLength;
}//int GetMaxLength(string dbType)
private int GetMemberValueLength(object value)
{
if (value == null) { return 0; }
if (value.GetType() == typeof(string)) { return ((string)value).Length; }
else if (value.GetType() == typeof(Binary)) { return ((Binary)value).Length; }
else { throw new ArgumentException("Unknown type."); }
}//int GetMemberValueLength(object value) }
}
}
Find exact column sql error from linq
