我有两个模式，像这样的：

I have two schemas, like this:

例如：

create table data.tblTest (TestKey int not null primary key);
create view ui.vwTest as select * from data.tblTest;

现在，如果我连接与SQL Studio的用户，一切都OK了：

Now, if I connect as a user with SQL Studio, everything is OK:

select * from ui.vwTest; -- WORKS (this is correct)
select * from data.tblTest; -- ERROR (this is correct)

insert into   ui.vwTest  (TestKey) values (17); -- WORKS (this is correct)
insert into data.tblTest (TestKey) values (17); -- ERROR (this is correct)

不过，如果我写的.NET / C＃程序，它使用SqlCommandBuilder：

However, if I write a program in .NET/C# which uses SqlCommandBuilder:

SqlDataAdapter ada = new SqlDataAdapter('select * from ui.vwTest', conn);
SqlCommandBuilder b = new SqlCommandBuilder(mSQLAda);
ada.UpdateCommand = b.GetUpdateCommand();
ada.InsertCommand = b.GetInsertCommand();
ada.DeleteCommand = b.GetDeleteCommand();

==>然后在下面的插入不工作！

：

该SqlCommandBuilder正在分析视图，而不是建立像

The SqlCommandBuilder is analyzing the View, and instead of creating a command like

INSERT INTO ui.vwTest ...

这是创建

INSERT INTO data.tblTest ...

因此​​，在事实上，SqlCommandBuilder尝试而不是访问视图是智能和访问视图的基础表，

So in fact, the SqlCommandBuilder tries to be "intelligent" and accesses the underlying tables of the view, instead of accessing the view.

问：能否这种行为被改变

顺便说一句，只是为了说得更清楚，我做的所有权链接在这里。

BTW, just to make it more clear, I am doing ownership chaining here.

我的用户可以看到的意见，方案的用户界面的权利，但他们没有权利架构数据。然而，由于所有权chaning，用户可以通过视图访问表的间接模式数据

My users have the right to see the views in schema ui, but they no rights to schema data. However, due to ownership chaning, the users can access the tables indirectly via the views in schema data.

在详细地说，一个用户被连接到自定义角色，例如ROLE_USER，和角色有权限的架构，如下所示：

In detail, a user is attached to a custom role, e.g. "role_user", and the role has rights to the schema, as follows:

GRANT SELECT, UPDATE, INSERT, DELETE ON SCHEMA ui TO role_user ;

但角色对架构'数据'！

but the role has NO RIGHTS on Schema 'data' !!

该设置的好处是，你可以申请行级安全性。随着视图中的过滤器的地方，你可以记录用户被允许看到的只是选择。

The nice thing of this setup is that you can apply row-level-security. With a where filter within the view, you can select only records the user is allowed to see.

至于说，它工作正常SQL窗口内，但不能与SQLCommandBuilder。该SQLCommandBuilder分析的观点，并试图直接访问，而不是访问视图的基础表。

As said, it works fine within the SQL window, but not with the SQLCommandBuilder. The SQLCommandBuilder analyzes the view, and tries to directly access the underlying tables, instead of accessing the view.

7年前，有人问这样的： http://stackoverflow.com/a/320684/2504785 他的解决方案，然后是写的SQL命令自己。 但可能是，现在存在着另一种解决方案？然而，我发现没有那么远，

7 years ago, someone asked this: http://stackoverflow.com/a/320684/2504785 And his solution then was to write the SQL commands himself. But possibly, there exists now another solution? However, I found none so far ...

[/编辑]

推荐答案

确定，答案现在明确的是：

在 SqlCommandBuilder 正在试图将智能化。如果你喜欢命令打开它SELECT * FROM vwTest ，然后分析了视图和基础表创建命令，如 INSERT操作TBLTEST ...

The SqlCommandBuilder is trying to be "intelligent". If you open it with a command like SELECT * FROM vwTest, then it analyzes the view and creates commands for the underlying table, like INSERT into tblTest ...

所以在问题是： SqlCommandBuilder 创造了基础表的命令，而不是对视图

So the problem is: SqlCommandBuilder creates commands for the underlying table, instead of for the view.

解决方法：

到目前为止，我发现没有办法改变 SqlCommandBuilder 的行为。

So far, I found no way to change the behaviour of SqlCommandBuilder.

所以，我改写了所有的加载和更新，而我做的手工，现在的一切。正在载入发生纯粹与 SqlDataReader的 - 无需加载到一个数据表与的SqlDataAdapter 。而所有的更新是通过创建和执行的SqlCommand 完成，无需 SqlCommandBuilder 。

Therefore, I rewrote all the loading and updating, and I am doing now everything manually. Loading now takes place purely with SqlDataReader -- without loading into a DataTable with SqlDataAdapter. And all updating is done via creating and executing SqlCommand, without SqlCommandBuilder.

这是一个大量的工作，但作为奖励，该应用程序现在是超快。加载据不是与 SqlCommandBuilder 和的SqlDataAdapter 更快。也许我会在一段时间比较基准。但是，当一个负载了5秒之前，现在完成immediatedly。

It was a lot of work, but as a reward, the application is now blazing fast. Loading is far faster than with the SqlCommandBuilder and SqlDataAdapter. Possibly I will make a benchmark comparison at some time. But when a load took 5 seconds before, it is now done "immediatedly".