英文:
Can I create a Model Driven app with Azure SQL
问题
可以创建一个模型驱动的应用程序，并直接将CRUD操作连接到Azure SQL Server吗？
英文:
Is it possible to create a model driven app and have the CRUD operation directly connecting to Azure SQL Server?
答案1
得分: 1
你可以创建一个与Azure SQL直接连接的Canvas应用程序。
你也可以创建一个Model-Driven应用程序，但需要将Dataverse与SQL数据库集成。
你还可以查看虚拟表是否是一个可行的选项。
文档在这里可用：https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve
从上面的链接中，这里有一些一般信息：
（TL;DR - 虚拟表可能适用于Model-Driven应用程序，有关它们是什么、如何工作以及限制的详细信息，请参见链接）
虚拟表是Dataverse平台上表的定义，而不是在Dataverse数据库中创建记录的相关物理表。而是在运行时，当需要记录时，它的状态会动态从关联的外部系统中检索。每个虚拟表类型都与一个虚拟表数据提供程序相关联，并（可选地）与关联的虚拟表数据源的一些配置信息相关联。
以下数据提供程序随Dataverse一起提供：
- 包含在服务中的OData v4提供程序是默认安装的。该提供程序支持创建、读取（检索、检索多个）、更新和删除操作。
- 可以从AppSource获取Azure Cosmos DB（以前称为Microsoft Document DB）提供程序。
如果找不到与外部数据源相关的数据提供程序，您可以开发自定义虚拟表数据提供程序；有关更多信息，请参见虚拟表数据提供程序。现在支持自定义虚拟表数据提供程序的完整CRUD操作。开发人员可以实现插件，并使用插件注册工具为支持虚拟表的每个CRUD操作进行注册。
以下是虚拟表中必须考虑的限制：
- 仅支持组织拥有的表。不支持用户拥有的表的安全筛选。可以根据其安全角色为个别用户打开或关闭对虚拟表数据的访问。不支持字段级安全性。
- 必须能够将外部数据建模为Dataverse表。这意味着：
- 外部数据源中的所有表必须具有关联的GUID主键。
- 所有表属性必须表示为Dataverse表列。您可以使用表示文本、数字、选择项、日期、图像和查找的简单类型。
- 您必须能够在Dataverse中建模任何表关系。
- 虚拟表上的列不能计算或汇总。任何所需的计算必须在外部进行，可能在数据提供程序内部或由其指导。
- 尽管可以将虚拟表列添加为网格或其他UI视图上的查找，但不能基于此虚拟表查找列进行筛选或排序。
- 不支持审计。
- 虚拟表不支持数据的持久性，因此不支持搜索功能。
- 不支持虚拟表的图表和仪表板。
- 虚拟表不能启用队列。
- 不支持虚拟表的离线值缓存。
- 虚拟表不能表示活动，不支持业务流程流。
- 一旦创建，虚拟表不能更改为标准（非虚拟）表。反之亦然：标准表不能转换为虚拟表。
- 在检索和检索多个查询中选择属性将不会应用，因为所有属性都将被返回。
英文:
You could create a Canvas app connecting directly to Azure SQL
You could make a Model-Driven app, but you will need to integrate the Dataverse with the SQL database
You could also see if Virtual Tables are a viable option.
The documentation is available here
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve
From the above link, here are some general information:
(TL;DR - Virtual Tables might work for you in a Model-Driven app, see the link for details about what they are, how they work, and the limitations)
A virtual table is a definition of a table in the Dataverse platform without the associated physical tables for records created in the Dataverse database. Instead during runtime, when a record is required, its state is dynamically retrieved from the associated external system. Each virtual table type is associated with a virtual table data provider and (optionally) some configuration information from an associated virtual table data source.
The following data providers ship with Dataverse:
- An OData v4 provider is included with the service and is installed by default. This provider supports create, read (retrieve, retrieve multiple), update and delete operations.
- An Azure Cosmos DB (formerly Microsoft Document DB) provider is available from AppSource.
If a data provider cannot be found for your external data source, you can develop a custom virtual table data provider; for more information, see Virtual table data providers. Full CRUD operation is now supported for custom virtual table data provider. Developers can implement plug-ins and register them using the Plug-in Registration tool for each of the CRUD operation supporting the virtual table.
Following are the limitations in virtual tables that must be considered.
- Only organization-owned tables are supported. The security filtering applied to user-owned tables is not supported. Access to the virtual table data can be turned on or off for individual users based on their security role. Field-level security is not supported.
- It must be possible to model the external data as a Dataverse table. This means:
- All tables in the external data source must have an associated GUID primary key.
- All table properties must be represented as Dataverse table columns. You can use simple types representing text, numbers, choices, dates, images, and lookups.
- You must be able to model any table relationships in Dataverse.
- A column on a virtual table cannot be calculated or rollup. Any desired calculations must be done on the external side, possibly within or directed by the data provider.
- Although you can add virtual table columns as a lookup on a grid or other UI views, you cannot filter or sort based on this virtual table lookup column.
- Auditing is not supported.
- Search functionality is not supported for virtual tables as they do not persist data.
- Charts and dashboards are not supported for virtual tables.
- Virtual tables cannot be enabled for queues.
- Offline caching of values is not supported for virtual tables.
- A virtual table cannot represent an activity and do not support business process flows.
- Once created, a virtual table cannot be changed to be a standard (non-virtual) table. The reverse is also true: a standard table cannot be converted into a virtual table.
- Selecting attributes in Retrieve and RetrieveMultiple queries will not be applied since all attributes will be returned
答案2
得分: -2
我不同意Alex的建议。你基本上在问是否可以将基于模型的用户界面连接到一个不是Dataverse的不同SQL服务器。简短的答案是“不”，因为这样做将花费比从头开始编写更多的成本，而且你将绕过整个安全模型。如果你的意图是在SQL中获得完全的数据控制，那么只需购买D365以在本地进行操作，然后你可以按自己的意愿使用SQL服务器。
英文:
I disagree with Alex's suggestion. You are basically asking if you can connect the model-driven UI to a different SQL server that is not Dataverse. The short answer is "no" because it would cost you more than just writing it from scratch and you would be bypassing the entire security model. If your intention is to get full control over the data in SQL, then just buy D365 for on-prem and you can do what you want with the SQL server from there.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论