alejandroMunozMulet
commented
8 years ago Same Issue here dapper.Contrip Insert is broken in 1.50, I had to downgrade to 1.43 to make it work.
Open justinjstark opened 8 years ago
alejandroMunozMulet
commented
8 years ago Same Issue here dapper.Contrip Insert is broken in 1.50, I had to downgrade to 1.43 to make it work.
cmarrades
commented
7 years ago +1.
Had to downgrade version to get it working.
vilix13
commented
7 years ago For me downgrade dont help. In mssql table I have bigint type, in class long?. Try with 1.5 dapper and 1.4.2
But if I change long? to int? I have another strange error:
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Additional information: The variable name '@Id' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@Surname".Code:
var empl2 = new Employee
{
Active = empl.Active,
Birthdate = empl.Birthdate,
CreatedAt = empl.CreatedAt,
Deleted = empl.Deleted,
Firstname = empl.Firstname,
Middlename = empl.Middlename,
Sex = empl.Sex,
Surname = "fdfdf",
UpdatedAt = empl.UpdatedAt,
ValidUntil = empl.ValidUntil
};
var id = db.Insert<Employee>(empl2);public class Employee : Entity
{
public int? Id { get; set; }
public string Surname { get; set; }
public string Firstname { get; set; }
public string Middlename { get; set; }
public string Sex { get; set; }
public DateTime Birthdate { get; set; }
public DateTime ValidUntil { get; set; }
public string Photo { get; set; }
public bool Active { get; set; }
public bool Deleted { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}Anyone knows anything about this?? Tried to update to the last version and still failing
bobteplitsky
commented
7 years ago I ran into this recently and discovered that using the [ExplicitKey] attribute on the Id property of my C# class fixed the issue. I had previously been using the [Key] attribute. Haven't tested with no attribute.
jons-aura
commented
6 years ago I just hit this as well. It looks like setting the ID value on the returned object is where the error happens
ChangeType doesn't handle Nullable values, hence the error.
Looks like this was fixed but not everywhere? https://github.com/StackExchange/Dapper/commit/07fe543f1530b33818c9ce989bf0672e145c954d
AGenius
commented
5 years ago I found this while diagnosing my problem
I have found that Dapper Insert returns the invalid cast when you have the Key field as a nullable Int or long
if you have the Key field as none nullable it works!!
I use the nullability to determine if a record is new or not so I need it so I ended up with a work around
[Dapper.Contrib.Extensions.Key]
public long ID
{
get; set;
}
[Computed]
public long? _ID
{
get
{
if (ID == 0)
{
return null;
}
return ID;
}
}
yeahe83
commented
4 years ago 1.60.1，need to add a extra model for insert without the nullable id prop
AGenius
commented
4 years ago That's a silly idea, why have another copy of the model just for inserts, defeats the whole point of the model object! maintaining 2 copies is not the option.
I have been using my own way around it just fine, I always use the _ID instead of ID
yeahe83
commented
4 years ago That's a silly idea, why have another copy of the model just for inserts, defeats the whole point of the model object! maintaining 2 copies is not the option.
I have been using my own way around it just fine, I always use the _ID instead of ID
Yes, it looks strange :)
// viewmodel
[Table("Car")]
public class Car_Get : Car_Edit
{
public string OwnerName { get; set; }
}
[Table("Car")]
public class Car_Edit : Car_Insert
{
[Key]
public long? ID { get; set; }
}
[Table("Car")]
public class Car_Insert
{
public string Brand { get; set; }
public string Color { get; set; }
public int? OwnerID { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
}or finally give up using nullable as key
[Table("Car")]
public class Car
{
[Key]
public long ID { get; set; }
public string Brand { get; set; }
public string Color { get; set; }
public int? OwnerID { get; set; }
[Computed]
public string OwnerName { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
public string XXX { get; set; }
}While this is a viable option for a simple couple of properties , its not good for complex many property models.
For example, and this is just one of a large number used in my project. I have a base model with the common properties and methods used for all models and I like to encapsulate the Read and Save inside the model, I know its not a simple POCO but it serves well and keeps all the logic inside the objects
For Example
using Dapper.Contrib.Extensions;
using SEPBusiness.Model.Main.Contact;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using AutoMapper;
using SEPBusiness.Model.Main.Common;
namespace SEPBusiness.Model.Main.Quote
{
[Table("QuoteHeader")]
public class QuoteHeader : BaseEntity
{
public QuoteHeader()
{
_DeletedDetailRecords = new List<int>();
_DetailRecords = null;
Status = eDocStatus.New;
Carriage = 0;
LOC_Cost = 0;
VatRate = 0;
VatOverride = false;
Confirmed = false;
DocDate = DateTime.Now.Date;
}
public QuoteHeader(bool isNew, int? LoggedInUserRecord)
{
_DeletedDetailRecords = new List<int>();
_DetailRecords = null;
Status = eDocStatus.New;
Carriage = 0;
LOC_Cost = 0;
VatRate = 0;
VatOverride = false;
Confirmed = false;
DocDate = DateTime.Now.Date;
if (isNew)
{
// Need to get the next DocID
Quote_Order_Docs newDoc = new Quote_Order_Docs();
newDoc.DocType = "Quote";
newDoc.CreatedBy = LoggedInUserRecord;
newDoc.CreatedOn = DateTime.Now;
newDoc.SaveRecord();
WOrder = newDoc.ID;
}
}
public QuoteHeader(int ID)
{
ReadRecord(ID);
}
public bool DeleteRecord(int? LoggedInUserRecord)
{
if (this._ID.HasValue)
{
// Now perform the common entity Delete
// Delete details
if (DetailRecords != null)
{
foreach (QuoteDetail item in DetailRecords)
{
item.DeleteRecord(LoggedInUserRecord);
}
}
if (this.SageAccountData._ID.HasValue)
{
_DeleteEntityRecord<DocSageData>(this.SageAccountData, this.SageAccountData.ID, LoggedInUserRecord);
}
return _DeleteEntityRecord(this, this._ID.Value, LoggedInUserRecord);
}
return false;
}
// Causes a reload of the record - used to discard any changes and ensures the record in memory is upto date.
public void ReadRecord()
{
if (_ID.HasValue)
{
ReadRecord(_ID.Value);
this.DetailRecords = null; // Forces a repopulation of the detail lines
}
}
// Return the record for the specified ID
public void ReadRecord(int ID)
{
_DeletedDetailRecords = new List<int>();
if (ID > 0)
{
// Ensure the product list cache is cleared before we load
GlobalLists.Products = null;
_ReadRecord<QuoteHeader>(ID);
this.DetailRecords = null; // Forces a repopulation of the detail lines
}
}
// Save Role Information
public bool SaveRecord(int? LoggedInUserID)
{
bool bNewRecord = false;
if (!this._ID.HasValue)
{
bNewRecord = true;
}
List<int> _deleteThese = _DeletedDetailRecords; // take copy before save as the trigger re-reads the record and
bool bOK = _SaveRecord(this, LoggedInUserID);
if (bOK)
{
// Remove old detail lines
foreach (int item in _deleteThese)
{
QuoteDetail deleteMe = new QuoteDetail(item);
deleteMe.DeleteRecord(LoggedInUserID);
}
_DeletedDetailRecords = new List<int>();
// Save Current prices
if (_DetailRecords != null)
{
foreach (QuoteDetail item in _DetailRecords)
{
if (item.HeaderID == 0)
{
item.HeaderID = this.ID; // Ensure header ID is on the detail record
}
item.SaveRecord(LoggedInUserID);
}
}
// Save the Sage Doc Data if not already saved
if (bNewRecord && this.SageAccountData == null)
{
Common.DocSageData docSage = new Common.DocSageData();
docSage.ACCOUNT_REF = ACCOUNT_REF;
docSage.DocType = "Quote";
docSage.HeaderID = ID;
docSage.NAME = ACCOUNTREFRecord.NAME;
docSage.Address1 = ACCOUNTREFRecord.ADDRESS_1;
docSage.Address2 = ACCOUNTREFRecord.ADDRESS_2;
docSage.Address3 = ACCOUNTREFRecord.ADDRESS_3;
docSage.Address4 = ACCOUNTREFRecord.ADDRESS_4;
docSage.Address5 = ACCOUNTREFRecord.ADDRESS_5;
docSage.Contact = ACCOUNTREFRecord.CONTACT_NAME;
docSage.Telephone = ACCOUNTREFRecord.TELEPHONE;
docSage.Fax = ACCOUNTREFRecord.FAX;
if (this.CustomerContact.HasValue)
{
docSage.DelAddress1 = CustomerContactRecord.Address1;
docSage.DelAddress2 = CustomerContactRecord.Address2;
docSage.DelAddress3 = CustomerContactRecord.City;
docSage.DelAddress4 = CustomerContactRecord.County;
docSage.DelAddress5 = CustomerContactRecord.PostCode;
docSage.DelContact = CustomerContactRecord.FullName;
docSage.DelTelephone = CustomerContactRecord.Telephone;
docSage.DelFax = CustomerContactRecord.Fax;
docSage.DelEmailAddress = CustomerContactRecord.EmailAddress;
docSage.DelMobile = CustomerContactRecord.MobilePhone;
}
else
{
docSage.DelAddress1 = ACCOUNTREFRecord.DEL_ADDRESS_1;
docSage.DelAddress2 = ACCOUNTREFRecord.DEL_ADDRESS_2;
docSage.DelAddress3 = ACCOUNTREFRecord.DEL_ADDRESS_3;
docSage.DelAddress4 = ACCOUNTREFRecord.DEL_ADDRESS_4;
docSage.DelAddress5 = ACCOUNTREFRecord.DEL_ADDRESS_5;
docSage.DelContact = ACCOUNTREFRecord.DEL_CONTACT_NAME;
docSage.DelTelephone = ACCOUNTREFRecord.DEL_TELEPHONE;
docSage.DelFax = ACCOUNTREFRecord.DEL_FAX;
docSage.DelEmailAddress = ACCOUNTREFRecord.E_MAIL;
docSage.DelMobile = ACCOUNTREFRecord.DEL_TELEPHONE;
}
docSage.SaveRecord(LoggedInUserID);
}
else
{
if (this.SageAccountData != null)
{
SageAccountData.ReadRecord();
if (SageAccountData.ACCOUNT_REF != ACCOUNT_REF)
{
SageAccountData.ACCOUNT_REF = ACCOUNT_REF;
SageAccountData.NAME = ACCOUNTREFRecord.NAME;
SageAccountData.Address1 = ACCOUNTREFRecord.ADDRESS_1;
SageAccountData.Address2 = ACCOUNTREFRecord.ADDRESS_2;
SageAccountData.Address3 = ACCOUNTREFRecord.ADDRESS_3;
SageAccountData.Address4 = ACCOUNTREFRecord.ADDRESS_4;
SageAccountData.Address5 = ACCOUNTREFRecord.ADDRESS_5;
SageAccountData.Contact = ACCOUNTREFRecord.CONTACT_NAME;
SageAccountData.Telephone = ACCOUNTREFRecord.TELEPHONE;
SageAccountData.Fax = ACCOUNTREFRecord.FAX;
if (this.CustomerContact.HasValue)
{
SageAccountData.DelAddress1 = CustomerContactRecord.Address1;
SageAccountData.DelAddress2 = CustomerContactRecord.Address2;
SageAccountData.DelAddress3 = CustomerContactRecord.City;
SageAccountData.DelAddress4 = CustomerContactRecord.County;
SageAccountData.DelAddress5 = CustomerContactRecord.PostCode;
SageAccountData.DelContact = CustomerContactRecord.FullName;
SageAccountData.DelTelephone = CustomerContactRecord.Telephone;
SageAccountData.DelFax = CustomerContactRecord.Fax;
SageAccountData.DelEmailAddress = CustomerContactRecord.EmailAddress;
SageAccountData.DelMobile = CustomerContactRecord.MobilePhone;
}
else
{
SageAccountData.DelAddress1 = ACCOUNTREFRecord.DEL_ADDRESS_1;
SageAccountData.DelAddress2 = ACCOUNTREFRecord.DEL_ADDRESS_2;
SageAccountData.DelAddress3 = ACCOUNTREFRecord.DEL_ADDRESS_3;
SageAccountData.DelAddress4 = ACCOUNTREFRecord.DEL_ADDRESS_4;
SageAccountData.DelAddress5 = ACCOUNTREFRecord.DEL_ADDRESS_5;
SageAccountData.DelContact = ACCOUNTREFRecord.DEL_CONTACT_NAME;
SageAccountData.DelTelephone = ACCOUNTREFRecord.DEL_TELEPHONE;
SageAccountData.DelFax = ACCOUNTREFRecord.DEL_FAX;
SageAccountData.DelEmailAddress = ACCOUNTREFRecord.E_MAIL;
SageAccountData.DelMobile = ACCOUNTREFRecord.DEL_TELEPHONE;
}
}
SageAccountData.DocType = "Quote"; // Ensures its the correct type
SageAccountData.HeaderID = this.ID;
this.SageAccountData.SaveRecord(LoggedInUserID);
}
}
}
return bOK;
}
public bool Updatefields(int LoggedInUserID)
{
return _UpdateChangedFields(this, LoggedInUserID);
}
[Computed]
public QuoteHeader OriginalRecord
{
get { return _OriginalRecord; }
}
[Computed]
private bool _DetailsLoaded { get; set; }
[Computed]
private List<QuoteDetail> _DetailRecords
{
get;
set;
}
[Editable(false)]
[Computed]
public List<QuoteDetail> DetailRecords
{
get
{
if (isRead && _ID.HasValue && (_DetailRecords == null || _DetailsLoaded == false))
{
_DetailRecords = new List<QuoteDetail>();
IList<QuoteDetail> records = BTPUtility.sqlDbAccessObject.ReadRecords<QuoteDetail>($"HeaderID = {_ID.Value} order by ItemNo");
_DetailsLoaded = true;
if (records != null && records.Count > 0)
{
foreach (QuoteDetail item in records)
{
try
{
item.ReadRecord(); // Read record to ensure the _OriginalRecord is setup
_DetailRecords.Add(item);
}
catch (Exception ex)
{
throw;
}
}
}
}
return _DetailRecords;
}
set
{
_DetailRecords = value;
if (value == null)
{
_DetailsLoaded = false;
}
}
}
[Computed]
private List<int> _DeletedDetailRecords
{
get;
set;
}
public bool RemoveDetailRecord(QuoteDetail entry)
{
QuoteDetail FoundEntry = _DetailRecords.Find(x => x == entry);
if (FoundEntry != null)
{
// Remove it from the collection
if (FoundEntry._ID.HasValue)
{
_DeletedDetailRecords.Add(entry.ID); // Record so it can be deleted if the record is saved
}
_DetailRecords.Remove(FoundEntry);
return true;
}
return false;
}
private Sage.SAGE_SALES_LEDGER _ACCOUNTREFRecord { get; set; }
[Computed]
[Editable(false)]
public Sage.SAGE_SALES_LEDGER ACCOUNTREFRecord
{
get
{
if (isRead || !_ID.HasValue)
{
if (!string.IsNullOrEmpty(ACCOUNT_REF) &&
_ACCOUNTREFRecord == null ||
OriginalRecord != null &&
ACCOUNT_REF != OriginalRecord.ACCOUNT_REF ||
_ACCOUNTREFRecord != null &&
_ACCOUNTREFRecord.ACCOUNT_REF != ACCOUNT_REF)
{
_ACCOUNTREFRecord = GlobalLists.SAGE_SALES_LEDGER.Where(x => x.ACCOUNT_REF == ACCOUNT_REF).SingleOrDefault();
}
}
return _ACCOUNTREFRecord;
}
set { _ACCOUNTREFRecord = value; }
}
[Computed]
[Editable(false)]
public string ACCOUNT_REFName
{
get
{
return ACCOUNTREFRecord != null ? ACCOUNTREFRecord.NAME : SageAccountData.NAME;
}
}
private ContactRecord _CustomerContactRecord { get; set; }
[Computed]
[Editable(false)]
public ContactRecord CustomerContactRecord
{
get
{
if (isRead || !_ID.HasValue)
{
if (CustomerContact.HasValue &&
_CustomerContactRecord == null ||
OriginalRecord != null &&
CustomerContact != OriginalRecord.CustomerContact ||
_CustomerContactRecord != null &&
_CustomerContactRecord.ID != ID)
{
if (CustomerContact.HasValue)
{
_CustomerContactRecord = BTPUtility.sqlDbAccessObject.ReadRecord<ContactRecord>(CustomerContact);
}
}
}
return _CustomerContactRecord;
}
set { _CustomerContactRecord = value; }
}
private Common.DocSageData _SageAccountData { get; set; }
[Computed]
[Editable(false)]
public Common.DocSageData SageAccountData
{
get
{
// Now populated using the Multi mapping feature in Dapper
if (_ID.HasValue && isRead && _SageAccountData == null)
{
_SageAccountData = BTPUtility.sqlDbAccessObject.ReadRecordWithWhere<Common.DocSageData>($"DocType = 'Quote' AND HeaderID = {_ID.Value}");
}
//else if (_ID.HasValue)
//{
// _SageAccountData = GlobalLists.DocSageData.Where(x => x.HeaderID == ID).SingleOrDefault();
//}
return _SageAccountData;
}
set { _SageAccountData = value; }
}
[Computed]
[Editable(false)]
public int RequiredCount
{
get
{
int cnt = 0;
foreach (QuoteDetail lineItem in DetailRecords)
{
if (lineItem.Required.HasValue && lineItem.Required.Value == true)
{
cnt++;
}
}
return cnt;
}
}
public void RecalculateTotals()
{
NetValue = 0;
VatValue = 0;
if (DetailRecords != null && DetailRecords.Count > 0)
{
foreach (QuoteDetail item in DetailRecords)
{
item.RecalculateTotals();
NetValue += item.NetValue.HasValue ? item.NetValue.Value : 0;
VatValue += item.VatValue.HasValue ? item.VatValue.Value : 0;
}
}
// Deal with VAT overridden on header
if (VatOverride.HasValue && VatOverride.Value == true)
{
// The Vat code on the document header should over ride the items vat
VatValue = (NetValue + (Carriage.HasValue ? Carriage.Value : 0) + (LOC_Cost.HasValue ? LOC_Cost.Value : 0)) * (VatRate.HasValue ? VatRate.Value : 0);
}
else
{
VatValue = (VatValue + (Carriage.HasValue ? Carriage.Value : 0) + (LOC_Cost.HasValue ? LOC_Cost.Value : 0));
}
GrossValue = NetValue + (Carriage.HasValue ? Carriage.Value : 0) + (LOC_Cost.HasValue ? LOC_Cost.Value : 0) + (VatValue.HasValue ? VatValue.Value : 0);
}
#region Properties
public int? WOrder { get { return Get<int?>(); } set { Set(value); } }
public string ACCOUNT_REF { get { return Get<string>(); } set { Set(value); } }
public DateTime? DocDate { get { return Get<DateTime?>(); } set { Set(value); } }
public string CustomerRef { get { return Get<string>(); } set { Set(value); } }
public string QuoteRef { get { return Get<string>(); } set { Set(value); } }
public int? CustomerContact { get { return Get<int?>(); } set { Set(value); } }
public string TransportMethod { get { return Get<string>(); } set { Set(value); } }
public string CourierTargetDelivery { get { return Get<string>(); } set { Set(value); } }
public string Note { get { return Get<string>(); } set { Set(value); } }
public bool? Confirmed { get { return Get<bool?>(); } set { Set(value); } }
public DateTime? DatePrinted { get { return Get<DateTime?>(); } set { Set(value); } }
public decimal? Carriage { get { return Get<decimal?>(); } set { Set(value); } }
public decimal? LOC_Cost { get { return Get<decimal?>(); } set { Set(value); } }
public decimal? NetValue { get { return Get<decimal?>(); } set { Set(value); } }
public string VatID { get { return Get<string>(); } set { Set(value); } }
public decimal? VatRate { get { return Get<decimal?>(); } set { Set(value); } }
public decimal? VatValue { get { return Get<decimal?>(); } set { Set(value); } }
public decimal? GrossValue { get { return Get<decimal?>(); } set { Set(value); } }
public bool? VatOverride { get { return Get<bool?>(); } set { Set(value); } }
//public string ConformanceType { get { return Get<string>(); } set { Set(value); } }
public DateTime? ConvertedToOrder
{
get { return Get<DateTime?>(); }
set { Set(value); }
}
public eDocStatus? Status { get { return Get<eDocStatus?>(); } set { Set(value); } }
public int? CRS_Comp { get { return Get<int?>(); } set { Set(value); } }
public int? CRS_Value { get { return Get<int?>(); } set { Set(value); } }
public bool? isCallOff { get { return Get<bool?>(); } set { Set(value); } }
#endregion
}
}
using Dapper.Contrib.Extensions;
using Newtonsoft.Json;
using SEPBusiness.Model.Main.Security;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using static ObjectExtensions;
namespace SEPBusiness.Model.Main
{
[Serializable]
public abstract class BaseEntity : BindableObject
{
[Computed]
internal dynamic _OriginalRecord { get; set; }
// Standard Properties for all objects
[Dapper.Contrib.Extensions.Key]
public int ID
{
get; set;
}
[Computed]
public int? _ID
{
get
{
if (ID == 0)
{
return null;
}
return ID;
}
}
/// <summary>Read Only property for Databinding</summary>
/// <value><para>True/False</para></value>
/// <remarks>Used for DataBound actions to set ReadOnly properties</remarks>
[Editable(false)]
[Computed]
public bool isReadOnly
{
get;
set;
}
/// <summary>Created On Date Time of record</summary>
/// <value><para>DateTime</para></value>
/// <remarks>DateTime Stamp for when record was created</remarks>
public DateTime? CreatedOn
{
get { return Get<DateTime?>(); }
set { Set(value); }
}
/// <summary>Created By</summary>
/// <value><para>Integer value</para></value>
/// <remarks>ID of the user that created the record</remarks>
public int? CreatedBy
{
get { return Get<int?>(); }
set { Set(value); }
}
[Editable(false)]
[JsonIgnore]
[Computed]
public string CreatedByName
{
get { return CreatedByRecord != null ? CreatedByRecord.FullName : ""; }
}
[NonSerialized]
[JsonIgnore]
private UserRecord _CreatedByRecord = null;
/// <summary>The UserRecord Object who created the record </summary>
/// <value><para>UserRecord class</para></value>
/// <remarks>Holds a copy of the UserRecord that created the entity record</remarks>
[Editable(false)]
[JsonIgnore]
[Computed]
public UserRecord CreatedByRecord
{
get
{
if (GlobalLists._UserRecord == null)
{
GlobalLists._UserRecord = BTPUtility.sqlDbAccessObject.ReadRecords<UserRecord>("Active=1");
}
if (_CreatedByRecord == null && CreatedBy.HasValue)
{
_CreatedByRecord = GlobalLists._UserRecord.Where(x => x.ID == CreatedBy.Value).SingleOrDefault();
}
return _CreatedByRecord;
}
}
/// <summary>Updated on Date Time of record</summary>
/// <value><para>DateTime</para></value>
/// <remarks>DateTime Stamp for when the record was updated</remarks>
public DateTime? UpdatedOn
{
get { return Get<DateTime?>(); }
set { Set(value); }
}
/// <summary>Updated By</summary>
/// <value><para>Integer alue</para></value>
/// <remarks>ID of the user that updated the record</remarks>
public int? UpdatedBy
{
get { return Get<int?>(); }
set { Set(value); }
}
[Editable(false)]
[JsonIgnore]
[Computed]
public string UpdatedByName
{
get { return UpdatedByRecord != null ? UpdatedByRecord.FullName : ""; }
}
/// <summary>The UserRecord Object who created the record </summary>
/// <value><para>UserRecord</para></value>
/// <remarks>Holds a copy of the UserRecord that updated the entity record</remarks>
[NonSerialized]
[JsonIgnore]
private UserRecord _UpdatedByRecord = null;
[Editable(false)]
[JsonIgnore]
[Computed]
public UserRecord UpdatedByRecord
{
get
{
if (GlobalLists._UserRecord == null)
{
GlobalLists._UserRecord = BTPUtility.sqlDbAccessObject.ReadRecords<UserRecord>("Active=1");
}
if (_UpdatedByRecord == null && UpdatedBy.HasValue)
{
_UpdatedByRecord = GlobalLists._UserRecord.Where(x => x.ID == UpdatedBy.Value).SingleOrDefault();
}
return _UpdatedByRecord;
}
}
// represents if the record was found in the database when read
public bool isRead = false;
/// <summary>Last Error recorded when the record was saved/read</summary>
/// <value><para>String Value Message</para></value>
/// <remarks></remarks>
[Editable(false)]
[Computed]
public string LastError
{
get;
protected set;
}
[Editable(false)]
[Computed]
public bool? LastSaveState
{
get;
private set;
}
#region Events
/// <summary>Event Handler for the RecordSaved event</summary>
/// <remarks></remarks>
public event EventHandler<RecordSavedEventArgs> RecordSaved;
/// <summary>Raise the Record Saved Event</summary>
internal void RaiseRecordSaved(int id, string message, bool success)
{
LastSaveState = success;
RecordSaved?.Invoke(this, new RecordSavedEventArgs(id, message, success));
}
#endregion Events
/// <summary>Save a Record to the Database</summary>
/// <typeparam name="TENTITY">the Generic Entity Type</typeparam>
/// <param name="entityRecord">The Entity object</param>
/// <param name="LoggedInUserID">The ID of the logged in user</param>
/// <param name="fromLogin">true if the save is being actioned from the login as the updated details are not required</param>
/// <returns>Returns True if saved correctly</returns>
internal bool _SaveRecord<TENTITY>(TENTITY entityRecord, int? LoggedInUserID, bool fromLogin = false) where TENTITY : class, new()
{
if (entityRecord != null)
{
int? ID = ((dynamic)entityRecord)._ID;
if (ID.HasValue && ID.Value > 0)
{
try
{
List<Variance> differences = PropertyDifferences<TENTITY>(entityRecord);
// Only physically save if there are differences
if (differences.Count > 0)
{
// check if only misc category changes exist (record objects mainly)
bool noChanges = true;
foreach (Variance variance in differences)
{
if (variance.NewValue != null)
{
if (!variance.NewValue.GetType().FullName.ToLower().Contains("list`1") &&
!variance.NewValue.GetType().FullName.ToLower().Contains("sepbusiness.model")
)
{
noChanges = false;
}
}
}
if (!noChanges)
{
if (!fromLogin)
{
this.UpdatedBy = LoggedInUserID;
this.UpdatedOn = DateTime.Now;
}
BTPUtility.sqlDbAccessObject.UpdateRecord<TENTITY>(entityRecord);
this.LastError = string.Empty;
// Record the records changes in the audit table
if (LoggedInUserID.HasValue)
{
foreach (Variance variance in differences)
{
string propName = variance.PropertyName.ToLower();
if (variance.PropertyCategory.ToLower() == "misc")
{
propName = "misc";
}
switch (propName)
{
case "createdon":
case "createdby":
case "updatedon":
case "updatedby":
//case "misc":
// Dont log these fields
break;
default:
AuditRecord auditItem = new AuditRecord
{
entity = entityRecord.GetType().Name,
entityid = ID.Value,
entrydate = DateTime.Now,
fieldname = variance.PropertyName,
fielddesc = variance.PropertyDisplayName,
oldvalue = variance.OldValue != null ? variance.OldValue.ToString() : "",
newvalue = variance.NewValue != null ? variance.NewValue.ToString() : "",
userid = LoggedInUserID,
message = "UPDATE"
};
auditItem.SaveRecord();
break;
}
}
}
// Update _OriginalRecord
//AutoMapper.Mapper.Reset();
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TENTITY, TENTITY>();
});
var mapper = config.CreateMapper();
mapper.Map(this, _OriginalRecord);
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(this, _OriginalRecord); // Take a copy for comparisons later
}
RaiseRecordSaved(ID.Value, LastError, true);
}
return true;
}
catch (Exception ex)
{
this.LastError = ex.Message;
RaiseRecordSaved(ID.Value, LastError, false);
throw;
}
}
else
{
this.CreatedBy = LoggedInUserID;
this.CreatedOn = DateTime.Now;
this.UpdatedBy = null;
this.UpdatedOn = null;
try
{
BTPUtility.sqlDbAccessObject.InsertRecord<TENTITY>(entityRecord);
if (LoggedInUserID.HasValue)
{
List<Variance> differences = PropertyDifferences<TENTITY>(entityRecord);
foreach (Variance variance in differences)
{
string propName = variance.PropertyName.ToLower();
switch (propName)
{
case "createdon":
case "createdby":
case "updatedon":
case "updatedby":
// Dont log these fields
break;
default:
AuditRecord auditItem = new AuditRecord
{
entity = entityRecord.GetType().Name,
entityid = ID.Value,
entrydate = DateTime.Now,
fieldname = variance.PropertyName,
oldvalue = variance.OldValue != null ? variance.OldValue.ToString() : "",
newvalue = variance.NewValue != null ? variance.NewValue.ToString() : "",
userid = LoggedInUserID,
message = "NEW"
};
auditItem.SaveRecord();
break;
}
}
}
// Update _OriginalRecord
_OriginalRecord = new TENTITY();
//AutoMapper.Mapper.Reset();
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TENTITY, TENTITY>();
});
var mapper = config.CreateMapper();
mapper.Map(this, _OriginalRecord);
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(this, _OriginalRecord); // Take a copy for comparisons later
RaiseRecordSaved(_ID.Value, LastError, true);
return true;
}
catch (Exception ex)
{
this.LastError = ex.Message;
RaiseRecordSaved(-1, LastError, false);
throw;
}
}
}
return false;
}
/// <summary>Only update the fields that have changed to the Database, This will use the original record to determine what properties have changed
/// and only commit those to the DB
/// <typeparam name="TENTITY"></typeparam>
/// <param name="entityRecord"></param>
/// <param name="LoggedInUserID"></param>
/// <param name="fromLogin"></param>
/// <returns></returns>
internal bool _UpdateChangedFields<TENTITY>(TENTITY entityRecord, int? LoggedInUserID, bool fromLogin = false) where TENTITY : class, new()
{
if (entityRecord != null)
{
int? ID = ((dynamic)entityRecord)._ID;
if (ID.HasValue && ID.Value > 0)
{
try
{
List<Variance> differences = PropertyDifferences<TENTITY>(entityRecord);
// Only physically save if there are differences
if (differences.Count > 0)
{
this.LastError = string.Empty;
List<string> fields = new List<string>();
foreach (Variance variance in differences)
{
string fieldName = variance.PropertyName;
if (fieldName.ToLower() != "originalrecord"
&& fieldName.ToLower() != "sageaccountdata"
&& fieldName.ToLower() != "archiveproduct")
{
fields.Add(fieldName);
}
}
BTPUtility.sqlDbAccessObject.UpdateFields<TENTITY>(entityRecord, fields);
// Record the records changes in the audit table
if (LoggedInUserID.HasValue)
{
foreach (Variance variance in differences)
{
string propName = variance.PropertyName.ToLower();
switch (propName)
{
case "createdon":
case "createdby":
case "updatedon":
case "updatedby":
case "originalrecord":
// Dont log these fields
break;
default:
AuditRecord auditItem = new AuditRecord
{
entity = entityRecord.GetType().Name,
entityid = ID.Value,
entrydate = DateTime.Now,
fieldname = variance.PropertyName,
oldvalue = variance.OldValue != null ? variance.OldValue.ToString() : "",
newvalue = variance.NewValue != null ? variance.NewValue.ToString() : "",
userid = LoggedInUserID,
message = "UPDATE"
};
auditItem.SaveRecord();
break;
}
}
}
// Update _OriginalRecord
//AutoMapper.Mapper.Reset();
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TENTITY, TENTITY>();
});
var mapper = config.CreateMapper();
mapper.Map(this, _OriginalRecord);
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(this, _OriginalRecord); // Take a copy for comparisons later
RaiseRecordSaved(ID.Value, LastError, true);
return true;
}
}
catch (Exception ex)
{
this.LastError = ex.Message;
RaiseRecordSaved(ID.Value, LastError, false);
throw;
}
}
}
return false;
}
/// <summary>Save a value for a single field to the database</summary>
/// <param name="FieldValue" type="object"><para>New value for the field in the Database</para></param>
internal bool _UpdateSingleField<TENTITY>(TENTITY entityRecord, string fieldName, object oldFieldValue, object newFieldValue, int? LoggedInUserID)
{
if (entityRecord != null)
{
int? ID = ((dynamic)entityRecord)._ID;
if (ID.HasValue && ID.Value > 0)
{
try
{
this.LastError = string.Empty;
List<string> fields = new List<string>();
fields.Add(fieldName);
BTPUtility.sqlDbAccessObject.UpdateField<TENTITY>(entityRecord, fieldName, newFieldValue);
// Record the records changes in the audit table
if (LoggedInUserID.HasValue)
{
switch (fieldName.ToLower())
{
case "createdon":
case "createdby":
case "updatedon":
case "updatedby":
case "originalrecord":
// Dont log these fields
break;
default:
AuditRecord auditItem = new AuditRecord
{
entity = entityRecord.GetType().Name,
entityid = ID.Value,
entrydate = DateTime.Now,
fieldname = fieldName,
oldvalue = oldFieldValue.ToString(),
newvalue = newFieldValue.ToString(),
userid = LoggedInUserID,
message = "UPDATE"
};
auditItem.SaveRecord();
break;
}
}
// Update _OriginalRecord
//AutoMapper.Mapper.Reset();
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TENTITY, TENTITY>();
});
var mapper = config.CreateMapper();
mapper.Map(this, _OriginalRecord);
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(this, _OriginalRecord); // Take a copy for comparisons later
RaiseRecordSaved(ID.Value, LastError, true);
return true;
}
catch (Exception ex)
{
this.LastError = ex.Message;
RaiseRecordSaved(ID.Value, LastError, false);
throw;
}
}
}
return false;
}
// Return the record for the specified ID
internal bool _ReadRecord<TENTITY>(int ID) where TENTITY : class, new()
{
_OriginalRecord = new TENTITY();
if (ID > 0)
{
TENTITY tmpRecord = BTPUtility.sqlDbAccessObject.ReadRecord<TENTITY>(ID);
if (tmpRecord != null)
{
//((dynamic)tmpRecord).isRead = true;
//AutoMapper.Mapper.Reset();
var config = new AutoMapper.MapperConfiguration(cfg =>
{
cfg.CreateMap<TENTITY, TENTITY>();
});
var mapper = config.CreateMapper();
mapper.Map(tmpRecord, this);
// AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(tmpRecord, this);
////AutoMapper.Mapper.Reset();
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//var config2 = new AutoMapper.MapperConfiguration(cfg =>
//{
// cfg.CreateMap<TENTITY, TENTITY>();
//});
//mapper = config2.CreateMapper();
mapper.Map(tmpRecord, _OriginalRecord);
//AutoMapper.Mapper.Map(tmpRecord, _OriginalRecord); // Take a copy for comparisons later
this.isRead = true;
return true;
}
}
return false;
}
public TENTITY CloneMe<TENTITY>()
{
return (TENTITY)this.MemberwiseClone();
//TENTITY copyRecord = new TENTITY();
////AutoMapper.Mapper.Reset();
//AutoMapper.Mapper.Initialize(cfg => cfg.CreateMap<TENTITY, TENTITY>());
//AutoMapper.Mapper.Map(sourceEntity, copyRecord);
//return copyRecord;
}
public object GetPropertyValue(string PropertyName)
{
return BTPUtility.GetValueForPropertyByStringName(this, PropertyName);
}
internal bool _DeleteEntityRecord<TENTITY>(TENTITY entityRecord, int ID, int? userRecordID) where TENTITY : class
{
LastSaveState = null;
BTPUtility.WriteLog(entityRecord.GetType().Name.ToString(), ID, "Record Deleted ", "", "", "", userRecordID.Value);
return BTPUtility.sqlDbAccessObject.DeleteRecord<TENTITY>(entityRecord);
}
/// <summary>Build a list of field changes for the object</summary>
/// <typeparam name="TENTITY"></typeparam>
/// <param name="entityRecord"></param>
public List<Variance> PropertyDifferences<TENTITY>(TENTITY entityRecord)
{
return BTPUtility.GetDifferences<TENTITY>(_OriginalRecord, entityRecord, true);
}
}
}To duplicate this just for the sake of the nullable ID is not practical
gilb195
commented
4 years ago I've been having this issue as well since I'm using a nullable int for the ID in my models. The open PR by @jurakovic seems to fix this issue.
Im Dapper.Contrib 1.50.0, when the model is defined with an Id of nullable int and the SQL Server database is an
INT IDENTITY(1,1), the result of a Dapper.ContribINSERTis:Reproduction:
This was working in Dapper.Contrib 1.43 but is broken in Dapper.Contrib 1.50.0.