C# SqlCommand.GetInt32方法代码示例

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

        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())
                            {
//.........这里部分代码省略.........

        public void Update()
        {
            Log.WriteLine(LogLevel.Info, "Looking for database updates...");
            int version = 0;
            int patch = 0;
            using (SqlConnection connection = new SqlConnection(ConnectionStringbuilder.CreateConnectionString(this._setting)))
            {
                connection.Open();
                // Check DB version
                try
                {
                    using (var reader = new SqlCommand("SELECT [Version] FROM [SolarVersion]", connection).ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            version = reader.GetInt32(0);
                        }
                    }
                }
                catch { } // default is set to 0 :D

                Log.WriteLine(LogLevel.Debug, "Current Database version is {0}", version);

                // Now we have the function, lets see which files there are...

                if (Directory.Exists("SQL"))
                {
                    string type = _type == DatabaseTypes.Login ? "login" : "world";
                    foreach (string filename in Directory.GetFiles("SQL", type + "_*.sql"))
                    {
                        try
                        {
                            string[] pieces = filename.Split('_'); // login_XX_dat-a-lawl.sql
                            int p = int.Parse(pieces[1]);

                            if (p <= version) continue; // Already ran this one!

                            if (p < patch)
                            {
                                Log.WriteLine(LogLevel.Warn, "Patch ID out of order O.o. Using last patch ID instead: {0}", patch);
                            }
                            else
                            {
                                patch = p;
                            }
                            string message = pieces[2].Replace(".sql", "");

                            Log.WriteLine(LogLevel.Info, "Trying to update {0} database with patch {1}. Message:", type, patch);
                            Log.WriteLine(LogLevel.Info, message);
                            RunFile(filename, connection);
                        }
                        catch (Exception ex)
                        {
                            Log.WriteLine(LogLevel.Exception, "Could not parse file {0}: {1}", filename, ex.ToString());
                            Console.ReadLine();
                            Environment.Exit(400);
                        }
                    }

                    if (version < patch)
                    {
                        Log.WriteLine(LogLevel.Info, "Database updated!");
                        version = patch;
                        // Try to update table to new version
                        using (SqlCommand cmd = new SqlCommand("DELETE FROM [SolarVersion];", connection))
                            cmd.ExecuteNonQuery();
                        using (SqlCommand cmd = new SqlCommand("INSERT INTO [SolarVersion] VALUES (" + patch.ToString() + ");", connection))
                            cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        Log.WriteLine(LogLevel.Info, "Database up-to-date!");
                    }
                }
                else
                {
                    Log.WriteLine(LogLevel.Error, "Couldn't find SQL dir. Cannot update db.");
                }

                connection.Close();
            }
        }