Below is the current code wish used to update/insert records from Oracle view to Sql server table using Dapper. there is not field to check last record updated date in the oracle view so I have added method to get hashcode by using property values. since Oracle view having more than 15k records and each record having 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)
}
}