Example #1
0
        private List<string> GetNodeMatches(string lookupText)
        {
            List<string> foundNodes = new List<string>();

            string sConn = ConfigurationManager.ConnectionStrings["EEREDB"].ToString();
            sConn += System.Text.Encoding.UTF8.GetString(System.Convert.FromBase64String(ConfigurationManager.AppSettings["word"].ToString()));

            using (SqlConnection conn = new SqlConnection(sConn))
            {
                conn.Open();
                using (SqlDataReader reader = new SqlCommand("SELECT DrupalNode FROM dbo.NodeLookup WHERE WebURL LIKE '%" + lookupText + "%' ", conn).ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            foundNodes.Add(reader.GetString(0));
                        }
                    }
                }
                conn.Close();
            }

            return foundNodes;
        }
 protected override void PopulateVersionAndTypes()
 {
     base._sqlVersion = "10";
     List<KeyValuePair<int, byte>> list = new List<KeyValuePair<int, byte>>();
     using (SqlDataReader reader = new SqlCommand("select system_type_id, user_type_id, name from sys.types where system_type_id = user_type_id", base._cx).ExecuteReader())
     {
         while (reader.Read())
         {
             DbTypeInfo dbTypeInfo = SqlSchemaReader.GetDbTypeInfo(reader.GetString(2));
             if (!((dbTypeInfo == null) || base._sqlTypes.ContainsKey(reader.GetInt32(1))))
             {
                 base._sqlTypes.Add(reader.GetInt32(1), dbTypeInfo);
             }
             else
             {
                 list.Add(new KeyValuePair<int, byte>(reader.GetInt32(1), reader.GetByte(0)));
             }
         }
     }
     foreach (KeyValuePair<int, byte> pair in list)
     {
         if (base._sqlTypes.ContainsKey(pair.Value))
         {
             base._sqlTypes[pair.Key] = base._sqlTypes[pair.Value];
         }
     }
 }
 private static string[] GetEnabledTables(string connectionString)
 {
     SqlDataReader reader = null;
     SqlConnection connection = null;
     ArrayList list = new ArrayList();
     try
     {
         connection = new SqlConnection(connectionString);
         connection.Open();
         reader = new SqlCommand("dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure", connection) { CommandType = CommandType.StoredProcedure }.ExecuteReader();
         while (reader.Read())
         {
             list.Add(reader.GetString(0));
         }
     }
     catch (Exception exception)
     {
         SqlException exception2 = exception as SqlException;
         if ((exception2 != null) && (exception2.Number == 0xafc))
         {
             throw new DatabaseNotEnabledForNotificationException(System.Web.SR.GetString("Database_not_enabled_for_notification", new object[] { connection.Database }));
         }
         throw new HttpException(System.Web.SR.GetString("Cant_get_enabled_tables_sql_cache_dep"), exception);
     }
     finally
     {
         try
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (connection != null)
             {
                 connection.Close();
             }
         }
         catch
         {
         }
     }
     return (string[]) list.ToArray(Type.GetType("System.String"));
 }
Example #4
0
        public long GetUser(string email, string hash)
        {
            long _result = -1;

            if (Connected)
            {
                using (SqlDataReader _reader = new SqlCommand(string.Format("Select * from MarketUser Where Email='{0}'", email), _sql).ExecuteReader())
                {
                    if (_reader.HasRows)
                    {
                        while (_reader.Read())
                        {
                            if (_reader.GetString(_reader.GetOrdinal("Password")).Equals(hash))
                            {
                                _result = _reader.GetInt64(_reader.GetOrdinal("UserId"));
                            }
                        }
                    }
                }
            }

            return _result;
        }
        public void TearDownDatabase()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["TeleConsult"].ConnectionString;
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var reader = new SqlCommand("select [Name] from sys.tables WHERE TYPE='U' AND [Name] != '__MigrationHistory'", connection).ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        var tableName = reader.GetString(0);
                        using (var connection2 = new SqlConnection(connectionString))
                        {
                            connection2.Open();
                            new SqlCommand(string.Format("DELETE FROM [{0}]", tableName), connection2).ExecuteNonQuery();
                            connection2.Close();
                        }

                    }
                }
                connection.Close();
            }
        }
 /// <summary>
 /// Get tag ID data from the SQL database table.
 /// </summary>
 public void GetData(ListView listview)
 {
     using (sqlConn = new SqlConnection(connectionString))
     {
         sqlConn.Open();
         using (SqlDataReader reader = new SqlCommand("SELECT * FROM TagInfo", sqlConn).ExecuteReader())
         {
             try
             {
                 while (reader.Read())
                 {
                     string[] rowitem = new string[2];
                     rowitem[0] = reader.GetString(reader.GetOrdinal("TagId"));
                     rowitem[1] = reader.GetDateTime(reader.GetOrdinal("TagTime")).ToString();
                     ListViewItem listitem = new ListViewItem(rowitem);
                     //table.Rows.Add(epc, time);
                     listview.Items.Add(listitem);
                 }
                 reader.Close();
             }
             catch (System.Data.SqlClient.SqlException ee)
             {
                 MessageBox.Show(ee.Message.ToString());
             }
         }
         sqlConn.Close();
     }
 }
Example #7
0
        public bool IsToken(long id, string AccessToken)
        {
            bool _result = false;

            if (Connected)
            {
                using (SqlDataReader _reader = new SqlCommand(string.Format("Select * From MarketToken Where UserId='{0}'", id), _sql).ExecuteReader())
                {
                    if (_reader.HasRows)
                    {
                        while (_reader.Read())
                        {
                            if (_reader.GetString(_reader.GetOrdinal("AccessToken")).Equals(AccessToken))
                            {
                                _result = true;

                                break;
                            }
                        }
                    }

                }
            }

            return _result;
        }
Example #8
0
        public void readFromDataBase()
        {
            items.Clear();
            SqlDataReader reader = new SqlCommand(string.Format("SELECT * FROM Items ORDER BY Id;SELECT * FROM Jobs ORDER BY Item;SELECT * FROM Jobs ORDER BY Item;"), cn).ExecuteReader();
            //читаем лист items
            while (reader.Read())
            {
                item newItem = new item();
                newItem.id = reader.GetInt32(0);
                newItem.firstName = reader.GetString(1);
                newItem.lastName = reader.GetString(2);
                items.Add(newItem);
            }

            int i;
            int lastid;

            //читаем лист jobs
            reader.NextResult();
            lastid = i = -1;
            while (reader.Read())
            {
                int k = reader.GetInt32(4);
                if (lastid != k)
                {
                    do ++i;
                    while (k != items[i].id);
                    lastid = reader.GetInt32(4);
                }
                items[i].jobs.Add(new job(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
            }

            //читаем лист positions
            reader.NextResult();
            lastid = i = -1;
            while (reader.Read())
            {
                if (lastid != reader.GetInt32(4))
                {
                    do i++;
                    while (reader.GetInt32(4) != items[i].id);
                    lastid = reader.GetInt32(4);
                }
                items[i].positions.Add(new position(reader.GetInt64(0),reader.GetInt64(1),reader.GetInt32(2),reader.GetDateTime(3)));
            }
            reader.Close();
        }
Example #9
0
        public static void Main(string[] args)
        {

            //Console.WriteLine(response);

            try
            {
                while (true)
                {
                    using (var sql = new SqlConnection(Secrets.ConnectionString))
                    {
                        sql.Open();
                        // dbo.StravaKeys contains registered API keys; let's collect all of them

                        #region strava

                        var api_base = "https://www.strava.com/api/v3/";
                        var keysQuery = "SELECT * from dbo.StravaKeys;";
                        Log.Debug(keysQuery);
                        var results = new List<Tuple<int, string>>();
                        using (var keysResults = new SqlCommand(keysQuery, sql).ExecuteReader())
                        {
                            while (keysResults.Read())
                            {
                                results.Add(new Tuple<int, string>(keysResults.GetInt32(0), keysResults.GetString(1)));
                            }
                        }

                        Log.Info("Got {0} keys to check", results.Count);

                        // get updated results for each StravaKey and stick them in the DB.
                        foreach (var res in results)
                        {
                            var accessToken = $"&access_token={res.Item2}";
                            var epoch = 1451624400; // jan 1 2016
                            long secondsSinceEpoch;

                            // select the last time we updated this user; if we've never updated them, get events since January 1, 2016
                            var lastRunQuery =
                                $"SELECT activity_time FROM dbo.StravaActivities WHERE strava_id={res.Item1} ORDER BY activity_time DESC;";
                            Log.Debug(lastRunQuery);
                            using (var lastRun = new SqlCommand(lastRunQuery, sql).ExecuteReader())
                            {
                                if (lastRun.HasRows)
                                {
                                    lastRun.Read();
                                    secondsSinceEpoch =
                                        (long)
                                            Math.Max(epoch,
                                                lastRun.GetDateTime(0).Subtract(new DateTime(1970, 1, 1)).TotalSeconds);
                                }
                                else
                                {
                                    secondsSinceEpoch = epoch;
                                }
                            }

                            // transform our DB results into <DateTime, double, string> tuples
                            var apiUrl =
                                $"{api_base}athlete/activities/?per_page=200&after={secondsSinceEpoch}{accessToken}";
                            Log.Info("Hitting API at {0}", apiUrl);
                            var runs = GetArrayFromApi(apiUrl)
                                .Select(e =>
                                    new Tuple<DateTime, double, string>(
                                        DateTime.Parse((string)e["start_date"]),
                                        (double)e["distance"] / 1609, // translate meters to miles
                                        (string)e["type"]))
                                .ToList();

                            // if we actually got any tuples, transform them into an INSERT query
                            Log.Info("Got {0} results from Strava API", runs.Count);
                            if (runs.Any())
                            {
                                // aggregate the tuples into a StringBuilder
                                var insertionQuery = runs.Aggregate(
                                    new StringBuilder(
                                        "INSERT INTO dbo.StravaActivities (strava_id, activity_time, activity_distance, activity_type) VALUES "),
                                    (acc, e) => acc.Append($"({res.Item1}, '{e.Item1}', {e.Item2}, '{e.Item3}'), "));
                                // trim the last ", " from the aggregated tuples and append a semicolon
                                insertionQuery.Remove(insertionQuery.Length - 2, 2).Append(";");
                                // insert! 
                                Log.Debug(insertionQuery);
                                using (var inserter = new SqlCommand(insertionQuery.ToString(), sql))
                                    inserter.ExecuteNonQuery();
                            }
                        }

                        #endregion

                        #region withings

                        Log.Info("Hitting Withings API");
                        var lastMeasure = new DateTime(2016, 1, 1);
                        var unixTimeStart = new DateTime(1970, 1, 1);

                        var lastEntryQuery = "SELECT measure_date FROM dbo.bodyFat ORDER BY measure_date DESC;";
                        using (var lastEntry = new SqlCommand(lastEntryQuery, sql).ExecuteReader())
                        {
                            if (lastEntry.Read())
                            {
                                lastMeasure = lastEntry.GetDateTime(0);
                            }
                        }
                        var withingsResponse = GetObjectFromApi(Secrets.WithingsApi);
                        var bodyFatMeasures = ((JArray)withingsResponse["body"]["measuregrps"])
                            .Where(e => (long)e["date"] > (long)lastMeasure.Subtract(unixTimeStart).TotalSeconds && ((JArray)e["measures"]).Any(f => (int)f["type"] == 8))
                            .Select(e => new Tuple<DateTime, double>(unixTimeStart.AddSeconds((int)e["date"]), (double)(((JArray)e["measures"]).First(token => (int)token["type"] == 8)["value"]) * Math.Pow(10, (double)((JArray)e["measures"]).First(token => (int)token["type"] == 8)["unit"])))
                            .ToList();
                        if (bodyFatMeasures.Any())
                        {
                            Log.Info("Got {0} new body fat measurements", bodyFatMeasures.Count);
                            var bodyFatQuery =
                                bodyFatMeasures.Aggregate(
                                    new StringBuilder("INSERT INTO dbo.bodyFat (measure_date, measure) VALUES "),
                                    (a, b) => a.Append($"('{b.Item1}', {b.Item2}), "));
                            bodyFatQuery.Remove(bodyFatQuery.Length - 2, 2).Append(";");
                            Log.Debug(bodyFatQuery);
                            using (var insertionQuery = new SqlCommand(bodyFatQuery.ToString(), sql))
                            {
                                insertionQuery.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            Log.Info("No new body fat measurements.");
                        }

                        #endregion
                    }
                    Log.Info("Sleeping...");
                    System.Threading.Thread.Sleep(15 * 1000 * 60);
                }
            }
            catch (WebException ex)
            {
                Log.Error(ex);
            }
            catch (Exception ex)
            {
                Log.Fatal(ex);
                throw;
            }
        }
 private void cbOrder_SelectedIndexChanged(object sender, EventArgs e)
 {
     using (SqlConnection connection = new SqlConnection(MainForm.STRCONN))
     {
         connection.Open();
         OrderBy orderBy = (OrderBy)cbOrderBy.SelectedItem;
         string q = String.Format("SELECT Country,League,Season{0} FROM dbo.Leagues ORDER BY {1} {2}",
             orderBy.SelectCol == "" ? "" : "," + orderBy.SelectCol, orderBy.OrderCol, cbOrderDir.SelectedItem);
         LeaguesKey lKey;
         lbLeagues.Items.Clear();
         using (SqlDataReader reader = new SqlCommand(q, connection).ExecuteReader())
         {
             while (reader.Read())
             {
                 lKey = new LeaguesKey();
                 lKey.Country = reader.GetString(0);
                 lKey.League = reader.GetString(1);
                 lKey.Season = reader.GetString(2);
                 if (reader.FieldCount >= 4) lKey.ExtraCol = reader.GetValue(3);
                 lbLeagues.Items.Add(lKey);
             }
         }
     }
 }
Example #11
0
        private void button11_Click(object sender, EventArgs e)
        {
            try
            {
                string netFileName = VissimSingleton.Instance.GetInputFileName();
                string workingDir = VissimSingleton.Instance.GetWorkingDirectory();
                string exeDir = VissimSingleton.Instance.GetExecutionDirectory();

                if (workingDir == exeDir) throw new Exception("ex");

                var files = Directory.GetFiles(workingDir, "*.*", SearchOption.TopDirectoryOnly);
                foreach (string fileName in files)
                {
                    File.Delete(fileName);
                }

                var sb = new SqlConnectionStringBuilder();
                sb.DataSource = "TOSHIBA";
                sb.InitialCatalog = "VISSIM";
                sb.IntegratedSecurity = true;

                using (var conn = new SqlConnection(sb.ConnectionString))
                {
                    conn.Open();
                    using (var reader = new SqlCommand("SELECT * FROM Files;", conn).ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                using (var fs = new FileStream(workingDir + reader.GetString(1), FileMode.Create, FileAccess.Write))
                                {
                                    var bytes = reader.GetSqlBytes(3);
                                    fs.Write(bytes.Buffer, 0, (int)bytes.Length);
                                }
                            }
                        }
                    }
                }
                
                VissimSingleton.Instance.LoadNet(workingDir + netFileName);
                VissimSingleton.Instance.LoadLayout(workingDir + "vissim.ini");
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, ex.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }