public static void insert_to_db(string table_name, string field_name, string field_value)
{
try
{
//Helper.Logger.LOG_IT("\nDB begin");
con.Open();
SQLiteCommand cmd = new SQLiteCommand(con);
//begin sqlite transaction
SQLiteTransaction sqlite_tran = con.BeginTransaction();
InsertQueryBuilder insert_query = new InsertQueryBuilder();
insert_query.Table = table_name;
insert_query.SetField(field_name, field_value);
cmdd_string = insert_query.BuildQuery();
cmd.CommandText = cmdd_string;
Debug.Write("\nSQL insert_to_db: " + cmd.CommandText);
//Helper.Logger.LOG_IT("\n" + cmd.CommandText + "\n");
cmd.ExecuteNonQuery();
sqlite_tran.Commit();
cmd.Dispose();
con.Close();
}
catch (SQLiteException ex)
{
Debug.Write("\n" + ex.Message);
}
catch (System.Exception ex)
{
Debug.Write("\n" + ex.Message);
}
}
public static bool updateOrNewConfig(string key, string value)
{
MysqlDatas data = getValue(key);
if (!data.hasRows)
{
InsertQueryBuilder query = new InsertQueryBuilder();
query.Table = _table;
query.addValue("config_key", key);
query.addValue("config_value", value);
query.addValue("created_at", Helper.GetDatabaseDateFormat());
string cmd = query.BuildQuery();
return(Utils.Mysql.execute(cmd));
}
else
{
UpdateQueryBuilder query = new UpdateQueryBuilder();
query.Table = _table;
query.addSet("config_value", value);
query.AddWhere("config_key", Comparison.Equals, key);
string cmd = query.BuildQuery();
return(Utils.Mysql.execute(cmd));
}
return(false);
}
public static void insert_data_to_db(string table_name, IndexedDictionary <string, string> field_value, int dict_count)
{
try
{
//Helper.Logger.LOG_IT("\nDB begin");
con.Open();
//SQLiteCommand cmd = new SQLiteCommand(con);
//begin sqlite transaction
SQLiteTransaction sqlite_tran = con.BeginTransaction();
InsertQueryBuilder insert_query = new InsertQueryBuilder();
insert_query.Table = table_name;
for (int i = 0; i < field_value.Count; i++)
{
Debug.Write("\nDB -" + "==" + i + field_value.GetKeyByIndex(i) + field_value[field_value.GetKeyByIndex(i)]);
insert_query.SetField(field_value.GetKeyByIndex(i), field_value[field_value.GetKeyByIndex(i)]);
}
cmdd_string = insert_query.BuildQuery();
cmd.CommandText = cmdd_string;
Debug.Write("\nSQL insert_data_to_db: " + cmd.CommandText + "\n");
cmd.ExecuteNonQuery();
sqlite_tran.Commit();
cmd.Dispose();
con.Close();
}
catch (SQLiteException ex)
{
Debug.Write("\n" + ex.Message);
}
catch (System.Exception ex)
{
Debug.Write("\n" + ex.Message);
}
}
private string GetQueryStr(GroupedColumns table)
{
var queryBuilder = new InsertQueryBuilder();
var insertedColumns = GetInsertedColumns(table.Columns);
queryBuilder.InsertIntoTable(table.Columns[0].TableName, insertedColumns, false);
var query = queryBuilder.BuildQuery();
return(query);
}
private string AddValueFromForeignKeyColumns(Guid tenantId, string tableName, string entityPrefix, string foreignKeyCoumn, Guid foreignKeyColValue, List <ColumnAndField> matchingColumns, List <ColumnAndField> entityColumns)
{
var queryBuilder = new InsertQueryBuilder();
var insertColumns = new Dictionary <string, string> ();
foreach (var match in matchingColumns)
{
if (match.TableName.Equals(tableName) && match.EntityPrefix.Equals(entityPrefix))
{
if (match.Value != null)
{
insertColumns.Add(match.ColumnName, match.Value.ToString());
}
}
}
var inverseId = Guid.NewGuid();
var inverPrimaryKeyColumns = entityColumns.FirstOrDefault(t => t.ColumnName.Equals(t.PrimaryKey) &&
t.TableName.Equals(tableName) &&
t.EntityPrefix.Equals(entityPrefix)
);
if (inverPrimaryKeyColumns == null)
{
throw new FieldAccessException("Primary key not found.");
}
insertColumns.Add(inverPrimaryKeyColumns.ColumnName, inverseId.ToString());
//tenant id.
var primaryTenantId = entityColumns.FirstOrDefault(x => x.ColumnName.Equals(_tenantId) && x.TableName.Equals(tableName));
if (primaryTenantId == null)
{
throw new FieldAccessException("Tenant id not found.");
}
insertColumns.Add(primaryTenantId.ColumnName, tenantId.ToString());
//foreignKey
var foreignKeyColumns = entityColumns.FirstOrDefault(x => x.ColumnName.Equals(foreignKeyCoumn) && x.TableName.Equals(tableName));
if (foreignKeyColumns == null)
{
throw new FieldAccessException("Tenant id not found.");
}
insertColumns.Add(foreignKeyColumns.ColumnName, foreignKeyColValue.ToString());
queryBuilder.InsertIntoTable(tableName, insertColumns, false);
var insertQuery = queryBuilder.BuildQuery();
IQueryAdmin admin = new QueryAdmin();
admin.SaveResult(tenantId, "inverse", insertQuery);
return(inverseId.ToString());
}
public static bool addValue(string value)
{
if (!Models.Faculty.getValue(value).hasRows)
{
InsertQueryBuilder query = new InsertQueryBuilder();
query.Table = _table;
query.addValue("value", value);
query.addValue("created_at", Helper.GetDatabaseDateFormat());
string cmd = query.BuildQuery();
bool result = Utils.Mysql.execute(cmd);
return(result);
}
return(false);
}
public bool AddRelations(Guid tenantId, string relationEntityName, string parentEntityName, Guid parentId, string childEntityName, List <Guid> childIds)
{
IMetadataManager _metadaManager = new VPC.Framework.Business.MetadataManager.Contracts.MetadataManager();
var tableName = _metadaManager.GetTableNameByEntityname(relationEntityName);
var primaryKey = _metadaManager.GetPrimaryKeyByEntityname(relationEntityName);
var fields = _metadaManager.GetColumnNameByEntityName(relationEntityName, null);
if (fields.Any())
{
var parentTableName = _metadaManager.GetTableNameByEntityname(parentEntityName);
var parentField = fields.FirstOrDefault(t => t.ReferenceTableName.Equals(parentTableName));
var childTableName = _metadaManager.GetTableNameByEntityname(childEntityName);
var childField = fields.FirstOrDefault(t => t.ReferenceTableName.Equals(childTableName));
//delete and create....
var queryBuilder = new DeleteQueryBuilder();
queryBuilder.SelectFromTable(tableName);
queryBuilder.AddWhere(parentField.ColumnName, Comparison.Equals, parentId.ToString(), 1);
var deleteQuery = queryBuilder.BuildQuery();
//----------------------------------------------------------------------------------------
IRelationQueryAdmin deleteAdmin = new RelationQueryAdmin();
var res = deleteAdmin.DeleteResult(tenantId, relationEntityName, deleteQuery);
//need to change this logic....
foreach (var childId in childIds)
{
var insertQueryBuilder = new InsertQueryBuilder();
var insertColumns = GetNecessaryColumns(tenantId, parentId, fields, parentField);
insertColumns.Add(childField.ColumnName, childId.ToString());
insertQueryBuilder.InsertIntoTable(tableName, insertColumns, false);
var insertQuery = insertQueryBuilder.BuildQuery();
IRelationQueryAdmin saveAdmin = new RelationQueryAdmin();
saveAdmin.SaveResult(tenantId, relationEntityName, insertQuery);
}
}
return(true);
}
/// <summary>
/// TrackerSql Method: This method will track a user and comit it to the datalayer.
/// </summary>
public void TrackerSql()
{
foreach (var area in _Areas)
{
switch (area) //add new enum here
{
case PerformancePortal.Mos:
AreaChosen = "MOS";
break;
case PerformancePortal.Ckpi:
AreaChosen = "Corporate KPI Scorecards";
break;
case PerformancePortal.Framework:
AreaChosen = "Incident KPI Framework";
break;
case PerformancePortal.Iat:
AreaChosen = "Incident Analysis Tool";
break;
case PerformancePortal.Ibt:
AreaChosen = "Internal Benchmarking Tool";
break;
case PerformancePortal.Iqt:
AreaChosen = "Incident Query Tool";
break;
case PerformancePortal.Kpi:
AreaChosen = "Incident KPI Tool";
break;
case PerformancePortal.Qrt:
AreaChosen = "Quarterly Reporting Tool";
break;
case PerformancePortal.Ss:
AreaChosen = "Station Scorecard";
break;
case PerformancePortal.Portal:
AreaChosen = "Performance Portal";
break;
case PerformancePortal.Er:
AreaChosen = "Emergency Reponse";
break;
}
string INSERTstatement;
string Selectstatement;
DataSet TrackerDataSet;
string strSQLconnection;
SQLConnection ConnectToDataBase = new SQLConnection();
SQLConnection InsertQuery = new SQLConnection();
strSQLconnection = SqlConnect;
InsertQueryBuilder insertQuery = new InsertQueryBuilder();
SelectQueryBuilder selectQuery = new SelectQueryBuilder();
//create select statement
selectQuery.SelectColumns("Name", "Department", "[Rank]");
selectQuery.SelectFromTable("OrganisationalStructure");
selectQuery.AddWhere("[Login]", Comparison.Equals, UserName);
Selectstatement = selectQuery.BuildQuery();
//retrieve select statement dataset
ConnectToDataBase.connection_string = strSQLconnection;
ConnectToDataBase.Sql = Selectstatement;
TrackerDataSet = ConnectToDataBase.GetConnection;
//get data from dataset table
var name = TrackerDataSet.Tables[0].Rows[0]["Name"].ToString();
var department = TrackerDataSet.Tables[0].Rows[0]["Department"].ToString();
var rank = TrackerDataSet.Tables[0].Rows[0]["Rank"].ToString();
//create insert statmement
insertQuery.Table = "PerfPortal_Tracker";
insertQuery.SetField("LoginName", UserName);
insertQuery.SetField("FullName", name);
insertQuery.SetField("Department", department);
insertQuery.SetField("JobTitle", rank);
insertQuery.SetField("[DateTime]", DateTime.Now.ToString(CultureInfo.InvariantCulture));
insertQuery.SetField("AreaAccessed", AreaChosen);
INSERTstatement = insertQuery.BuildQuery();
//send sql statement to server
InsertQuery.Query(INSERTstatement, SqlConnect);
}
}
/// <summary>
/// TrackerSql Method: This method will track a user and comit it to the datalayer.
/// </summary>
public void TrackerSql()
{
try
{
foreach (var area in _Areas)
{
switch (area) //add new enum here
{
case PerformancePortal.Mos:
AreaChosen = "MOS";
break;
case PerformancePortal.Ckpi:
AreaChosen = "Corporate KPI Scorecards";
break;
case PerformancePortal.Framework:
AreaChosen = "Incident KPI Framework";
break;
case PerformancePortal.Iat:
AreaChosen = "Incident Analysis Tool";
break;
case PerformancePortal.Ibt:
AreaChosen = "Internal Benchmarking Tool";
break;
case PerformancePortal.Iqt:
AreaChosen = "Incident Query Tool";
break;
case PerformancePortal.Kpi:
AreaChosen = "Incident KPI Tool";
break;
case PerformancePortal.Qrt:
AreaChosen = "Quarterly Reporting Tool";
break;
case PerformancePortal.Ss:
AreaChosen = "Station Scorecard";
break;
case PerformancePortal.Portal:
AreaChosen = "Performance Portal";
break;
case PerformancePortal.Er:
AreaChosen = "Emergency Reponse";
break;
}
string INSERTstatement;
string Selectstatement;
DataSet TrackerDataSet;
string strSQLconnection;
SQLConnection ConnectToDataBase = new SQLConnection();
SQLConnection InsertQuery = new SQLConnection();
strSQLconnection = SqlConnect;
InsertQueryBuilder insertQuery = new InsertQueryBuilder();
SelectQueryBuilder selectQuery = new SelectQueryBuilder();
//create select statement
selectQuery.SelectColumns("Name", "Department", "[Rank]");
selectQuery.SelectFromTable("OrganisationalStructure");
if (UserName.Length < 5)
{
selectQuery.AddWhere("[Login]", Comparison.Equals, @"GMFS\Hughesm");
}
else
{
selectQuery.AddWhere("[Login]", Comparison.Equals, UserName);
}
Selectstatement = selectQuery.BuildQuery();
//retrieve select statement dataset
ConnectToDataBase.connection_string = strSQLconnection;
ConnectToDataBase.Sql = Selectstatement;
TrackerDataSet = ConnectToDataBase.GetConnection;
//get data from dataset table
var name = TrackerDataSet.Tables[0].Rows[0]["Name"].ToString();
var department = TrackerDataSet.Tables[0].Rows[0]["Department"].ToString();
var rank = TrackerDataSet.Tables[0].Rows[0]["Rank"].ToString();
//create insert statmement
insertQuery.Table = "PerfPortal_Tracker";
insertQuery.SetField("LoginName", UserName);
insertQuery.SetField("FullName", name);
insertQuery.SetField("Department", department);
insertQuery.SetField("JobTitle", rank);
insertQuery.SetField("[DateTime]", DateTime.Now.ToString(CultureInfo.InvariantCulture));
insertQuery.SetField("AreaAccessed", AreaChosen);
INSERTstatement = insertQuery.BuildQuery();
//send sql statement to server
InsertQuery.Query(INSERTstatement, SqlConnect);
}
}
catch (Exception exec)
{
Console.WriteLine(exec);
}
}
