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