/// <summary>
/// 把一行数据转换成一个对象，每一列是一个属性
/// </summary>
private object ConvertRowData(DataRow row)
{
var rowData = new Dictionary <string, object>();
foreach (DataColumn column in options.Data.Columns)
{
var data = row[column];
var type = typeRow[column.Ordinal].ToString();
rowData[GetColumnName(column)] = ExportManager.GetColumnValue(data, type);
}
return(rowData);
}
/// <summary>
/// 将表单内容转换成INSERT语句
/// </summary>
private string GetTableContentSQL()
{
StringBuilder sbContent = new StringBuilder();
StringBuilder sbNames = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
sbContent.AppendFormat("TRUNCATE TABLE `{0}`;\n", options.Name);
sbContent.AppendFormat("LOCK TABLES `{0}` WRITE;\n", options.Name);
var sheet = options.Data;
//-- 字段名称列表
foreach (DataColumn column in sheet.Columns)
{
if (sbNames.Length > 0)
{
sbNames.Append(", ");
}
sbNames.Append("`" + column.ToString() + "`");
}
//-- 逐行转换数据
for (int i = 0; i < sheet.Rows.Count; i++)
{
DataRow row = sheet.Rows[i];
if (i != 0)
{
sbValues.Append(", \n");
}
sbValues.Append("(");
for (int j = 0; j < sheet.Columns.Count; j++)
{
var column = sheet.Columns[j];
if (j != 0)
{
sbValues.Append(", ");
}
var value = ExportManager.GetColumnValue(row[column], typeRow[column.Ordinal].ToString());
if (value.GetType() == typeof(string))
{
sbValues.AppendFormat("'{0}'", value);
}
else
{
sbValues.AppendFormat("{0}", value);
}
}
sbValues.Append(")");
}
sbContent.AppendFormat("INSERT DELAYED INTO `{0}` ({1}) VALUES \n{2};\n", options.Name, sbNames.ToString(), sbValues.ToString());
sbContent.AppendLine("UNLOCK TABLES;\n");
return(sbContent.ToString());
}
