c# - 使用 SqlDataAdapter 更新数据库 View

Question

在我的应用程序中，我想将 GridControl 绑定(bind)到包含来自多个数据库表(使用外键引用)的数据的 DataTable。

我在更新数据时遇到了问题，因为我在引用多个数据库表的命令上使用了 SqlDataAdapter。我收到错误:

Dynamic sql generation is not supported against multiple base tables

我试图通过创建一个将多个表组合成一个“表”的 View 来解决这个问题，然后我可以将其绑定(bind)到我的 GridControl。

插入数据是在我将适当数据添加到适当表的 View 上使用“而不是”触发器完成的。在普通的 t-sql 中，插入工作完美无缺。我可以将数据插入到我的 View 中，并且它被插入到适当的表中。我的 SqlDataAdapter 更新应该没有问题，因为它正在更新一个“表”，其余更新由触发器完成。

不幸的是，我仍然遇到错误

Dynamic sql generation is not supported against multiple base tables

您是否知道为什么 SqlDataAdapter 仍然不让我更新，甚至认为它现在只更新一个“表”- View ？

我的代码:

View :

CREATE view [dbo].[v_TestTypeParameter_grid]
as
(
select t1.Id, t3.Name, t3.Description, t2.MinValue, t2.MaxValue, t4.Symbol 
from TestTypes as t1 join 
TestTypeParameters as t2 on t1.Id = t2.TestType join 
Parameters as t3 on t2.Parameter = t3.Id left join 
Units as t4 on t2.Unit = t4.Id
)

而不是在 View 上触发:

CREATE TRIGGER [dbo].[tr_TestTypeParameterAdded] ON [dbo] [v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitSymbol = Symbol FROM Inserted;
    SELECT @ParameterId = Id FROM Parameters WHERE Name = @ParameterName;
    SELECT @UnitId = Id from Units WHERESymbol = @UnitSymbol;

    INSERT INTO TestTypeParameters(TestType, Parameter, MinValue, MaxValue, Unit) VALUES(@TestTypeId, @ParameterId, @MinValue, @MaxValue, @UnitId);
END

我的绑定(bind)代码:

mvarParameterListAdapter = DBService.GetDataAdapter("select * from v_TestTypeParameter_grid where Id  = " + mvarTestTypeId);

mvarParameterTable = new DataTable();
mvarParameterListAdapter.Fill(mvarParameterTable);
gcParameters.DataSource = mvarParameterTable;
gcParameters.RefreshDataSource();

DBService.GetDataAdapter 方法:

public static SqlDataAdapter GetDataAdapter(String command, DataTable parameters = null)
    {
        SqlConnection lvarConnection = GetConnection();

        SqlCommand lvarCommand = new SqlCommand(command, lvarConnection);
        if (parameters != null && parameters.Rows.Count > 0)
        {
            foreach (DataRow lvarRow in parameters.Rows)
            {
                lvarCommand.Parameters.AddWithValue("@" + lvarRow[0], lvarRow[1]);
            }
        }

        SqlDataAdapter lvarAdapter = new SqlDataAdapter(lvarCommand);
        SqlCommandBuilder lvarCommandBuilder = new SqlCommandBuilder(lvarAdapter);
        return lvarAdapter;
    }

更新代码:

try
{
    mvarParameterListAdapter.Update(mvarParameterTable);
}
catch (Exception ex)
{
    // Here, I'm getting the error
}

在 t-sql 中工作的插入:

INSERT INTO v_TestTypeParameter_grid VALUES (2, 'Fe', 'Iron', 5.2, 7.9, '%')

因为我有几个这样工作的 GridControl(使用不同的数据)，所以我尝试不自己为 SqlDataAdapter 生成选择、插入、更新和删除命令，而是使用 SqlCommandBuilder 来使其更简单。

谢谢!

编辑:

为了(希望)把事情弄清楚:

这是我正在处理的 GridControl。请原谅我的列名，它们是波兰语的。 Identyfikator = Id, 纳兹瓦 = 名字， Opis = 描述， 最小值 = 最小值， 最大值 = 最大值， Jednostka = 单位

现在，我正在向 GridControl 添加另一行，其中仅包含“Nazwa(名称)”和“Opis(描述)”字段。

我在“Identyfikator(Id)”字段中填充我要添加参数的 TestType 的 ID，并在其他字段中填充默认值。新行自动添加到绑定(bind)到我的 GridControl 的数据表中，在用户单击“保存”后，我想使用 SqlDataAdapter.Update() 方法将这些更改推送到数据库。这是我收到错误的地方:

Dynamic sql generation is not supported against multiple base tables

我希望它清楚地说明了我想要实现的目标以及我正在努力实现的目标。

编辑:

我设法找到了解决这个问题的方法。您可以在下面的我的回答中查看。

Answer 1

我终于设法完成了项目的那一部分。我还找到了解决问题的方法。

首先，我创建了一个模仿我的 GridControl 的 View 。它看起来像这样:

CREATE VIEW [dbo].[v_TestTypeParameter_grid]
AS
SELECT t1.Id, t3.Name, t3.Description, t2.MinValue, t2.MaxValue, t2.Unit as Unit, 
t3.Id AS ParameterId
FROM            
dbo.TestTypes AS t1 INNER JOIN
    dbo.TestTypeParameters AS t2 ON t1.Id = t2.TestType INNER JOIN
    dbo.Parameters AS t3 ON t2.Parameter = t3.Id

重要的是 View 包含使我的目标表中的行唯一的 ID(在本例中为:TestTypeId 和 ParameterId)。

然后，因为 SQL 仍然不允许我插入、更新或删除行，因为

Dynamic sql generation is not supported against multiple base tables

错误。

我通过在我的 View 上创建 INSTEAD OF 触发器(一个用于插入、更新和删除)来跳过它。

我的触发器看起来像这样(是的，我知道它们应该基于设置):

插入:

CREATE TRIGGER [dbo].[tr_TestTypeParameterAdded] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit from Inserted;
    SELECT @ParameterId = Id from Parameters where Name = @ParameterName;

    insert into TestTypeParameters(TestType, Parameter, MinValue, MaxValue, Unit) values (@TestTypeId, @ParameterId, @MinValue, @MaxValue, @UnitId);
END

更新:

CREATE TRIGGER [dbo].[tr_TestTypeParameterUpdated] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF UPDATE
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2)

    select * from inserted
    SELECT @TestTypeId = Id, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit, @ParameterId = ParameterId from Inserted;

    update TestTypeParameters set MinValue = @MinValue, MaxValue = @MaxValue, Unit = @UnitId where TestType = @TestTypeId and Parameter = @ParameterId;
END

删除:

CREATE TRIGGER [dbo].[tr_TestTypeParameterDeleted] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF DELETE
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit, @ParameterId = ParameterId from deleted;

    delete from TestTypeParameters where TestType = @TestTypeId and Parameter = @ParameterId;
END

我还必须更改创建 SqlDataAdapters 的方式:

SqlCommand lvarInsert =
    new SqlCommand(
        "insert into v_TestTypeParameter_grid values (@Id, @Name, @Description, @MinValue, @MaxValue, @Unit, @ParameterId)", DBService.Connection);
mvarParameterListAdapter.InsertCommand = lvarInsert;   
lvarInsert.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarInsert.Parameters.Add("@Name", SqlDbType.NVarChar, 5, "Name");
lvarInsert.Parameters.Add("@Description", SqlDbType.NVarChar, 5, "Description");
lvarInsert.Parameters.Add("@MinValue", SqlDbType.Decimal, 5, "MinValue");
lvarInsert.Parameters.Add("@MaxValue", SqlDbType.Decimal, 5, "MaxValue");
lvarInsert.Parameters.Add("@Unit", SqlDbType.Int, 5, "Unit");
lvarInsert.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");

SqlCommand lvarSelect = new SqlCommand("select * from v_TestTypeParameter_grid where Id = " + mvarTestTypeId, DBService.Connection);
mvarParameterListAdapter.SelectCommand = lvarSelect;

SqlCommand lvarUpdate =
new SqlCommand(
"update v_TestTypeParameter_grid set Id = @Id, ParameterId = @ParameterId, Name = @Name, Description = @Description, MinValue = @MinValue, MaxValue = @MaxValue, Unit = @Unit where Id = @Id and ParameterId = @ParameterId", DBService.Connection);
mvarParameterListAdapter.UpdateCommand = lvarUpdate; 
lvarUpdate.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarUpdate.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");
lvarUpdate.Parameters.Add("@Name", SqlDbType.NVarChar, 5, "Name");
lvarUpdate.Parameters.Add("@Description", SqlDbType.NVarChar, 5, "Description");
lvarUpdate.Parameters.Add("@MinValue", SqlDbType.Decimal, 5, "MinValue");
lvarUpdate.Parameters.Add("@MaxValue", SqlDbType.Decimal, 5, "MaxValue");
lvarUpdate.Parameters.Add("@Unit", SqlDbType.Int, 5, "Unit");

SqlCommand lvarDelete =new SqlCommand(
"delete from v_TestTypeParameter_grid where Id = @Id and ParameterId = @ParameterId", DBService.Connection);
mvarParameterListAdapter.DeleteCommand = lvarDelete;   
lvarDelete.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarDelete.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");

我几乎可以肯定它可以用不同的(更好的)方式完成，但我想分享它，因为我花了一些时间来修复它，而且我在网上找不到任何解决方案。

如果您对如何让它变得更好有任何想法，请随时发表评论。

尽情享受吧!