如何找到违反约束的列?
我有一个强类型数据集,其引发此错误空值,如何找到违反约束的列?
System.Data.ConstraintException:未能启用约束。一个或多个行包含违反非空,唯一或外键约束的值。在System.Data.DataTable.EnableConstraints()处 System.Data.Common.DataAdapter.FillFromReader System.Data.DataTable.EndLoadData()(数据集的数据集,数据表 数据表,字符串srcTable要,DataReaderContainer的DataReader, 的Int32 startRecord, INT32最大记录,的DataColumn parentChapterColumn, 对象parentChapterValue)在 System.Data.Common.DataAdapter.Fill(数据表[]数据表, IDataReader的DataReader的,INT32 startRecord,INT32最大记录)在 System.Data.Common.DbDataAdapter.FillInternal(数据集数据集, DataTable [] datatables,Int32 startRecord,Int32 maxRecords,字符串 srcTable,IDbCommand命令,CommandBehavior行为)在 System.Data.Com mon.DbDataAdapter.Fill(数据表[]数据表,的Int32 startRecord,的Int32最大记录,IDbCommand的命令,的CommandBehavior 行为)在System.Data.Common.DbDataAdapter.Fill(数据表 的dataTable)在 Fruits.DataAccess.FruitsTableAdapters.FruitsExtTableAdapter。 GetFruits(字符串 用户,字符串滤波器)在 Fruits.DataAccess.FruitsDataAccess.GetFruits(字符串用户,字符串 滤波器)在Fruits.WebServices.External.Fruity.GetFruits(字符串 滤波器)
所有的列用1单行填充我正在测试它,
USE [FruitDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fruits](
[ID] [int] NOT NULL,
[CategoryID] [int] NOT NULL,
[Title] [nvarchar](255) NOT NULL,
[URL] [nvarchar](255) NOT NULL,
[Status] [nvarchar](70) NOT NULL,
[Description] [nvarchar](1024) NULL,
[User1] [nvarchar](50) NOT NULL,
[Date] [datetime] NOT NULL,
[User2] [nvarchar](50) NULL,
[Date2] [datetime] NULL,
[Impact] [nvarchar](255) NULL,
[Solution] [nvarchar](1024) NULL,
CONSTRAINT [PK_Fruits] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Fruits] WITH CHECK ADD CONSTRAINT [FK_Fruits_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([ID])
GO
ALTER TABLE [dbo].[Fruits] CHECK CONSTRAINT [FK_Fruits_Categories]
GO
你可以在你的代码中使用此方法。 .NET检查数据集以抛出异常。
public void CheckDataSet(DataSet dataSet)
{
Assembly assembly = Assembly.LoadFrom(@"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.dll");
Type type = assembly.GetType("System.Data.ConstraintEnumerator");
ConstructorInfo ctor = type.GetConstructor(new[] { typeof(DataSet) });
object instance = ctor.Invoke(new object[] { dataSet });
BindingFlags bf = BindingFlags.Instance | BindingFlags.Public;
MethodInfo m_GetNext = type.GetMethod("GetNext", bf);
while ((bool)m_GetNext.Invoke(instance, null))
{
bool flag = false;
MethodInfo m_GetConstraint = type.GetMethod("GetConstraint", bf);
Constraint constraint = (Constraint) m_GetConstraint.Invoke(instance, null);
Type constraintType = constraint.GetType();
BindingFlags bfInternal = BindingFlags.Instance | BindingFlags.NonPublic;
MethodInfo m_IsConstraintViolated = constraintType.GetMethod("IsConstraintViolated", bfInternal);
flag = (bool)m_IsConstraintViolated.Invoke(constraint, null);
if (flag)
Debug.WriteLine("Constraint violated, ConstraintName: " + constraint.ConstraintName + ", tableName: " + constraint.Table);
}
foreach (DataTable table in dataSet.Tables)
{
foreach (DataColumn column in table.Columns)
{
Type columnType = column.GetType();
BindingFlags bfInternal = BindingFlags.Instance | BindingFlags.NonPublic;
bool flag = false;
if (!column.AllowDBNull)
{
MethodInfo m_IsNotAllowDBNullViolated = columnType.GetMethod("IsNotAllowDBNullViolated", bfInternal);
flag = (bool)m_IsNotAllowDBNullViolated.Invoke(column, null);
if (flag)
{
Debug.WriteLine("DBnull violated --> ColumnName: " + column.ColumnName + ", tableName: " + column.Table.TableName);
}
}
if (column.MaxLength >= 0)
{
MethodInfo m_IsMaxLengthViolated = columnType.GetMethod("IsMaxLengthViolated", bfInternal);
flag = (bool)m_IsMaxLengthViolated.Invoke(column, null);
if (flag)
Debug.WriteLine("MaxLength violated --> ColumnName: " + column.ColumnName + ", tableName: " + column.Table.TableName);
}
}
}
}
我没有测试过这个,但我会接受它的努力,谢谢 – Mathematics 2014-11-14 15:14:30
问题在于其中一个列名称在数据集中不匹配,不知道是什么触发了它,但匹配列的名称和返回的列名解决了问题。
我还使用强类型数据集的“预览数据”功能,帮助我找出问题所在。
DataSet/DataTable有属性可以获取有关错误的更多详细信息。
因此,这些细节本身并不属于例外本身,因为你可以这么想,这就是诀窍。见http://www.codeproject.com/Tips/405938/Debugging-DataSet-Constraint-Errors
样品:
catch (ConstraintException)
{
DataRow[] rowErrors = this.YourDataSet.YourDataTable.GetErrors();
System.Diagnostics.Debug.WriteLine("YourDataTable Errors:"
+ rowErrors.Length);
for (int i = 0; i < rowErrors.Length; i++)
{
System.Diagnostics.Debug.WriteLine(rowErrors[i].RowError);
foreach (DataColumn col in rowErrors[i].GetColumnsInError())
{
System.Diagnostics.Debug.WriteLine(col.ColumnName
+ ":" + rowErrors[i].GetColumnError(col));
}
}
}
简短回答:没有 你可以用动态SQL做一些坏事,但我不会建议它。 – Tristan 2014-11-03 09:26:42
谢谢@Tristan,我现在改变了我的问题 – Mathematics 2014-11-03 09:32:03
尝试'SELECT f。* FROM dbo.Fruits AS f WHERE NOT EXISTS(SELECT 1 FROM dbo.Categories AS c WHERE c.id = f.categoryID);'找到哪些行违反FK约束。 – 2014-11-03 09:51:23