public void ParseDataRow_CreatesFieldNames()
{
var testData = "firstValuesecondValuethirdValueTrue12.1";
var columns = new ColumnSpecification[]
{
new ColumnSpecification {
Type = ColumnType.String, Length = 10
},
new ColumnSpecification {
Type = ColumnType.String, Length = 11
},
new ColumnSpecification {
Type = ColumnType.String, Length = 10
},
new ColumnSpecification {
Type = ColumnType.Boolean, Length = 4
},
new ColumnSpecification {
Type = ColumnType.Double, Length = 4
}
};
var result = Utils.ParseDataRow(testData, columns);
Assert.AreEqual(5, result.Count);
Assert.AreEqual("Field_1", result.Keys.First());
Assert.AreEqual("Field_5", result.Keys.Last());
}
public void ParseDataRow_TypeCasting_Test()
{
var testData = "truet1,51234567890201804249";
var columns = new ColumnSpecification[] {
new ColumnSpecification {
Type = ColumnType.Boolean, Length = 4, Name = "Boolean"
},
new ColumnSpecification {
Type = ColumnType.Char, Length = 1, Name = "Char"
},
new ColumnSpecification {
Type = ColumnType.Double, Length = 3, Name = "Double"
},
new ColumnSpecification {
Type = ColumnType.Long, Length = 10, Name = "Long"
},
new ColumnSpecification {
Type = ColumnType.DateTime, DateTimeFormat = "yyyyMMdd", Length = 8, Name = "DateTime"
},
new ColumnSpecification {
Type = ColumnType.Int, Length = 1, Name = "Int"
}
};
var result = Utils.ParseDataRow(testData, columns);
Assert.AreEqual(6, result.Count);
Assert.AreEqual(typeof(bool), result["Boolean"].GetType());
Assert.IsTrue(result["Char"] is Char);
Assert.AreEqual(typeof(double), result["Double"].GetType());
Assert.AreEqual(typeof(long), result["Long"].GetType());
Assert.AreEqual(typeof(DateTime), result["DateTime"].GetType());
Assert.AreEqual(typeof(int), result["Int"].GetType());
}
/// <summary>
/// Build a T-SQL update statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the update statement.</param>
/// <param name="keyColumn">The key column to use for the update predicate.</param>
/// <returns>A string update statement. Empty string if keyColumn is null.</returns>
private string BuildUpdateStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
var updateSetList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (col.IsIdentity || col.IsComputed)
{
continue;
}
if (updateSetList.Length > 0)
{
updateSetList.Append(", ");
}
updateSetList.Append(QuoteName(col.ColumnName) + " = @" + col.ColumnName);
}
return(string.Format(TemplateLibrary.TSql.Update, QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), updateSetList.ToString(), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
private ParseResult CreateResultWithEmptyValues()
{
string fileContent =
@"First;Second;Third
firstValue ThirdValue";
var columnSpec = new ColumnSpecification[] {
new ColumnSpecification {
Length = 10, Type = ColumnType.String
},
new ColumnSpecification {
Length = 4, Type = ColumnType.String
},
new ColumnSpecification {
Length = 10, Type = ColumnType.String
}
};
var parseInput = new ParseInput
{
ColumnSpecifications = columnSpec,
FlatFileContent = fileContent,
HeaderDelimiter = ";",
HeaderRow = HeaderRowType.Delimited
};
var options = new ParseOptions {
SkipRows = false
};
var result = FixedWidthFlatFileTask.Parse(parseInput, options);
return(result);
}
/// <summary>
/// Build a T-SQL delete statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the delete statement.</param>
/// <param name="keyColumn">The key column to use for the delete predicate.</param>
/// <returns>A string delete statement. Empty string if keyColumn is null.</returns>
private string BuildDeleteStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
return(string.Format(TemplateLibrary.TSql.Delete, QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
/// <summary>
/// Build a property string for the given column specification.
/// </summary>
/// <param name="columnSpecification">The column for which to build a property string.</param>
/// <param name="provider">The column specification provider (e.g., SQL Server).</param>
/// <returns>Property string.</returns>
private string BuildPropertyString(ColumnSpecification columnSpecification, SpecificationProvider provider)
{
string prop = null;
Type propType = null;
switch (provider)
{
case SpecificationProvider.SqlServer:
(prop, propType) = BuildSqlServerPropertyString(columnSpecification);
break;
default:
break;
}
return(AddAnnotations ? BuildPropertyAnnotationString(columnSpecification, propType) + prop : prop);
}
public void SplitToList_FixedWidth_Test()
{
var input = "hodor22tenchars10";
var columnSpecification = new ColumnSpecification[3];
columnSpecification[0] = new ColumnSpecification {
Name = "h1", Length = 5, Type = ColumnType.String
};
columnSpecification[1] = new ColumnSpecification {
Name = "h2", Length = 2, Type = ColumnType.Int
};
columnSpecification[2] = new ColumnSpecification {
Name = "h3", Length = 10, Type = ColumnType.String
};
var result = input.SplitToList(columnSpecification);
Assert.AreEqual(3, result.Count);
Assert.AreEqual("hodor", result.First());
Assert.AreEqual("tenchars10", result.Last());
}
/// <summary>
/// Build a T-SQL delete statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the select (get by key) statement.</param>
/// <param name="keyColumn">The key column to use for the select predicate.</param>
/// <returns>A string select statement. Empty string if keyColumn is null.</returns>
private string BuildGetByKeyStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
var getColumnList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (getColumnList.Length > 0)
{
getColumnList.Append(", ");
}
getColumnList.Append(QuoteName(col.ColumnName));
}
return(string.Format(TemplateLibrary.TSql.GetByKey, getColumnList.ToString(), QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
public void Parse_DataWithDelimitedHeader_Test()
{
string fileContent =
@"Name;Street;StartDate
Veijo FrendsStr 20180527
Hodor HodorsStr 20180101 ";
var columnSpecs = new ColumnSpecification[] {
new ColumnSpecification {
Length = 8, Type = ColumnType.String
},
new ColumnSpecification {
Length = 10, Type = ColumnType.String
},
new ColumnSpecification {
Length = 9, Type = ColumnType.DateTime, DateTimeFormat = "yyyyMMdd"
}
};
_parseInput = new ParseInput {
ColumnSpecifications = columnSpecs, FlatFileContent = fileContent, HeaderRow = HeaderRowType.Delimited, HeaderDelimiter = ";"
};
_options = new ParseOptions {
SkipRows = false
};
var result = FixedWidthFlatFileTask.Parse(_parseInput, _options);
Assert.AreEqual(2, result.Data.Count);
var firstRow = result.Data.First();
Assert.IsTrue(firstRow.ContainsKey("Name"));
Assert.AreEqual("Veijo", firstRow["Name"]);
Assert.IsTrue(firstRow.ContainsKey("Street"));
Assert.AreEqual("FrendsStr", firstRow["Street"]);
Assert.IsTrue(firstRow.ContainsKey("StartDate"));
}
public void Parse_AddsGenericKeys_ForValuesWithoutName()
{
string fileContent = @"Veijo FrendsStr 20180527
Hodor HodorsStr 20180101 " + System.Environment.NewLine;
var columnSpecs = new ColumnSpecification[] {
new ColumnSpecification {
Length = 8, Type = ColumnType.String, Name = "Name"
},
new ColumnSpecification {
Length = 10, Type = ColumnType.String
},
new ColumnSpecification {
Length = 9, Type = ColumnType.DateTime, DateTimeFormat = "yyyyMMdd"
}
};
_parseInput = new ParseInput {
ColumnSpecifications = columnSpecs, FlatFileContent = fileContent, HeaderRow = HeaderRowType.None
};
_options = new ParseOptions {
SkipRows = false
};
var result = FixedWidthFlatFileTask.Parse(_parseInput, _options);
Assert.AreEqual(2, result.Data.Count);
var firstRow = result.Data.First();
Assert.IsTrue(firstRow.ContainsKey("Name"));
Assert.AreEqual("Veijo", firstRow["Name"]);
Assert.IsTrue(firstRow.ContainsKey("Field_2"));
Assert.AreEqual("FrendsStr", firstRow["Field_2"]);
Assert.IsTrue(firstRow.ContainsKey("Field_3"));
}
/// <summary>
/// Build a property string based on SQL Server column types. Some non-standard property types may return a null Type.
/// </summary>
/// <param name="columnSpecification">The column for which to build a property string.</param>
/// <returns>Tuple containing the property string and property type.</returns>
private (string, Type) BuildSqlServerPropertyString(ColumnSpecification columnSpecification)
{
var columnType = (SqlServerColumnType)columnSpecification.ColumnType;
Type type = null; // Note that SQL Server types are left as null. There is no point including an additional library reference when knowing the SQL types is of no value.
var typeName = string.Empty;
var basePropertyString = "public {0} {1} {{ get; set; }}";
switch (columnType)
{
case SqlServerColumnType.Char:
case SqlServerColumnType.VarChar:
case SqlServerColumnType.Text:
case SqlServerColumnType.NText:
case SqlServerColumnType.NVarChar:
case SqlServerColumnType.NChar:
case SqlServerColumnType.SysName:
typeName = "string";
type = typeof(string);
break;
case SqlServerColumnType.UniqueIdentifier:
typeName = "Guid" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(Guid);
break;
case SqlServerColumnType.Date:
case SqlServerColumnType.Time:
case SqlServerColumnType.DateTime:
case SqlServerColumnType.DateTime2:
case SqlServerColumnType.DateTimeOffset:
case SqlServerColumnType.SmallDateTime:
typeName = "DateTime" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(DateTime);
break;
case SqlServerColumnType.TinyInt:
typeName = "sbyte" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(sbyte);
break;
case SqlServerColumnType.SmallInt:
typeName = "short" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(short);
break;
case SqlServerColumnType.Int:
typeName = "int" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(int);
break;
case SqlServerColumnType.BigInt:
case SqlServerColumnType.TimeStamp:
typeName = "long" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(long);
break;
case SqlServerColumnType.Real:
case SqlServerColumnType.Float:
typeName = "double" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(double);
break;
case SqlServerColumnType.Sql_Variant:
typeName = "object";
type = typeof(object);
break;
case SqlServerColumnType.Bit:
typeName = "bool" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(bool);
break;
case SqlServerColumnType.Decimal:
case SqlServerColumnType.Numeric:
case SqlServerColumnType.Money:
case SqlServerColumnType.SmallMoney:
typeName = "decimal" + (columnSpecification.IsNullable ? "?" : "");
type = typeof(decimal);
break;
case SqlServerColumnType.HierarchyId:
typeName = "SqlHierarchyId" + (columnSpecification.IsNullable ? "?" : "");
break;
case SqlServerColumnType.Geometry:
typeName = "SqlGeometry";
break;
case SqlServerColumnType.Geography:
typeName = "SqlGeography";
break;
case SqlServerColumnType.VarBinary:
case SqlServerColumnType.Binary:
case SqlServerColumnType.Image:
typeName = "byte[]";
type = typeof(byte[]);
break;
case SqlServerColumnType.Xml:
typeName = "XElement";
type = typeof(XElement);
break;
default:
typeName = "object";
type = typeof(object);
break;
}
return(string.Format(basePropertyString, typeName, columnSpecification.ColumnName), type);
}
/// <summary>
/// Builds an annotation string for the given column specification.
/// </summary>
/// <param name="columnSpecification">The column for which to build an annotation string.</param>
/// <param name="columnType">The C# type of the column.</param>
/// <returns>Annotation string.</returns>
private string BuildPropertyAnnotationString(ColumnSpecification columnSpecification, Type columnType)
{
const string whiteSpace = "\n\t\t";
var basePasswordString = "password";
var baseEmailString = "email";
var basePhoneString = "phone";
var baseCreditCardString = "creditcard";
var baseZipCodeString = "zip";
var basePostalCodeString = "postal";
var emailStrings = new List <string>()
{
baseEmailString, "emailaddress"
};
var phoneStrings = new List <string>()
{
basePhoneString, "phonenumber", "homephone", "cellphone"
};
var creditCardStrings = new List <string>()
{
baseCreditCardString, "creditcardnumber"
};
var postalCodeStrings = new List <string>()
{
baseZipCodeString, "zipcode", basePostalCodeString, "postalcode"
};
var columnName = new string(Array.FindAll(columnSpecification.ColumnName.ToCharArray(), x => char.IsLetterOrDigit(x)));
var sb = new StringBuilder();
// Required
if (!columnSpecification.IsNullable)
{
sb.Append("[Required]" + whiteSpace);
}
// Read Only
if (columnSpecification.IsComputed)
{
sb.Append("[ReadOnly]" + whiteSpace);
}
// String Length
if (columnType == typeof(string) && columnSpecification.ColumnLength > 0)
{
sb.Append("[StringLength(" + columnSpecification.ColumnLength + ")]" + whiteSpace);
}
// Email
if (columnType == typeof(string) &&
emailStrings.Any(x => x.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) ||
(!OnlyExactMatchForAnnotations && columnName.Contains(baseEmailString, StringComparison.InvariantCultureIgnoreCase)))
{
sb.Append("[DataType(DataType.EmailAddress)]" + whiteSpace);
}
// Phone
if (columnType == typeof(string) &&
phoneStrings.Any(x => x.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) ||
(!OnlyExactMatchForAnnotations && columnName.Contains("phone", StringComparison.InvariantCultureIgnoreCase)))
{
sb.Append("[DataType(DataType.PhoneNumber)]" + whiteSpace);
}
// Password
if (columnType == typeof(string) &&
string.Equals(columnName, basePasswordString, StringComparison.InvariantCultureIgnoreCase) ||
(!OnlyExactMatchForAnnotations && columnName.Contains(basePasswordString, StringComparison.InvariantCultureIgnoreCase)))
{
sb.Append("[DataType(DataType.Password)]" + whiteSpace);
}
// Credit Card
if (columnType == typeof(string) &&
creditCardStrings.Any(x => x.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) ||
(!OnlyExactMatchForAnnotations && columnName.Contains(baseCreditCardString, StringComparison.InvariantCultureIgnoreCase)))
{
sb.Append("[DataType(DataType.CreditCard)]" + whiteSpace);
}
// Postal Code
if (columnType == typeof(string) &&
postalCodeStrings.Any(x => x.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) ||
(!OnlyExactMatchForAnnotations && (columnName.Contains(baseZipCodeString, StringComparison.InvariantCultureIgnoreCase) || columnName.Contains(basePostalCodeString, StringComparison.InvariantCultureIgnoreCase))))
{
sb.Append("[DataType(DataType.PostalCode)]" + whiteSpace);
}
return(sb.ToString());
}
