public void MapColumns(DataColumnCollection columns)
{
QRColumnNames obj;
foreach(var column in columns.Cast<DataColumn>()
.Select((value, index) => new { index = index, value = value })
.Where(x => QRColumnNames.TryParse(x.value.ColumnName, true, out obj))
.Select(x => new {cnmae=x.value.ColumnName,idx=x.index}))
template.Add(column.idx, CreateAct(column.cnmae));
}
/// <summary>
/// Gets the Columne name from the Collections
/// </summary>
/// <param name="cols"></param>
/// <param name="phrase"></param>
/// <returns></returns>
static string getColumnName(DataColumnCollection cols, string phrase)
{
var colName = from c in cols.Cast<DataColumn>()
where c.ColumnName.ToLower().Contains(phrase.ToLower())
select c.ColumnName.ToString();
return colName.ToList()[0].ToString();
}
/// <summary>
/// Maps columns by name. Required by SqlBulkCopy if schema changes or columns are out of order.
/// </summary>
private void MapColumns(SqlBulkCopyColumnMappingCollection columnMappings, IEnumerable<string> tableColumnNames,
DataColumnCollection importFileColumns)
{
var importColumnNames = importFileColumns.Cast<DataColumn>().Select(c => c.ColumnName);
var columnsInCommon = tableColumnNames.Intersect(importColumnNames);
foreach (var column in columnsInCommon)
{
columnMappings.Add(column, column);
}
}
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;
}
}
private void InitializeColumns(DataColumnCollection dataColumnCollection, IEnumerable<string> columns)
{
if (columns == null)
{
Columns.AddRange(
dataColumnCollection.Cast<DataColumn>().Select(dc => new ColumnInfo(dc)));
}
else
{
Columns.AddRange(
columns.Select(columnName => new ColumnInfo(dataColumnCollection[columnName])));
}
}
