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"));
}
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();
}
}
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;
}
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();
}
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);
}
}
}
}
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);
}
}