private string CreateColumnSQL(Column col)
{
StringBuilder sb = new StringBuilder(col.Name);
sb.Append(" ");
switch (col.Type)
{
case ColumnType.StringType:
sb.AppendFormat("nvarchar({0})", col.Size);
break;
case ColumnType.IntegerType:
sb.Append("int");
break;
case ColumnType.DecimalType:
sb.Append("decimal");
break;
case ColumnType.DateType:
sb.Append("date");
break;
default:
break;
}
sb.AppendFormat(" {0}NULL", (col.Nullable ? string.Empty : "NOT "));
if (col.Autoincrement)
{
sb.AppendFormat(" IDENTITY({0},{1})", col.AutoincrementSeed, col.AutoincrementStep);
}
if (col.IsPrimaryKey)
{
sb.Append(" PRIMARY KEY");
}
return sb.ToString();
}
private Column GetColumn(string name, string value)
{
Column col = new Column(name);
col.Type = this.GetColumnType(value);
if (col.Type == ColumnType.StringType)
{
col.Size = 512;
}
col.Nullable = true;
return col;
}
/// <summary>
/// Loads the csv columns.
/// </summary>
/// <param name="settings">The settings.</param>
/// <param name="file">The file.</param>
/// <exception cref="CsvException">When number of headers doesn`t have number of values.</exception>
public void LoadColumns(CsvSettings settings, string file)
{
string contents = null;
string rowSep = null;
switch (settings.RowSeparator.ToUpper())
{
case "[ENTER]":
rowSep = Environment.NewLine;
break;
default:
rowSep = settings.RowSeparator;
break;
}
string colSep = null;
switch (settings.FieldSeparator.ToUpper())
{
case "[TAB]":
colSep = "\t";
break;
default:
colSep = settings.FieldSeparator;
break;
}
this.Columns.Clear();
if (settings.GenerateID)
{
Column autoCol = new Column("ID", ColumnType.IntegerType);
autoCol.Size = 0;
autoCol.Precision = 0;
autoCol.Scale = 0;
autoCol.Autoincrement = true;
autoCol.AutoincrementSeed = 1;
autoCol.AutoincrementStep = 1;
autoCol.Nullable = false;
autoCol.Unique = true;
autoCol.IsPrimaryKey = true;
this.Columns.Add(autoCol);
}
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs, Encoding.GetEncoding(settings.Encoding)))
{
contents = sr.ReadToEnd();
}
}
string[] rows = contents.Split(new string[] { rowSep }, StringSplitOptions.RemoveEmptyEntries);
if (settings.FirstRowHeader)
{
if (rows.Length > 1)
{
string[] names = rows[0].Split(new string[] { colSep }, StringSplitOptions.RemoveEmptyEntries);
string[] values = rows[1].Split(colSep.ToCharArray());
for (int i = 0; i <= names.Length - 1; i++)
{
this.Columns.Add(this.GetColumn(names[i], values[i]));
}
}
}
else
{
if (rows.Length > 0)
{
if (rows[0].EndsWith(colSep))
{
rows[0] = rows[0].Substring(0, rows[0].Length - 1);
}
string[] values = rows[0].Split(colSep.ToCharArray());
for (int i = 0; i <= values.Length - 1; i++)
{
this.Columns.Add(this.GetColumn(string.Format("FLD{0:00}", i + 1), values[i]));
}
}
}
}