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); }