如何在C#中表示MySQL数据库架构? [英] How to represent a MySQL database schema in C#?

问题描述

标题不太准确，但我想不出更好的标题.

The title is not so accurate, but I couldn't come up with a better one.

我正试图为MS’Forefront Identity Manager(FIM本质上是一个同步引擎，使用meta目录在各种数据源之间同步身份)的MySQL连接器.但是我很难提出一个合适的设计.

I’m trying to write a MySQL Connector for MS‘ Forefront Identity Manager (FIM is basically a sync engine that synchronizes identities between various data sources using a meta directory). But I’m having difficulties to come up with an appropriate design.

假设我想将用户数据从数据库导入FIM的metaverse.用户对象具有各种属性，例如名字，姓氏，地址等.在数据库中，这些属性可以分布在多个表之间. FIM最终需要将这些属性合并到一个对象中.因此，用户需要配置连接器以告诉它如何将数据存储在数据库中.

Let’s say I want to import user data from a db into FIM’s metaverse. A user object has various attributes like firstname, lastname, address etc. In the database these attributes can be distributed between multiple tables. FIM ultimately needs these attributes to be merged into one object. So the user needs to configure the connector to tell it how the data is stored in the DB.

我想知道代表这种配置的最佳"方法是什么. (我)想到了两种选择:

I was wondering what would be the "best" way to represent this configuration. Two alternatives come to (my) mind:

1. 我可以只保存一个合并/联接数据的选择查询，以便结果是具有所有所需属性的单个表".问题是我想我必须对此查询字符串进行某种解析才能从中创建一个fim-compatible-schema(这基本上是对象类型的名称(fe"person"))，属性列表).这种模式需要仅从查询字符串创建而无需实际执行查询(如果可以简化过程，我可以执行一些假查询).
2. 我可以创建一些类来表示数据库模式，即表和关系.由于我对MySQL的知识(或与此有关的数据库)经验不足，因此冒着遗漏某些特殊情况的风险.同样，这可能有些矫kill过正，因为一旦配置好该架构，就可以将其假定为固定的.

在选择哪种替代品以及如何解决随之而来的问题上，是否有人有相同的建议?还是有我没想到的另一种更好的选择?任何建议将不胜感激！ 如果不清楚，请告诉我.

Does anyone have same advice on which alternative to choose and how to tackle the problems that would come with it? Or is there another – better – alternative I didn’t think of? Any advice would be greatly appreciated! If something is not clear, please let me know.

编辑:由于用例存在一些问题，因此我将进行详细说明:

Edit: Since there have been some questions on the use case, I'm going to elaborate a bit:

正如我所说，我正在为FIM开发一个管理代理. FIM提供了一个所谓的可扩展连接管理代理，它基本上是一个实现几个接口的单一类. (请参阅此Technet指南示例实施).

As I've said, I'm developing a Management Agent for FIM. FIM provides a so called Extensible Connectivity Management Agent, which is basically one single class implementing a few interfaces. (See this technet guide for a sample implementation).

由于我想开发一个通用代理来管理MySQL数据库中的身份，因此我不知道编译时的数据库布局.当最终用户想要使用管理代理时，他需要决定要管理身份的哪些属性.因此，我需要为用户提供一些配置管理代理的方法.我的主要问题是，如何设计类以保存此配置.

Since I want to develop a generic agent for managing identities in a MySQL database, I don't know the database layout at compile time. When the enduser wants to use the management agent, he needs to decide, which attributes of the identities he'd like to manage. So I need to give the user some way to configure the management agent. My main question is, how to design the classes to save this configuration.

让我们看一个简单的例子:

Lets look at a simple example:

说您要管理员工身份.为简单起见，我们具有三个属性:

Say you want to manage employee identities. To keep it simple, we have three attributes:

• 名字
• 姓氏
• 部门

在此示例中，可能是只有一个包含4列的表格(属性和ID).但这也可能是更好的设计，它使用两个表，一个用户表和一个部门表，并以1:1关系定义用户部门.

In this example case it could be f.e. just one single table with 4 columns (the attributes plus an id). But it could also be the much better design, which uses two tables, one user table and one department table, using a 1:1 relation to define the users department.

FIM要求我将这些属性合并到一个对象中.它提供了具有AttributeChanges集合成员的类CSEntryChange.然后，我将创建AttributeChange的一些实例(基本上包含属性名称及其值)，并将其添加到集合中.因此，用户可编辑的配置必须告诉管理代理它如何从db 数据库中获取具有所有已定义属性的用户，以及如何在该数据库中创建和修改用户.

FIM requires me to consolidate these attributes in one object. It provides a class CSEntryChange which has an AttributeChanges collection member. I would then create some instances of AttributeChange (which basically contains the attribute name und it's value) and add them to the collection. So the user-editable configuration must tell the management agent how it can get the users with all defined attributes from the db and how to create and modify users in that database.

因此，理想情况下，我将具有一些"MySQLSchema"类(由用户预先配置)的实例，该类可能返回List<CSEntryChange>(出于这个原因，我实际上不会使用CSEntryChange类解耦，但您应该明白这一点)，它包含db中的所有用户(分页可能是一个要求，但我以后可以弄清楚).另外，我希望能够传递给它一个CSEntryChange，这将导致相应的数据库条目被更新(或者如果不存在则创建).

So ideally I'd have an intance of some "MySQLSchema" class (which is configured by the user up front), that could return a List<CSEntryChange> (I wouldn't actually use the CSEntryChange class for the sake of decoupling, but you should get the point) that contains all users in the db (pagination might be a requirement but I can figure that out later). In addition I'd like to able to pass it a CSEntryChange which would result in the corresponding database entries beeing updated (or created if not yet present).

我希望这可以使它更加清晰:)

I hope this clear it up a bit more :)

推荐答案

我认为您真正的问题是，"如何通过C#访问MySQL实体?"

I think that your real question is, "How to access MySQL entities over C#?"

首先，希望您将其作为 MVC应用程序构建..

To begin with, I hope you are building this in as a MVC application.

出于学习和易于实施的目的，我建议坚持使用完整的Microsoft堆栈.

I would suggest sticking to a full Microsoft stack for purposes of learning and ease of implementation.

考虑到这一点，您将需要在以下步骤中创建一个EntityFramework MySQL数据提供程序:

With this in mind, you will want to create an EntityFramework MySQL data provider in the following steps:

1. 通过键入 Install-Package EntityFramework -Version 6.0.2 (并添加对此项目的引用)，通过Nuget软件包管理器UI或软件包管理器控制台创建一个新项目和EntityFramework.从您的网络项目中).在页面的中间查看配置EntityFramework以使用MySQL数据库".

1. Create a new project and and EntityFramework either through the Nuget package manager UI or package manager console by typing Install-Package EntityFramework -Version 6.0.2 (and add a reference to this project from your web project). Look half way down the page for "Configure EntityFramework to work with a MySQL database".

通过Nuget程序包管理器UI或在程序包管理器控制台中键入 Install-Package MySql.Data.Entity

Install the MySQL provider for entity framework through the Nuget package manager UI or by typing Install-Package MySql.Data.Entity in the package manager console

下一步需要了解数据库配置更改，这些更改非常详细地

The next step requires understanding of db configuration changes, that are nicely detailed here - Configure EntityFramework to work with a MySQL database.

您应该最终得到一个不错的类结构，该结构将允许您通过EF遍历实体的导航属性.

You should end up with a nice class structure which will allow you to traverse your entities' navigation properties through EF.

根据您的应用程序要求的安全级别，您可能还需要创建数据传输对象(DTO)仅包含远程调用所需的数据-保持数据调用的效率.

Depending on the level of security your application requires, you may also want to create data transfer objects (DTOs) that contains only the data required for your remote calls - keeping your data calls efficient.

这绝不是如何执行此操作的权威指南，但希望可以为您提供正确方向的开始.

This is by no means a definitive guide on how to do this, but hopefully gives you a start in the right direction.

关于您上面的第1步:

我可以只保存一个合并/联接数据的选择查询，这样 结果是具有所有所需属性的单个表".这 问题是我想我必须做某种 对此查询字符串进行解析以创建一个fim-compatible-schema 它(基本上是对象类型的名称(例如"person"))和 属性列表).此架构需要从 单独使用查询字符串，而无需实际执行查询(我可以 如果可以简化流程，请执行一些假查询.

I could just save a select query that merges/joins the data, so that the result is a single "table" with all the desired attributes. The problem with this is that I think I would have to do some kind of parsing on this query-string to create a fim-compatible-schema out of it (which is basically the name of the object type (f.e. "person") and a list of attributes). This schema needs to be creatable from the query-string alone without actually executing the query (I could execute some fake queries if that would simplify the process).

对此我有些困惑.您是说要动态更新基于数据库模式的应用程序请求吗?

I am slightly confused by this. Are you saying that you want to dynamically update your database schema based application requests?

这篇关于如何在C#中表示MySQL数据库架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！