1
\$\begingroup\$

I got my code works fine, but I am not really sure whether the approach that I took is a best practice or not. So, I decided to ask in this forum instead.

So here is my situation:

  • I got my model which looks like this:

    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; }
    }
    
  • And from the client side, what I pass to the server is like this:

    {
       "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"
      }
    }
    
  • Then my controller looked like this (the data that I want to pass from client side, will converted into Member model which have all data in it):

    [HttpPost]
    public HttpActionResult AddMember([FromBody] Member member)
    {
          var response = AddMemberToDatabase(member);
    
          return Ok(response);
    }
    
  • But, since I didn't know on how Dapper (I am using Dapper for communication to Database) convert the mapped variable (in this case MemberExperience and MemberInformation) into single object and to be recognized. So, what I did is like this:

    1. Take all member data, but before insert it to the database, I will make 1 class that stored the list of data from MemberExperience and MemberInformation.

    2. Join all data into 1 single long string with separated by separator that only Database only allow to access it and knows it.

  • SingleMember class

    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 function

    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;
        }
    }
    
  • Then, from the MyStoredProcedure, it will split the joined data by the defined format $Format, and insert it into table 1 by 1 until the item that is being splitted no more left from being splitted.

    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
    

Is there any better way of doing this?

Your answer much appreciated.

Thanks

\$\endgroup\$
  • \$\begingroup\$ why using Dapper - why not something like EntityFrameworks? \$\endgroup\$ – BKSpurgeon Sep 24 '17 at 9:34
  • \$\begingroup\$ Hi @BKSpurgeon, there is no special reason why I decided to used Dapper instead of EF, but as because I am more familiar with Dapper throughout my previous projects, so I decided to use Dapper instead. But, from my previous projects, I didn't handle this kind of thing which I encountered now (like nested model class inside model class), so I just came in the solution like the question above, though not really sure is the one I did follows best practice or not. \$\endgroup\$ – Reinhardt Sep 24 '17 at 10:00
  • \$\begingroup\$ also why is the member ID nullable? isn't it the primary key? \$\endgroup\$ – BKSpurgeon Sep 24 '17 at 13:41
  • \$\begingroup\$ Hi @BKSpurgeon, the reason is because when inserting a new member to the database, the ID is unnecessary, but when it is retrieving or updating the existing member, then the ID is compulsory. Thanks \$\endgroup\$ – Reinhardt Sep 25 '17 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.