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