public void Constructor()
{
TestClass.test();
string[] tableNames = { "users",
"business",
"Cash Debit Book",
"Cash Credit Book" };
SqlController TEST = new SqlController(tableNames);
TEST.ChangeField(0, 0, 1, "IT WORKS!", true);
TEST.ChangeField("users", 0, 1, "William", true);
TEST.InsertRow("users", new string[] { "COMPLETE", "COMPLETE", "COMPLETE" }, true);
TEST.DeleteRow("users", 2);
try
{
MessageBox.Show(TEST.CommitToDatabase());
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
//Assert.AreEqual(DataSet_EX.ActionType.CHANGE, (DataSet_EX.ActionType)tables._transactionType[0]);
//Assert.AreEqual(DataSet_EX.ActionType.CHANGE, (DataSet_EX.ActionType)tables._transactionType[1]);
//Assert.AreEqual(DataSet_EX.ActionType.ADD, (DataSet_EX.ActionType)tables._transactionType[2]);
//Assert.AreEqual(DataSet_EX.ActionType.DELETE, (DataSet_EX.ActionType)tables._transactionType[3]);
}
public string CommitToDatabase(SqlController sqlcontroller)
{
string sql = "";
int[] TCount = GetCounts();
int sum = TCount[0] + TCount[1] + TCount[2];
if (sum != _transactionType.Count)
{
throw new Exception("TransactionType.Count does not equal sum");
}
foreach (_TransactionType t in _transactionType)
{
ActionType key = t.ACTIONTYPE;
bool primaryProcessed = true;
if (t.HASPRIMARYKEY == HasPrimaryKey.YES)
{
primaryProcessed = false;
}
switch (key)
{
case ActionType.ADD:
{
sql += "INSERT INTO " + _addedRows[0].TABLENAME +
" VALUES(";
foreach (object field in _addedRows[0].FIELDS)
{
if (primaryProcessed)
{
Type test = typeof(string);
if (field.GetType() == test)
{
sql += "'";
}
sql += $"{field.ToString()}";
if (field.GetType() == test)
{
sql += "'";
}
sql += ",";
}
primaryProcessed = true;
}
int removeIndex = sql.Length - 1;
sql = sql.Remove(removeIndex, 1);
sql += ");";
try
{
sqlcontroller.InsertIntoDataBase(sql);
_addedRows.RemoveAt(0);
}
catch (Exception ex)
{
return(ex.Message);
}
sql = "";
break;
}
case ActionType.CHANGE:
{
DataRow row = Tables[_changedRow[0].OLDROW.TABLENAME].NewRow();
DataColumnCollection columns = row.Table.Columns;
bool primaryProcessedStep2 = true;
for (int i = 0; i < columns.Count; i++)
{
if (t.HASPRIMARYKEY == HasPrimaryKey.YES && primaryProcessed == false)
{
primaryProcessedStep2 = false;
primaryProcessed = true;
continue;
}
sql += $"UPDATE [{_changedRow[0].NEWROW.TABLENAME}] SET [{columns[i].ColumnName}] =";
Type columnType = _changedRow[0].NEWROW.FIELDS[i].GetType();
if (columnType == typeof(string))
{
sql += "'";
}
sql += $"{_changedRow[0].NEWROW.FIELDS[i]}";
if (columnType == typeof(string))
{
sql += "'";
}
sql += " WHERE ";
int valueIndex = 0;
foreach (DataColumn column in columns)
{
if (primaryProcessedStep2 == true)
{
columnType = _changedRow[0].NEWROW.FIELDS[valueIndex].GetType();
sql += $"[{column.ColumnName}] = ";
if (columnType == typeof(string))
{
sql += "'";
}
sql += $"{_changedRow[0].OLDROW.FIELDS[valueIndex].ToString()}";
if (columnType == typeof(string))
{
sql += "'";
}
sql += " AND ";
}
valueIndex++;
primaryProcessedStep2 = true;
}
int removeIndex = sql.Length - 5;
sql = sql.Remove(removeIndex, 5);
sql += ";\r";
}
try
{
sqlcontroller.UpdateRowIntoDatabase(sql);
_changedRow.RemoveAt(0);
}catch (Exception ex) { return(ex.Message); }
finally { sql = ""; }
break;
}
case ActionType.DELETE:
{
sql += $"DELETE FROM [{_deletedRows[0].TABLENAME}] WHERE ";
DataRow row = Tables[_deletedRows[0].TABLENAME].NewRow();
DataColumnCollection columns = row.Table.Columns;
int valueIndex = 0;
foreach (DataColumn column in columns)
{
Type columnType = column.DataType;
sql += $"[{column.ColumnName}] = ";
if (columnType == typeof(string))
{
sql += "'";
}
sql += $"{_deletedRows[0].FIELDS[valueIndex]}";
if (columnType == typeof(string))
{
sql += "'";
}
sql += " AND ";
valueIndex++;
}
int removeIndex = sql.Length - 5;
sql = sql.Remove(removeIndex, 5);
sql += ";\r";
try
{
sqlcontroller.DeleteRowFromDatabase(sql);
_deletedRows.RemoveAt(0);
}
catch (Exception ex) { return(ex.Message); }
finally { sql = ""; }
break;
}
}
}
_transactionType.Clear();
return(sql);
}