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>()); }