Using Event Submission Stored Procedures
Notification Services provides event collection stored procedures that allow you to submit single events or batches of events. Use these stored procedures to submit events using Transact-SQL code. You can manually run these stored procedures, run the stored procedures in a Microsoft SQL Server Agent job, or use these stored procedures in triggers so that you run the query in response to an insert or update on another table.
Submitting Event Data
One set of stored procedures allows you to open an event batch, individually submit one or more events, and then close the event batch. Use the following stored procedures to submit individual events to a Notification Services application:
- NSEventBeginBatchEventClassName takes the name of the event provider that is submitting the event data as an input argument, and returns the batch ID of the new event batch.
- NSEventWriteEventClassName adds a single event to the event batch. These stored procedures arguments are the event batch ID and a list of values for the event fields.
- NSEventFlushBatchEventClassName closes the event batch and submits the set of events to the application
Make sure to execute all of these stored procedures in the same transaction so that Notification Services opens the batch, submits the events, and then closes the batch in one transaction.
For more information and examples, see the following stored procedure topics:
- NSEventBeginBatch<EventClassName> (Transact-SQL)
- NSEventWrite<EventClassName> (Transact-SQL)
- NSEventFlushBatch<EventClassName> (Transact-SQL)
Using a Query to Collect and Submit Events
If you want to collect events by using a SELECT statement, you can use the NSEventSubmitBatchEventClassName stored procedure. This stored procedure uses two queries: one to collect events using a query you define, and one post-collection query (called a post query) to run after gathering events. You can use this post query to do any necessary clean-up, such as changing a tracking field from "new" to "collected" so that you do not collect the same data again.
For more information and an example, see NSEventSubmitBatch<EventClassName> (Transact-SQL).
Using the Event Submission Stored Procedures in Applications
You can run the event collection stored procedures from within both managed and unmanaged code. A common way to execute a stored procedure from within managed code is to use a SqlCommand object. You can specify a stored procedure and its arguments in the SqlCommand object, and then execute the procedure using its ExecuteNonQuery method.
Example: Adding a Single Event
This example uses the following namespaces:
public bool EventSPs()
{
// These variables would normally be defined for
// the class. They would be set based on the values
// provided by the args argument of the Initialize method.
string instanceName = "Tutorial";
string applicationName = "Weather";
string eventClassName = "WeatherEvents";
string eventProviderName = "WeatherSPs";
bool returnValue = true;
SqlConnection databaseConnection = null;
try
{
// Set the connection to SQL Server.
databaseConnection = new SqlConnection();
// Build the connection string.
StringBuilder connectBuilder = new StringBuilder();
connectBuilder.Append("Integrated Security=SSPI;");
connectBuilder.Append("Data Source=MyServer;");
connectBuilder.Append("Initial Catalog=TutorialWeather");
databaseConnection.ConnectionString =
connectBuilder.ToString();
// Open the connection.
databaseConnection.Open();
// Use NSEventBeginBatchEventClassName
// to create a new event batch and return the ID.
SqlCommand databaseCommand = new SqlCommand(string.Format
("\"NSEventBeginBatch{0}\"", eventClassName));
databaseCommand.Connection = databaseConnection;
databaseCommand.CommandType = CommandType.StoredProcedure;
databaseCommand.Parameters.AddWithValue
("@ProviderName", eventProviderName);
SqlParameter storedProcParameter =
databaseCommand.Parameters.Add
("@EventBatchId", SqlDbType.BigInt);
storedProcParameter.Direction = ParameterDirection.Output;
databaseCommand.ExecuteNonQuery();
long eventBatchId =
(long)databaseCommand.Parameters["@EventBatchId"].Value;
// Use NSEventWriteEventClassName
// to write the event to the database.
databaseCommand.Parameters.Clear();
databaseCommand.CommandText =
string.Format("\"NSEventWrite{0}\"", eventClassName);
databaseCommand.Parameters.AddWithValue("@EventBatchId",
eventBatchId);
databaseCommand.Parameters.AddWithValue("@City", "Redmond");
databaseCommand.Parameters.AddWithValue("@Date", "4/5/05");
databaseCommand.Parameters.AddWithValue("@Low", 50.0);
databaseCommand.Parameters.AddWithValue("@High", 55.5);
databaseCommand.Parameters.AddWithValue("@Forecast",
"Partly cloudy");
Console.WriteLine(databaseCommand.CommandText);
databaseCommand.ExecuteNonQuery();
// Use NSEventFlushBatchEventClassName
// to commit the event batch.
databaseCommand.Parameters.Clear();
databaseCommand.CommandText =
string.Format("\"NSEventFlushBatch{0}\"", eventClassName);
databaseCommand.Parameters.AddWithValue("@EventBatchId",
eventBatchId);
long eventsSubmitted = (long)databaseCommand.ExecuteScalar();
}
catch(SqlException ex)
{
Console.WriteLine(ex);
}
finally
{
if (null != databaseConnection)
{
databaseConnection.Close();
databaseConnection = null;
}
}
return true;
}
Example: Adding Multiple Events
This example uses the following namespaces:
- System
- System.Text
- System.Data
- System.Data.SqlClient
- Microsoft.SqlServer.NotificationServices
public bool EventQuery()
{
// These variables would normally be defined for
// the class. They would be set based on the values
// provided by the args argument of the Initialize method.
string instanceName = "Tutorial";
string applicationName = "Weather";
string eventClassName = "WeatherEvents";
string eventProviderName = "WeatherSPs";
StringBuilder builder =
new StringBuilder("SELECT City, GetDate() AS Date, ");
builder.Append("Low, High, Forecast ");
builder.Append("FROM dbo.WeatherData;");
string eventsQuery = builder.ToString();
bool returnValue = true;
SqlConnection databaseConnection = null;
try
{
// Set the connection to SQL Server.
databaseConnection = new SqlConnection();
// Build the connection string.
StringBuilder connectBuilder = new StringBuilder();
connectBuilder.Append("Integrated Security=SSPI;");
connectBuilder.Append("Data Source=MyServer;");
connectBuilder.Append("Initial Catalog=TutorialWeather");
databaseConnection.ConnectionString =
connectBuilder.ToString();
// Open the connection.
databaseConnection.Open();
// Use NSEventSubmitBatchEventClassName
// to create and submit a batch of events.
SqlCommand databaseCommand = new SqlCommand(string.Format
("\"NSEventSubmitBatch{0}\"", eventClassName));
databaseCommand.Connection = databaseConnection;
databaseCommand.CommandType = CommandType.StoredProcedure;
databaseCommand.Parameters.AddWithValue
("@ProviderName", eventProviderName);
databaseCommand.Parameters.AddWithValue("@EventsQuery",
eventsQuery);
databaseCommand.Parameters.AddWithValue("@PostQuery", " ");
long eventsInBatch = (long)databaseCommand.ExecuteScalar();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
finally
{
if (null != databaseConnection)
{
databaseConnection.Close();
databaseConnection = null;
}
}
return true;
}
See Also
Other Resources
Developing a Custom Event Provider
Notification Services Stored Procedures (Transact-SQL)