public DataRow InsertCSVRow(DataRow row,
Dictionary <string, int> attributes,
string[] fields,
Dictionary <string, string> constants)
{
DataColumnCollection dataColumns = dt.Columns;
DataRow tmpRow = row;
foreach (var item in attributes)
{
string attribute = item.Key;
int column = item.Value;
string attributeValue = fields[column];
Type columnType = dataColumns.Cast <DataColumn>().Where(c => c.ColumnName == attribute).Select(x => x.DataType).First();
if (columnType.Name == "Decimal")
{
decimal number;
if (decimal.TryParse(attributeValue, out number))
{
tmpRow[attribute] = number;
}
}
else if (columnType.Name == "String")
{
if (!string.IsNullOrEmpty(attributeValue))
{
int length = dataColumns.Cast <DataColumn>().Where(c => c.ColumnName == attribute).Select(x => x.MaxLength).First();
if (attributeValue.Length > length)
{
tmpRow[attribute] = attributeValue.Substring(0, length);
}
else
{
tmpRow[attribute] = attributeValue;
}
}
}
else
{
if (!string.IsNullOrEmpty(attributeValue))
{
tmpRow[attribute] = attributeValue;
}
}
}
foreach (var constant in constants)
{
string attribute = constant.Key;
string attributeValue = constant.Value;
tmpRow[attribute] = attributeValue;
}
return(tmpRow);
}
private static List <DataColumn> GetColumnsForResponse(HttpContext context, BaseRestServiceMethod method, DataColumnCollection commandColumns)
{
List <DataColumn> columns = new List <DataColumn>();
if (method.EnablePartialResponse)
{
string fieldsValue = null;
//if we have a parameter we can use for partial response
if (!String.IsNullOrEmpty(method.PartialResponseParameter))
{
//set fields for partial response from request
fieldsValue = context.Request[method.PartialResponseParameter];
}
if (String.IsNullOrEmpty(fieldsValue))
{
//this means either we dont have parameter enabled for partial response
//or no partial response fields were passed in
//so lets see if we have any defaults
fieldsValue = method.PartialResponseDefaultFields;
}
if (String.IsNullOrEmpty(fieldsValue))
{
//we still don't have any fields to use for partial response
//so return all fields from data source
columns = commandColumns.Cast <DataColumn>().ToList <DataColumn>();
}
else
{
//we do have fields to use for partial response
//lets split them by commas
//and return only those fields
string[] fields = fieldsValue.Split(',');
for (int i = 0; i < fields.Length; i++)
{
string columnName = fields[i];
if (commandColumns.Contains(columnName))
{
DataColumn col = commandColumns[columnName];
columns.Add(col);
}
}
}
}
else
{
//partial response not supported - return all fields from data source
columns = commandColumns.Cast <DataColumn>().ToList <DataColumn>();
}
return(columns);
}
/// <summary>
/// Maps a comma seperated list of column names with use of the given dictionary.
/// </summary>
/// <param name="ASortingColumnsAsText">Comma seperated list of Column Names.</param>
/// <param name="AColumns">Column Collection of the DataTable to check if the given names are valid.</param>
/// <param name="AMappingDictionaryWithoutBlanks">The Dictionary to use for the translation. Key is the old name, values is the new name. </param>
/// <param name="AReportName">Used for the Log.</param>
/// <returns></returns>
public static string ColumnMapping(string ASortingColumnsAsText,
DataColumnCollection AColumns,
Dictionary <string, string> AMappingDictionaryWithoutBlanks, string AReportName)
{
return(ColumnNameMapping(ASortingColumnsAsText, AColumns.Cast <DataColumn>().Select(
x => x.ColumnName).ToArray(), AMappingDictionaryWithoutBlanks, AReportName));
}
public static string BulkCopyCommand(this DataColumnCollection cols, string tableName)
{
if (!TableNameChecker.IsMatch(tableName))
{
throw new ArgumentException("Incorrect table name: " + tableName);
}
return($"COPY {tableName} ({string.Join(", ", cols.Cast<DataColumn>().Select(col => col.ColumnName))}) FROM STDIN (FORMAT BINARY)");
}
private static RowData CreateRowData(int sheetId, DataColumnCollection columns, int fgColorHeader, int bgColorHeader, bool doFullFormatting)
{
string[] columnNames = columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
return(CreateRowData(sheetId, columnNames, fgColorHeader, bgColorHeader, doFullFormatting));
}
/// <summary>
/// 获取更新或插入某个表的sql 语句
/// </summary>
/// <param name="tableName">要更新或插入的表名</param>
/// <param name="row">要更新或插入的数据</param>
/// <param name="locationColumnNames">在定位数据行的列名</param>
/// <returns></returns>
private string GetUpdateSql(string tableName, DataRow row, params string[] locationColumnNames)
{
DataColumnCollection columns = row.Table.Columns;
StringBuilder sql = new StringBuilder();
string filter = string.Empty;
for (int i = 0; i < locationColumnNames.Length; i++)
{
if (columns.Contains(locationColumnNames[i].ToLower()))
{
filter += string.Format("{0}='{1}'", locationColumnNames[i], Convert.ToString(row[locationColumnNames[i]]).Replace("'", "''").Trim());
if (i != locationColumnNames.Length - 1)
{
filter += " and ";
}
}
else
{
return(string.Empty);
}
}
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++)
{
if (!columns[i].ColumnName.ToLower().IsIn(locationColumnNames))
{
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(",LastEditDate=getdate()");
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.AppendLine(")");
sql.AppendLine("end");
return(sql.ToString());
}
private IEnumerable <Column> WriteHeader(StringBuilder text, DataColumnCollection columns)
{
var tableColumns = Table.Columns().Where(w => columns.Cast <DataColumn>().Any(a => a.ColumnName.Equals(w.Name))).ToArray();
foreach (var item in tableColumns)
{
text.Append(item.Name.PadRight(item.EndIndex));
}
text.AppendLine();
return(tableColumns);
}
public void CreateTable(DataColumnCollection columnCollection)
{
var createStatement = $"CREATE TABLE {_tableName}";
var columns = string.Join(
", ",
columnCollection.Cast <DataColumn>().Select(col => $"[{col.ColumnName}] {col.DataType.GetSqlType()}")
);
var query = $"{createStatement} ({columns})";
sqliteService.ExecuteQuery(query);
}
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 void Reset(DataColumnCollection columns, bool resetitems)
{
lbItems.Items.Clear(); //Update column filter
if (columns != null)
{
lbItems.Items.AddRange(columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray());
}
else
{
cbBox.Items[0] = "[All]";
}
if (resetitems)
{
ToggleCheck(true);
}
}
/// <summary><para>Returns an <see cref="IEnumerable{T}"/> of <see cref="DataColumn"/> values that represents <paramref name="collection"/>.</para></summary>
/// <param name="collection"><para>The target of the extension method.</para></param>
/// <param name="predicate"><para>The predicate of the extension method.</para></param>
/// <returns><para><see cref="IEnumerable{T}"/> of <see cref="DataColumn"/> values or <see cref="Enumerable.Empty{TResult}"/>.</para></returns>
public static IEnumerable <DataColumn> Where(this DataColumnCollection collection, Func <DataColumn, bool> predicate)
{
if (collection == null)
{
throw new ArgumentNullException(nameof(collection));
}
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
if (collection.Count == 0)
{
return(Enumerable.Empty <DataColumn>());
}
else
{
return(collection.Cast <DataColumn>().Where(predicate));
}
}
/// <summary><para>Returns an <see cref="IEnumerable{T}"/> of <see cref="DataColumn"/> values that represents <paramref name="collection"/>.</para></summary>
/// <param name="collection"><para>The target of the extension method.</para></param>
/// <param name="selector"><para>The selector of the extension method.</para></param>
/// <returns><para><see cref="IEnumerable{T}"/> of <see cref="DataColumn"/> values or <see cref="Enumerable.Empty{TResult}"/>.</para></returns>
public static IEnumerable <T> Select <T>(this DataColumnCollection collection, Func <DataColumn, T> selector)
{
if (collection == null)
{
throw new ArgumentNullException(nameof(collection));
}
if (selector == null)
{
throw new ArgumentNullException(nameof(selector));
}
//Determines if there are no entries in the collection. If not, return an empty enumerable. Otherwise, process the selector.
if (collection.Count == 0)
{
return(Enumerable.Empty <T>());
}
else
{
return(collection.Cast <DataColumn>().Select(selector));
}
}
/// <summary>
/// Copies the fields from <paramref name="row"/> to the properties of
/// <paramref name="instance"/> using <paramref name="columns"/> for mappings.
/// </summary>
/// <typeparam name="T">The destination type</typeparam>
/// <param name="row">The input data row</param>
/// <param name="columns">The columns of the input data row</param>
/// <param name="instance">The destination instance</param>
/// <returns>An instance of type <typeparamref name="T"/></returns>
private static T CopyProperties <T>(this DataRow row, DataColumnCollection columns, T instance)
{
var type = instance.GetType();
foreach (var column in columns.Cast <DataColumn>())
{
var property = type.GetPropertyInfo(column.ColumnName);
if (property != null)
{
var value = row[column];
if (!(value is DBNull))
{
property.SetValue(instance, value);
}
}
}
return(instance);
}
public static CheckColumnsVerdict VerifyColumns(this DataColumnCollection columns)
{
if (columns.Count != 5)
{
return(CheckColumnsVerdict.SomeColumnsAreMissing);
}
var values = (from c in columns.Cast <DataColumn>()
select c.ColumnName).ToList();
for (int cnt = 0; cnt < 5; cnt++)
{
if (values[cnt].Length == 0)
{
return(CheckColumnsVerdict.SomeColumnsAreMissing);
}
if (!values.Contains(header[cnt]))
{
return(CheckColumnsVerdict.ColumnsValuesAreNotAsExpected);
}
}
return(CheckColumnsVerdict.AllGood);
}
/// <summary>
/// Finds the property mappings for the specified target type, CSV column names and user declared mappings.
/// </summary>
static Dictionary <string, string> FindPropertyMappings(Type targetType, DataColumnCollection columns, object declaredMappings)
{
var result = new Dictionary <string, string>();
if (declaredMappings != null)
{
foreach (var property in declaredMappings.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
{
if (property.Name.StartsWith("set_"))
{
if (!result.ContainsKey(property.Name.TrimStart("set_")))
{
result.Add(property.Name.TrimStart("set_"), null);
}
continue;
}
// Validate property name:
var propertyInTarget = targetType.GetProperty(property.Name);
if (propertyInTarget == null)
{
throw new Exception(targetType.FullName + " does not have a property named " + property.Name);
}
if (!propertyInTarget.CanWrite)
{
throw new Exception("{0}.{1} property is read-only.".FormatWith(targetType.FullName, property.Name));
}
var mappedName = (string)property.GetValue(declaredMappings);
result[property.Name] = mappedName;
}
}
var columnNames = columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray();
foreach (var property in targetType.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public))
{
if (!property.CanWrite)
{
continue;
}
if (result.ContainsKey(property.Name) && result[property.Name] != null)
{
continue; // Already added in explicit mappings.
}
// Otherwise, if a column with that name is available, then that's it:
var potential = columnNames.Where(c => c.Replace(" ", "").ToLower() == property.Name.ToLower());
if (potential.IsSingle())
{
result[property.Name] = potential.Single();
}
else if (potential.Any())
{
throw new Exception("The specified data contains multiple potential matches for the property '{0}'. The potentially matched columns found: {1}. You must use explicit mappings in this case."
.FormatWith(property.Name, potential.Select(c => $"'{c}'").ToString(", ")));
}
}
return(result);
}
public static IEnumerable <DataColumn> AsEnumerable(this DataColumnCollection source)
{
return(source.Cast <DataColumn>());
}
public override IEnumerable <string> GetDynamicMemberNames()
{
return(_columns.Cast <DataColumn>().Select(c => c.ColumnName));
}
public static List <string> GetColumnValues(DataColumnCollection cols)
{
return(cols.Cast <DataColumn>().Select(c => c.ColumnName).ToList());
}
/// <summary>
/// Get list of columns from Data Column collection
/// </summary>
/// <param name="columnCollection">The data column collection object</param>
/// <returns>Collection of data columns</returns>
public static IEnumerable <DataColumn> AsEnumerable(this DataColumnCollection columnCollection)
{
return(columnCollection.Cast <DataColumn>());
}
public static IEnumerable <DataColumn> AsEnumerable(this DataColumnCollection cols) => cols.Cast <DataColumn>();
/// <id>46D812BA-C9CE-476A-B1E6-C3E6E59B5630</id>
/// <summary>
/// A DataColumnCollection extension method that converts an obj to a list.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <returns>obj as a List<DataColumn></returns>
public static List <DataColumn> ToList(this DataColumnCollection @this)
{
return(@this.Cast <DataColumn>().Select(x => x).ToList());
}
/// <id>E3F7A75F-F5A9-47C2-BC2C-BF2562BBAC9D</id>
/// <summary>
/// A DataColumnCollection extension method that enumerables the given object.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <returns>The IEnumerable object.</returns>
public static IEnumerable <DataColumn> AsEnumerable(this DataColumnCollection @this)
{
return(@this.Cast <DataColumn>());
}