Insertar o actualizar los datos del modelo mapeado en la base de datos

asp.net c# dapper

Pregunta

Obtuve mi código funciona bien, pero no estoy muy seguro de si el enfoque que tomé es una mejor práctica o no. Entonces, decidí preguntar en este foro.

Así que aquí esta mi situación:

Tengo mi modelo que se ve así:

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; }
}

Y desde el lado del cliente, lo que paso al servidor es así:

{
   "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"
  }
}

Entonces mi controlador se veía así (los datos que quiero pasar del lado del cliente, se convertirán en un modelo de Member que tenga todos los datos):

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

      return Ok(response);
}

Pero, dado que no sabía cómo Dapper (estoy usando Dapper para la comunicación a la base de datos) convertir la variable asignada (en este caso MemberExperience y MemberInformation ) en un solo objeto y para ser reconocido. Entonces, lo que hice es así:

  1. Tome todos los datos de los miembros, pero antes de insertarlos en la base de datos, MemberExperience 1 clase que almacena la lista de datos de MemberExperience y MemberInformation .

  2. Une todos los datos en 1 sola cadena larga con separador separado que solo la Base de datos solo permite acceder y lo sabe.

Clase 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; }
}

Función 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;
    }
}

Luego, desde MyStoredProcedure , dividirá los datos unidos por el formato $Format definido, e insertará en la tabla 1 por 1 hasta que el elemento que se está dividiendo no quede más a partir de la división.

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

¿Hay alguna forma mejor de hacer esto?

Tu respuesta muy apreciada.

Gracias

Respuesta popular

Creo que no es efectivo crear procedimientos almacenados para operaciones CRUD. Si tiene muchas tablas, cree c # support en su aplicación para generar comandos SQL (Insertar, Actualizar, Eliminar) y envíelas en transacción (TransactionScope) - como SqlCommand con parámetros - a la base de datos.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué