Exemple #1
0
        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();
            }
        }
Exemple #2
0
        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());
            }
        }
Exemple #3
0
        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();
        }
Exemple #4
0
        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);
            }
        }
Exemple #7
0
        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);
        }
Exemple #9
0
        /// <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;
                    }
                }
            }
        }
Exemple #10
0
        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);
        }
Exemple #11
0
        /// <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);
                }
            }
        }
Exemple #12
0
        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);
        }
Exemple #14
0
 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);
 }
Exemple #15
0
        /// <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);
        }
Exemple #16
0
        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);
        }
Exemple #17
0
        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);
        }
Exemple #18
0
        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);
        }
    }
Exemple #20
0
 public static InsertQueryBuilder Into(this InsertQueryBuilder @this, string tableName)
 {
     @this.Tables.Clear();
     @this.Tables.Add(new Table(tableName));
     return(@this);
 }
Exemple #21
0
        //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;
            }
        }