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

我的代码工作正常，但我不确定我采取的方法是否是最佳实践。所以，我决定在这个论坛上提问。

所以这是我的情况：

我的模型看起来像这样：

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 ）转换为单个对象并被识别。所以，我做的是这样的：

1. 获取所有成员数据，但在将其插入数据库之前，我将创建一个存储来自MemberExperience和MemberInformation的数据列表的类。

2. 将所有数据加入1个单个长字符串中，并用分隔符分隔，只有数据库才允许访问它并知道它。

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

有没有更好的方法呢？

你的回答非常感谢。

谢谢