public virtual int Insert(TEntity entity)
{
dynamic id;
int primaryKeyValue = 0;
string query = QB <TEntity> .Insert();
if (Transaction != null)
{
id = Connection.Query(query, entity, transaction: Transaction).FirstOrDefault();
}
else
{
id = Connection.Query(query, entity).FirstOrDefault();
}
if (id != null)
{
var firstItem = (IDictionary <string, object>)id;
foreach (var v in firstItem)
{
primaryKeyValue = Convert.ToInt32(v.Value);
}
}
return(primaryKeyValue);
}
public virtual int Insert(T entity)
{
SetOtherKeyValue(entity);
var query = QB <T> .Insert();
return(_dbContext.SqlConnection.Query <int>(query, entity).FirstOrDefault());
}
public int Process_LOAN_CL(List <AST_LOAN_CL_TMP> portFolios)
{
var sql = QB <AST_LOAN_CL_TMP> .Insert();
var affectedRows = Connection.Execute(sql, portFolios);
return(affectedRows);
}
public int Process_LOAN_PORTFOLIO(List <AST_RM_PORTFOLIO_TMP> portFolios)
{
//var sql = @"insert into ERMP.AST_LOAN_PORTFOLIO_TMP (File_Process_ID,ID_of_Area,Name_of_Area,Brn_Code,Branch_Name,ID_of_RM,Name_of_RM,Loan_Acct_No,INS_BY,INS_DATE)
// values (:File_Process_ID,:ID_of_Area,:Name_of_Area,:Brn_Code,:Branch_Name,:ID_of_RM,:Name_of_RM,:Loan_Acct_No,:INS_BY,:INS_DATE)";
var sql = QB <AST_RM_PORTFOLIO_TMP> .Insert();
var affectedRows = Connection.Execute(sql, portFolios);
return(affectedRows);
}
public void Test_QB_Insert_Update()
{
var person = new
{
Id = new Guid("cf9fad7a-9775-28b9-7693-11e6ea3b1484"),
Name = "John",
BirthDate = new DateTime(1975, 03, 17),
Version = Environment.TickCount
};
string tableName = "CustomTableName";
string n1 = nameof(person.Id);
string n2 = nameof(person.Name);
string n3 = nameof(person.BirthDate);
string n4 = nameof(person.Version);
string p1 = $"@#{n1}";
string p2 = $"@#{n2}";
string p3 = $"@#{n3}";
string p4 = $"@#{n4}";
(object, Type)p = default;
{ // Insert - all properties
var query = QB.Insert(person, tableName: tableName.AsSqlName());
string expectedSQL = $"INSERT [{tableName}] ([{n1}],[{n2}],[{n3}],[{n4}]) VALUES ({p1},{p2},{p3},{p4})";
Assert.IsTrue(query.SQL == expectedSQL);
Assert.IsTrue(query.ParameterMap.Count == 4);
p = query.ParameterMap[p1];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
p = query.ParameterMap[p2];
Assert.IsTrue((string)p.Item1 == person.Name);
Assert.IsTrue(p.Item2 == typeof(string));
p = query.ParameterMap[p3];
Assert.IsTrue((DateTime)p.Item1 == person.BirthDate);
Assert.IsTrue(p.Item2 == typeof(DateTime));
p = query.ParameterMap[p4];
Assert.IsTrue((int)p.Item1 == person.Version);
Assert.IsTrue(p.Item2 == typeof(int));
}
{ // Insert - excluded properties
var query = QB.Insert(person, excludedProperties: n => n == nameof(person.Version), tableName: tableName.AsSqlName());
string expectedSQL = $"INSERT [{tableName}] ([{n1}],[{n2}],[{n3}]) VALUES ({p1},{p2},{p3})";
Assert.IsTrue(query.SQL == expectedSQL);
Assert.IsTrue(query.ParameterMap.Count == 3);
p = query.ParameterMap[p1];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
p = query.ParameterMap[p2];
Assert.IsTrue((string)p.Item1 == person.Name);
Assert.IsTrue(p.Item2 == typeof(string));
p = query.ParameterMap[p3];
Assert.IsTrue((DateTime)p.Item1 == person.BirthDate);
Assert.IsTrue(p.Item2 == typeof(DateTime));
}
string p5 = $"@w@{n1}";
{ // Update - all properties
var query = QB.Update(person, tableName: tableName.AsSqlName());
string expectedSQL = $"UPDATE [{tableName}] SET [{n1}]={p1},[{n2}]={p2},[{n3}]={p3},[{n4}]={p4} WHERE [{n1}]={p5}";
Assert.IsTrue(query.SQL == expectedSQL);
Assert.IsTrue(query.ParameterMap.Count == 5);
p = query.ParameterMap[p1];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
p = query.ParameterMap[p2];
Assert.IsTrue((string)p.Item1 == person.Name);
Assert.IsTrue(p.Item2 == typeof(string));
p = query.ParameterMap[p3];
Assert.IsTrue((DateTime)p.Item1 == person.BirthDate);
Assert.IsTrue(p.Item2 == typeof(DateTime));
p = query.ParameterMap[p4];
Assert.IsTrue((int)p.Item1 == person.Version);
Assert.IsTrue(p.Item2 == typeof(int));
p = query.ParameterMap[p5];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
}
{ // Update - included properties - one property
var query = QB.Update(person, includedProperties: n => n == nameof(person.Name), tableName: tableName.AsSqlName());
string expectedSQL = $"UPDATE [{tableName}] SET [{n2}]={p2} WHERE [{n1}]={p5}";
Assert.IsTrue(query.SQL == expectedSQL);
Assert.IsTrue(query.ParameterMap.Count == 2);
p = query.ParameterMap[p2];
Assert.IsTrue((string)p.Item1 == person.Name);
Assert.IsTrue(p.Item2 == typeof(string));
p = query.ParameterMap[p5];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
}
{ // Update - included properties - all except two
var query = QB.Update(person, includedProperties: n => n != nameof(person.Id) && n != nameof(person.Version), tableName: tableName.AsSqlName());
string expectedSQL = $"UPDATE [{tableName}] SET [{n2}]={p2},[{n3}]={p3} WHERE [{n1}]={p5}";
Assert.IsTrue(query.SQL == expectedSQL);
Assert.IsTrue(query.ParameterMap.Count == 3);
p = query.ParameterMap[p2];
Assert.IsTrue((string)p.Item1 == person.Name);
Assert.IsTrue(p.Item2 == typeof(string));
p = query.ParameterMap[p3];
Assert.IsTrue((DateTime)p.Item1 == person.BirthDate);
Assert.IsTrue(p.Item2 == typeof(DateTime));
p = query.ParameterMap[p5];
Assert.IsTrue((Guid)p.Item1 == person.Id);
Assert.IsTrue(p.Item2 == typeof(Guid));
}
} // Test_QB_Insert_Update()