public void PersistSessionObject(IDbConnectionAbstraction connectionAbstraction)
{
Dictionary <string, object> .Enumerator parameters = _parameters.GetEnumerator();
using (SqlCommand command = new SqlCommand())
{
IQueryBuilder queryBuilder = null;
if (IsNew)
{
queryBuilder = new InsertQueryBuilder();
}
else if (IsDirty)
{
queryBuilder = new UpdateQueryBuilder();
}
else
{
return;
}
while (parameters.MoveNext())
{
queryBuilder.AddParameter(parameters.Current.Key, parameters.Current.Value);
}
command.Parameters.AddRange(queryBuilder.GenerateSqlParameters());
command.Connection = connectionAbstraction.Connection;
command.CommandText = queryBuilder.GenerateSqlQuery();
connectionAbstraction.Open();
TransactionId = long.Parse(command.ExecuteScalar().ToString());
connectionAbstraction.Close();
}
}
public static void InsertMultiples <T>(string connectionString, IList <T> entities, bool getLastId = true) where T : new()
{
var query = new StringBuilder();
entities.ToList().ForEach(entity =>
{
var insertBuilder = new InsertQueryBuilder()
.WithFields(EntityFactory.GetFieldsWithValues(entity, true));
query.Append(insertBuilder.Build <T>());
query.Append(";");
});
if (getLastId && KeyAttribute.HasEntityAutoIncremenetalKey <T>())
{
query.Append("SELECT LAST_INSERT_ID()");
using var exe = new DataBaseExecuteReader(connectionString, query.ToString());
if (exe.DataReader.Read())
{
var keyProperty = KeyAttribute.GetAutoIncrementKeyProperty <T>();
var lastId = (int)exe.DataReader.GetULong(0) - entities.Count();
entities.ToList().ForEach(entity =>
{
keyProperty.SetValue(entity, Convert.ChangeType(++lastId, keyProperty.PropertyType));
});
}
}
else
{
DataBaseDataReader.ExecuteNonQuery(connectionString, query.ToString());
}
}
static void Main(string[] args)
{
var services = new ServiceCollection();
services.AddSingleton <ITest1, TestClass1>();
services.AddSingleton <ITest, TestClass>();
var serviceProvider = services.BuildServiceProvider();
ActivatorUtilities.CreateInstance <TestClass>(serviceProvider);
var select = new SelectQueryBuilder()
.Select(Tables.U.ID, Tables.U.Username, Tables.P.Email)
.Where(Tables.U.Age > 30 | Tables.P.Email.Like("*****@*****.**"))
.Where(Tables.U.ID != null)
.From(Tables.Users.As("U").InnerJoin(Tables.UserProfile.As("P")).On(Tables.U.ID == Tables.P.ID))
.Build();
var insert = new InsertQueryBuilder()
.InsertInto(Tables.Users, Names.ID, Names.Username, Names.Email)
.WithValues(ValuesClause.Values((1, "Ivony", "*****@*****.**")))
.Build();
var parser = new SqlQueryParser();
ShowQuery(parser.ParseSelectQuery(select));
ShowQuery(parser.ParseInsertQuery(insert));
Console.ReadKey();
}
private bool UpdateValueInForeignTable(Guid tenantId, string tableName, string entityPrefix, string primaryKey, List <ColumnAndField> matchingColumns, string primaryValue)
{
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());
}
}
}
if (!insertColumns.Any())
{
return(true);
}
UpdateQueryBuilder query = new UpdateQueryBuilder();
query.AddTable(tableName, insertColumns);
query.AddWhere(primaryKey, Comparison.Equals, primaryValue.ToString(), 1);
var updateQuery = query.BuildQuery();
IQueryAdmin admin = new QueryAdmin();
var status = admin.UpdateResult(tenantId, "inverse", updateQuery);
return(status);
}
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 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 BuildInsert(IDictionary <string, object> parameters, InsertQueryBuilder query)
{
if (query == null)
{
return(null);
}
if (query.Tables.Count == 0)
{
return(null);
}
if (query.Sets.Count == 0)
{
return(null);
}
var builder = new StringBuilder();
var result = BuildInsertSet(parameters, query.Sets);
builder.Append("INSERT INTO ")
.Append(Environment.NewLine)
.Append(BuildTables(parameters, query.Tables))
.Append($"({string.Join(",", result.Select(n => HBDCommon.GetSqlName(n.Key)))})")
.Append(Environment.NewLine)
.Append("VALUES (")
.Append($"{string.Join(",", result.Select(v => v.Value))}")
.Append(")");
return(builder.ToString());
}
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);
}
/// <summary>
/// Imports a Player XML Sheet from the specified path
/// </summary>
/// <param name="XmlPath">The full path to the XML file</param>
public static void ImportPlayerXml(string XmlPath)
{
// Connect to database first!
using (StatsDatabase Driver = new StatsDatabase())
{
// Load elements
XDocument Doc = XDocument.Load(new FileStream(XmlPath, FileMode.Open, FileAccess.Read));
XElement Info = Doc.Root.Element("Info");
XElement TableData = Doc.Root.Element("TableData");
// Make sure player doesnt already exist
int Pid = Int32.Parse(Info.Element("Pid").Value);
if (Driver.PlayerExists(Pid))
{
throw new Exception(String.Format("Player with PID {0} already exists!", Pid));
}
// Begin Transaction
using (DbTransaction Transaction = Driver.BeginTransaction())
{
try
{
// Loop through tables
foreach (XElement Table in TableData.Elements())
{
// Loop through Rows
foreach (XElement Row in Table.Elements())
{
InsertQueryBuilder QueryBuilder = new InsertQueryBuilder(Table.Name.LocalName, Driver);
foreach (XElement Col in Row.Elements())
{
if (Col.Name.LocalName == "name")
{
QueryBuilder.SetField(Col.Name.LocalName, Col.Value.UnescapeXML());
}
else
{
QueryBuilder.SetField(Col.Name.LocalName, Col.Value);
}
}
QueryBuilder.Execute();
}
}
// Commit Transaction
Transaction.Commit();
}
catch
{
Transaction.Rollback();
throw;
}
}
}
}
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);
}
/// <summary>
/// Inserts a new Entity into the database, and sets any Foreign key properties. If the
/// entity table has a single integer primary key, the primary key value will be updated
/// with the <see cref="SQLiteConnection.LastInsertRowId"/>.
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to add to the dataset</param>
public void Add(TEntity obj)
{
// For fetching the RowID
AttributeInfo rowid = EntityTable.RowIdColumn;
// Generate the SQL
if (InsertQuery == null)
{
using (var query = new InsertQueryBuilder(EntityTable.TableName, Context))
{
foreach (var attribute in EntityTable.Columns)
{
// Grab value
PropertyInfo property = attribute.Value.Property;
bool isKey = attribute.Value.PrimaryKey;
// Check for integer primary keys
if (isKey && EntityTable.HasRowIdAlias && EntityTable.RowIdColumn == attribute.Value)
{
continue;
}
// Add attribute to the field list
query.Set(attribute.Key, new SqlLiteral($"@{attribute.Key}"));
}
InsertQuery = new PreparedNonQuery(query.BuildCommand());
}
}
// Execute the SQL Command
lock (InsertQuery)
{
InsertQuery.SetParameters(obj, EntityTable);
int result = InsertQuery.Execute();
// If the insert was successful, lets build our Entity relationships
if (result > 0)
{
// If we have a Primary key that is determined database side,
// than we can update the current object's key value here
if (EntityTable.HasRowIdAlias)
{
long rowId = Context.Connection.LastInsertRowId;
rowid.Property.SetValue(obj, Convert.ChangeType(rowId, rowid.Property.PropertyType));
}
// Build relationships after a fresh insert
EntityTable.CreateRelationships(obj, Context);
}
}
}
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());
}
private string GenerateInsertQuery(IList <Item> items)
{
var builder = new InsertQueryBuilder();
builder.SetTable("items");
builder.SetColumns(new List <string>
{
"name", "description", "price", "quantity", "picture"
});
builder.AddItem(items);
return(builder.Query);
}
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);
}
/// <summary>
/// Adds a server's posted snapshot into the Snapshot Processing Queue, which
/// will process the snapshot as soon as possible. This method is Non-Blocking.
/// </summary>
/// <remarks>
/// Any errors that occur during the actual import of the data will be
/// logged inside the StatsDebug log
/// </remarks>
/// <param name="Data">The snapshot data provided by the server.</param>
/// <param name="ServerAddress">The IP address of the server.</param>
/// <exception cref="UnauthorizedAccessException">
/// Thrown if the Server IP is not authorized to post game data to this server
/// </exception>
/// <exception cref="InvalidDataException">
/// Thrown if the provided Snapshot data is not valid, and cannot be processed
/// </exception>
public static void QueueServerSnapshot(string Data, IPAddress ServerAddress)
{
// Make sure the server is authorized
if (!IsAuthorizedGameServer(ServerAddress))
{
throw new UnauthorizedAccessException("Un-Authorised Gameserver (Ip: " + ServerAddress + ")");
}
// Create the Snapshot Object
Snapshot Snap = new Snapshot(Data, ServerAddress);
// Update this server in the Database
using (StatsDatabase Database = new StatsDatabase())
{
// Try and grab the ID of this server
int id = Database.ExecuteScalar <int>(
"SELECT COALESCE(id, -1), COUNT(id) FROM servers WHERE ip=@P0 AND port=@P1",
ServerAddress, Snap.ServerPort
);
// New server?
if (id < 0)
{
InsertQueryBuilder builder = new InsertQueryBuilder(Database);
builder.SetTable("servers");
builder.SetField("ip", ServerAddress);
builder.SetField("port", Snap.ServerPort);
builder.SetField("prefix", Snap.ServerPrefix);
builder.SetField("name", Snap.ServerName);
builder.SetField("queryport", Snap.QueryPort);
builder.SetField("lastupdate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
builder.Execute();
}
else // existing
{
UpdateQueryBuilder builder = new UpdateQueryBuilder(Database);
builder.SetTable("servers");
builder.SetField("prefix", Snap.ServerPrefix);
builder.SetField("name", Snap.ServerName);
builder.SetField("queryport", Snap.QueryPort);
builder.SetField("lastupdate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
builder.AddWhere("id", Comparison.Equals, id);
builder.Execute();
}
}
// Add snapshot to Queue
SnapshotQueue.Enqueue(Snap);
}
public void GenerateSimpleQueryTest()
{
// Arrange
const string queryExpected = "INSERT INTO `books` (`Title`, `Price`) VALUES ('Essential C#', 20.99)";
var fields = new Dictionary <string, object>
{
{ GetColumnNameWithQuotes <Book>(nameof(Book.Title)), "Essential C#" },
{ GetColumnNameWithQuotes <Book>(nameof(Book.Price)), 20.99d }
};
var insertQueryBuilder = new InsertQueryBuilder()
.WithFields(fields);
// Act
var query = insertQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
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);
}
public void PersistSessionObject(IDbConnectionAbstraction connectionAbstraction)
{
Dictionary<string, object>.Enumerator parameters = _parameters.GetEnumerator();
using (SqlCommand command = new SqlCommand())
{
IQueryBuilder queryBuilder = null;
if (IsNew)
{
queryBuilder = new InsertQueryBuilder();
}
else if (IsDirty)
{
queryBuilder = new UpdateQueryBuilder();
}
else
{
return;
}
while (parameters.MoveNext())
{
queryBuilder.AddParameter(parameters.Current.Key, parameters.Current.Value);
}
command.Parameters.AddRange(queryBuilder.GenerateSqlParameters());
command.Connection = connectionAbstraction.Connection;
command.CommandText = queryBuilder.GenerateSqlQuery();
connectionAbstraction.Open();
TransactionId = long.Parse(command.ExecuteScalar().ToString());
connectionAbstraction.Close();
}
}
/// <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);
}
}
public static InsertQueryBuilder Into(this InsertQueryBuilder @this, string tableName)
{
@this.Tables.Clear();
@this.Tables.Add(new Table(tableName));
return(@this);
}
//public static InsertQueryBuilder Values(this InsertQueryBuilder @this, Func<FieldBuilder, FieldSet> field)
//{
// var f = field.Invoke(FieldBuilder.Current);
// return @this.Values(f);
//}
//public static InsertQueryBuilder Values(this InsertQueryBuilder @this, Func<FieldBuilder, FieldSet[]> fields)
//{
// var fs = fields.Invoke(FieldBuilder.Current);
// return @this.Values(fs);
//}
public static FieldSet <InsertQueryBuilder> Values(this InsertQueryBuilder @this, string fieldName)
=> new FieldSet <InsertQueryBuilder>(@this, fieldName);
/// <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);
}
}
/// <summary>
/// Imports a players stats from the official gamespy ASP.
/// This method is to be used in a background worker
/// </summary>
public void bWorker_ImportEaStats(object sender, DoWorkEventArgs e)
{
// Setup variables
BackgroundWorker Worker = (BackgroundWorker)sender;
int Pid = Int32.Parse(e.Argument.ToString());
// Do work
using (StatsDatabase Driver = new StatsDatabase())
{
// Make sure the player doesnt exist!
if (Driver.PlayerExists(Pid))
{
throw new Exception(String.Format("Player with PID {0} already exists!", Pid));
}
// Build variables
Uri GsUrl;
WebRequest Request;
HttpWebResponse Response;
List <string[]> PlayerLines;
List <string[]> AwardLines;
List <string[]> MapLines;
InsertQueryBuilder Query;
// Create Request
string Url = String.Format(
"getplayerinfo.aspx?pid={0}&info=per*,cmb*,twsc,cpcp,cacp,dfcp,kila,heal,rviv,rsup,rpar,tgte,dkas,dsab,cdsc,rank,cmsc,kick,kill,deth,suic,"
+ "ospm,klpm,klpr,dtpr,bksk,wdsk,bbrs,tcdr,ban,dtpm,lbtl,osaa,vrk,tsql,tsqm,tlwf,mvks,vmks,mvn*,vmr*,fkit,fmap,fveh,fwea,wtm-,wkl-,wdt-,"
+ "wac-,wkd-,vtm-,vkl-,vdt-,vkd-,vkr-,atm-,awn-,alo-,abr-,ktm-,kkl-,kdt-,kkd-",
Pid);
Worker.ReportProgress(1, "Requesting Player Stats");
GsUrl = new Uri("http://bf2web.gamespy.com/ASP/" + Url);
Request = WebRequest.Create(GsUrl);
// Get response
Response = (HttpWebResponse)Request.GetResponse();
if (Response.StatusCode != HttpStatusCode.OK)
{
throw new Exception("Unable to connect to the Gamespy ASP Webservice!");
}
// Read response data
Worker.ReportProgress(2, "Parsing Stats Response");
PlayerLines = new List <string[]>();
using (StreamReader Reader = new StreamReader(Response.GetResponseStream()))
while (!Reader.EndOfStream)
{
PlayerLines.Add(Reader.ReadLine().Split('\t'));
}
// Does the player exist?
if (PlayerLines[0][0] != "O")
{
throw new Exception("Player does not exist on the gamespy servers!");
}
// Fetch player mapinfo
Worker.ReportProgress(3, "Requesting Player Map Data");
GsUrl = new Uri(String.Format("http://bf2web.gamespy.com/ASP/getplayerinfo.aspx?pid={0}&info=mtm-,mwn-,mls-", Pid));
Request = WebRequest.Create(GsUrl);
// Get response
Response = (HttpWebResponse)Request.GetResponse();
if (Response.StatusCode != HttpStatusCode.OK)
{
throw new Exception("Unable to connect to the Gamespy ASP Webservice!");
}
// Read response data
Worker.ReportProgress(4, "Parsing Map Data Response");
MapLines = new List <string[]>();
using (StreamReader Reader = new StreamReader(Response.GetResponseStream()))
while (!Reader.EndOfStream)
{
MapLines.Add(Reader.ReadLine().Split('\t'));
}
// Fetch player awards
Worker.ReportProgress(5, "Requesting Player Awards");
GsUrl = new Uri(String.Format("http://bf2web.gamespy.com/ASP/getawardsinfo.aspx?pid={0}", Pid));
Request = WebRequest.Create(GsUrl);
// Get response
Response = (HttpWebResponse)Request.GetResponse();
if (Response.StatusCode != HttpStatusCode.OK)
{
throw new Exception("Unable to connect to the Gamespy ASP Webservice!");
}
// Read response data
Worker.ReportProgress(6, "Parsing Player Awards Response");
AwardLines = new List <string[]>();
using (StreamReader Reader = new StreamReader(Response.GetResponseStream()))
while (!Reader.EndOfStream)
{
AwardLines.Add(Reader.ReadLine().Split('\t'));
}
// === Process Player Info === //
// Parse Player Data
Dictionary <string, string> PlayerInfo = StatsParser.ParseHeaderData(PlayerLines[3], PlayerLines[4]);
int Rounds = Int32.Parse(PlayerInfo["mode0"]) + Int32.Parse(PlayerInfo["mode1"]) + Int32.Parse(PlayerInfo["mode2"]);
// Begin database transaction
DbTransaction Transaction = Driver.BeginTransaction();
// Wrap all DB inserts into a try block so we can rollback on error
try
{
// Insert Player Data
Worker.ReportProgress(7, "Inserting Player Data Into Table: player");
Query = new InsertQueryBuilder("player", Driver);
Query.SetField("id", Pid);
Query.SetField("name", " " + PlayerInfo["nick"].Trim()); // Online accounts always start with space in bf2stats
Query.SetField("country", "xx");
Query.SetField("time", PlayerInfo["time"]);
Query.SetField("rounds", Rounds);
Query.SetField("ip", "127.0.0.1");
Query.SetField("score", PlayerInfo["scor"]);
Query.SetField("cmdscore", PlayerInfo["cdsc"]);
Query.SetField("skillscore", PlayerInfo["cmsc"]);
Query.SetField("teamscore", PlayerInfo["twsc"]);
Query.SetField("kills", PlayerInfo["kill"]);
Query.SetField("deaths", PlayerInfo["deth"]);
Query.SetField("captures", PlayerInfo["cpcp"]);
Query.SetField("captureassists", PlayerInfo["cacp"]);
Query.SetField("defends", PlayerInfo["dfcp"]);
Query.SetField("damageassists", PlayerInfo["kila"]);
Query.SetField("heals", PlayerInfo["heal"]);
Query.SetField("revives", PlayerInfo["rviv"]);
Query.SetField("ammos", PlayerInfo["rsup"]);
Query.SetField("repairs", PlayerInfo["rpar"]);
Query.SetField("driverspecials", PlayerInfo["dsab"]);
Query.SetField("suicides", PlayerInfo["suic"]);
Query.SetField("killstreak", PlayerInfo["bksk"]);
Query.SetField("deathstreak", PlayerInfo["wdsk"]);
Query.SetField("rank", PlayerInfo["rank"]);
Query.SetField("banned", PlayerInfo["ban"]);
Query.SetField("kicked", PlayerInfo["kick"]);
Query.SetField("cmdtime", PlayerInfo["tcdr"]);
Query.SetField("sqltime", PlayerInfo["tsql"]);
Query.SetField("sqmtime", PlayerInfo["tsqm"]);
Query.SetField("lwtime", PlayerInfo["tlwf"]);
Query.SetField("wins", PlayerInfo["wins"]);
Query.SetField("losses", PlayerInfo["loss"]);
Query.SetField("joined", PlayerInfo["jond"]);
Query.SetField("rndscore", PlayerInfo["bbrs"]);
Query.SetField("lastonline", PlayerInfo["lbtl"]);
Query.SetField("mode0", PlayerInfo["mode0"]);
Query.SetField("mode1", PlayerInfo["mode1"]);
Query.SetField("mode2", PlayerInfo["mode2"]);
Query.Execute();
// Insert Army Data
Worker.ReportProgress(8, "Inserting Player Data Into Table: army");
Query = new InsertQueryBuilder("army", Driver);
Query.SetField("id", Pid);
for (int i = 0; i < 10; i++)
{
Query.SetField("time" + i, PlayerInfo["atm-" + i]);
Query.SetField("win" + i, PlayerInfo["awn-" + i]);
Query.SetField("loss" + i, PlayerInfo["alo-" + i]);
Query.SetField("best" + i, PlayerInfo["abr-" + i]);
}
Query.Execute();
// Insert Kit Data
Worker.ReportProgress(9, "Inserting Player Data Into Table: kits");
Query = new InsertQueryBuilder("kits", Driver);
Query.SetField("id", Pid);
for (int i = 0; i < 7; i++)
{
Query.SetField("time" + i, PlayerInfo["ktm-" + i]);
Query.SetField("kills" + i, PlayerInfo["kkl-" + i]);
Query.SetField("deaths" + i, PlayerInfo["kdt-" + i]);
}
Query.Execute();
// Insert Vehicle Data
Worker.ReportProgress(10, "Inserting Player Data Into Table: vehicles");
Query = new InsertQueryBuilder("vehicles", Driver);
Query.SetField("id", Pid);
Query.SetField("timepara", 0);
for (int i = 0; i < 7; i++)
{
Query.SetField("time" + i, PlayerInfo["vtm-" + i]);
Query.SetField("kills" + i, PlayerInfo["vkl-" + i]);
Query.SetField("deaths" + i, PlayerInfo["vdt-" + i]);
Query.SetField("rk" + i, PlayerInfo["vkr-" + i]);
}
Query.Execute();
// Insert Weapon Data
Worker.ReportProgress(11, "Inserting Player Data Into Table: weapons");
Query = new InsertQueryBuilder("weapons", Driver);
Query.SetField("id", Pid);
for (int i = 0; i < 9; i++)
{
Query.SetField("time" + i, PlayerInfo["wtm-" + i]);
Query.SetField("kills" + i, PlayerInfo["wkl-" + i]);
Query.SetField("deaths" + i, PlayerInfo["wdt-" + i]);
}
// Knife
Query.SetField("knifetime", PlayerInfo["wtm-9"]);
Query.SetField("knifekills", PlayerInfo["wkl-9"]);
Query.SetField("knifedeaths", PlayerInfo["wdt-9"]);
// Shockpad
Query.SetField("shockpadtime", PlayerInfo["wtm-10"]);
Query.SetField("shockpadkills", PlayerInfo["wkl-10"]);
Query.SetField("shockpaddeaths", PlayerInfo["wdt-10"]);
// Claymore
Query.SetField("claymoretime", PlayerInfo["wtm-11"]);
Query.SetField("claymorekills", PlayerInfo["wkl-11"]);
Query.SetField("claymoredeaths", PlayerInfo["wdt-11"]);
// Handgrenade
Query.SetField("handgrenadetime", PlayerInfo["wtm-12"]);
Query.SetField("handgrenadekills", PlayerInfo["wkl-12"]);
Query.SetField("handgrenadedeaths", PlayerInfo["wdt-12"]);
// SF Weapn Data
Query.SetField("tacticaldeployed", PlayerInfo["de-6"]);
Query.SetField("grapplinghookdeployed", PlayerInfo["de-7"]);
Query.SetField("ziplinedeployed", PlayerInfo["de-8"]);
Query.Execute();
// === Process Awards Data === //
Worker.ReportProgress(12, "Inserting Player Awards");
List <Dictionary <string, string> > Awards = StatsParser.ParseAwards(AwardLines);
foreach (Dictionary <string, string> Award in Awards)
{
Query = new InsertQueryBuilder("awards", Driver);
Query.SetField("id", Pid);
Query.SetField("awd", Award["id"]);
Query.SetField("level", Award["level"]);
Query.SetField("earned", Award["when"]);
Query.SetField("first", Award["first"]);
Query.Execute();
}
// === Process Map Data === //
Worker.ReportProgress(13, "Inserting Player Map Data");
PlayerInfo = StatsParser.ParseHeaderData(MapLines[3], MapLines[4]);
int[] Maps = new int[] {
0, 1, 2, 3, 4, 5, 6, 100, 101, 102, 103, 105,
601, 300, 301, 302, 303, 304, 305, 306, 307,
10, 11, 110, 200, 201, 202, 12
};
foreach (int MapId in Maps)
{
if (PlayerInfo.ContainsKey("mtm-" + MapId))
{
Query = new InsertQueryBuilder("maps", Driver);
Query.SetField("id", Pid);
Query.SetField("mapid", MapId);
Query.SetField("time", PlayerInfo["mtm-" + MapId]);
Query.SetField("win", PlayerInfo["mwn-" + MapId]);
Query.SetField("loss", PlayerInfo["mls-" + MapId]);
Query.SetField("best", 0);
Query.SetField("worst", 0);
Query.Execute();
}
}
// Commit transaction
Transaction.Commit();
}
catch (Exception)
{
Transaction.Rollback();
throw;
}
finally
{
// Dispose dispose the transaction
Transaction.Dispose();
}
}
}
public void Run()
{
var _distinctdata = _alldata.SelectMany(e => e.BaseObject.Select(w => w.Key)).Distinct();
var ValidKeys = _tableInfo.ColumnInfos.Where(q => q.IsKey).Select(e => e.ColumnName).Intersect(_distinctdata).ToList();
var AllValidfields = _tableInfo.ColumnInfos.Select(e => e.ColumnName).Intersect(_distinctdata);
if (ValidKeys.Count != _tableInfo.ColumnInfos.Where(e => e.IsKey).Select(e => e.ColumnName).Distinct().Count())
{
Console.WriteLine($"Key missed -Type {_tableName} ");
EmailSender sender = new EmailSender();
sender.SendEmailAsync("*****@*****.**", "Error--key", $"Key missed -Type {_tableName} ");
}
querybulder = new InsertQueryBuilder(AllValidfields, _tableName);
List <SqlParameter> _paramsSql = new List <SqlParameter>();
int _index = 0;
foreach (var item in _alldata)
{
List <string> stringparamForQuery = new List <string>();
foreach (var field in AllValidfields)
{
string param = $"@p{_index++}";
stringparamForQuery.Add(param);
var dbpar = new SqlParameter(param, shcema[_tableName][field]);
if (item.BaseObject.ContainsKey(field))
{
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, field, item.BaseObject[field]);
}
else
{
dbpar.Value = DBNull.Value;
}
_paramsSql.Add(dbpar);
}
querybulder.AddQueryParametrs(stringparamForQuery.ToList());
if (_paramsSql.Count > InsertCommandParametersCount)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
querybulder.Reset();
}
}
if (_paramsSql.Count > 0)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
}
}
