public ActionResult ImportData(string isB2BData)
{
ViewBag.IsB2BData = Convert.ToBoolean(isB2BData);
ViewBag.Title = ViewBag.IsB2BData ? "Import B2B Data" : "Import B2C Data";
if (Request.Files[0] != null && Request.Files[0].ContentLength > 0)
{
string fileName = string.Format("{0}_{1}", DateTime.Now.ToFileTimeUtc(), Path.GetFileName(Request.Files[0].FileName));
string filePath = Server.MapPath("~/TempExcelFiles/UpLoad/" + fileName);
Request.Files[0].SaveAs(filePath);
DataTable tb = ExcelHelper.GetTableFromExcel("导入数据", filePath);
if (Convert.ToBoolean(isB2BData))
{
DataTable masterDatas = GetPartData(tb, m_B2BMastersFields.Select(p => p.FieldName).ToList());
DataTable contactDatas = GetPartData(tb, m_B2BContactsFields.Select(p => p.FieldName).ToList());
foreach (DataRow item in masterDatas.Rows)
{
string tempSql = GetUpdateSql(m_B2BMastersTableName, item, "公司名称");
if (tempSql.Length > 0)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
}
foreach (DataRow item in contactDatas.Rows)
{
string tempSql = GetUpdateSql(m_B2BContactsTableName, item, "公司名称", "联系人姓名");
if (tempSql.Length > 0)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
}
}
else
{
DataTable masterDatas = GetPartData(tb, m_B2CMastersFields.Select(p => p.FieldName).ToList());
foreach (DataRow item in masterDatas.Rows)
{
DBAccesser.GetData(DBAccesser.DefaultConnection, GetUpdateSql(m_B2CMastersTableName, item, "姓名", "联系电话"));
}
}
DeleteUploadExcel(filePath);
ViewBag.ImportMsg = "导入成功!";
}
return(View("ImportData"));
}
private void WriteToDB(DataRow row, DataColumnCollection columns, bool isB2BData)
{
try
{
string tableName = isB2BData ? "B2BDatas" : "B2CDatas";
//filter 需要确认,即需要确认那些字段可以确定唯一一条记录,逻辑意义上, 这些字段在上传的时候是比需要有的字段
string filter = string.Format("数据编码=N'{0}'", Convert.ToString(row["数据编码"]).Replace("'", "''").Trim());
string fieldValues = string.Empty;
StringBuilder sql = new StringBuilder();
sql.AppendLine(string.Format("if exists(select top 1 1 from dataCenter.dbo.{0} with(nolock) where {1})", tableName, filter));
sql.AppendLine("begin");
sql.AppendLine(string.Format("update dataCenter.dbo.{0}", tableName));
sql.AppendLine("set ");
for (int i = 0; i < columns.Count; i++)
{
sql.AppendLine(string.Format("{0}=N'{1}'", columns[i].ColumnName, Convert.ToString(row[columns[i]]).Replace("'", "''").Trim()));
if (i != columns.Count - 1)
{
sql.Append(",");
}
}
sql.AppendLine(string.Format("where {0}", filter));
sql.AppendLine("end");
sql.AppendLine("else");
sql.AppendLine("begin");
sql.AppendLine(string.Format("insert into dataCenter.dbo.{0}", tableName));
sql.AppendLine("(" + string.Join(",", columns.Cast <DataColumn>().Select(p => p.ColumnName)) + ")");
sql.AppendLine("values(");
for (int i = 0; i < columns.Count; i++)
{
sql.AppendLine(string.Format("N'{0}'", Convert.ToString(row[columns[i]]).Replace("'", "''").Trim()));
if (i != columns.Count - 1)
{
sql.Append(",");
}
}
sql.Append(")");
sql.AppendLine("end");
string tempSql = sql.ToString();
DBAccesser.GetData(DBAccesser.DefaultConnection, tempSql);
}
catch (Exception ex)
{
throw;
}
}
public string ExportData()
{
bool isB2BData = Convert.ToBoolean(Request.QueryString["isB2BData"]);
string sqlSelect = Request.Form["Columns"];
string sqlWhere = string.Empty;
for (int i = 0; i < Request.Form.Keys.Count; i++)
{
string fieldNameKey = string.Format("Filters[{0}][FieldName]", i);
string operationSignKey = string.Format("Filters[{0}][OperationSign]", i);
string inputValueKey = string.Format("Filters[{0}][InputValue]", i);
if (Request.Form[fieldNameKey] != null && Request.Form[operationSignKey] != null && Request.Form[inputValueKey] != null)
{
sqlWhere += string.Format(" {0} {1} {2} and", Request.Form[fieldNameKey], Request.Form[operationSignKey], Request.Form[inputValueKey]);
}
}
string sql = string.Empty;
if (isB2BData)
{
sql = string.Format("select distinct {0} from dataCenter.dbo.B2BMasters with(nolock) left join dataCenter.dbo.B2BContacts with(nolock) on B2BContacts.公司名称 = B2BMasters.公司名称 where {1}", sqlSelect, sqlWhere.TrimEnd(" and".ToArray()));
}
else
{
sql = string.Format("select distinct {0} from dataCenter.dbo.B2CMasters with(nolock) where {1}", sqlSelect, sqlWhere.TrimEnd(" and".ToArray()));
}
DataSet ds = DBAccesser.GetData(DBAccesser.DefaultConnection, sql);
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
string excelXmlStr = ExcelHelper.DataTableToExcelTableXML(ds.Tables[0], null);
string fileName = string.Format("{0}_{1}.xml", DateTime.Now.ToFileTimeUtc(), isB2BData ? "B2BDatas" : "B2CDatas");
string filePath = Server.MapPath("~/TempExcelFiles/DownLoad/" + fileName);
SaveExcel(filePath, excelXmlStr);
return(fileName);
}
else
{
return("error-nodata");
}
}