在Lightswitch中使用连接表(MS SQL Express中的数据) [英] Use of Junction Tables in Lightswitch (Data in MS SQL Express)

问题描述

我有一个SQL Server Express数据库作为我正在开发的lightswitch应用程序的数据源.

I have an SQL Server Express database as the data source for a lightswitch application I am developing.

我在三个表的集合之间有许多联结表关系(http://en.wikipedia.org/wiki/Junction_table)，其形式为:

I have a number of junction table relationships (http://en.wikipedia.org/wiki/Junction_table), between sets of three tables, which take the form:

[表1] - 1对多- [表2] - 多对1 - [表3]

有效地，表2由表1和表3中的外键(整数)组成，并表示了有关该关系的一些支持数据(例如记录它的日期时间).

Effectively, Table 2 is made up of foreign keys (integers) from Tables 1 and 3, with some supporting data about the relationship being represented (such as the datetime it was recorded).

一个示例，其中表1代表人员，表3代表公司，表2是雇佣记录的列表(带有两个外键和开始/结束日期).显然，表2中可能有许多记录与表中的任何记录有关 1和3.

An example is where Table 1 represents a Person, Table 3 represents a Company, and Table 2 is a list of employment records (with the two foreign keys and start/finish dates). Obviously there can be many records in Table 2 relating to any record in Tables 1 and 3.

在lightswitch中，我希望能够显示表1的详细信息屏幕，并包括表2中的所有相关记录，但是我不想显示外键，而是显示表中的相关字段3.

In lightswitch, I wish to be able to show the detail screen for Table 1, and include all of the related records from Table 2, but I don't want to display the foreign key, but rather the relevant fields from Table 3.

我还希望能够添加新记录，并将外键绑定到任何屏幕上下文(即，如果我在表1的屏幕中，我只想为表3定义FK值) ).

I'd also like to be able to add new records, with the foreign key bound to whatever the screen context is (i.e. if I am in the screen for table 1, I only want to define the FK value for Table 3).

我可以通过SSMS中的查询生成所需的数据(见下文)，但是我不知道如何在lightswitch中使用它.

I can generate the data I want by through a query in SSMS (see below), but I've got no idea how to use this in lightswitch.

SELECT Person.PersonUID, Person.Fullname, EmploymentRecord.StartDate, EmploymentRecord.EndDate, Organisation.OrganisationName, EmploymentRecord.EmployerFK
FROM Person
LEFT JOIN  EmploymentRecord ON Person.PersonUID = EmploymentRecord.PersonFK
LEFT JOIN Organisation ON EmploymentRecord.EmployerFK = Organisation.OrganisationUID 

我有其他类似的情况，其中表3是静态值的查找表，表2提供了表3中与表1有关的值的历史记录(而不是直接将表1链接到表3并记录表1的值).值会发生变化).

I have other similar situations where Table 3 is a lookup table of static values, and Table 2 provides a historical record of a value from Table 3, relating to Table 1 (rather than linking table 1 directly to table 3 and recording the value changes somewhere).

我第一次尝试设计适当的"商品时所付出的努力可能远远超过了我的努力.数据库，但我将是主要用户和管理员，因此一直在尝试使其尽可能强大和灵活.

I've probably bitten off more than I could chew for a first attempt at designing a "proper" database, but I will be the primary user and administrator, so have been trying to make it as robust and flexible as possible.

我所有的数据库知识都是自学成才的，所以请您滥用任何行话！

All of my database knowledge is self-taught and ad-hoc, so please excuse any misuse of jargon!

干杯

EngIT

推荐答案

此处提供的机会可以描述LightSwitch的出色表现.

What you have here is an opportunity to describe what LightSwitch does very well.

首先建立您的关系:

1. 听起来这是一个现有的数据库，所以我的首选是使用SQL Server中的数据库图表示关系.很好地设置数据库中的关系，然后在使用LightSwitch时完成项目的1/2.
2. 在数据源"的右键菜单中的LightSwitch中，选择添加数据源"
3. 如果您已在数据库中正确设置了业务关系，则这些关系将已经在LightSwitch中表示出来.无需其他工作！ (我认为这真的很酷)

设置屏幕

1. 右键单击屏幕，然后选择选择添加屏幕"
2. 选择您的屏幕类型.对于此示例，我将使用新数据"屏幕...默认情况下不添加任何数据...，但是您可以执行任何类型.
3. 单击添加数据项".
4. 选择查询并选择您的父表(您的人表)
5. 您将在左侧栏中看到查询.点击添加您的链接表 (您的就业记录表") .
6. 这将在左侧添加您的链接表.
7. 在您的示例中，将使用自动ACB(自动完成框)链接到组织表，因此无需为此添加子表)..
8. 在此示例中，我将使用所有网格:因此，在设计器中，单击ADD，然后选择父表，将数据保留在网格中.
9. 对链接表再次执行此操作，将其添加为网格.
10. 现在，删除不需要的任何列显示
11. 运行

1. Right Click on Screens and choose Choose "Add Screen"
2. Choose your screen type.  For this example I will use the New Data Screen... no data added by default... but you could do whatever type.
3. Click on Add Data Item.
4. Choose Query and select your Parent Table (your Person Table)
5. You will see your Query added in the left column.  Click on the Add your Link Table (your EmploymentRecord Table).
6. That will add your Linking Table on the left.
7. In your example, the automatic ACB (Auto Complete Box) will be there on employment to link to the organization table, so no need to add the Child Table for that purpose).
8. For this example, I'll use all Grids: So in the designer click on ADD, then select your Parent Table, leaving the data in a grid.
9. Do this again for your Linking table, adding it as a grid.
10. Now DELETE any columns you don't want to show
11. Run

这应该为您提供准确的数据以及如何查看它们，并允许您编辑任何父表"或链接表"字段.

That should give you exactly the data and how it should be viewed AND allow you to EDIT any Parent or Linking Table fields.

我知道您的帖子很旧，所以您可能已经做完了，但是您要尝试的是Lightswitch轻松完成的工作，一旦您进入就可以了.

I know your post is old, so you may have it already done, but what you are trying to do, is what Lightswitch does out of the box with very little effort, ONCE you get into it.

欢迎您中的任何LightSwitch资深人士选择我的示例，并为我和EngIT提供久经考验的建议！

Any of you LightSwitch Veterans are welcome to pick-appart my example and give me and EngIT the benefit of your time-tested advice!

祝你好运！

这篇关于在Lightswitch中使用连接表(MS SQL Express中的数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！