I am moving my current application to a multi tenant architecture. With a single code base, i need to address multiple tenants. I am using single database, multiple schema approach. Each tenant will be assigned a separate schema with the meta data saved in the default schema.
我正在将我当前的应用程序转移到多租户架构。使用单一代码库,我需要解决多个租户问题。我正在使用单一数据库,多种模式方法。将为每个租户分配一个单独的模式,其中元数据保存在默认模式中。
Application is built with ASP.NET MVC. I use Dapper to connect to my SQL Server. I am having 50+ functions which calls the database using direct query as well as stored procedures. Is there any method by which i can set the schema when the dapper is initialized for each tenant without altering the functions.
应用程序是使用ASP.NET MVC构建的。我使用Dapper连接到我的SQL Server。我有50多个函数使用直接查询和存储过程调用数据库。是否有任何方法可以在为每个租户初始化dapper时设置架构而无需更改功能。
2 个解决方案
#1
4
In SQL Server, the schemas are associated with users. Each user has atleast one default schema. You can associate the tenant with schema, that means you will have mapping for
在SQL Server中,模式与用户关联。每个用户至少有一个默认架构。您可以将租户与架构相关联,这意味着您将拥有映射
Tenant -> Schema -> User
租户 - >架构 - >用户
Dynamically construct your SQL Connection string and pass the SQL user corresponding to that tenant. This way the default schema is automatically pointed out and you Dont have to change your Dapper queries and dont have to write dynamic queries as well.
动态构造SQL连接字符串并传递与该租户对应的SQL用户。这样就可以自动指出默认模式,您不必更改Dapper查询,也不必编写动态查询。
Note: Don't forget to check the Connection pooling implication.
注意:不要忘记检查连接池含义。
#2
1
I would try a simple approach.
我会尝试一种简单的方法。
With Dapper, for easy maintenance, I store the querys in an external XML File. So, you could have an external XML file for each of your customers. Another solution, but I didn't tried it before, I think that you could add to your query a parameter that would be the schema name. So in every query you would always need to specify the schema.
使用Dapper,为了便于维护,我将查询存储在外部XML文件中。因此,您可以为每个客户提供外部XML文件。另一个解决方案,但我之前没有尝试过,我认为您可以在查询中添加一个参数,即模式名称。因此,在每个查询中,您始终需要指定架构。
#1
4
In SQL Server, the schemas are associated with users. Each user has atleast one default schema. You can associate the tenant with schema, that means you will have mapping for
在SQL Server中,模式与用户关联。每个用户至少有一个默认架构。您可以将租户与架构相关联,这意味着您将拥有映射
Tenant -> Schema -> User
租户 - >架构 - >用户
Dynamically construct your SQL Connection string and pass the SQL user corresponding to that tenant. This way the default schema is automatically pointed out and you Dont have to change your Dapper queries and dont have to write dynamic queries as well.
动态构造SQL连接字符串并传递与该租户对应的SQL用户。这样就可以自动指出默认模式,您不必更改Dapper查询,也不必编写动态查询。
Note: Don't forget to check the Connection pooling implication.
注意:不要忘记检查连接池含义。
#2
1
I would try a simple approach.
我会尝试一种简单的方法。
With Dapper, for easy maintenance, I store the querys in an external XML File. So, you could have an external XML file for each of your customers. Another solution, but I didn't tried it before, I think that you could add to your query a parameter that would be the schema name. So in every query you would always need to specify the schema.
使用Dapper,为了便于维护,我将查询存储在外部XML文件中。因此,您可以为每个客户提供外部XML文件。另一个解决方案,但我之前没有尝试过,我认为您可以在查询中添加一个参数,即模式名称。因此,在每个查询中,您始终需要指定架构。