如何对返回表的不同列的数据集运行查询？

I'm trying to pull some data from a SQL table in my dataset using C#.

我正在尝试使用C#从我的数据集中的SQL表中提取一些数据。

In this case I do not need all the columns just a few specific ones, however as I am not pulling back a column with a mandatory NOT NULL, the copy of the table is throwing the exception

在这种情况下,我不需要所有列只是几个特定的列,但是因为我没有撤回具有强制NOT NULL的列,所以表的副本抛出异常

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

I'm sure I can work around this by returning the unNullable column to my table however I want to avoid returning unnecessary data.

我确定我可以通过将unNullable列返回到我的表来解决这个问题,但是我想避免返回不必要的数据。

The query I am using which throws this exception is

我正在使用的查询抛出此异常是

SELECT     DeviceSerialNumber, BuildID, LEMSCredentialsID, LEMSSoftwareID, OwnerID, RestagedDate
FROM         tblDevice
WHERE     (DeviceSerialNumber = @SerialNumber)

This excludes the mandatory column "tblLocationID". In reality though, this column is only mandatory when considering the database as a whole, not when I just need build and software detail for use in my form.

这排除了必填列“tblLocationID”。实际上,这个专栏只是在考虑整个数据库时才是强制性的,而不是在我只需要在我的表单中使用构建和软件细节时。

I am trying to use this query in the following manner.

我试图以下面的方式使用此查询。

private DataTable dtDevice;

dtDevice = taDevice.GetDataByDeviceSN_ForRestage(txtDeviceSerial.Text);

I notice when browsing the preview data, Visual Studio draws columns that are not specified in my SQL including the column tblLOcationID it does not however populate these columns with data.

我注意到在浏览预览数据时,Visual Studio会绘制未在我的SQL中指定的列,包括列tblLOcationID,但它不会使用数据填充这些列。

Is there anyway I can use this data in a temporary table without importing the non-nullable aspect of the column? preferably by not pulling throught the non-selected columns at all?

反正我是否可以在临时表中使用此数据而不导入列的非可空方面?最好不要通过非选择的列?

For completeness, here's the definition (- minus foreign key definitions) of the source table:

为了完整性,这里是源表的定义( - 减去外键定义):

CREATE TABLE [dbo].[tblDevice](
[DeviceSerialNumber] [nvarchar](50) NOT NULL,
[Model] [nvarchar](50) NULL,
[ManufactureDate] [smalldatetime] NULL,
[CleanBootDate] [smalldatetime] NULL,
[BuildID] [int] NULL,
[Notes] [nvarchar](3000) NULL,
[AuditID] [int] NULL,
[LocationID] [int] NOT NULL,
[SimID] [int] NULL,
[LEMSCredentialsID] [int] NULL,
[LEMSSoftwareID] [int] NULL,
[OwnerID] [int] NULL,
[RestagedDate] [smalldatetime] NULL,
[Boxed] [bit] NULL,
CONSTRAINT [PK_tblDevice_1] PRIMARY KEY CLUSTERED 
([DeviceSerialNumber] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

3 个解决方案

#1

I assume taDevice is a tableadapter ? generated with the typed dataset ?

我假设taDevice是tableadapter?使用类型化数据集生成?

You could also generate the "FillDataByDeviceSN" method (you can generate a Get and a Fill)

您还可以生成“FillDataByDeviceSN”方法(您可以生成Get和a Fill)

then you get (pseudo-ish code):

然后你得到(伪ish代码):

tblDeviceDataTable dtService = new tblDeviceDataTable();
dtService.tblLocationID.AllowDbNull = true;
taDevice.FillDataByDeviceSN(dtService,txtDeviceSerial.Text);

#2

The column is mandatory because the metadata tells the client that to add any new rows (or alter rows), values for this column have to be provided. If you aren't altering the data and don't need two-way mapping, something more lightweight like a DataReader might be more appropriate.

该列是必需的,因为元数据告诉客户端要添加任何新行(或更改行),必须提供此列的值。如果您没有改变数据并且不需要双向映射,那么像DataReader更轻量级的东西可能更合适。

#3

Here are a few options:

以下是一些选项:

Create an "untyped dataset" for just the fields you want to use.

仅为要使用的字段创建“无类型数据集”。

Change the NullValue property of the NOT NULL field in your typed dataset from the default of "throw exception" to "empty".

将类型化数据集中NOT NULL字段的NullValue属性从默认的“throw exception”更改为“empty”。

Try what Patrick suggested for setting the EnforceConstraints to False.

尝试Patrick建议将EnforceConstraints设置为False。

#1