SQL Server Parse HTML Content from Data

With this TSQL script, we can remove HTML content as long as there are valid start and ending HTML tags.

Script:

Create FUNCTION [dbo].[udf_StripHTMLContent]

(@content VARCHAR(MAX),@tagStart varchar(55),  @tagEnd varchar(55))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @start INT=1

DECLARE @end INT=1

DECLARE @len INT=1

           

WHILE @start > 0 AND @end > 0 AND @len > 0

BEGIN

                       

      SET @start = CHARINDEX(@tagStart,@content)

      SET @end = (len(@tagEnd)-1) + CHARINDEX(@tagEnd,@content,CHARINDEX(@tagStart,@content))        

      SET @len = (@end - @start) + 1     

      IF @start > 0 AND @end > 0 AND @len > 0 and (@len > 1 and @end > len(@tagEnd))

      BEGIN

            SET @content = STUFF(@content,@start,@len,'')              

      END

      ELSE

      BEGIN

            break;           

      END                                

END

RETURN REPLACE(@content,' ',' ')

This script parses the content looking for the start and end tags. It replaces all the content that is contained by those tags. It continues to parse the string until no more matching tags are found or the end of the string is reached.

Example:

Use the following examples to see how this function can be used. The first couple of examples just parse a string. The last example does an inline SQL update to remove HTML tags from some inventory records.

declare @html varchar(max), @start varchar(55), @end varchar(55)

set @start = '<div><a href="bad.com">'

set @end = '</a></div>'

set @html = 'This item has many features<div><a href="bad.com">Unwanted content</a></div>'

select @html as before

set @html  =dbo.udf_StripHTMLContent(@html,@start, @end)

select @html as after

set @start = '<p style="display:none">'

set @end = '</p>'

set @html = 'This item has many features<p style="display:none"><a href="bad.com">Unwanted content</a></p>'

select @html as before

set @html  =dbo.udf_StripHTMLContent(@html,@start, @end)

select @html as after

--TABLE UPDATE CALL

update a set ItemDetail = dbo.udf_StripHTMLContent(ItemDetail,@start, @end)

from Inventory..Item a

...

This function can also be used to strip out content that matches a particular start and ending pattern.

I hope this is useful.

With this TSQL script, we can remove HTML content as long as there are valid start and ending HTML tags. Script: Create FUNCTION ...

Read Article →

Recover a SQL Server management studio query

We often come across the case when SSMS crashes and perhaps your query was not saved. If you have the AutoSave feature enabled, you can find a temporary file at this location:

c:\users\{username}\SQL Server Management Studio\Backup Files\Solution1

If you do not have this feature, you can still recover the script to the state when it was last executed by reading the information from the dynamic management views and functions. This can allow us to capture the SQL statements that were executed on the system for a particular period of time. Please note that this only works while the query statistic cache is not reset. 

To recover your query, use the following query and enter a search value and time frame.

USE MASTER 
GO

SELECT query.last_execution_time AS [Date Time], execsql.text AS [Script]

FROM sys.dm_exec_query_stats AS query

CROSS APPLY sys.dm_exec_sql_text(query.sql_handle) AS execsql

WHERE execsql.text like '%TAG-TO-SEARCH%' and last_execution_time > getdate()-1

ORDER BY query.last_execution_time DESC

In this query, I am only looking for queries for the last day. You can remove that date constraint to expand the search.  The TAG-TO-SEARCH value should be replaced by a word that can help you get a closer match.

You can find detail information of dynamic views here

We often come across the case when SSMS crashes and perhaps your query was not saved. If you have the AutoSave feature enabled, you can fin...

Read Article →

Client Side HTML Semantic Templates with Handlebars.js

When working with ASP.NET MVC Web applications, a common approach is to create the user interface by defining server side views and binding the model/data to these views. Basically the HTML gets generated on the server. What if we would just like to download the data and templates to reduce the HTML download and leverage the client processing resources?

This is where handlebars.js can help us. With this JavaScript framework, we can define HTML semantics templates into views which can be used to render the user interface.  With this approach, we can use AJAX to call a web service and get JSON data (model) into the application, reference views as JavaScript libraries and bind the JSON data to render the content.

For Visual Studio projects

To start using Handlebars on your web project, we fist need to add the NuGet package to our project. This is done by entering this command on the Visual Studio Package Manager Console:

PM> Install-Package handlebars.js

This should add the following JavaScript files:

File Name	Description
Handlebars.js	This is the framework file with no compression. You can use this during the development process.
Handlebars.min.js	This is the minimize file for the production environment
Handlebars.runtime.js	This is a smaller file which does not contain any of the compilation support for the framework. Handlebars compiles views as JavaScript objects. The result is not machine code as you would expect. It is just a way to optimize the templates. This file is only needed if you precompile the templates. Note*: If you get an error on this call Handlebars.compile(view), this is because you are including the Handlebars.runtime.js file. To fix this, remove the reference to the runtime.js file.

HTML Content

Now that we have a reference to Handlebars we can start building our JavaScript views.  We are going to start with this simple HTML page:

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

    <title>ozkary - semantic template</title>

<script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>

<script src="/Scripts/handlebars.js"></script>

</head>

<body>

    <div id="content">content here</div>

</body>   

</html> 

Defining the Model

There is currently nothing on the page. We would like to show a list of vehicles right inside the #content element. The model that we are using has the following definition:

 

The JSON string for this model looks like this:

{Makes: [

{"Name": "Acura", "Models": [{"Name": "ILX"}, {"Name": "MDX" }, {"Name": "NSX Coupe" }] },

{"Name": "Ford", "Models": [{"Name": "Escape"}, {"Name": "Edge" }, {"Name": "Focus" }] },

{"Name": "Nissan", "Models": [{"Name": "Altima"}, {"Name": "Sentra" },{"Name": "XTerra" }] }

]};

The model is a simple parent - child JSON structure. It displays vehicle makes a few models for each make.

Defining the View

We now need to define a handlebars template which can be used to render the JSON model.  A template is defined in a Script block. The requirement for a handlebars template is that we need to add a Script block of type text/x-handlebars-template. The HTML that is used for binding the model should be enclosed within the script tags. We are using a simple layout with headers and un-order list to show each make and associated models. We can now add this block to our HTML page:

<script id="makes-template" type="text/x-handlebars-template">

{{#Makes}}

<h1>{{Name}}</h1>

<ul>

{{#Models}}

<li>{{Name}}</li>

{{/Models}}

</ul>

{{/Makes}} 

</script>

This is where it gets really interested. When using handlebars, we must use the double curly brackets for each expression.  In this example, the template takes the model and iterates each Make displaying the name in the h1 tags. It then iterates thru all the children of each make and displays the Model name in between the li tags. We should notice that each iteration block has this format:

{{#Tag}}

…

{{/Tag}} 

The hash (#) is used to start the block and the forward slash (/) is used to terminate it.

Process the template with the model context

Now that we have defined the model and template, we now need to process this information and add it to the document.  This is done by adding these tasks:

• Load the model (JSON)
• Load the template
• Compile the template
• Bind the model to the template
• Render the html

The JavaScript code that does this looks like this:

  var makesModel = {

        Makes: [

    { "Name": "Acura", "Models": [{ "Name": "ILX" }, { "Name": "MDX" }, { "Name": "NSX Coupe" }] },

    { "Name": "Ford", "Models": [{ "Name": "Escape" }, { "Name": "Edge" }, { "Name": "Focus" }] },

    { "Name": "Nissan", "Models": [{ "Name": "Altima" }, { "Name": "Sentra" }, { "Name": "XTerra" }] }

        ]

    };

    function init() {

        var source = $("#makes-template").html();

        var template = Handlebars.compile(source);

        $("#content").html(template(makesModel));

    }

    init();

The HTML Content

If we run this script in JSFiddle, we will get the following content:

With this approach, we can see how to render web content using a MVVM design pattern. We eliminate the need to write code with model information, and we use semantics templates to generate the user interface on the client side.

Thanks for reading.

When working with ASP.NET MVC Web applications, a common approach is to create the user interface by defining server side views and bindi...

Read Article →

Change SharePoint ms-core-brandingtext with JavaScript

A quick note about this task is that you can rename the SharePoint site brand name with SharePoint Management PowerShell to avoid having to use this approach. When we do not have access to PowerShell, we can do this with some JavaScript on the site’s master page. We should use SharePoint 2013 Designer for this change.

We first need to identify the master page that our site is using. We then need to find the HTML element that contains the site brand name. For SharePoint 2013, the brand name is contained in the following HTML element:

<div class="ms-core-brandingText">SharePoint</div>

On the master page, this is generated from a server side control with this mark up:

<SharePoint:DelegateControl id="ID_SuiteBarBrandingDelegate" ControlId="SuiteBarBrandingDelegate" runat="server" />

We need to just change the HTML that is rendered on the client side. We can quickly do this by scrolling to the end of the master page and entering our JavaScript snippet to change the text on that HTML element.

<sharepoint:scriptblock runat="server">

try{

   document.querySelectorAll('div.ms-core-brandingText')[0].innerHTML = 'All About .NET';

}catch(e){}

</sharepoint:scriptblock>

The snippet is selecting all the div elements with the ms-core-brandingText class. There should only be one element with that class name. We then just set the innerHTML to the text that we need. Make the change, save the master page, and refresh the site. It should now look like this:

A drawback about this is that it needs to be done in each master page that your site is using and for each sub-site.

I hope this helps.

A quick note about this task is that you can rename the SharePoint site brand name with SharePoint Management PowerShell to avoid having to...

Read Article →

TFS Build Web.config Transformation

With Visual Studio, we are able to create different build configurations with the Configuration Manager. This allows us to create different web.config files for a particular build target like Stage, Prod. The idea behind this is that each target configuration contains the correct application settings for that target environment.

When we build our projects on Visual Studio, we notice that the web.config file is transformed to contain the changes that are needed for the build target. When we use TFS Team Build, this does not happen by default.  An approach that we can take to address this is as follows:

Add a target directive only on the web project definition

For this step, we need to edit the .csproj file and add the following XML entries:

<UsingTask TaskName="TransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll" />

<Target Name="AfterBuild" Condition="'$(IsTFSBuild)' != ''"> 

<Message Text="'Message After Build: TFSBuild Status $(IsTFSBuild) - $(OutDir)\_PublishedWebsites\$(TargetName) '" />

  <TransformXml Condition="Exists('$(OutDir)\_PublishedWebsites\$(TargetName)')"

       Source="Web.config"

       Transform="Web.$(Configuration).config"

       Destination="$(OutDir)\_PublishedWebsites\$(TargetName)\Web.config"

/>

Element Name	Description
UsingTask	This element allows the MSBuild process to know in what assembly to find the TrasnformXml task. We need to note that the path is the one on the Build Server not a personal work station.
Target	The target is the action that runs the transformation process. We want this to be done AfterBuild has completed, and we only want this done when the IsTFSBuild argument is not empty. We add this argument to the TFS build definition.
Message	The message element helps us make a log entry, so that we can validate if this action is getting executed.
TransformXml	With this directive, we are executing a transformation task and merging the web.config file with another config file that is associated to the build target. For example for a release build, the $(Configuration) is set to “Release”. The result is merged and copied on the _publishedWebsites folder

*Note this can also be done as part of the build definition, but web.config is only applicable to web projects.

Save your file and unit test the change using the MSBuild application from the Visual Studio command shell. The command should look like this:

msbuild webproject.csproj /p:Configuration=Release /p:IsTFSBuild=True > out.log

On our desktop the OutDir is set to the Bin folder, so we can change the folder paths to read like this:

<TransformXml Condition="Exists('$(OutDir')"

       Source="Web.config"

       Transform Web.$(Configuration).config "

       Destination="$(OutDir)\Web.config"

/>

A merged web.config file should be copied to the Bin folder. Make sure to not include two target elements with the same name (AfterBuild). The last element always override the one that was declared before it.

Add a MSBuild Argument to the build definition

Now that the project file has been checked-in, we can test this on Team Build. First we need to add the IsTFSBuild argument to the build definition. This is done on Visual Studio. Add the parameter in the MSBuild Argument textbox as shown below:

Now, we can queue a build and take a look at the _PublishedWebSites folder. The web.config file should be merged with the configuration from the build target.  In case this did not happen, we can look at the build log file. We can look for the AfterBuild tag and find the message that was added to the project file. If this is not visible in the log file, we need to make sure that there are no typos on the changes and arguments that we are using.

Key Benefits

This process is a key component for the delivery a build and deployment automation process.

Management of configuration files for multiple environments.

With Visual Studio, we are able to create different build configurations with the Configuration Manager. This allows us to create different...

Read Article →

Custom Error Page 401 Access Denied when Using Windows Authentication

ASP.NET provides us with the ability to add custom pages for HTTP errors via Web.config CustomErrors tag.  This however does not work when we try to handle the 401 error under Windows authentication.  The reason for this is that this error (401) is raised during the Authorization request event on the HttpApplication process pipeline (see below), and Custom error settings are processed during the Action Method invocation (ASP.NET Handler.ProcessRequest event).

HTTP Process Pipeline

HTTP 401 Error browser Interaction

The common interaction between the browser and the server is as follows: The browser sends request with no authentication tokens. The server responds with a 401.2 HTTP error. The browser send authentication tokens if the user is already logged. If the user is not, the browser shows a login dialog. Even if the user is logged on, but he does not have the required role for this access, the server returns a 401.2 error and displays the Access Denied Page.

To provide a custom page for the Access Denied error, we should implement the following:

• Use httpErrors Configuration settings
• Allow Anonymous Access to content
• Allow Anonymous Access to controller actions

Use httpErrors Configuration settings

<configuration>

<system.webServer>

  <httpErrors errorMode="Custom" xdt:Transform="Insert">

    <remove statusCode="401" />

    <error statusCode="401" path="/Error/NotAuthorized" responseMode="ExecuteURL" />

  </httpErrors>

</system.webServer>

</configuration>

*Note that this setting only works when the application is running under IIS. This setting can be added to the Web.Release.config file, so when the deployment is done these settings are merged into the Web.config file. This is done with the help of the xdt:Transform=insert attribute.

With this setting, we are basically telling IIS that we want to use our own custom page when the 401 error is raised. Note that since the machine.config already has this setting defined, we need to first remove the entry. We can then add the entry with our own directives. In this case, we are doing a server execute in the response mode which requires a relative path to our page or route. There is also support to redirect to an absolute Url. For this case, we are using a controller action named Error.NotAuthorized that is relative to the application.

Allow anonymous access to content

Since our custom error page may need to download images and other resources, we need to add some settings in our web.config to indicate that these resources should be unprotected. This is important because even if we redirect to a custom view, the images and bundled resources would also raise a 401 error, and the page may not look as the rest of the application. To allow access to other content, we can add the following settings to our web.config file.

Location Setting	Paths that should be allowed
<location path="Error">     <system.web>       <authorization>         <allow users ="*" />       </authorization>     </system.web>   </location>	Images Error The route for our custom error page Bundles This is the path use to download bundled resources. Content For CSS files Favicon.ico Favorite icon *Note add one location setting per path *Use fiddler to get an idea of the resources that are downloaded

This setting allows all users to have access to the path defined by the location path attribute. We should note that if we are using a particular folder or route for our custom view, this also should be allowed to all users.

Allow Anonymous Access to controller action

When using a controller to provide the custom error page, we must allow anonymous access to the action that should process this error.  This can be done as follows:

[Authorize]

public class ErrorController : Controller

{

//

// GET: /Error/

public ActionResult Index()

{

return View("Error");

}

//GET: /Error/NotAuthorized

[AllowAnonymous]

public ActionResult NotAuthorized()

{

return View("NotAuthorized", "NoChromeLayout");

}

}

The AllowAnonymous attribute allows non-authenticated users to have access to this request. Our view and layout provide only content which does not required authentication.

How about using Application EndRequest handler?

Another approach often use is to implement a redirect on the end request handler (global.asx.cs) as follows:

 protected void Application_EndRequest()

{

// If the user is not authorized redirect to error page

if (Response.StatusCode == 401)

{

Response.ClearContent();

              Response.RedirectToRoute("NotAuthorized ");

}

}

The problem I find with this approach is that it can lead to endless redirect scenario. The end request event is raised multiple times during the request life cycle of a page. The page can download more  content like images, CSS, JavaScript files each one sending a request thus raising an EndRequest event. If one of those resources is not properly configured, the status code would also be 401 and another redirect would be initiated.

Conclusion

og-bit.com
CodeProject

The HttpError custom page configuration should be less intrusive approach to add custom error pages to our application as it is a configuration task instead of an implementation concern.

ASP.NET provides us with the ability to add custom pages for HTTP errors via Web.config CustomErrors tag.  This however does not work when w...

Read Article →