求助：基于Visual Studio+SQL Server实现Usagetime表每分钟自动累加时长

Hey there! Let's figure out how to automatically increment the usage duration field in your Usagetime table every minute, using your Visual Studio + SQL Server setup. I'll walk you through two reliable approaches—pick the one that fits your workflow best.

方案1：使用SQL Server Agent作业（推荐，数据库级定时任务） #

This is the most robust option because it runs directly on the database server, no need to rely on a separate application staying online. Here's how to set it up:

• 第一步：编写更新SQL脚本
  First, create the T-SQL command that does the increment. Replace [使用时长字段名] with your actual column name:

  UPDATE Usagetime 
  SET [使用时长字段名] = [使用时长字段名] + 1;
  

  Tip: If you only need to update specific rows, add a WHERE clause to avoid full-table updates (e.g., WHERE IsActive = 1).

• 第二步：创建SQL Server Agent作业

  1. Open SQL Server Management Studio (SSMS) and connect to your database server.
  2. Expand the SQL Server Agent node in the Object Explorer, right-click Jobs > New Job.
  3. Give your job a name (like "Usagetime_Minute_Increment") and add a description if you want.
  4. Go to the Steps tab, click New:
     • Set Step name (e.g., "Increment Usage Duration")
     • Choose Transact-SQL (T-SQL) as the Type
     • Select your target database from the dropdown
     • Paste the SQL script you wrote earlier into the Command box
     • Click OK to save the step
  5. Go to the Schedules tab, click New:
     • Name your schedule (e.g., "Every Minute")
     • Set Frequency to "Daily"
     • Under "Daily frequency", select "Occurs every" and enter 1 minute
     • Adjust the start time to when you want the job to begin running
     • Click OK to save the schedule
  6. Click OK to finalize the job.

• 第三步：启用并测试作业
  Right-click your new job in SSMS > Start Job at Step... to test it immediately. You can check the job history (right-click job > View History) to confirm it's running successfully every minute.

Important: Make sure the SQL Server Agent service is running on your server. You can check this in Windows Services (look for "SQL Server Agent (MSSQLSERVER)").

方案2：在Visual Studio应用中实现定时任务 #

If you need to tie the update to your application logic (e.g., only run when the app is active), you can build a simple console app or Windows Service in Visual Studio using C#. Here's a quick example with System.Timers.Timer:

using System;
using System.Data.SqlClient;
using System.Timers;

namespace UsageTimeAutoUpdater
{
    class Program
    {
        private static Timer _updateTimer;
        // Replace with your actual SQL Server connection string
        private static readonly string _dbConnectionString = "Server=YOUR_SERVER_NAME;Database=YOUR_DB_NAME;Integrated Security=True;";

        static void Main(string[] args)
        {
            // Initialize timer to trigger every 60 seconds (60000 ms)
            _updateTimer = new Timer(60000);
            _updateTimer.Elapsed += PerformUsageUpdate;
            _updateTimer.AutoReset = true; // Repeat automatically
            _updateTimer.Enabled = true;

            Console.WriteLine("Usage time updater running. Press any key to exit...");
            Console.ReadKey();

            // Cleanup on exit
            _updateTimer.Stop();
            _updateTimer.Dispose();
        }

        private static void PerformUsageUpdate(object sender, ElapsedEventArgs e)
        {
            try
            {
                using (var connection = new SqlConnection(_dbConnectionString))
                {
                    connection.Open();
                    var updateQuery = "UPDATE Usagetime SET [使用时长字段名] = [使用时长字段名] + 1;";
                    using (var command = new SqlCommand(updateQuery, connection))
                    {
                        int rowsUpdated = command.ExecuteNonQuery();
                        Console.WriteLine($"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] Updated {rowsUpdated} rows successfully.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] Update failed: {ex.Message}");
            }
        }
    }
}

两种方案对比 #

注意事项 #

• 权限: Ensure the account running the SQL Agent job or your application has UPDATE permissions on the Usagetime table.
• 性能: If your Usagetime table is large, adding a WHERE clause to target only necessary rows will prevent unnecessary performance hits.
• Error Handling: For production use, add logging (instead of just console output) to track failures and successes.

内容的提问来源于stack exchange，提问作者Joe Bry Basil