//UPDATE POINT3D SET 'X' =1, 'Y' = 2, 'Z' = 3 WHERE ID = 3;
public static void Update(SQLiteCommand command, Point3dModel model)
{
List <List <object> > itemList = new List <List <object> >
{
new List <object> {
DBPoint3DName.X, DBPoint3D_AT.x, model.X
},
new List <object> {
DBPoint3DName.Y, DBPoint3D_AT.y, model.Y
},
new List <object> {
DBPoint3DName.Z, DBPoint3D_AT.z, model.Z
}
};
Dictionary <string, string> variable = new Dictionary <string, string>();
Dictionary <string, object> paraDict = new Dictionary <string, object>();
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBPoint3DName.ID, DBPoint3D_AT.id }
};
foreach (List <object> item in itemList)
{
variable.Add((string)item[0], (string)item[1]);
paraDict.Add((string)item[1], item[2]);
}
;
paraDict.Add(DBPoint3D_AT.id, model.ID);
DBCommand.UpdateRow(DBPoint3DName.tableName, variable, conDict, paraDict, command);
}
public static void InsertRow(SQLiteCommand command, DwgFileModel model)
{
string relPath = model.relativePath;
List <List <object> > items = new List <List <object> >
{
new List <object> {
DBDwgFileName.ISP_NOTES, DBDwgFileName_AT.iNote, model.isP_Notes
},
new List <object> {
DBDwgFileName.MODIFIEDDATE, DBDwgFileName_AT.modDate, model.modifieddate
},
new List <object> {
DBDwgFileName.RELATIVE_PATH, DBDwgFileName_AT.relPath, relPath
}
};
List <string> variables = new List <string>();
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0]);
paraDict.Add((string)item[1], item[2]);
}
DBCommand.InsertCommand(DBDwgFileName.name, variables, paraDict, command);
}
public static void UpdateRow(SQLiteCommand command, DwgFileModel model)
{
string relPath = model.relativePath;
List <List <object> > items = new List <List <object> >()
{
new List <object> {
DBDwgFileName.RELATIVE_PATH, DBDwgFileName_AT.relPath, relPath
},
new List <object> {
DBDwgFileName.MODIFIEDDATE, DBDwgFileName_AT.modDate, model.modifieddate
},
new List <object> {
DBDwgFileName.ISP_NOTES, DBDwgFileName_AT.iNote, model.isP_Notes
},
};
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBDwgFileName.ID, DBDwgFileName_AT.id }
};
Dictionary <string, string> variables = new Dictionary <string, string>();
Dictionary <string, object> paradict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0], (string)item[1]);
paradict.Add((string)item[1], item[2]);
}
paradict.Add(DBDwgFileName.ID, model.ID);
DBCommand.UpdateRow(DBDwgFileName.name, variables, conDict, paradict, command);
command.ExecuteNonQuery();
}
public static void SelectRow(SQLiteCommand command, string relPath)
{
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBDwgFileName.RELATIVE_PATH, DBDwgFileName_AT.relPath }
};
Dictionary <string, object> paraDict = new Dictionary <string, object> {
{ DBDwgFileName_AT.relPath, relPath }
};
DBCommand.SelectRow(DBDwgFileName.name, conDict, paraDict, command);
}
public static bool HasRowID(SQLiteConnection connection, long ID)
{
long count = 0;
using (SQLiteCommand command = connection.CreateCommand())
{
DBCommand.SelectCount(DBDwgFileName.name, ID, command);
count = Convert.ToInt64(command.ExecuteScalar());
}
return(count == 1);
}
public static void GetPNoteFile(SQLiteCommand command)
{
int isNote = 1;
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBDwgFileName.ISP_NOTES, DBDwgFileName_AT.iNote }
};
Dictionary <string, object> paraDict = new Dictionary <string, object> {
{ DBDwgFileName_AT.iNote, isNote }
};
DBCommand.SelectRow(DBDwgFileName.name, conDict, paraDict, command);
}
public static void Insert(SQLiteCommand command, Matrix3dModel model)
{
List <List <object> > items = getItems(model);
List <string> variables = new List <string>();
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0]);
paraDict.Add((string)item[1], item[2]);
}
DBCommand.InsertCommand(DBMatrixName.name, variables, paraDict, command);
}
public static void Insert(SQLiteCommand command, Point3dModel model)
{
//"INSERT INTO POINT3D ('X', 'Y', 'Z') VALUES (034.34, 233, 0);"
List <string> variables = new List <string> {
DBPoint3DName.X, DBPoint3DName.Y, DBPoint3DName.Z
};
Dictionary <string, object> paraDict = new Dictionary <string, object> {
{ DBPoint3D_AT.x, model.X },
{ DBPoint3D_AT.y, model.Y },
{ DBPoint3D_AT.z, model.Z }
};
DBCommand.InsertCommand(DBPoint3DName.tableName, variables, paraDict, command);
}
private static void DeleteOthers(DwgFileModel model, SQLiteConnection connection)
{
if (model != null)
{
using (SQLiteCommand command = connection.CreateCommand())
{
//MUST DELETE CHILD FIRST.
List <FixtureDetailsModel> fixtures = DBFixtureDetails.SelectRows(connection, model.relativePath);
List <FixtureBeingUsedAreaModel> areas = DBFixtureBeingUsedArea.SelectRows(connection, model.relativePath);
List <InsertPointModel> insertPoints = DBInsertPoint.SelectRows(connection, model.relativePath);
List <TableModel> tables = DBTable.SelectRows(connection, model.relativePath);
List <AreaBorderModel> areaU = DBAreaBorder.SelectRows(connection, model.relativePath);
List <FixtureUnitModel> units = DBFixture_Unit.SelectRows(connection, model.relativePath);
foreach (FixtureDetailsModel fixture in fixtures)
{
DBFixtureDetails.DeleteRow(connection, fixture.ID);
}
foreach (FixtureBeingUsedAreaModel area in areas)
{
DBFixtureBeingUsedArea.DeleteRow(connection, area.ID);
}
foreach (InsertPointModel insertPoint in insertPoints)
{
DBInsertPoint.DeleteRow(connection, insertPoint.ID);
}
foreach (TableModel table in tables)
{
DBTable.DeleteRow(connection, table.ID);
}
foreach (AreaBorderModel m in areaU)
{
DBTable.DeleteRow(connection, m.ID);
}
foreach (FixtureUnitModel m in units)
{
DBTable.DeleteRow(connection, m.ID);
}
DBCommand.DeleteRow(DBDwgFileName.name, model.ID, command);
long check = command.ExecuteNonQuery();
}
}
}
public static void InsertRow(FixtureDetailsModel model, SQLiteCommand command)
{
List <List <object> > items = getListItems(model);
List <string> variables = new List <string>();
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0]);
paraDict.Add((string)item[1], item[2]);
}
DBCommand.InsertCommand(DBFixtureDetailsNames.name, variables, paraDict, command);
}
public static void InitTable(string databasePath)
{
SQLiteConnection sqliteConn = new SQLiteConnection(DBCommand.GetConnectionString(databasePath));
try
{
sqliteConn.Open();
SQLiteTransaction sqliteTrans = sqliteConn.BeginTransaction();
DBMatrix3d.CreateTable(sqliteConn);
DBPoint3D.CreateTable(sqliteConn);
DBInsertPoint.CreateTable(sqliteConn);
DBFixtureDetails.CreateTable(sqliteConn);
DBFixtureBeingUsedArea.CreateTable(sqliteConn);
DBDwgFile.CreateTable(sqliteConn);
DBTable.CreateTable(sqliteConn);
DBFixture_Unit.CreateTable(sqliteConn);
DBAreaBorder.CreateTable(sqliteConn);
if (projectElement.P_NOTE != null)
{
projectElement.P_NOTE.WriteToDatabase(sqliteConn);
}
if (projectElement.Dwgs != null)
{
foreach (DwgFileModel model in projectElement.Dwgs)
{
model.WriteToDatabase(sqliteConn);
}
}
sqliteTrans.Commit();
sqliteTrans.Dispose();
sqliteConn.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception e)
{
DebugMessage(string.Format(@"Error opening Database: {0}, Error:{1}", databasePath, e.Message));
}
}
public static void Update(SQLiteCommand command, Matrix3dModel model)
{
List <List <object> > items = getItems(model);
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBMatrixName.ID, DBMatrixName_AT.id }
};
Dictionary <string, string> variables = new Dictionary <string, string>();
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0], (string)item[1]);
paraDict.Add((string)item[1], item[2]);
}
paraDict.Add(DBMatrixName_AT.id, model.ID);
DBCommand.UpdateRow(DBMatrixName.name, variables, conDict, paraDict, command);
}
public static void UpdateRow(SQLiteCommand command, InsertPointModel model)
{
DBPoint3D.UpdateRow(model.position, command.Connection);
DBMatrix3d.Update(command.Connection, model.matrixTransform);
List <List <object> > items = GetItemsList(model);
Dictionary <string, string> variables = new Dictionary <string, string>();
Dictionary <string, string> conditions = new Dictionary <string, string> {
{ DBInsertPointName.ID, DBInsertPointName_AT.id }
};
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0], (string)item[1]);
paraDict.Add((string)item[1], item[2]);
}
paraDict.Add(DBBlockName_AT.id, model.ID);
DBCommand.UpdateRow(DBInsertPointName.name, variables, conditions, paraDict, command);
}
public static void UpdateRow(FixtureDetailsModel model, SQLiteCommand command)
{
DBPoint3D.UpdateRow(model.position, command.Connection);
DBMatrix3d.Update(command.Connection, model.matrixTransform);
List <List <object> > items = getListItems(model);
Dictionary <string, string> variables = new Dictionary <string, string>();
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBFixtureDetailsNames.ID, DBFixtureDetailsNames_AT.id }
};
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0], (string)item[1]);
paraDict.Add((string)item[1], item[2]);
}
paraDict.Add(DBFixtureDetailsNames_AT.id, model.ID);
DBCommand.UpdateRow(DBFixtureDetailsNames.name, variables, conDict, paraDict, command);
}
public static void UpdateRow(SQLiteCommand command, FixtureBeingUsedAreaModel model)
{
DBPoint3D.UpdateRow(model.pointTop, command.Connection);
DBPoint3D.UpdateRow(model.pointBottom, command.Connection);
DBPoint3D.UpdateRow(model.origin, command.Connection);
DBPoint3D.UpdateRow(model.position, command.Connection);
DBMatrix3d.Update(command.Connection, model.matrixTransform);
List <List <object> > items = GetItems(model);
Dictionary <string, string> variables = new Dictionary <string, string>();
Dictionary <string, string> conDict = new Dictionary <string, string> {
{ DBFixtureBeingUsedAreaName.ID, DBFixtureBeingUsedAreaName_AT.id }
};
Dictionary <string, object> paraDict = new Dictionary <string, object>();
foreach (List <object> item in items)
{
variables.Add((string)item[0], (string)item[1]);
paraDict.Add((string)item[1], item[2]);
}
DBCommand.UpdateRow(DBFixtureBeingUsedAreaName.name, variables, conDict, paraDict, command);
}
public static void DeleteTable(SQLiteCommand command)
{
DBCommand.DeleteTable(DBMatrixName.name, command);
}
public static void DeleteTable(SQLiteCommand command)
{
DBCommand.DeleteTable(DBDwgFileName.name, command);
}
public static void DeleteTable(SQLiteCommand command)
{
DBCommand.DeleteTable(DBFixtureDetailsNames.name, command);
}
public static SQLiteConnection OpenSqliteConnection(string dbPath)
{
return(new SQLiteConnection(DBCommand.GetConnectionString(dbPath)));
}
