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:
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
.
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 split no more left from being split.
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
I think it is not effective create stored procedures for CRUD operations. If you have a lot of tables, create c# support in your application for generating SQL commands (Insert, Update, Delete) and send them in transaction (TransactionScope) - as SqlCommand with parameters - into database.