/// <summary>
/// Insert LibraryBook
/// </summary>
/// <param name="LibraryBook"></param>
/// <param name="LibraryBookCode"></param>
/// <returns>The<see cref="int"/> </returns>
public virtual int InsertLibraryBook(LibraryBookApiModel LibraryBook, out string LibraryBookCode, TransactionParam transactionParam = null)
{
LibraryBookCode = _randomKeyGenerator.GetUniqueKey(9);
LibraryBook.LibraryBookCode = LibraryBookCode;
var parameters = new DynamicParameters();
parameters.Add(name: "LibraryBookCode", value: LibraryBookCode);
parameters.Add(name: "isbn", value: LibraryBook.ISBN);
parameters.Add(name: "title", value: LibraryBook.Title);
parameters.Add(name: "author", value: LibraryBook.Author);
parameters.Add(name: "isStolen", value: LibraryBook.IsStolen);
parameters.Add(name: "isLost", value: LibraryBook.IsLost);
parameters.Add(name: "copyNumber", value: LibraryBook.CopyNumber);
parameters.Add(name: "createdBy", value: LibraryBook.CreatedBy);
//parameters.Add(name: "dateCreated", value: LibraryBook.DateCreated.ToString("yyyy-MM-dd H:mm:ss"));
parameters.Add(name: "modifiedBy", value: LibraryBook.ModifiedBy);
//parameters.Add(name: "dateModified", value: LibraryBook.DateModified.ToString("yyyy-MM-dd H:mm:ss"));
int rowaffected = 0;
string sql = @"INSERT INTO LibraryBook (
LibraryBookCode, ISBN, Title, Author, IsStolen, IsLost, CopyNumber, CreatedBy, ModifiedBy)
VALUES (@libraryBookCode, @isbn, @title, @author, @isStolen, @isLost, @copyNumber,
@createdBy, @modifiedBy)" ;
if (transactionParam != null)
{
rowaffected = transactionParam.Connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text,
transaction: transactionParam.Transaction);
}
else
{
using (var connection = this.OpenConnection())
{
rowaffected = connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text);
}
}
return(rowaffected);
}
/// <summary>
/// Insert LibraryBookStatus
/// </summary>
/// <param name="libraryBookStatus"></param>
/// <param name="libraryBookStatusCode"></param>
/// <returns>The<see cref="int"/> </returns>
public virtual int InsertLibraryBookStatus(LibraryBookStatusApiModel libraryBookStatus, out string libraryBookStatusCode, TransactionParam transactionParam = null)
{
libraryBookStatusCode = _randomKeyGenerator.GetUniqueKey(9);
libraryBookStatus.LibraryBookStatusCode = libraryBookStatusCode;
var parameters = new DynamicParameters();
parameters.Add(name: "libraryBookStatusCode", value: libraryBookStatusCode);
parameters.Add(name: "dateCheckedOut", value: libraryBookStatus.DateCheckedOut);
parameters.Add(name: "dateReturned", value: libraryBookStatus.DateReturned);
parameters.Add(name: "libraryUserCode", value: libraryBookStatus.LibraryUser.LibraryUserCode);
parameters.Add(name: "createdBy", value: libraryBookStatus.CreatedBy);
//parameters.Add(name: "dateCreated", value: libraryBookStatus.DateCreated);
parameters.Add(name: "modifiedBy", value: libraryBookStatus.ModifiedBy);
//parameters.Add(name: "dateModified", value: libraryBookStatus.DateModified);
int rowaffected = 0;
string sql = @"INSERT INTO LibraryBookStatus (
LibraryBookStatusCode, DateCheckedOut, DateReturned, DateCreated, CreatedBy, DateModified, ModifiedBy,
LibraryBookId, LibraryBookUserId
)
SELECT @libraryBookStatusCode, @dateCheckedOut, @dateReturned, NOW(), @createdBy, NOW(), @modifiedBy,
@libraryBookId, LU.LibraryUserId
FROM LibraryUser LU
WHERE LU.LibraryUserCode = @libraryUserCode";
if (transactionParam != null)
{
int bookId = transactionParam.Connection.Query <int>(@"SELECT LibraryBookId FROM LibraryBook WHERE LibraryBookCode = @libraryBookCode",
new { libraryBookStatus.LibraryBook.LibraryBookCode }
).SingleOrDefault();
parameters.Add(name: "@libraryBookId", value: bookId);
rowaffected = transactionParam.Connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text,
transaction: transactionParam.Transaction);
}
else
{
using (var connection = this.OpenConnection())
{
int bookId = connection.Query <int>(@"SELECT LibraryBookId FROM LibraryBook WHERE LibraryBookCode = @libraryBookCode",
new { libraryBookStatus.LibraryBook.LibraryBookCode }
).SingleOrDefault();
parameters.Add(name: "@libraryBookId", value: bookId);
rowaffected = connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text);
}
}
return(rowaffected);
}
/// <summary>
/// Insert LibraryUser
/// </summary>
/// <param name="LibraryUser"></param>
/// <param name="LibraryUserCode"></param>
/// <returns>The<see cref="int"/> </returns>
public virtual int InsertLibraryUser(LibraryUserApiModel LibraryUser, out string LibraryUserCode, TransactionParam transactionParam = null)
{
LibraryUserCode = _randomKeyGenerator.GetUniqueKey(9);
LibraryUser.LibraryUserCode = LibraryUserCode;
var parameters = new DynamicParameters();
parameters.Add(name: "LibraryUserCode", value: LibraryUserCode);
parameters.Add(name: "title", value: LibraryUser.Title);
parameters.Add(name: "name", value: LibraryUser.Name);
parameters.Add(name: "phoneNumber", value: LibraryUser.PhoneNumber);
parameters.Add(name: "mobilePhoneNumber", value: LibraryUser.MobilePhoneNumber);
parameters.Add(name: "email", value: LibraryUser.Email);
parameters.Add(name: "alternativePhoneNumber", value: LibraryUser.AlternativePhoneNumber);
parameters.Add(name: "alternativeEmail", value: LibraryUser.AlternativeEmail);
parameters.Add(name: "addressLine1", value: LibraryUser.AddressLine1);
parameters.Add(name: "addressLine2", value: LibraryUser.AddressLine2);
parameters.Add(name: "addressLine3", value: LibraryUser.AddressLine3);
parameters.Add(name: "city", value: LibraryUser.City);
parameters.Add(name: "county", value: LibraryUser.County);
parameters.Add(name: "country", value: LibraryUser.Country);
parameters.Add(name: "postcode", value: LibraryUser.Postcode);
parameters.Add(name: "GDPRInformedDate", value: LibraryUser.GDPRInformedDate);
parameters.Add(name: "GDPRInformedBy", value: LibraryUser.GDPRInformedBy);
parameters.Add(name: "GDPRHowInformed", value: LibraryUser.GDPRHowInformed);
parameters.Add(name: "GDPRNotes", value: LibraryUser.GDPRNotes);
parameters.Add(name: "LibraryUserByPost", value: LibraryUser.LibraryUserByPost);
parameters.Add(name: "LibraryUserByPostConsentDate", value: LibraryUser.LibraryUserByPostConsentDate);
parameters.Add(name: "LibraryUserByEmail", value: LibraryUser.LibraryUserByEmail);
parameters.Add(name: "LibraryUserByEmailConsentDate", value: LibraryUser.LibraryUserByEmailConsentDate);
parameters.Add(name: "LibraryUserByPhone", value: LibraryUser.LibraryUserByPhone);
parameters.Add(name: "LibraryUserByPhoneConsentDate", value: LibraryUser.LibraryUserByPhoneConsentDate);
parameters.Add(name: "LibraryUserBySMS", value: LibraryUser.LibraryUserBySMS);
parameters.Add(name: "LibraryUserBySMSConsentDate", value: LibraryUser.LibraryUserBySMSConsentDate);
parameters.Add(name: "createdBy", value: LibraryUser.CreatedBy);
parameters.Add(name: "dateCreated", value: LibraryUser.DateCreated);
parameters.Add(name: "modifiedBy", value: LibraryUser.ModifiedBy);
parameters.Add(name: "dateModified", value: LibraryUser.DateModified);
int rowaffected = 0;
string sql = @"INSERT INTO LibraryUser (
LibraryUserCode,Title,Name,PhoneNumber,MobilePhoneNumber,Email, AlternativePhoneNumber, AlternativeEmail,
AddressLine1, AddressLine2, AddressLine3, City, County, Country, Postcode,
GDPRInformedDate,GDPRInformedBy,GDPRHowInformed,GDPRNotes,LibraryUserByPost,LibraryUserByPostConsentDate,LibraryUserByEmail,
LibraryUserByEmailConsentDate,LibraryUserByPhone,LibraryUserByPhoneConsentDate,LibraryUserBySMS,LibraryUserBySMSConsentDate,
CreatedBy, DateCreated, ModifiedBy, DateModified
)
VALUES(
@LibraryUserCode, @title, @name, @phoneNumber, @mobilePhoneNumber, @email, @alternativePhoneNumber, @alternativeEmail,
@addressLine1, @addressLine2, @addressLine3, @city, @county, @country, @postcode,
@GDPRInformedDate,@GDPRInformedBy,@GDPRHowInformed,@GDPRNotes,@LibraryUserByPost,@LibraryUserByPostConsentDate,@LibraryUserByEmail,
@LibraryUserByEmailConsentDate,@LibraryUserByPhone,@LibraryUserByPhoneConsentDate,@LibraryUserBySMS,@LibraryUserBySMSConsentDate,
@createdBy, NOW(), @modifiedBy, NOW())" ;
if (transactionParam != null)
{
rowaffected = transactionParam.Connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text,
transaction: transactionParam.Transaction);
}
else
{
using (var connection = this.OpenConnection())
{
rowaffected = connection.Execute(
sql,
param: parameters,
commandType: CommandType.Text);
}
}
return(rowaffected);
}