Contents tagged with Database

  • Broadcasting Database Changes Through SignalR

    There are several posts out there on using SignalR to signal changes in a database, but because I really didn’t quite like any of them, I decided to write my own.

    The possibility to detect changes in a database comes from the SqlDependency class. Other implementations exist, such as SqlCacheDependency and SqlNotificationRequest, but with these we don’t really receive a notification event. The process is a bit tricky, lots of people out there have problems. These classes only work with SQL Server non-Express, but there are equivalents for other databases, such as OracleDependency for Oracle.

    Because I like to make things generic and reusable, I decided to encapsulate things in my  class. Unfortunately, since SqlDependency does not have an ADO.NET base class, we have to use it directly.

    So, my purpose is to:

    • Have a generic class that, given a database connection and SQL string that illustrates the changes I would like to detect, starts monitoring them, and raises an event whenever it sees changes;
    • I also want a SignalR hub that I can call from the outside – in this case, the changes event – and broadcasts a message about these changes.

    First things first, here is the ChangeNotifier class:

       1: public sealed class ChangeNotifier : IDisposable
       2: {
       3:     private SqlConnection con;
       4:     private SqlCommand cmd;
       5:     private SqlDependency dependency;
       6:     private String connectionString;
       7:     private String dependencyCheckSql;
       8:  
       9:     ~ChangeNotifier()
      10:     {
      11:         this.Dispose();
      12:     }
      13:  
      14:     public event EventHandler<ChangeEventArgs> Change;
      15:  
      16:     public Boolean Start(String connectionStringName, String dependencyCheckSql)
      17:     {
      18:         new SqlClientPermission(PermissionState.Unrestricted).Demand();
      19:  
      20:         this.connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
      21:         this.dependencyCheckSql = dependencyCheckSql;
      22:  
      23:         var result = SqlDependency.Start(this.connectionString);
      24:  
      25:         this.con = new SqlConnection(this.connectionString);
      26:         this.con.Open();
      27:  
      28:         this.cmd = this.con.CreateCommand();
      29:         this.cmd.CommandText = this.dependencyCheckSql;
      30:  
      31:         this.Setup(true);
      32:  
      33:         return (result);
      34:     }
      35:  
      36:     public Boolean Stop()
      37:     {
      38:         var result = false;
      39:  
      40:         if (this.cmd != null)
      41:         {
      42:             this.cmd.Notification = null;
      43:             this.cmd.Dispose();
      44:             this.cmd = null;
      45:         }
      46:  
      47:         if (this.con != null)
      48:         {
      49:             this.con.Close();
      50:             this.con = null;
      51:         }
      52:  
      53:         if (this.dependency != null)
      54:         {
      55:             result = SqlDependency.Stop(this.connectionString);
      56:             this.dependency.OnChange -= this.OnChange;
      57:             this.dependency = null;
      58:         }
      59:  
      60:         this.Change = null;
      61:  
      62:         return (result);
      63:     }
      64:  
      65:     private void Setup(Boolean initial)
      66:     {
      67:         if (initial == false)
      68:         {
      69:             this.dependency.OnChange -= this.OnChange;
      70:         }
      71:  
      72:         this.cmd.Notification = null;
      73:         this.dependency = new SqlDependency(this.cmd);
      74:         this.dependency.OnChange += this.OnChange;
      75:  
      76:         this.cmd.ExecuteScalar();
      77:     }
      78:  
      79:     private void OnChange(Object sender, SqlNotificationEventArgs e)
      80:     {
      81:         this.Setup(false);
      82:  
      83:         var handler = this.Change;
      84:  
      85:         if (handler != null)
      86:         {
      87:             handler(sender, new ChangeEventArgs((ChangeInfo)(Int32)e.Info, (ChangeSource)(Int32)e.Source, (ChangeType)(Int32)e.Type));
      88:         }
      89:     }
      90:  
      91:     public void Dispose()
      92:     {
      93:         this.Stop();
      94:     }
      95: }

    It all starts with the Start method, which receives the name of a connection string stored in Web.config, and the SQL to use for detecting changes. When they occur, it raises the Change event. Action stops when the Stop or Dispose methods are called – do note that this class implements the Dispose Pattern, but in a particular way, because it is not meant to be subclassed. Whenever the OnChange event is raised, we need to set it up again, that’s how SqlDependency works. Because I don’t want to expose directly .NET classes, I created my own event classes and enumerations:

       1: public enum ChangeInfo
       2: {
       3:     AlreadyChanged = -2,
       4:     Unknown = -1,
       5:     Truncate = 0,
       6:     Insert = 1,
       7:     Update = 2,
       8:     Delete = 3,
       9:     Drop = 4,
      10:     Alter = 5,
      11:     Restart = 6,
      12:     Error = 7,
      13:     Query = 8,
      14:     Invalid = 9,
      15:     Options = 10,
      16:     Isolation = 11,
      17:     Expired = 12,
      18:     Resource = 13,
      19:     PreviousFire = 14,
      20:     TemplateLimit = 15,
      21:     Merge = 16,
      22: }
      23:  
      24: public enum ChangeSource
      25: {
      26:     Client = -2,
      27:     Unknown = -1,
      28:     Data = 0,
      29:     Timeout = 1,
      30:     Object = 2,
      31:     Database = 3,
      32:     System = 4,
      33:     Statement = 5,
      34:     Environment = 6,
      35:     Execution = 7,
      36:     Owner = 8,
      37: }
      38:  
      39: public enum ChangeType
      40: {
      41:     Unknown = -1,
      42:     Change = 0,
      43:     Subscribe = 1,
      44: }
      45:  
      46: [Serializable]
      47: public sealed class ChangeEventArgs : EventArgs
      48: {
      49:     public ChangeEventArgs(ChangeInfo info, ChangeSource source, ChangeType type)
      50:     {
      51:         this.Info = info;
      52:         this.Source = source;
      53:         this.Type = type;
      54:     }
      55:  
      56:     public ChangeInfo Info { get; private set; }
      57:     public ChangeSource Source { get; private set; }
      58:     public ChangeType Type { get; private set; }
      59: }

    We start the notifier in Global.asax.cs, maybe in the Application_Start method:

       1: var notifier = new ChangeNotifier();
       2: notifier.Change += this.OnChange;
       3: notifier.Start("MyConnection", "SELECT SomeColumn FROM dbo.SomeTable");

    A connection named MyConnection should exist in Web.config, as well as a table SomeTable with a column SomeColumn. Do change those for your own.

    Next, a SignalR notification hub:

       1: public class ChangeNotificationHub : Hub
       2: {
       3:     public void OnChange(Int32 info, Int32 source, Int32 type)
       4:     {
       5:         this.Clients.All.onChange(info, source, type);
       6:     }
       7: }

    What it does is merely broadcast to connected parties an OnChange message. We can get a reference to it by calling GetHubContext:

       1: private void OnChange(object sender, ChangeEventArgs e)
       2: {
       3:     var context = GlobalHost.ConnectionManager.GetHubContext<ChangeNotificationHub>();
       4:     context.Clients.All.onChange(e.Info, e.Source, e.Type);
       5: }

    Notice that we call an onChange method (uncapitalized), not OnChange, this is so as to respect JavaScript conventions. We hook our page to SignalR using the following script:

       1: <script type="text/javascript">
       1:  
       2:  
       3:     function onChange(info, source, type)
       4:     {
       5:         //do something
       6:     }
       7:  
       8:     window.addEventListener('load', function() {
       9:         var notifier = $.connection.changeNotificationHub;
      10:         notifier.client.onChange = onChange;
      11:         $.connection.hub.start();
      12:     });
    </script>

    The call to $.connection should match you hub class, but using camel casing. The callback function onChange is the same as you call in ChangeNotificationHub and in the OnChange event handler.

    I take it for granted that you know how to setup SignalR, if not, do read the introduction in the ASP.NET site.

    And that’s basically it!

    Some common gotchas:

    • Does not work with SQL Server Express;
    • There’s a specific order by which the connection and SqlDependency.Start must be called, if you stick to my code, it should work;
    • Needs SQL Server Service Broker enabled for the database that we are monitoring, but does not require creating a queue or service, SqlDependency does that for us automatically;
    • Your application pool identity needs to have permissions to connect to the notification queue, see them here;
    • Won’t work with any SQL query, a number of restrictions apply; see here;
    • SqlDependency is very sensitive, and you can easily run into problems if you try different things.

    Read more...

  • A Tour of SharePoint Data Source Controls

    Introduction

    Besides the classic ASP.NET data source controls, SharePoint brings along its own. These allow us to retrieve data not just from SharePoint, but also from external sources, such as web services or SQL databases. Using SharePoint Designer you end up using these data sources without even knowing, and, specially, ignoring what other options you have. Let’s look at these controls one by one and see how the can be used. I already talked about the SPDataSource and AggregateDataSource, so I won’t cover these here. I also won’t be covering them in depth, but instead will provide an example of how to use them.

    SPHierarchyDataSourceControl

    The SPHierarchyDataSourceControl allows us to retrieve data (documents, list items) hierarchically from either a site or a list. It can only be bound to a control that can display hierarchical data, such as the SPTreeView control. It cannot be easily customized, except by using CSS and JavaScript.

    A simple example is:

       1: <SharePoint:SPHierarchyDataSourceControl runat="server" ID="hierarchy" RootContextObject="Web" ShowDocLibChildren="true" ShowListChildren="true" ShowFolderChildren="true" ShowWebChildren="true"/>
       2: <SharePoint:SPTreeView runat="server" DataSourceID="hierarchy"/>

    BdcDataSource

    A BdcDataSource let’s you retrieve data from a BCS data source (external content type). This may happen when you don’t have an external list created or you want to call a specific finder method. You need to specify the namespace, LOB instance, entity, and finder method. If the finder needs parameters, you will need to supply them. It can be customized by hosting it in a DataFormWebPart and by applying XSLT.

    Here’s an example:

       1: <WebPartPages:DataFormWebPart runat="server" Title="BCS" DisplayName="BCS" ID="bcs">
       2:     <DataSources>
       3:         <SharePoint:BdcDataSource runat="server" Mode="List" EntityName="Parent" LobSystemInstanceName="BCS" EntityNamespace="http://sp2013" FinderName="Read List"/>
       4:     </DataSources>
       5:     <Xsl>
       6:         <xsl:stylesheet version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
       7:             <xsl:output method="html" indent="no"/>
       8:  
       9:                 <xsl:template match="/">
      10:                     <xsl:variable name="Rows" select="/dsQueryResponse/NewDataSet/Row"/>
      11:                     Count: <xsl:value-of select="count($Rows)"/>
      12:                 </xsl:template>
      13:             
      14:         </xsl:stylesheet>
      15:     </Xsl>
      16: </WebPartPages:DataFormWebPart>

    SoapDataSource

    SoapDataSource is the control that retrieves data from a SOAP web service. You specify the URL of the web service, the action to call and the SOAP envelope, together with any required parameters. It should be hosted in a DataFormWebPart and can thus use XSLT for the formatting of its contents.

    An example of calling the Lists.asmx web service:

       1: <WebPartPages:DataFormWebPart runat="server" DisplayName="SOAP" Title="SOAP" ID="soap">
       2:     <DataSources>
       3:         <SharePoint:SoapDataSource runat="server" WsdlPath="http://sp2013/_vti_bin/lists.asmx?WSDL" SelectUrl="http://sp2013/_vti_bin/lists.asmx" SelectAction="http://schemas.microsoft.com/sharepoint/soap/GetListCollection" SelectPort="ListsSoap" SelectServiceName="Lists">
       4:             <SelectCommand>
       5:                 <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       6:                     <soap:Body>
       7:                         <GetListCollection xmlns="http://schemas.microsoft.com/sharepoint/soap/"/>
       8:                     </soap:Body>
       9:                 </soap:Envelope>
      10:             </SelectCommand>
      11:         </SharePoint:SoapDataSource>
      12:     </DataSources>
      13:     <XSL>
      14:         <xsl:stylesheet xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ddw1="http://schemas.microsoft.com/sharepoint/soap/" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
      15:             <xsl:output method="html" indent="no"/>
      16:     
      17:             <xsl:template match="/">
      18:                 <xsl:variable name="Rows" select="/soap:Envelope/soap:Body/ddw1:GetListCollectionResponse/ddw1:GetListCollectionResult/ddw1:Lists/ddw1:List"/>
      19:                 Count: <xsl:value-of select="count($Rows)"/>
      20:             </xsl:template>
      21:         </xsl:stylesheet>
      22:     </XSL>
      23: </WebPartPages:DataFormWebPart>

    XsltListViewWebPart (External List)

    External lists can be displayed using the XsltListViewWebPart. Nothing really new here.

    A simple example (of course, do replace the list and view GUIDs):

       1: <WebPartPages:XsltListViewWebPart runat="server" Title="External List" DisplayName="External List" ListName="{C9DD692D-27C6-4B99-A197-F8892F293C04}" ID="external">
       2:     <XmlDefinition>
       3:         <View Name="{DDD613DD-BA38-4DBF-9A89-5502B0152EE1}" Type="HTML" DisplayName="Read List" Url="/Lists/ExternalList/Read List.aspx" BaseViewID="1">
       4:             <Method Name="Read List"/>
       5:         </View>
       6:     </XmlDefinition>
       7:     <Xsl>
       8:         <xsl:stylesheet version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
       9:             <xsl:output method="html" indent="no"/>
      10:  
      11:                 <xsl:template match="/dsQueryResponse">
      12:                     <xsl:variable name="Rows" select="Rows/Row"/>
      13:                     Count: <xsl:value-of select="count($Rows)"/>    
      14:                 </xsl:template>
      15:         </xsl:stylesheet>
      16:     </Xsl>
      17: </WebPartPages:XsltListViewWebPart>

    XmlUrlDataSource

    The XmlUrlDataSource is used for invoking REST web services. Similar to  SoapDataSource, you need to pass it the URL, but also the HTTP method and any parameters. I is also usually hosted in a DataFormWebPart.

    Here’s an example of calling the weather service I talked about in another post:

       1: <WebPartPages:DataFormWebPart runat="server" Title="REST" DisplayName="REST" ID="rest">
       2:     <DataSources>
       3:         <SharePoint:XmlUrlDataSource runat="server" AuthType="None" HttpMethod="GET" SelectCommand="http://api.openweathermap.org/data/2.5/weather">
       4:             <SelectParameters>
       5:                 <WebPartPages:DataFormParameter Name="id" ParameterKey="id" PropertyName="ParameterValues" DefaultValue="2740637"/>
       6:                 <WebPartPages:DataFormParameter Name="mode" ParameterKey="mode" PropertyName="ParameterValues" DefaultValue="xml"/>
       7:                 <WebPartPages:DataFormParameter Name="units" ParameterKey="units" PropertyName="ParameterValues" DefaultValue="metric"/>
       8:             </SelectParameters>
       9:         </SharePoint:XmlUrlDataSource>
      10:     </DataSources>
      11:     <Xsl>
      12:         <xsl:stylesheet version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
      13:             <xsl:output method="html" indent="no"/>
      14:  
      15:                 <xsl:template match="/">
      16:                     <xsl:variable name="Rows" select="/current"/>
      17:                     Count: <xsl:value-of select="count($Rows)"/>
      18:                 </xsl:template>
      19:             
      20:         </xsl:stylesheet>
      21:     </Xsl>
      22: </WebPartPages:DataFormWebPart>

    SPSqlDataSource

    The SPSqlDataSource is what SharePoint uses to access a SQL database. If you use SharePoint Designer to add one such data source, you might be surprised to see that it places a SqlDataSource instead. The thing is, SharePoint uses ASP.NET tag mapping to replace any SqlDataSource control for a SPSqlDataSource. Again, we host it inside a DataFormWebPart for easily formatting its contents and you can pass parameters to the SQL.

    An example:

       1: <WebPartPages:DataFormWebPart runat="server" Title="SQL" DisplayName="SQL" ID="sql">
       2:     <DataSources>
       3:         <SharePoint:SPSqlDataSource runat="server" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=servername;User ID=username;Password=password;Initial Catalog=database;" SelectCommand="SELECT * FROM [SomeTable]"/>
       4:     </DataSources>
       5:     <Xsl>
       6:         <xsl:stylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
       7:             <xsl:output method="html" indent="no"/>
       8:  
       9:                 <xsl:template match="/">
      10:                     <xsl:variable name="Rows" select="/dsQueryResponse/NewDataSet/Row"/>
      11:                     Count: <xsl:value-of select="count($Rows)"/>
      12:                 </xsl:template>
      13:         </xsl:stylesheet>
      14:     </Xsl>
      15: </WebPartPages:DataFormWebPart>

    SPCalendarDataSource

    SPCalendarDataSource cannot be used exclusively on the markup, it needs a List property that can only be set through code. It is usually used to populate a SPCalendarView control.

    An example of the markup:

       1: <SharePoint:SPCalendarDataSource runat="server" ID="calendar" ViewType="Week"/>
       2: <SharePoint:SPCalendarView runat="server" DataSourceID="calendar"/>

    Conclusion

    While SPDataSource is generally the most generic and useful control, it cannot do everything, namely, access external sources. For that, we have other options; the AggregateDataSource can be used to bring together data from all of these sources, except SPHierarchyDataSourceControl and SPCalendarDataSource, but you can easily replace these by SPDataSource. I did not mention SPWorkflowDataSource because it isn't a general-purpose data source control, it is merely used to set or get workflow association parameters.

    Let me know if you have any questions!

    Read more...

  • Getting the Topmost Hierarchical Parent in T-SQL

    Introduction

    It is normal in databases to have hierarchical tables, that is, tables that are related with themselves, forming a parent-child relation. For example, consider this:

    image

    The parent_id column points to the parent record, which, in some cases, will not exist.

    So, imagine we have a number of records, such as:

       1: INSERT INTO dbo.list (id, parent_id) VALUES (1, NULL)
       2: INSERT INTO dbo.list (id, parent_id) VALUES (2, 1)
       3: INSERT INTO dbo.list (id, parent_id) VALUES (3, 2)
       4: INSERT INTO dbo.list (id, parent_id) VALUES (4, 3)

    How can we find the id of the topmost parent? In this case, it will always be 1, of course.

    In SQL Server, we have two options:

    1. A Common Table Expression (CTE);
    2. A recursive function.

    Let’s see how to implement each.

    Common Table Expression Approach

    We need to write a CTE that starts with some record and goes all the way up until it finds the parent. Let’s wrap it in a nice scalar function:

       1: CREATE FUNCTION dbo.GetTopmostParentCTE
       2: (
       3:     @id INT
       4: ) 
       5: RETURNS INT
       6: AS
       7:     BEGIN
       8:         DECLARE @parentId INT
       9:  
      10:         ;WITH cte AS 
      11:         (
      12:             SELECT a.id, a.parent_id
      13:             FROM dbo.list AS a 
      14:             WHERE a.id = @id
      15:             UNION ALL
      16:             SELECT b.id, b.parent_id 
      17:             FROM dbo.list AS b
      18:             INNER JOIN cte AS c
      19:             ON c.parent_id = b.id
      20:         )
      21:  
      22:         SELECT TOP 1 @parentId = id
      23:         FROM cte
      24:         WHERE parent_id IS NULL
      25:  
      26:         RETURN @parentid
      27:     END
      28: GO

    I won’t explain here how CTEs work, they have been around for quite some time, and there are several posts how there for that.

    Recursive Function Approach

    The other approach is using a recursive function. The gotcha here is that when we create a function, it is compiled, and if it has a reference to itself – which doesn’t exist first – it will fail. Therefore, we need to first create a dummy function and then change it to do what we want:

       1: CREATE FUNCTION dbo.GetTopmostParent
       2: (
       3:     @id INT
       4: )
       5: RETURNS INT
       6: AS
       7: BEGIN
       8:     RETURN
       9:     (
      10:         SELECT 0
      11:     )
      12: END
      13: GO
      14:  
      15: ALTER FUNCTION dbo.GetTopmostParent
      16: (
      17:     @id INT
      18: )
      19: RETURNS INT
      20: AS
      21: BEGIN
      22:     RETURN
      23:     (
      24:         SELECT CASE WHEN parent_id IS NULL THEN id ELSE dbo.GetTopmostParent(parent_id) END
      25:         FROM dbo.list
      26:         WHERE id = @id
      27:     )
      28: END
      29: GO

    Conclusion

    You can get results from the two functions by running the following T-SQL queries:

       1: SELECT dbo.GetTopmostParent(4)
       2: SELECT dbo.GetTopmostParentCTE(4)

    Interesting, both execution plans are exactly the same:

    image

    I can’t really recommend one over the other, since from my tests, both took the same amount of time (you will need far more records than the ones from my sample to tell that).

    So, any thoughts from database gurus out there?

    Read more...