/// <summary>
/// Returns a dictionary containing all class Properties and its values
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static Dictionary <string, object> GetFieldsAndValues(Object obj)
{
Dictionary <string, object> ret = new Dictionary <string, object>();
foreach (var item in ReflectionTool <TSource> .getPropertyNames())
{
ret.Add(item, ReflectionTool <TSource> .GetPropertyValue(obj, item, ""));
}
return(ret);
}
public static string getXLSCreteScript(string[] ExcludedFieldnames = null)
{
string result = "";
foreach (var item in ReflectionTool <T> .getPropertyNames(ExcludedFieldnames))
{
result += (result.Length == 0 ? "" : ", \r\n") +
"[" + item + "] " + GetOleDbTypeString(ReflectionTool <T> .GetPropertyType(item)).ToString().ToLower();
}
result = "CREATE TABLE " + typeof(T).Name + "s (" + result + ")";
return(result);
}
public static string getXLSInsertScript(string[] ExcludedFieldnames = null)
{
string result = "";
string fields = "", values = "";
foreach (var item in ReflectionTool <T> .getPropertyNames(ExcludedFieldnames))
{
fields += (fields.Length == 0 ? "" : ", ") + "[" + item + "] ";
values += (values.Length == 0 ? "" : ", ") + "@" + item;
}
result = "INSERT INTO " + typeof(T).Name + "s (" + fields + ") VALUES (" + values + ")";
return(result);
}
private static void CopyValues <TDest>(List <string> FieldList, object Source, object Destination)
{
foreach (var propName in ReflectionTool <TSource> .getPropertyNames())
{
if ((FieldList == null || FieldList.Contains(propName))
&& //check if destination object has the same propetyName
ReflectionTool <TDest> .ContainsPropertyName(propName))
{
var value = ReflectionTool <TSource> .GetPropertyValue(Source, propName, GetDefaultValue(Source.GetType()));
ReflectionTool <TDest> .SetPropertyValue(Destination, propName, value);
}
}
}
public static bool ExportExcel(List <T> dataList, string FileName, string FilePath, string[] ExcludedFieldnames = null)
{
StringBuilder sb = new StringBuilder();
if (dataList.Count > 0)
{
string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";
if (System.IO.File.Exists(FilePath))
{
System.IO.File.Delete(FilePath);
}
using (OleDbConnection con = new OleDbConnection(conString))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
OleDbCommand cmd = new OleDbCommand(getXLSCreteScript(ExcludedFieldnames), con);
cmd.ExecuteNonQuery();
OleDbCommand cmdIns = new OleDbCommand(getXLSInsertScript(), con);
List <string> FieldList = ReflectionTool <T> .getPropertyNames(ExcludedFieldnames);
foreach (string fieldName in FieldList)
{
OleDbType paramType = GetOleDbType(ReflectionTool <T> .GetPropertyType(fieldName));
if (paramType == OleDbType.VarChar)
{
cmdIns.Parameters.Add("@" + fieldName, paramType, 4000);
}
else
{
cmdIns.Parameters.Add("@" + fieldName, paramType);
}
}
foreach (object field in dataList)
{
try
{
foreach (string fieldName in FieldList)
{
var type = ReflectionTool <T> .GetPropertyType(fieldName);
object defaultValue = "";
if (type.Equals(typeof(String)))
{
defaultValue = (object)"";
}
else if (type.Equals(typeof(DateTime)))
{
defaultValue = DBNull.Value; //new DateTime(); //(DateTime?)null;
}
else
{
defaultValue = (object)0;
}
cmdIns.Parameters["@" + fieldName].Value = ReflectionTool <T> .GetPropertyValue(field, fieldName, defaultValue);
}
cmdIns.ExecuteNonQuery();
}
catch (Exception)
{
if (!System.Diagnostics.Debugger.IsAttached)
{
throw;
}
}
}
con.Close();
}
}
return(true);
}
