public List <T> LoadData(T Obj, string strQueryCondition, int intCurrPage, string SPName, int PageSize,
Dictionary <string, object> Params)
{
var Cols = new List <T>();
try
{
Conn.Open();
comm = new SqlCommand(SPName, Conn);
comm.CommandTimeout = 2000;
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@CURRENTPAGE", SqlDbType.BigInt).Value = intCurrPage;
comm.Parameters.Add("@PAGESIZE", SqlDbType.BigInt).Value = PageSize;
comm.Parameters.Add("@STRQUERY", SqlDbType.VarChar, 4000).Value = strQueryCondition;
if (Params != null)
{
foreach (string Key in Params.Keys)
{
comm.Parameters.Add(Key, SqlDbType.DateTime).Value = Params[Key];
}
}
dr = comm.ExecuteReader();
DynamicBuilder <T> DynamicBuilder = null;
DynamicBuilder = DynamicBuilder <T> .CreateBuilder(dr);
if (dr.HasRows)
{
while (dr.Read())
{
T col = DynamicBuilder.Build(dr); // CreateInstanceT();
Cols.Add(col);
}
dr.NextResult();
//Record Count
if (dr.HasRows)
{
while (dr.Read())
{
TotalRecord = Convert.ToInt32(dr[0]);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.Close();
}
return(Cols);
}
public List <T> LoadData(T Obj, string strQueryCondition, int intCurrPage, string SPName, int PageSize,
string SortBy, string Sort)
{
var Cols = new List <T>();
try
{
Conn.Open();
comm = new SqlCommand(SPName, Conn);
comm.CommandTimeout = 2000;
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@CURRENTPAGE", SqlDbType.BigInt).Value = intCurrPage;
comm.Parameters.Add("@PAGESIZE", SqlDbType.BigInt).Value = PageSize;
comm.Parameters.Add("@STRQUERY", SqlDbType.VarChar, 4000).Value = strQueryCondition;
comm.Parameters.Add("@SORT_BY", SqlDbType.VarChar, 50).Value = SortBy;
comm.Parameters.Add("@SORT", SqlDbType.VarChar, 50).Value = Sort;
dr = comm.ExecuteReader();
DynamicBuilder <T> DynamicBuilder = null;
DynamicBuilder = DynamicBuilder <T> .CreateBuilder(dr);
if (dr.HasRows)
{
while (dr.Read())
{
T col = DynamicBuilder.Build(dr); // CreateInstanceT();
}
dr.NextResult();
//Record Count
if (dr.HasRows)
{
while (dr.Read())
{
TotalRecord = dr.GetInt32(0);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.Close();
}
return(Cols);
}
public List <T> GetData(string SQL)
{
var Cols = new List <T>();
var conn = new SqlConnection(constr);
try
{
conn.Open();
comm = new SqlCommand(SQL, conn);
comm.CommandTimeout = 30000;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader();
DynamicBuilder <T> DynamicBuilder = null;
DynamicBuilder = DynamicBuilder <T> .CreateBuilder(dr);
if (dr.HasRows)
{
while (dr.Read())
{
T col = DynamicBuilder.Build(dr); // CreateInstanceT();
Cols.Add(col);
}
dr.NextResult();
//Record Count
if (dr.HasRows)
{
while (dr.Read())
{
TotalRecord = dr.GetInt32(0);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(Cols);
}
public static DynamicBuilder <T> CreateBuilder(IDataRecord dataRecord)
{
var dynamicBuilder = new DynamicBuilder <T>();
int i = 0;
var method = new DynamicMethod("DynamicCreate", typeof(T), new[] { typeof(IDataRecord) }, typeof(T), true);
ILGenerator generator = method.GetILGenerator();
LocalBuilder result = generator.DeclareLocal(typeof(T));
generator.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));
generator.Emit(OpCodes.Stloc, result);
for (i = 0; i <= dataRecord.FieldCount - 1; i++)
{
PropertyInfo propertyInfo =
typeof(T).GetProperties().FirstOrDefault(x => x.Name.Contains(dataRecord.GetName(i)));
//if (propertyInfo == null ) propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i).ToUpper());
//if (propertyInfo == null) propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i).ToLower());
Label endIfLabel = generator.DefineLabel();
if ((propertyInfo != null))
{
if ((propertyInfo.GetSetMethod() != null))
{
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, isDBNullMethod);
generator.Emit(OpCodes.Brtrue, endIfLabel);
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, getValueMethod);
Type dataType = dataRecord.GetFieldType(i);
bool isNullable = false;
if (propertyInfo.PropertyType.Name.ToLower().Contains("nullable"))
{
isNullable = true;
}
if (isNullable)
{
generator.Emit(OpCodes.Unbox_Any, getNullableType(dataType));
}
else
{
generator.Emit(OpCodes.Unbox_Any, dataType);
}
generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod());
generator.MarkLabel(endIfLabel);
}
}
}
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ret);
dynamicBuilder.handler = (Load)method.CreateDelegate(typeof(Load));
return(dynamicBuilder);
}