Web Application to know database update

问题:

My web application (ASP.NET 4) connects to a database (SQL Server 2005). For every new row inserted in a certain table, I want the web app can do some process (like send an email to me with new data).

How do I implement the system? My first though is having web app check the table SN column, and have a variable lastProcessedSN. And keep polling every minute. Is it possible to have DB notify web app when there's a new row being inserted?

Please advise, thanks.

EDIT: Sending an email is just an example, not the exact thing I want to do. So this question is focusing on how to implement the trigger on MSSQL table insert to call my program.

回答1:

you could use SqlDependency`s see

http://www.codeproject.com/KB/database/chatter.aspx

&

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

回答2:

You could modify the insert script or stored procedure to send yourself an email. If you're writing with stored procedures in SQL you can send email from there: Send Email from SQL

Otherwise if you're using EF/NHibernate/Linq2SQL you could write C# code to email yourself

回答3:

Probably the easiest approach would be to setup a Windows scheduled task that ran every X minutes that would execute a console app you create. Your app would select the newest rows from the table (based on timestamp, or possibly a Flag you can set) and e-mail them.

回答4:

You can write a trigger for that table so that when a new record comes to the table, it can call a dot net assembly.you can have the email sending code in your dot net assembly

回答5:

For every new row inserted in a certain table, I want the web app can do some process (like send an email to me with new data).

For this implementation, According to my knowledge you should use the window application.

Is it possible to have DB notify web app when there's a new row being inserted?

Window application will use System.Windows.Forms.Timer. Below is the sample code.

System.Windows.Forms.Timer myTimer = new System.Windows.Forms.Timer();

myTimer.Tick += new EventHandler(TimerEventProcessor);
myTimer.Interval = Mention the time interval in milliseconds;
myTimer.Start();

Timers can be following as well.

System.Threading.Timer

System.Timers.Timer

For more information about the Selection of Timer, you should follow this link
System.Windows.Forms.Timer vs. System.Threading.Timer vs. System.Timers.Timer

private void TimerEventProcessor(Object myObject,
                                            EventArgs myEventArgs) {
{
   using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) {
        con.Open();
        using (SqlCommand cmd = new SqlCommand()) {
            string expression = "Parameter value";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Your Stored Procedure";
            cmd.Parameters.Add("Your Parameter Name", SqlDbType.VarChar).Value = expression;
            cmd.Connection = con;
            using (IDataReader dr = cmd.ExecuteReader()) {
                if (dr.Read()) {
                    //Your functionality
                }
            }
        }
    }

}