public T GetSqlResultAsObject <T>(string sql, List <SqlParameter> parameterList)
{
T output = default(T);
using (DataSet ds = new DataSet())
{
ExecuteDataSet(false, sql, parameterList);
DataTable dt = ds.Tables[0];
DataColumnCollection columnCollection = dt.Columns;
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
dynamic myObject = typeof(T).GetConstructor(System.Type.EmptyTypes).Invoke(null);
//invoke constructor to get an instance of object
columnCollection.OfType <DataColumn>().ToList <DataColumn>().ForEach(y =>
{
PropertyInfo myObjectPropertyInfo = typeof(T).GetProperty(y.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
if (myObjectPropertyInfo != null)
{
myObjectPropertyInfo.SetValue(myObject, dr[y] == DBNull.Value ? null : dr[y], null); //sets the property values of the created object
}
});
output = (T)myObject;
}
}
return(output);
}
private void LoadObject(string sql, List <SqlParameter> parameterList, List <List <dynamic> > FinalOutputListOfList, Dictionary <int, Type> typeDictionary)
{
using (DataSet ds = new DataSet())
{
ExecuteDataSet(false, sql, parameterList);
int count = ds.Tables.Count;
for (int i = 1; i <= count; i++)
{
DataTable dt = ds.Tables[i - 1];
DataColumnCollection columnCollection = dt.Columns;
List <dynamic> outputList = new List <dynamic>();
dt.AsEnumerable().ToList <DataRow>().ForEach(x =>
{
//invoke constructor to get an instance of object
dynamic myObject = typeDictionary[i].GetConstructor(System.Type.EmptyTypes).Invoke(null);
columnCollection.OfType <DataColumn>().ToList <DataColumn>().ForEach(y =>
{
PropertyInfo myObjectPropertyInfo = typeDictionary[i].GetProperty(y.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
if (myObjectPropertyInfo != null)
{
myObjectPropertyInfo.SetValue(myObject, x[y] == DBNull.Value ? null : x[y], null); //sets the property values of the created object
}
});
outputList.Add(myObject);
});
FinalOutputListOfList.Add(outputList);
}
}
}
public static DataColumn GetAutoIncrementColumn(DataColumnCollection columns)
{
return(columns.OfType <DataColumn>().First(c => c.AutoIncrement));
}
public List <T> GetSqlResultAsListOfObjects <T>(string sql, List <SqlParameter> parameterList)
{
List <T> outputList = new List <T>();
dynamic objectValue = 0;
using (DataSet ds = new DataSet())
{
ExecuteDataSet(false, sql, parameterList);
DataTable dt = ds.Tables[0];
DataColumnCollection columnCollection = dt.Columns;
dt.AsEnumerable().ToList <DataRow>().ForEach(x =>
{
dynamic myObject = typeof(T).GetConstructor(System.Type.EmptyTypes).Invoke(null);
//invoke constructor to get an instance of object
columnCollection.OfType <DataColumn>().ToList <DataColumn>().ForEach(y =>
{
PropertyInfo myObjectPropertyInfo = typeof(T).GetProperty(y.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
if (myObjectPropertyInfo != null)
{
var customAttributes = myObjectPropertyInfo.GetCustomAttributes(false);
if (customAttributes.Count() > 0)
{
if (customAttributes.Any(z => z.GetType().Name.Equals("PrimaryKey", StringComparison.OrdinalIgnoreCase)))
{
if (x[y] == System.DBNull.Value)
{
objectValue = 0;
}
else
{
objectValue = x[y];
}
}
else
{
if (x[y] == DBNull.Value)
{
objectValue = null;
}
else
{
objectValue = x[y];
}
}
}
else
{
if (x[y] == DBNull.Value)
{
objectValue = null;
}
else
{
objectValue = x[y];
}
}
myObjectPropertyInfo.SetValue(myObject, objectValue, null); //sets the property values of the created object
}
});
outputList.Add((T)myObject);
});
}
return(outputList);
}
