Centralizing SqlDataSource Events
Introduction:
I was working with an old legacy web-form application. This web-form includes a good amount of physical web-form pages and each web-form uses SqlDataSource control heavily. I was required to do some things with all SqlDataSource controls in all web-form pages. Doing this with each SqlDataSource control in all web-form pages is a daunting task. I was able to centralize SqlDataSource events to perform my tasks with all SqlDataSource controls just at one place. In this article, I will show you how to log the time taken by a SqlDataSource in a/an INSERT/UPDATE/DELETE/SELECT operation. I will also show you how to change the database isolation-level globally with SqlDataSource control.
Description:
In my legacy application, I have a base page class. If you don't have a base page then please create it first. Let's say we have WebForm1.aspx,
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebFormApp.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateSelectButton="True" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SampleConnectionString1 %>" DeleteCommand="DELETE FROM [Product] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Product] ([Id], [Name]) VALUES (@Id, @Name)" ProviderName="<%$ ConnectionStrings:SampleConnectionString1.ProviderName %>" SelectCommand="SELECT [Id], [Name] FROM [Product]" UpdateCommand="UPDATE [Product] SET [Name] = @Name WHERE [Id] = @Id">
<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Id" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
I have a Product table with Id and Name. This simple web-form page allows user to see, update and delete a product. Here is the WebForm1 code-behind,
public partial class WebForm1 : BasePage
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
Note that WebForm1 is inheriting from BasePage instead of System.Web.UI.Page. Your all web-form pages should be inherit from BasePage in order to centralize SqlDataSource events. For making our work easy, we will create an extension methods for the ControllCollection class,
public static class ControllCollectionExtensions
{
public static IEnumerable<Control> FindAll(this ControlCollection collection)
{
foreach (Control item in collection)
{
yield return item;
if (item.HasControls())
{
foreach (var subItem in item.Controls.FindAll())
{
yield return subItem;
}
}
}
}
public static IEnumerable<T> FindAll<T>(this ControlCollection collection) where T : Control
{
return collection.FindAll().OfType<T>();
}
}
The ControlCollection.FindAll extension method recursively find all controls of a specific type. Here is a simple version of BasePage.cs,
public class BasePage : System.Web.UI.Page
{
private Logger logger = LogManager.GetCurrentClassLogger();
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
RegisterEvents();
}
protected virtual void ExecutingSqlDatasource(object sender, SqlDataSourceCommandEventArgs e)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
Context.Items[e.Command.CommandText] = stopWatch;
e.Command.Connection.Open();
e.Command.Transaction = e.Command.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
}
protected virtual void ExecutedSqlDatasource(object sender, SqlDataSourceStatusEventArgs e)
{
var stopWatch = Context.Items[e.Command.CommandText] as Stopwatch;
stopWatch.Stop();
logger.Info(string.Format("{0} takes {1}", e.Command.CommandText, stopWatch.Elapsed.ToString()));
if (e.Exception == null)
{
e.Command.Transaction.Commit();
}
else
{
e.Command.Transaction.Rollback();
}
}
private void RegisterEvents()
{
var sqlDataSourceCollection = Controls.FindAll<SqlDataSource>();
foreach (var sqlDataSource in sqlDataSourceCollection)
{
if (!string.IsNullOrEmpty(sqlDataSource.SelectCommand))
{
sqlDataSource.Selecting += ExecutingSqlDatasource;
sqlDataSource.Selected += ExecutedSqlDatasource;
}
if (!string.IsNullOrEmpty(sqlDataSource.InsertCommand))
{
sqlDataSource.Inserting += ExecutingSqlDatasource;
sqlDataSource.Inserted += ExecutedSqlDatasource;
}
if (!string.IsNullOrEmpty(sqlDataSource.UpdateCommand))
{
sqlDataSource.Updating += ExecutingSqlDatasource;
sqlDataSource.Updated += ExecutedSqlDatasource;
}
if (!string.IsNullOrEmpty(sqlDataSource.DeleteCommand))
{
sqlDataSource.Deleting += ExecutingSqlDatasource;
sqlDataSource.Deleted += ExecutedSqlDatasource;
}
}
}
}
BasePage simply invoke RegisterEvents method during OnLoad. RegisterEvents method first grab all the SqlDataSource controls on the current page using the above FindAll extension method. Then for each SqlDataSource control, register the XXXing and XXXted event if their related command exist(for example, check SqlDataSource.SelectCommand exist before registering SqlDataSource.Selecting and SqlDataSource.Selected events). Then on a XXXing event, I am creating and starting a Stopwatch and then saving this Stopwatch in Context.Items for later use. I am also setting the sql connection transaction-level as read-uncommitted/dirty-read(please don't use this transaction-level unless you have some valid reason). Then inside XXXted event, I am committing or roll-backing the transaction depending on exception exist. I am also grabbing the Stopwatch, stopping and logging the total elapsed time. Here I am using I am using NLog to log the timespan. You can use any other logging framework.
Summary:
Centralizing the SqlDataSource events is a daunting task. Specially, when you have a huge amount of web-form pages. In this article, I showed you how easily one can centralize the SqlDataSource events. I also showed you how to change the transaction-level used in SqlDataSource and how to log the timespan for each raw SQL(or SP) used by SqlDataSource. Hopefully you enjoyed my this article too.