如何在特定时间通过 C# 运行 SQL 存储过程? [英] How to run an SQL stored procedure through C# at a specific time?

问题描述

我现在正在为图书馆建立一个网站，我需要每天同时检查是否有人需要在接下来的五天内归还图书，并通过电子邮件向他们发送提醒.

我的问题是这样做的正确方法是什么?

我需要完成的是，当一天中的特定时间到来时，我需要运行 sql 存储过程并通过 Visual Studio 2010 或任何其他方式检查存储过程是否返回了我需要通过电子邮件发送的任何结果.

有没有办法在 C# 上不断检查系统时间而不是作为触发事件?

解决方案

我认为最简单的方法是在 SQL Server 代理中创建一个新作业来每天调用您的存储过程，而不是尝试在您编写的应用程序中实现这一目标.

有关创建 SQL 作业的更多信息，请参阅以下链接

编辑 - 抱歉，我真的不应该在做其他工作的同时尝试回答 SO 帖子.我完全错过了你想发送电子邮件的事实，而且我想你想在屏幕上报告过期记录.呃.

要在每天的某个时间发送过期的电子邮件，我想 Windows 服务肯定可以完成这项工作，但似乎有点过头了，您需要完全控制它正在运行的服务器在.

您可以改为设置一个计划任务来每天调用 sendOverdueMail.aspx 网页来完成这项工作吗?像这样>

显然也有 SQL Server 代理电子邮件选项，尽管我不确定您对生成的电子邮件格式有多少控制.

最后，本教程逐步创建计划任务类型效果，方法是在 ASP.net 服务器上挂钩缓存到期事件，然后允许您调用电子邮件代码.

private const string DummyCacheItemKey = "GagaGuguGigi";protected void Application_Start(Object sender, EventArgs e){RegisterCacheEntry();}private bool RegisterCacheEntry(){if( null != HttpContext.Current.Cache[ DummyCacheItemKey ] ) 返回假；HttpContext.Current.Cache.Add( DummyCacheItemKey, "Test", null,DateTime.MaxValue, TimeSpan.FromMinutes(1),CacheItemPriority.Normal,新的 CacheItemRemovedCallback( CacheItemRemovedCallback ) );返回真；}公共无效CacheItemRemovedCallback(字符串键，对象值，CacheItemRemovedReason 原因){Debug.WriteLine("缓存项回调:" + DateTime.Now.ToString());//在这里发送提醒邮件做工作();}

I am building a website now for a library and i need to check every day at the same time if there are people who need to return their books in the next five days and to send them a reminder via email.

My question is what will be the correct way to do that?

What i need to accomplish is when the specific time of day comes i need to run an sql stored procedure and check either through visual studio 2010 or any other way if the stored procedure has returned any results to which i need to email.

Is there a way to maybe check the system time constantly on C# and not as a triggered event?

解决方案

I'd image the simplest way to do this would be by creating a new job in the SQL Server Agent to call your stored procedure every day, rather than trying to achieve this in an application you've written.

See the following link for more information on creating SQL Jobs

Edit - Sorry, I really shouldn't try to answer SO posts while doing other work too. I completely missed the fact you wanted to send off emails, and got it into my head that you wanted to report overdue records on screen. Duh.

To send off overdue emails at a certain time every day, i'd guess a windows service would certainly do the job, but seems a little over the top, and you'd need to have full control over the server it's running on.

You could instead set up a scheduled task to call a sendOverdueMail.aspx web page every day to do the work perhaps? Like this

Obviously there is the SQL Server Agent emailing option too, although I'm not sure how much control you'd have over the format of the emails that would produce.

Finally, this tutorial steps through creating a scheduled task type effect, by hooking into cache expiry events on an ASP.net server, which then allow you to call your emailing code.

private const string DummyCacheItemKey = "GagaGuguGigi";
protected void Application_Start(Object sender, EventArgs e)
{
    RegisterCacheEntry();
}

private bool RegisterCacheEntry()
{ 
    if( null != HttpContext.Current.Cache[ DummyCacheItemKey ] ) return false;

        HttpContext.Current.Cache.Add( DummyCacheItemKey, "Test", null, 
        DateTime.MaxValue, TimeSpan.FromMinutes(1), 
        CacheItemPriority.Normal,
        new CacheItemRemovedCallback( CacheItemRemovedCallback ) );

    return true;
}


public void CacheItemRemovedCallback( string key, 
        object value, CacheItemRemovedReason reason)
{
    Debug.WriteLine("Cache item callback: " + DateTime.Now.ToString() );

    // send reminder emails here
    DoWork();
}

这篇关于如何在特定时间通过 C# 运行 SQL 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！