插入或更新映射模型数据到数据库
问题描述:
我有我的代码工作正常,但我真的不知道,我采取的方法是否是最好的做法还是不行。所以,我决定在这个论坛上提问。插入或更新映射模型数据到数据库
因此,这里是我的情况:
我得到了我的模型,看起来像这样:
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
数据的列表1类。加入所有的数据转换成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,直到正在拆分的项目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
有没有更好的方法来做到这一点?
您的回答非常感谢。
感谢
答
,我认为它不是有效的创建CRUD操作的存储过程。如果你有很多的表,在应用程序中创建生成SQL命令(插入,更新,删除),并在交易给他们(的TransactionScope)C#支持 - 作为SqlCommand时使用的参数 - 到数据库中。
如何在DB中映射的数据?数据存储在多少个表中?你能提供一些关于这方面的细节吗? –
嗨@PiyushKhanna,请参见上面的更新问题,我已经包括了存储过程中也是如此。谢谢 – Reinhardt