处理SQL Server中的空GUID和强类型数据集

I have a table in SQL server:

我在SQL服务器中有一个表:

Categories
--------------
CategoryID (uniqueidentifier)
ParentCategoryID (uniqueidentifier) allow nulls

ParentCategoryID is meant to hold a value in CategoryID to indicate which category is the parent. If it has no parent (i.e. it's a top category) then ParentCategoryID should be null.

ParentCategoryID用于在CategoryID中保存一个值,以指示哪个类别是父类。如果它没有父级(即它是*类别),则ParentCategoryID应为null。

I'm using strongly typed datasets (table adapters) and for the properties for ParentCategoryID it does not allow it to be null. I've tried to change the properties for the field in the typed dataset but it says trying to make a guid "empty" or "nothing" is not valid. The only option is to throw an exception on null. This results in an error:

我正在使用强类型数据集(表适配器),而对于ParentCategoryID的属性,它不允许它为null。我试图更改类型化数据集中字段的属性,但它表示尝试使guid“空”或“无”无效。唯一的选择是在null上抛出异常。这会导致错误:

The value for column 'ParentCategoryID ' in table 'Categories' is DBNull.

表'Categories'中'ParentCategoryID'列的值为DBNull。

Is this how it is, or is there a way to handle null GUID/uniqueidentifiers when using typed datasets?

这是这样的,或者在使用类型化数据集时是否有办法处理空GUID / uniqueidentifier?

1 个解决方案

#1

If you have used the Visual Studio generators and everything is detected properly for your table, then a nullable column will generate the following on your strongly typed DataRow:

如果您已经使用了Visual Studio生成器并且已经为您的表正确检测到了所有内容,则可以为空的列将在您的强类型DataRow上生成以下内容:

A public property named for the table Column name ("ParentCategoryID")

以表列名称命名的公共属性(“ParentCategoryID”)

A public method that detects a null entry ("bool IsParentCategoryIDNull()")

检测空条目的公共方法(“bool IsParentCategoryIDNull()”)

A public method that "nulls" the entry ("void SetParentCategoryIDNull()")

一个“空”条目的公共方法(“void SetParentCategoryIDNull()”)

Given that your strongly typed table is named "My" (Generates MyDataTable and MyDataRow), your DataSet is named MyDataSetType, and the instance is named myDataSet:

鉴于您的强类型表名为“My”(生成MyDataTable和MyDataRow),您的DataSet名为MyDataSetType,实例名为myDataSet:

MyDataSetType.MyRow row = myDataSet.My.NewMyRow();
row.ParentCategoryID = Guid.Empty; //OPTION 1: explicitly set GUID
row.SetParentCategoryIDNull(); //OPTION 2: explicitly set Null
myDataSet.My.AddMyRow(row);

You can also look at the implementation of SetParentCategoryID to see what is used to do a "nulling".

您还可以查看SetParentCategoryID的实现,以查看用于执行“归零”的内容。

Further, to detect a "null guid":

此外,要检测“null guid”:

if (row.IsParentCategoryIDNull())
{
  //Do something spectacular
}

So now you have three different types of values to represent state:

所以现在你有三种不同类型的值来表示状态:

null entry in database/dataset (no parent category)

数据库/数据集中的空条目(无父类别)

non-null entry in database (parent category, presumably)

数据库中的非空条目(父类别,大概)

non-null entry in database that is the empty (Guid.Empty) guid (???)

数据库中的非空条目是空(Guid.Empty)guid(???)

When i first ran into this problem, I thought Guid.Empty should have been used to represent a null entry in the database, but that would have required custom handling of the guid type. Using the wrapper functions, the strongly typed dataset can provide consistent handling of any number of nullable columns based on struct-style types.

当我第一次遇到这个问题时,我认为Guid.Empty应该用于表示数据库中的空条目,但这需要自定义处理guid类型。使用包装器函数,强类型数据集可以基于struct样式类型提供对任意数量的可空列的一致处理。

#1