Below is the current code which is used to update/insert records from Oracle view to SQL Server table using Dapper. There is not a field to check last record updated date in the oracle view so I have added a method to get hashcode by using property values. Since Oracle table has more than 15k records and each record has more more than 60 columns, this approach take more than 5 minutes. Any ideas/suggestions to improve below code?
using System;
using System.Configuration;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using Dapper;
namespace SyncSQLSvrWithHRDB
{
internal class Program
{
public static PropertyInfo[] PropertyNames = typeof(Employee).GetProperties();
private static void Main(string[] args)
{
var OracleConStr = ConfigurationManager.ConnectionStrings["OracleCon"].ConnectionString;
var SqlSvrConStr = ConfigurationManager.ConnectionStrings["SqlSvrCon"].ConnectionString;
using (OracleConnection OraCon = new OracleConnection(OracleConStr))
{
var res = OraCon.Query<Employee>(Constants.SelectSql).ToList();
res = res.GroupBy(x => x.EmpNumber.ToUpper()).Select(x => x.LastOrDefault()).ToList();
using (SqlConnection Sqlcon = new SqlConnection(SqlSvrConStr))
{
Sqlcon.Open();
for (int i = 0; i < res.Count; i++)
{
var item = Sqlcon.Query<Employee>(Constants.SelectEmpSql, new { EmpNumber= res[i].EmpNumber}).FirstOrDefault();
if (item == null) // new record found
{
Sqlcon.Execute(Constants.InsertSql, res[i]);
}
else if (GetHashcode(res[i]) != GetHashcode(item)) // record updated
{
Sqlcon.Execute(Constants.UpdateSql, res[i]);
}
}
}
}
}
public static int GetHashcode(Employee o)
{
int ret = 0;
foreach (var prop in PropertyNames)
{
object propValue = o.GetType().GetProperty(prop.Name).GetValue(o, null);
if (propValue != null)
{
ret += propValue.GetHashCode();
}
}
return ret;
}
}
public class Employee
{
public String EmpNumber{ get; set; }
// ... other properties (70)
}
}