private void SimpleTransaction_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
} catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Debug.WriteLine(sqlex.Message);
}
finally
{
trans.Commit();
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Debug.WriteLine(cmd.ExecuteScalar());
}
}
}
static void SimpleTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'A Value" + i + "');";
cmd.ExecuteNonQuery();
}
trans.Commit(); //Commit since all inserts are completed.
//Test if transaction is open
if (trans.IsOpen)
{
trans.Commit();
}
}
catch (SqlDatabaseException sqlex)
{
//Rollback as their was an error.
if (trans.IsOpen)
{
trans.Rollback();
}
Console.WriteLine(sqlex.Message);
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());
// Pure SQL Way of starting and committing transaction.
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
// Your SQL Statements and commands can reside here to run with-in a transaction.
// INSERT UPDATE and DELETE
cmd.CommandText = "COMMIT"; //ROLLBACK
cmd.ExecuteNonQuery();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
BackupData = new DataTable(Table.Name);
for (var i = 0; i < cbColumns.Items.Count; i++)
{
var col = this.Table.Columns.First(o => o == cbColumns.Items[i]);
col.IsPKey = cbColumns.GetItemChecked(i);
}
using (var con = new SqlDatabaseConnection(Table.ConnectionString))
{
con.Open();
using (var cmd = new SqlDatabaseCommand("SELECT * FROM " + Table.Name, con))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(BackupData);
}
}
var transaction = con.BeginTransaction();
try
{
using (var cmd = new SqlDatabaseCommand("DROP TABLE " + Table.Name, con))
{
cmd.ExecuteNonQuery();
}
Utils.CreateTableFromDefinition(Table, con);
Utils.RestoreTableFromBackup(BackupData, con, transaction);
transaction.Commit();
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] (");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
querySb.Append(
$"[{column.ColumnName}] VARCHAR({int.MaxValue}){(column.IsKey ? " NOT NULL UNIQUE" : "")},");
if (column.IsKey)
{
primaryKeySb.Append($"[{column.ColumnName}],");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// read file into db
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
var file = streamWrapper.StreamReader;
string line;
var rowsRead = 0;
var rowsSkipped = 0;
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
querySb.Append($"[{column.ColumnName}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
var paramName = $"@param{rootPath.ModeSettings.FixedWidthSettings.Columns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
var trans = _conn.BeginTransaction();
try
{
// skip lines
if (rootPath.SkipLines > 0)
{
while (file.ReadLine() != null && rowsSkipped < rootPath.SkipLines)
{
rowsSkipped++;
}
}
// read all lines from file
while ((line = file.ReadLine()) != null && rowsRead < limit)
{
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
var rawValue = line.Substring(column.ColumnStart, column.ColumnEnd - column.ColumnStart + 1);
cmd.Parameters[$"@param{rootPath.ModeSettings.FixedWidthSettings.Columns.IndexOf(column)}"].Value = column.TrimWhitespace ? rawValue.Trim() : rawValue;
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down file
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
return(rowsRead);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1)
{
var rowsRead = 0;
var rowsSkipped = 0;
List <string> headerColumns = new List <string>();
List <int> columnIndexes;
List <ExcelCell> orderedExcelCells = rootPath.ModeSettings.ExcelModeSettings.GetOrderedExcelCells();
Dictionary <string, object> excelCellsValues = new Dictionary <string, object>();
if (orderedExcelCells.Count > 0)
{
var cellsStreamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
cellsStreamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(cellsStreamWrapper.Stream))
{
var currentRow = 0;
foreach (var cell in orderedExcelCells)
{
while (reader.Read() && currentRow < cell.RowIndex)
{
currentRow++;
}
var rawValue = reader.GetValue(cell.ColumnIndex);
if (!excelCellsValues.TryAdd(cell.ColumnName, rawValue))
{
excelCellsValues.Add(cell.GetUniqueName(), rawValue);
}
currentRow++;
}
}
}
// close down stream
cellsStreamWrapper.Close();
}
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
streamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(memoryStream))
{
// skip lines
if (rootPath.SkipLines > 0)
{
while (reader.Read() && rowsSkipped < rootPath.SkipLines)
{
rowsSkipped++;
}
}
else
{
reader.Read();
}
// get desired column indexes
columnIndexes = rootPath.ModeSettings.ExcelModeSettings.GetAllExcelColumnIndexes();
if (columnIndexes.Count == 0)
{
columnIndexes = Enumerable.Range(0, reader.FieldCount).ToList();
}
// get column names
foreach (var i in columnIndexes)
{
if (rootPath.ModeSettings.ExcelModeSettings.HasHeader)
{
var field = reader.GetValue(i)?.ToString();
if (string.IsNullOrWhiteSpace(field))
{
field = $"NO_HEADER_COLUMN_{i}";
}
if (headerColumns.Contains(field))
{
headerColumns.Add($"{field}_DUPLICATE_{i}");
}
else
{
headerColumns.Add(field);
}
}
else
{
headerColumns.Add($"COLUMN_{i}");
}
}
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append(
$"[{column}] VARCHAR({int.MaxValue}),");
}
foreach (var cell in excelCellsValues)
{
querySb.Append(
$"[{cell.Key}] VARCHAR({int.MaxValue}),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append($"[{column}],");
}
foreach (var cell in excelCellsValues)
{
querySb.Append($"[{cell.Key}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in headerColumns)
{
var paramName = $"@param{headerColumns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
foreach (var cell in excelCellsValues)
{
var paramName = $"@param{cell.Key.Replace(" ", "")}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
// read records
var trans = _conn.BeginTransaction();
try
{
// read all lines from file
while (reader.Read() && rowsRead < limit)
{
foreach (var column in headerColumns)
{
var rawValue = reader.GetValue(columnIndexes[headerColumns.IndexOf(column)])
?.ToString();
cmd.Parameters[$"@param{headerColumns.IndexOf(column)}"].Value = rawValue;
}
foreach (var cell in excelCellsValues)
{
var rawValue = cell.Value?.ToString();
cmd.Parameters[$"@param{cell.Key.Replace(" ", "")}"].Value = rawValue;
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down stream
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
}
return(rowsRead);
}
static void MixedLanguagesUTF8()
{
//Uncomment if your console window does not show all utf8 characters.
//Console.OutputEncoding = Encoding.UTF8;
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE Languages (Id Integer Primary Key AutoIncrement, LanguageName Text, LangText Text);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Languages VALUES (null, @Language, @LangText);";
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Language"
});
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@LangText"
});
cmd.Parameters["@Language"].Value = "English";
cmd.Parameters["@LangText"].Value = "Hello World";
cmd.ExecuteNonQuery();
//Languages written right to left must use parameters intead of string concatenation of sql text.
cmd.Parameters["@Language"].Value = "Urdu";
cmd.Parameters["@LangText"].Value = "ہیلو ورلڈ";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Arabic";
cmd.Parameters["@LangText"].Value = "مرحبا بالعالم";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Chinese Traditional";
cmd.Parameters["@LangText"].Value = "你好，世界";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Japanese";
cmd.Parameters["@LangText"].Value = "こんにちは世界";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Russian";
cmd.Parameters["@LangText"].Value = "Привет мир";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Hindi";
cmd.Parameters["@LangText"].Value = "नमस्ते दुनिया";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Languages; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
Console.WriteLine("---- Search -----");
//Urdu and Arabic should return when searching like ر which is R character in english.
cmd.CommandText = "SELECT * FROM Languages WHERE LangText LIKE @LikeSearch;"; //note no single quotes around @LikeSearch parameter LIKE '%w%'
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@LikeSearch", Value = "%ر%"
});
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
Console.WriteLine("---- Search With OR operator -----");
//Now it should return English, Urdu, Arabic and Russian due to OR operator
cmd.CommandText = "SELECT * FROM Languages WHERE (LangText LIKE '%W%') OR (LangText LIKE @LikeSearch) OR (LangText = @LangText);"; //note no single quotes around @LikeSearch parameter LIKE '%w%'
//Parameters can be cleared using : cmd.Parameters.Clear(); //however we are reusing existing parameter names.
cmd.Parameters["@LikeSearch"].Value = "%ر%"; //since parameter @LikeSearch already exist assign new value.
cmd.Parameters["@LangText"].Value = "Привет мир"; //parameter @LangText already exist in this Command object.
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
}
}
}
static void MultipleCommandTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
// Create a table
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "Create Table If not exists temptable2(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
}
// Start a transaction on this connection
SqlDatabaseTransaction trans = cnn.BeginTransaction();
try
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
// Other processes can run here.
// Transaction stays active even after command object is closed and can be attached to other objects.
//Create another command object and insert in temptable2 using same transaction.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable2 VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Console.WriteLine(sqlex.Message);
}
// Let's check the record count.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Record Count temptable : {0}", cmd.ExecuteScalar());
cmd.CommandText = "SELECT COUNT(*) FROM temptable2;";
Console.WriteLine("Record Count temptable2 : {0}", cmd.ExecuteScalar());
}
}
}
static void SavePoint()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans;
cmd.CommandText = "CREATE TABLE SavePointExample (id Integer); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (1); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT a; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (2); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT b; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (3); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT c; ";
cmd.ExecuteNonQuery();
//should return 1, 2, 3 since no rollback or released has occured.
cmd.CommandText = "SELECT * FROM SavePointExample; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
//rollback save point to b without committing transaction. The value 3 and savepoint c will be gone.
cmd.CommandText = "ROLLBACK TO b"; //b
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
//if we uncomment and release c it wil produce logical error as savepoint c does not exists due to rollback to b.
//cmd.CommandText = "RELEASE c"; //c
//cmd.ExecuteNonQuery();
cmd.CommandText = "RELEASE b;"; //release b means commit the deffered transaction.
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
//We can still rollback entire transaction
//trans.Rollback();
//commit an entire transaction
trans.Commit();
//If we rollback transaction above regardless of release savepoint (i.e. saving)
//following will produce an error that SavePointExample table not found.
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
}
}
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
var formatCmdDictionary = new Dictionary <string, SqlDatabaseCommand>();
var recordGlobalHeaderMap = new Dictionary <string, string>();
var recordHeaderMap = new Dictionary <string, Dictionary <string, string> >();
var recordMap = new Dictionary <string, Dictionary <string, string> >();
var columnsMap = new Dictionary <string, List <Column> >();
// foreach (var format in AS400.Format25) // POC
foreach (var format in rootPath.ModeSettings.AS400Settings.Formats)
{
if (format.IsGlobalHeader)
{
continue;
}
var schemaName = _schemaName;
var tableName = $"{_tableName}_{format.KeyValue.Name}";
if (format.SingleRecordPerLine)
{
var columns = new List <Column>(format.Columns);
// foreach (var globalFormat in AS400.Format25.FindAll(f => f.IsGlobalHeader)) // POC
foreach (var globalFormat in rootPath.ModeSettings.AS400Settings.Formats.FindAll(f => f.IsGlobalHeader))
{
columns.AddRange(globalFormat.Columns);
}
CreateTable(schemaName, tableName, columns);
formatCmdDictionary[format.KeyValue.Name] = GetInsertCmd(schemaName, tableName, columns);
columnsMap[format.KeyValue.Name] = columns;
}
else
{
var columns = new List <Column>(format.MultiLineColumns);
// foreach (var globalFormat in AS400.Format25.FindAll(f => f.IsGlobalHeader)) // POC
foreach (var globalFormat in rootPath.ModeSettings.AS400Settings.Formats.FindAll(f => f.IsGlobalHeader))
{
columns.AddRange(globalFormat.Columns);
}
CreateTable(schemaName, tableName, columns);
formatCmdDictionary[format.KeyValue.Name] = GetInsertCmd(schemaName, tableName, columns);
columnsMap[format.KeyValue.Name] = columns;
}
recordHeaderMap[format.KeyValue.Name] = new Dictionary <string, string>();
recordMap[format.KeyValue.Name] = new Dictionary <string, string>();
}
// read file into db
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
var file = streamWrapper.StreamReader;
string line;
var recordsInserted = 0;
var trans = _conn.BeginTransaction();
try
{
// read all lines from file
while ((line = file.ReadLine()) != null && recordsInserted < limit)
{
// var keyValue = line.Substring(0, 2; // POC
var keyValue = line.Substring(0, rootPath.ModeSettings.AS400Settings.KeyValueWidth);
// var format = AS400.Format25.FirstOrDefault(f => f.KeyValue.Value == keyValue); // POC
var format = rootPath.ModeSettings.AS400Settings.Formats.FirstOrDefault(f => f.KeyValue.Value == keyValue);
if (format == null)
{
continue;
}
if (format.SingleRecordPerLine)
{
foreach (var column in format.Columns)
{
var valueLength = column.ColumnEnd -
column.ColumnStart + 1;
var rawValue = new string(line.Substring(column.ColumnStart)
.Take(valueLength).ToArray());
if (format.IsGlobalHeader)
{
recordGlobalHeaderMap.TryAdd(column.ColumnName, rawValue);
continue;
}
// TODO handle not global header
}
}
else
{
var tagNameLength = format.MultiLineDefinition.TagNameEnd -
format.MultiLineDefinition.TagNameStart + 1;
var tagName = line.Substring(format.MultiLineDefinition.TagNameStart, tagNameLength).Trim();
var recordId = tagName.Split(format.MultiLineDefinition.TagNameDelimiter).First();
var columnName =
tagName.Substring(tagName.IndexOf(format.MultiLineDefinition.TagNameDelimiter) + 1);
var valueLength = int.Parse(line.Substring(format.MultiLineDefinition.ValueLengthStart,
format.MultiLineDefinition.ValueLengthEnd - format.MultiLineDefinition.ValueLengthStart +
1));
var rawValue = new string(line.Substring(format.MultiLineDefinition.ValueStart)
.Take(valueLength).ToArray());
bool insert = false;
bool headerTrigger = false;
bool recordTrigger = false;
if (format.HeaderRecordKeys.Contains(recordId))
{
insert = !recordHeaderMap[format.KeyValue.Name].TryAdd(tagName, rawValue);
headerTrigger = insert;
}
else
{
if (recordHeaderMap[format.KeyValue.Name].Count != 0)
{
insert = !recordMap[format.KeyValue.Name].TryAdd(columnName, rawValue);
recordTrigger = insert;
}
}
if (insert)
{
var cmd = formatCmdDictionary[format.KeyValue.Name];
if (cmd != null)
{
var columns = columnsMap[format.KeyValue.Name];
foreach (var column in columns)
{
string value;
if (column.IsHeader)
{
if (!recordHeaderMap[format.KeyValue.Name]
.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
else if (column.IsGlobalHeader)
{
if (!recordGlobalHeaderMap.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
else
{
if (!recordMap[format.KeyValue.Name]
.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
}
var hasKey = true;
foreach (var keyColumn in format.MultiLineColumns.Where(c => c.IsKey))
{
if (string.IsNullOrWhiteSpace(cmd.Parameters[$"@{GetParamName(keyColumn)}"].Value
.ToString()))
{
hasKey = false;
break;
}
}
// insert record
if (recordHeaderMap[format.KeyValue.Name].Count > 0 && hasKey)
{
cmd.ExecuteNonQuery();
recordsInserted++;
}
}
// clear storage
recordMap[format.KeyValue.Name].Clear();
if (headerTrigger)
{
recordHeaderMap[format.KeyValue.Name].Clear();
recordHeaderMap[format.KeyValue.Name].TryAdd(tagName, rawValue);
}
if (recordTrigger)
{
recordMap[format.KeyValue.Name].TryAdd(columnName, rawValue);
}
}
}
// commit every 1000 rows
if (recordsInserted % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down file
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
return(recordsInserted);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1)
{
var totalRowsRead = 0;
// get the global key
var globalKeyId = "GLOBAL_KEY";
var globalKeyIndexId = "GLOBAL_KEY_INDEX";
var globalKeySb = new StringBuilder();
var globalKeyValue = "";
// load xml doc
var stream = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
XmlDocument doc = new XmlDocument();
doc.Load(stream.Stream);
foreach (var xmlKey in rootPath.ModeSettings.XMLSettings.XmlKeys)
{
var elements = doc.GetElementsByTagName(xmlKey.ElementId);
XmlNode element;
try
{
element = elements[0];
}
catch (Exception e)
{
throw new Exception($"Element {xmlKey.ElementId} is defined as a key and the element does not exist in the file {Path.GetFileName(filePathAndName)}");
}
if (!string.IsNullOrWhiteSpace(xmlKey.AttributeId))
{
if (element.Attributes?[xmlKey.AttributeId] == null)
{
throw new Exception($"Attribute {xmlKey.AttributeId} is defined as a key on element {xmlKey.ElementId} and the attribute is null or not exist in the file {Path.GetFileName(filePathAndName)}");
}
var attribute = element.Attributes[xmlKey.AttributeId];
if (string.IsNullOrWhiteSpace(attribute.Value))
{
throw new Exception($"Attribute {xmlKey.AttributeId} is defined as a key on element {xmlKey.ElementId} and the attribute is null or not exist in the file {Path.GetFileName(filePathAndName)}");
}
globalKeySb.Append(attribute.Value);
}
else
{
var elementValue = element.Value;
if (string.IsNullOrWhiteSpace(elementValue))
{
throw new Exception($"Element {xmlKey.ElementId} is defined as a key and the element value is null in the file {Path.GetFileName(filePathAndName)}");
}
globalKeySb.Append(elementValue);
}
}
globalKeyValue = globalKeySb.ToString();
stream.Close();
// parse xml into multiple tables
DataSet dataSet = new DataSet();
stream = Utility.Utility.GetStream(rootPath.ModeSettings.XMLSettings.XsdFilePathAndName,
rootPath.FileReadMode, downloadToLocal);
dataSet.ReadXmlSchema(stream.Stream);
stream.Close();
dataSet.Locale = CultureInfo.InvariantCulture;
dataSet.EnforceConstraints = false;
stream = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
dataSet.ReadXml(stream.Stream, XmlReadMode.Auto);
stream.Close();
// create and load each table
foreach (DataTable table in dataSet.Tables)
{
var rowsRead = 0;
// create table
var fullTableName = $"{_tableName}_{table.TableName}";
var columns = new List <string>();
for (var i = 0; i < table.Columns.Count; i++)
{
columns.Add(table.Columns[i].ColumnName);
}
// append global key and global key index columns
columns.Add(globalKeyId);
columns.Add(globalKeyIndexId);
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{fullTableName}] (");
// add the columns
foreach (var column in columns)
{
querySb.Append(
$"[{column}] VARCHAR({int.MaxValue}),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{fullTableName}] (");
foreach (var column in columns)
{
querySb.Append($"[{column}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in columns)
{
var paramName = $"@param{columns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
// load data
var trans = _conn.BeginTransaction();
try
{
foreach (var row in table.AsEnumerable())
{
var rowIndex = table.AsEnumerable().ToList().IndexOf(row);
foreach (var column in columns)
{
var columnIndex = columns.IndexOf(column);
if (column == globalKeyId)
{
cmd.Parameters[$"@param{columnIndex}"].Value = globalKeyValue;
}
else if (column == globalKeyIndexId)
{
cmd.Parameters[$"@param{columnIndex}"].Value = $"{globalKeyValue}_{rowIndex}";
}
else
{
var rawValue = row[columnIndex]?.ToString();
cmd.Parameters[$"@param{columnIndex}"].Value = rawValue;
}
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
if (rowsRead > limit)
{
break;
}
}
// commit any pending inserts
trans.Commit();
totalRowsRead += rowsRead;
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
return(totalRowsRead);
}
private void ParallelInsertFile_Click(object sender, EventArgs e)
{
DialogResult dlgresult = MessageBox.Show("This example will copy file names from MyDocuments folder, continue ?", "Important Question", MessageBoxButtons.YesNo);
if (dlgresult.ToString() == "No")
{
return;
}
string[] files = System.IO.Directory.GetFiles(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "*.*");
string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "files.db");
try
{
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
} catch (IOException ioe)
{
MessageBox.Show(ioe.Message);
return;
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + dbfilepath + ";"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE FileNames (Id Integer primary key autoincrement, InsertDateTime Text, ThreadId Integer, FileName Text); ";
cmd.ExecuteNonQuery();
}
SqlDatabaseTransaction trans = cnn.BeginTransaction();
try
{
Parallel.ForEach(files, (currentFile) =>
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO FileNames VALUES (null, GetDate(), @ThreadId, @FileName); ";
cmd.Parameters.Add("@ThreadId", Thread.CurrentThread.ManagedThreadId);
cmd.Parameters.Add("@FileName", currentFile);
cmd.ExecuteNonQuery();
}
});
}
catch
{
trans.Rollback();
}
trans.Commit();
//Now try reading first 100 rows by using LIMIT 100..
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT * FROM FileNames LIMIT 100; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Debug.Write(dr.GetValue(c) + "\t");
}
Debug.WriteLine("");
}
}
}
//Delete the database file since we don't need it.
try
{
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
}
catch (IOException ioe)
{
MessageBox.Show(ioe.Message);
return;
}
}
