文章/答案/技术大牛

发布

社区首页 >问答首页 >将映射的模型数据插入或更新到数据库中

问将映射的模型数据插入或更新到数据库中
EN

Stack Overflow用户

提问于 2017-09-24 03:56:03

回答 1查看 117关注 0票数 0

我的代码运行良好，但我不确定我采用的方法是否是最佳实践。所以，我决定在这个论坛上问。

下面是我的情况：

我的模特是这样的：

public class Member
{
    [Range(1, int.MaxValue)]
    public int? MemberID { get; set; }

    [Required]
    public List<MemberExperience> MemberExperiences { get; set; }

    [Required]
    public string MemberAddress { get; set; }

    [Required]
    public MemberInformation MemberInformation { get; set; }
}

public class MemberExperience
{
    [Required]
    [Range(1, int.MaxValue)]
    public int FromYear { get; set; }

    [Required]
    [Range(1, int.MaxValue)]
    public int ToYear { get; set; }

    [Required]
    public string CompanyAddress { get; set; }

    [Required]
    public string ProgrammingLanguage { get; set; }
}

public class MemberInformation
{
    [Required]
    public string FullName { get; set; }

    [Required]
    public DateTime BirthDate { get; set; }

    [Required]
    public string TelephoneNumber { get; set; }
}

从客户端，我传递给服务器的内容如下：

{
   "MemberExperiences": [
    {
        "FromYear": 2005,
        "ToYear": 2008,
        "CompanyAddress": "string",
        "ProgrammingLanguage": "Javascript"
    },
    {
        "FromYear": 2009,
        "ToYear": 2012,
        "CompanyAddress": "string",
        "ProgrammingLanguage": "C++"
    },
    {
        "FromYear": 2013,
        "ToYear": 2017,
        "CompanyAddress": "string",
        "ProgrammingLanguage": "C#"
    }
  ],
  "MemberAddress": "string",
  "MemberInformation": {
      "FullName": "string",
      "BirthDate": "1992-01-01",
      "TelephoneNumber": "string"
  }
}

然后，我的控制器看起来如下(我想从客户端传递的数据，将转换为Member模型，其中包含所有数据)：

[HttpPost]
public HttpActionResult AddMember([FromBody] Member member)
{
      var response = AddMemberToDatabase(member);

      return Ok(response);
}

但是，由于我不知道Dapper (我正在使用Dapper与数据库通信)如何将映射的变量(在本例中是MemberExperience和MemberInformation)转换为单个对象并被识别。所以，我做的是这样的：

获取所有成员数据，但在将其插入数据库之前，我将创建一个类来存储来自MemberExperience和MemberInformation的数据列表。
将所有数据连接到一个单独的长字符串中，分隔符为分隔符，只有数据库才允许访问它并知道它。

SingleMember类：

public class SingleMember
{
    public int? MemberID { get; set; }
    public string FromYears { get; set; }
    public string ToYears { get; set; }
    public string CompanyAddresses { get; set; }
    public string ProgrammingLanguages { get; set; }
    public string MemberAddress { get; set; }
    public string FullName { get; set; }
    public DateTime BirthDate { get; set; }
    public string TelephoneNumber { get; set; }
}

AddMemberToDatabase函数：

private int AddMemberToDatabase(Member members)
{
    var separator = "$Format";

    var singleMember = new SingleMember
    {
        FromYears = string.Join(separator, members.MemberExperiences.Select(x => x.FromYear)),
        ToYears = string.Join(separator, members.MemberExperiences.Select(x => x.ToYear)),
        CompanyAddresses = string.Join(separator, members.MemberExperiences.Select(x => x.CompanyAddress)),
        ProgrammingLanguages = string.Join(separator, members.MemberExperiences.Select(x => x.ProgrammingLanguage)),
        MemberAddress = members.MemberAddress;
        FullName = members.MemberInformation.FullName;
        BirthDate = members.MemberInformation.BirthDate;
        TelephoneNumber = members.MemberInformation.TelephoneNumber;
    };

    using (TransactionScope trans = new TransactionScope())
    using (IDbConnection conn = new SqlConnection("MyConnection"))
    {
        conn.Open();

        int rowsAffected = conn.Execute("MyStoredProcedure", singleMember);

        trans.Complete();

        return rowsAffected;
    }
}

然后，从MyStoredProcedure中，它将根据定义的格式$Format拆分已连接的数据，并将其插入表1×1中，直到正在拆分的项不再被拆分。

ALTER PROCEDURE [dbo].[MyStoredProcedure]
(
    @FromYears              NVARCHAR(MAX), // will be 2005$Format2009$Format2013
    @ToYears                NVARCHAR(MAX), // will be 2008$Format2012$Format2017
    @CompanyAddresses       NVARCHAR(MAX), // will be string$Formatstring$Formatstring
    @ProgrammingLanguages   NVARCHAR(MAX), // will be Javascript$FormatC++$FormatC#
    @MemberAddress          NVARCHAR(MAX),
    @FullName               NVARCHAR(MAX),
    @BirthDate              DATETIME,
    @TelephoneNumber        NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE
        @MemberCount            INT,
        @FromYear               INT,
        @ToYear                 INT,
        @CompanyAddress         NVARCHAR(MAX),
        @ProgrammingLanguage    NVARCHAR(MAX)

    DECLARE @FromYearTable      TABLE (
        [ID]            INT         IDENTITY,
        [FromYear]      INT         NOT NULL
    )

    DECLARE @ToYearTable        TABLE (
        [ID]            INT         IDENTITY,
        [ToYear]        INT         NOT NULL
    )

    DECLARE @CompanyAddressTable        TABLE (
        [ID]                INT             IDENTITY,
        [CompanyAddress]    NVARCHAR(MAX)   NOT NULL
    )

    DECLARE @ProgrammingLanguageTable           TABLE (
        [ID]                    INT             IDENTITY,
        [ProgrammingLanguage]   NVARCHAR(MAX)   NOT NULL
    )

    DECLARE @MemberTable            TABLE (
        [ID]                    INT             IDENTITY,
        [FromYear]              INT             NOT NULL,
        [ToYear]                INT             NOT NULL,
        [CompanyAddress]        NVARCHAR(MAX)   NOT NULL,
        [ProgrammingLanguage]   NVARCHAR(MAX)   NOT NULL
    )

    INSERT INTO @FromYearTable
    SELECT [SplittedItem] FROM [StringSplit] (@FromYears, '$Format')

    INSERT INTO @ToYearTable
    SELECT [SplittedItem] FROM [StringSplit] (@ToYears, '$Format')

    INSERT INTO @CompanyAddressTable
    SELECT [SplittedItem] FROM [StringSplit] (@CompanyAddresses, '$Format')

    INSERT INTO @ProgrammingLanguageTable
    SELECT [SplittedItem] FROM [StringSplit] (@ProgrammingLanguages, '$Format')

    INSERT INTO @MemberTable
    SELECT a.[FromYear], b.[ToYear], c.[CompanyAddress], d.[ProgrammingLanguage]
    FROM @FromYearTable a
    INNER JOIN @ToYearTable b ON a.[ID] = b.[ID]
    INNER JOIN @CompanyAddressTable c ON a.[ID] = c.[ID]
    INNER JOIN @ProgrammingLanguageTable d ON a.[ID] = d.[ID]

    /*
        Will be like:
        ID  FromYear    ToYear  CompanyAddress  ProgrammingLanguage
        1   2005        2008    string          Javascript
        2   2009        2012    string          C++
        3   2013        2017    string          C#
    */

    SET @MemberCount = (SELECT COUNT(*) FROM @MemberTable)

    WHILE (@MemberCount > 0)
    BEGIN
        SET @FromYear = (SELECT TOP 1 [FromYear] FROM @MemberTable WHERE [ID] = @MemberCount)
        SET @ToYear = (SELECT TOP 1 [ToYear] FROM @MemberTable WHERE [ID] = @MemberCount)
        SET @CompanyAddress = (SELECT TOP 1 [CompanyAddress] FROM @MemberTable WHERE [ID] = @MemberCount)
        SET @ProgrammingLanguage = (SELECT TOP 1 [ProgrammingLanguage] FROM @MemberTable WHERE [ID] = @MemberCount)

        INSERT INTO [MyTable] (@FromYear, @ToYear, @CompanyAddress, @ProgrammingLanguage, @MemberAddress, @FullName, @BirthDate, @TelephoneNumber)

        SET @MemberCount -= 1
    END
END

有什么更好的方法吗？

你的回答非常感谢。

谢谢

dapper

asp.net

回答 1

Stack Overflow用户

发布于 2017-09-24 08:15:27

我认为它不能有效地为CRUD操作创建存储过程。如果您有很多表，请在应用程序中创建c#支持来生成SQL命令(插入、更新、删除)，并将它们作为带有参数的SqlCommand发送到数据库中。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/46386436

复制

相似问题

问将映射的模型数据插入或更新到数据库中
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将映射的模型数据插入或更新到数据库中EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将映射的模型数据插入或更新到数据库中
EN