private Database OpenDB(CsvSettings settings)
{
var _with1 = settings;
string server = _with1.Server;
string database = _with1.Database;
string userName = Convert.ToString((_with1.SqlServerSecurity ? _with1.UserName : null));
string password = Convert.ToString((_with1.SqlServerSecurity ? _with1.Password : null));
return new Database(server, database, userName, password);
}
/// <summary>
/// Loads the table.
/// </summary>
/// <param name="settings">The settings.</param>
/// <param name="file">The file.</param>
/// <returns><see cref="DataTable" /></returns>
public DataTable LoadTable(CsvSettings settings, string file)
{
char[] rowSep = null;
switch (settings.RowSeparator.ToUpper()) {
case "[ENTER]":
rowSep = Environment.NewLine.ToCharArray();
break;
default:
rowSep = settings.RowSeparator.ToCharArray();
break;
}
char[] colSep = null;
switch (settings.FieldSeparator.ToUpper()) {
case "[TAB]":
colSep = new char[]{ '\t' };
break;
default:
colSep = settings.FieldSeparator.ToCharArray();
break;
}
DataTable tbl = this.GenerateTable();
string contents = null;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) {
using (StreamReader sr = new StreamReader(fs, Encoding.GetEncoding(settings.Encoding))) {
contents = sr.ReadToEnd();
}
}
int rowIndex = 0;
int columnOffset = Convert.ToInt32((settings.GenerateID ? 1 : 0));
foreach (string row in contents.Split(rowSep, StringSplitOptions.RemoveEmptyEntries)) {
rowIndex += 1;
if (settings.FirstRowHeader & rowIndex == 1) {
continue;
}
DataRow newRow = tbl.NewRow();
string[] values = row.Split(colSep);
for (int i = columnOffset; i <= this.Columns.Count - 1; i++) {
if (this.Columns[i].Enabled) {
string value = values[i - columnOffset].Trim();
object newValue = value;
if (string.IsNullOrEmpty(value) | value == "DBNull") {
if (string.IsNullOrEmpty(this.Columns[i].DefaultValue) | this.Columns[i].DefaultValue == "DBNull") {
newValue = DBNull.Value;
}
}
newRow[this.Columns[i].Name] = newValue;
}
}
tbl.Rows.Add(newRow);
}
return tbl;
}
/// <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]));
}
}
}
}
/// <summary>
/// Imports the files.
/// </summary>
/// <param name="settings">The settings.</param>
/// <param name="files">The files.</param>
public void ImportFiles(CsvSettings settings, IEnumerable files)
{
// calculate count
int count = 0;
foreach (string f in files)
{
count += 1;
}
ProgressEventArgs progressEA = new ProgressEventArgs(0, count);
using (Database db = this.OpenDB(settings))
{
if (settings.CreateTable)
{
progressEA.Message = "Creating data table...";
this.OnProgress(progressEA);
this.CreateTable(settings, db);
progressEA.Message = "Table created!";
this.OnProgress(progressEA);
progressEA.Message = null;
if (progressEA.Cancel)
{
this.OnDone(true);
return;
}
}
foreach (string f in files)
{
try
{
this.ImportFile(settings, db, f);
progressEA.Value += 1;
progressEA.Message = string.Format("{0} / {1}", progressEA.Value, progressEA.MaxValue);
this.OnProgress(progressEA);
if (progressEA.Cancel)
{
this.OnDone(true);
return;
}
}
catch (Exception ex)
{
this.OnWorkerError(new CsvException("Error importing file: " + f, ex));
}
}
}
this.OnDone(false);
}
/// <summary>
/// Imports the file.
/// </summary>
/// <param name="settings">The settings.</param>
/// <param name="db">The db.</param>
/// <param name="file">The file.</param>
public void ImportFile(CsvSettings settings, Database db, string file)
{
using (DataTable tbl = this.LoadTable(settings, file))
{
string sql = this.CreateInsertSQL(settings.Table);
foreach (DataRow row in tbl.Rows)
{
using (IDbCommand cmd = db.CreateCommand(sql))
{
foreach (Column col in this.Columns)
{
if (col.Enabled && !col.Autoincrement)
{
cmd.Parameters.Add(db.CreateParameter(col.Name, col.SystemType, row[col.Name]));
}
}
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Creates the table.
/// </summary>
/// <param name="settings">The settings.</param>
/// <param name="db">The database.</param>
public void CreateTable(CsvSettings settings, Database db)
{
using (IDbCommand cmd = db.CreateCommand("select count(*) from sys.objects where type='U' and name=@name"))
{
cmd.Parameters.Add(db.CreateParameter("name", DbType.String, settings.Table));
if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
{
using (IDbCommand cmdDrop = db.CreateCommand(string.Format("drop table {0}", settings.Table)))
{
cmdDrop.ExecuteNonQuery();
}
}
}
string sql = this.CreateTableSQL(settings.Table);
using (IDbCommand cmd = db.CreateCommand(sql))
{
cmd.ExecuteNonQuery();
}
}