public void BuildWhereClause(OleDbCommand cmd, ObjectState objState)
{
StringBuilder builder = new StringBuilder();
List <ExcelColumnAttribute> cols = ExcelMapReader.GetColumnList(objState.Entity.GetType());
foreach (ExcelColumnAttribute col in cols)
{
PropertyManager pm = objState.GetProperty(col.GetProperty().Name);
if (builder.Length > 0)
{
builder.Append(" and ");
}
builder.AppendFormat("[{0}]", col.GetSelectColumn());
//fix from Andrew 4/2/08 to handle empty cells
if (pm.OrginalValue == System.DBNull.Value)
{
builder.Append(" IS NULL");
}
else
{
builder.Append(" = ");
string paraNum = "@x" + cmd.Parameters.Count.ToString();
builder.Append(paraNum);
OleDbParameter para = new OleDbParameter(paraNum, pm.OrginalValue);
cmd.Parameters.Add(para);
}
}
cmd.CommandText = cmd.CommandText + " WHERE " + builder.ToString();
}
public void BuildUpdateClause(OleDbCommand cmd, ObjectState objState)
{
StringBuilder builder = new StringBuilder();
string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
builder.Append("UPDATE [");
builder.Append(sheet);
builder.Append("$] SET ");
StringBuilder changeBuilder = new StringBuilder();
List <ExcelColumnAttribute> cols = ExcelMapReader.GetColumnList(objState.Entity.GetType());
List <ExcelColumnAttribute> changedCols =
(from c in cols
join p in objState.ChangedProperties on c.GetProperty().Name equals p.PropertyName
where p.HasChanged == true
select c).ToList();
foreach (ExcelColumnAttribute col in changedCols)
{
if (changeBuilder.Length > 0)
{
changeBuilder.Append(", ");
}
string paraNum = "@x" + cmd.Parameters.Count.ToString();
changeBuilder.AppendFormat("[{0}]", col.GetSelectColumn());
changeBuilder.Append(" = ");
changeBuilder.Append(paraNum);
object val = col.GetProperty().GetValue(objState.Entity, null);
OleDbParameter para = new OleDbParameter(paraNum, val);
cmd.Parameters.Add(para);
}
builder.Append(changeBuilder.ToString());
cmd.CommandText = builder.ToString();
BuildWhereClause(cmd, objState);
}
public void BuildInsertClause(OleDbCommand cmd, ObjectState objState)
{
string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO [");
builder.Append(sheet);
builder.Append("$]");
StringBuilder columns = new StringBuilder();
StringBuilder values = new StringBuilder();
foreach (ExcelColumnAttribute col in ExcelMapReader.GetColumnList(objState.Entity.GetType()))
{
if (columns.Length > 0)
{
columns.Append(", ");
values.Append(", ");
}
columns.AppendFormat("[{0}]", col.GetSelectColumn());
string paraNum = "@x" + cmd.Parameters.Count.ToString();
values.Append(paraNum);
object val = col.GetProperty().GetValue(objState.Entity, null);
OleDbParameter para = new OleDbParameter(paraNum, val);
cmd.Parameters.Add(para);
}
cmd.CommandText = builder.ToString() + "(" + columns.ToString() + ") VALUES (" +
values.ToString() + ")";
}
public void BuildDeleteClause(OleDbCommand cmd, ObjectState objState)
{
StringBuilder builder = new StringBuilder();
string sheet = ExcelMapReader.GetSheetName(objState.Entity.GetType());
builder.Append("DELETE FROM [");
builder.Append(sheet);
builder.Append("$]");
cmd.CommandText = builder.ToString();
BuildWhereClause(cmd, objState);
}
private string BuildSelect()
{
string sheet = ExcelMapReader.GetSheetName(typeof(T));
StringBuilder builder = new StringBuilder();
foreach (ExcelColumnAttribute col in ExcelMapReader.GetColumnList(typeof(T)))
{
if (builder.Length > 0)
{
builder.Append(", ");
}
builder.AppendFormat("[{0}]", col.GetSelectColumn());
}
builder.Append(" FROM [");
builder.Append(sheet);
builder.Append("$]");
return("SELECT " + builder.ToString());
}
private void Load()
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;IMEX=1""";
if (provider.Filepath.EndsWith(".xlsx"))
{
connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;IMEX=1""";
}
connectionString = string.Format(connectionString, provider.Filepath);
List <ExcelColumnAttribute> columns = ExcelMapReader.GetColumnList(typeof(T));
rows.Clear();
if (!File.Exists(provider.Filepath))
{
//创建新的Excel文件
Object oMissing = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.Application();
excel.Visible = false;
Excel.Workbook workbook = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
for (int i = 1; i <= columns.Count; i++)
{
worksheet.Cells[1, i] = columns[i - 1].GetSelectColumn();
}
// 如果是Excel 2007
if (excel.Version == "12.0")
{
// 保存为Excel 2003 兼容格式
worksheet.SaveAs(provider.Filepath, Excel.XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
worksheet.SaveAs(provider.Filepath, Excel.XlFileFormat.xlXMLSpreadsheet, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
excel.Application.DisplayAlerts = false;
excel.Quit();
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = BuildSelect();
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
T item = CreateInstance();
List <PropertyManager> pm = new List <PropertyManager>();
int Colcount = 1;
object tempVal = null;
foreach (ExcelColumnAttribute col in columns)
{
object val = reader[col.GetSelectColumn()];
tempVal = val;
if (Colcount == 1 && tempVal.ToString() == "")
{
break;
}
if (col.IsFieldStorage())
{
FieldInfo fi = typeof(T).GetField(col.GetStorageName(), BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.SetField);
try
{
fi.SetValue(item, val);
}
catch (Exception ex)
{
if (fi.FieldType == typeof(string))
{
fi.SetValue(item, Convert.ToString(val));
}
if (fi.FieldType == typeof(double))
{
try
{
fi.SetValue(item, Convert.ToDouble(val));
}
catch (Exception)
{
fi.SetValue(item, Convert.ToDouble(0));
}
}
if (fi.FieldType == typeof(DateTime))
{
fi.SetValue(item, Convert.ToDateTime(val));
}
if (fi.FieldType == typeof(int))
{
try
{
fi.SetValue(item, Convert.ToInt32(val));
}
catch (Exception)
{
fi.SetValue(item, Convert.ToInt32(0));
}
}
if (fi.FieldType == typeof(decimal))
{
try
{
fi.SetValue(item, Convert.ToDecimal(val));
}
catch (Exception)
{
fi.SetValue(item, Convert.ToDecimal(0));
}
}
if (fi.FieldType == typeof(decimal?))
{
try
{
fi.SetValue(item, Convert.ToDecimal(val));
}
catch (Exception)
{
fi.SetValue(item, Convert.ToDecimal(0));
}
}
if (fi.FieldType == typeof(bool))
{
fi.SetValue(item, Convert.ToBoolean(Convert.ToInt32(val)));
}
}
}
else
{
typeof(T).GetProperty(col.GetStorageName()).SetValue(item, val, null);
}
pm.Add(new PropertyManager(col.GetProperty().Name, val));
Colcount++;
}
if (Colcount == 1 && tempVal.ToString() == "")
{
continue;
}
rows.Add(item);
AddToTracking(item, pm);
}
}
}
}
}
