How To Enable SQL Full Text Indexing

The following steps will tell you if your database version supports full text searching and if so how to enable it.

  1. Open SQL server management console
  2. Right click on the database and select properties
  3. On the left select files
  4. If “Full-Text Indexing” is greyed out it means that full-text indexing is not enabled.
    NOTE: If you are using SQL Server 2012 Please see the SQL Server 2012 notes at the bottom of this article.

How to enable Full Text Searching

  1. Click on the New Query button top left of the screen and make sure the database is selected from the drop down list below the button.
  2. This query will tell if Full Text Search is enabled:
    SELECT DATABASEPROPERTY('REPLACE_WITH_NAME_OF_DATABASE',
                            'IsFulltextEnabled')

    then click the Execute button

  3. If you get a 1 in the results window Full Text Search is already enabled.
  4. If the result is null, you may have the database name incorrect.
  5. If the result is zero, Full Text Search can be enabled by running these queries:
    exec sp_fulltext_database 'enable'
  6. Now run this again:
    SELECT DATABASEPROPERTY('REPLACE_WITH_NAME_OF_DATABASE',
                            'IsFulltextEnabled')

If you get a one, that shows that Full Text Search is now enabled. If the response is still 0, then your installation of SQL Server cannot enable Full Text Search. Some reasons for this include:

  • You may need to change the install of SQL Server to include Full Text Search
  • Your SQL Server edition does not support Full Text Search.

You will need to ensure that any installation of SQL Server has Full Text Search enabled, standard and enterprise editions have this by default however express editions need the advanced services version to be installed. SQL Express Web Edition does not support Full Text Search at all.

Index The Tables

Once Full Text Search has been enabled you need to the build the indexes that will be used to return results of your search query.

The following will show you how to enable this and only need to be done once:

  1. Open the MS SQL Server Management Studio and login
  2. Expand the databases folder
  3. Expand the database
  4. Expand the Storage folder
  5. Right Click on Full Text Catalogs and select New Full-Text Catalog
  6. Provide the name as database_fullcatalog then click OK
  7. Right Click on Full Text Catalogs and select Refresh and you new catalog should appear

Define The Indexed Data

image

Once you have created the catalogs they need to be told what data to index:

  1. Right click on the database_fullcatalog catalog and select properties
  2. Click on Tables/Views
  3. From the table list on the left select a table and move it to the right
  4. from the list of columns tick the columns you want in the full catalog
  5. Click Ok

The catalogs will auto rebuild each time any of the data changes for the selected columns.

Another way is from the table design. Right click and select Fulltext index. Here you can choose the columns for this table.

image

SQL Server 2012 Notes

It appears that there is a bug in SQL Server 2012 SP1 where the server will report that Full Text Search is not supported in this edition of SQL Server when it clearly is. The workaround is to create the initial catalog by using a query:

CREATE FULLTEXT CATALOG database_fulltext;

Run each query seperatly against the database then you can follow the “Define The Indexed Data” section.

This bug is supposed to be fixed within the SP2 timeframe according to Microsoft.

How to remove HTML tags from data with SQL

The purpose of this article is to provide a way of cleaning up of HTML tags within the data. When we use various styles or tabular format data in UI using Rich Text Editor/ Rad Grid etc, it will save data in database with HTML tags.

When you save in database this kind of field you have:

An HTML element starts with a start tag (<p>) and ends with end tag (<p/>) and everything between Start tag and End tag is HTML element. e.g.

<b>Following are the popular databases: <br /> </b>1. SQL Server <br /> 2. Oracle <br /> 3.
Teradata <br /> 4. Sybase

We are using SQL Server CHARINDEX function to get the positions of ‘<’ and ‘>’ and once found replacing the string between <….> with blank using STUFF function. We are using WHILE Loop that will run till presence of ‘<’ & ‘>’ in the string. Below is the UDF script that performs HTML Tags clean up from data.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[usp_ClearHTMLTags]  
    (@String NVARCHAR(MAX))  
    RETURNS NVARCHAR(MAX)  

AS  
    BEGIN  
        DECLARE @Start INT,  
                @End INT,  
                @Length INT  

        WHILE CHARINDEX('<', @String) > 0 AND CHARINDEX('>', @String, CHARINDEX('<', @String)) > 0  
        BEGIN  
            SELECT  @Start  = CHARINDEX('<', @String),   
                    @End    = CHARINDEX('>', @String, CHARINDEX('<', @String))  
            SELECT @Length = (@End - @Start) + 1  

            IF @Length > 0  
            BEGIN  
                SELECT @String = STUFF(@String, @Start, @Length, '')  
             END  
         END  
        RETURN @String  
    END  
GO

By using above UDF, We can clean the HTML tags from the data.

SQL SERVER – Find Current Location of Data and Log File of All the Database

As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary files I have left there taking up my hard drive space. 

I run following query to find out which .mdf and .ldf files are used and delete all other files. If your SQL Server is up and running OS will not let you delete .mdf and .ldf files any way giving you error that file already in use. This list also helps sometime to do documentation of which files are in being used by which database.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output of files used by my SQL Server instance.

How to strip all HTML tags and entities and get clear text?

I was encouraged to write this Tip/Trick because of so many questions received for this issue.
Suppose you're having a bunch of HTML strings, but you just want to remove all the HTML tags and want a plain text.

You can use Regex to come to the rescue.

The Regex I had developed before was more cumbersome, then Chris made a suggestion, so I will now go further with the regex suggested by Chris that is a "\<[^\>]*\>".

I have tested it for many cases. It detects all types of HTML tags, but there may be loopholes inside so if you find any tags which are not passing through this Regex, then kindly inform me about the same.

Regex Definition

  • Regex :\<[^\>]*\>
    • Literal >
    • Any character that NOT in this class:[\>], any number of repetations
    • Literal >

Visual Basic

''' 
''' Remove HTML from string with Regex
''' 
Function StripTags(ByVal html As String) As String
    ' Remove HTML tags.
    Return Regex.Replace(html, "<.*?>", "")
End Function

C#

/// 
/// Remove HTML from string with Regex
/// 
public static string StripTags(string source)
{
    return Regex.Replace(source, "<.*?>", string.Empty);
}

Happy coding!

UWA Message Dialog

To show a Message Dialog in Windows 10 (Universal Windows App) you have to use Windows.UI.Popups

using Windows.UI.Popups;

After you can create a basic dialog like this:

// Create the message dialog and set its content; it will get a default "Close" button since there aren't any other buttons being added
var messageDialog = new MessageDialog("You've exceeded your trial period.");

// Show the message dialog and wait
var res = messageDialog.ShowAsync();

If you want to customize your button, you can use another way:

/// 
/// Click handler for the 'CancelCommandButton' button.
/// Demonstrates setting the command to be invoked when the 'escape' key is pressed.
/// Also demonstrates retrieval of the label of the chosen command and setting a callback to a function.
/// A message will be displayed indicating which command was invoked.
/// In this scenario, 'Try again' is selected as the default choice, and the 'escape' key will invoke the command named 'Close'
/// 
private async void CancelCommandButton_Click()
{
	// Create the message dialog and set its content
        var messageDialog = new MessageDialog("No internet connection has been found.");

	// Add commands and set their callbacks; both buttons use the same callback function instead of inline event handlers
	messageDialog.Commands.Add(new UICommand("Try again", new UICommandInvokedHandler(this.CommandInvokedHandler)));
	messageDialog.Commands.Add(new UICommand("Close", new UICommandInvokedHandler(this.CommandInvokedHandler)));

	// Set the command that will be invoked by default
	messageDialog.DefaultCommandIndex = 0;

        // Set the command to be invoked when escape is pressed
        messageDialog.CancelCommandIndex = 1;

        // Show the message dialog
        await messageDialog.ShowAsync();
}

#region Commands
/// 
/// Callback function for the invocation of the dialog commands.
/// 
/// The command that was invoked.
private void CommandInvokedHandler(IUICommand command)
{
	// Display message showing the label of the command that was invoked
        Debug.WriteLine("The '" + command.Label + "' command has been selected.");
}
#endregion

Using SQLite in Windows 10 Universal apps

Using SQLite in Windows 10 Universal apps is really easy even in this preview phase. Even though Entity Framework 7 support for Windows 10 Universal apps is almost here, you still might decide to just continue using a lighter SQLite.Net-PCL library that you're used to since Windows Phone 8/WinRT.

If you are using Visual Studio 2015 RTM and 10240 SDK, there's now the official (no more pre-release stuff) SQLite VSIX package that you can download from SQLite.org. Search for "Universal App Platform" and you're good to go! The rest of the blog post should apply to this version as well.

sqlite-latest_wcqqnu

The next step is to add the SQLite.Net-PCL library that I already mentioned. Use the (redesigned) NuGet to do that.

sqlite_uap_2

It will add two references

  • SQLite.Net
  • SQLite.Net.Platform.WinRT

Now, remember the VSIX package (Visual Studio extension) installed earlier? It installs SQLite extensions that you need to reference by simply right-clicking on References and choosing "Add Reference..." and then finding the right reference under Windows Universal -> Extensions.

sqlite-latest-extension

And that's it! You can start using SQLite!

To test it, I defined a simple model class called User.

public class User  
{
    public int Id { get; set; }
    public string Name { get; set; }
}

And then I created a table which will hold User entities.

var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
 
using (SQLite.Net.SQLiteConnection conn = 
       new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))  
{
    conn.CreateTable<user>();
}

Adding SQLite support to Windows 10 Universal apps is really simple! After all of the extensions are added, and the ORM/client library fetched over NuGet, the usage is the same as before. This is really neat for simpler scenarios and until Entity Framework 7 officially, fully supports Windows 10 Universal apps.

Print a table structure in SQL Server

I was wondering how it's possible to get the structure of one or more table(s) in a database, using possibly a SELECT; I need to retrieve the composition of the DB I'm working onto to attach it in some docs about the webapp I'm currently developing.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME

Track events for Google Analytics and Piwik

After my post of yesterday, I've worked for creating a script to integrate Google Analytics (new version with analytics.js) and Piwik (Piwik is the leading open-source analytics platform similar to Google Analytics. You can download it from its site or directly in your IIS with WebMatrix).

With this code you have only one function to call in every part of your page. Automatically the function detects download, email, phone number, external links and tracks them. You can insert in an anchor a code like:

<a href="http://www.puresourcecode.com/" 
   onclick="TrackEvent('Link to my site', 'PSC', 
            'Click from the user', 'Url or something else');">
   Visit PureSourceCode
</a>

You find the code for that here:

// Piwik
var _paq = _paq || [];
_paq.push(["setDocumentTitle", document.domain + "/" + document.title]);
_paq.push(["setCookieDomain", "*"]);
_paq.push(["setDomains", ["*"]]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function () {
    var u = "//a.esia.info/";
    _paq.push(['setTrackerUrl', u + 'piwik.php']);
    _paq.push(['setSiteId', 28]);
    var d = document, g = d.createElement('script'), s = d.getElementsByTagName('script')[0];
    g.type = 'text/javascript'; g.async = true; g.defer = true; g.src = u + 'piwik.js'; s.parentNode.insertBefore(g, s);
})();

// Google analytics
(function (i, s, o, g, r, a, m) {
    i['GoogleAnalyticsObject'] = r; i[r] = i[r] || function () {
        (i[r].q = i[r].q || []).push(arguments)
    }, i[r].l = 1 * new Date(); a = s.createElement(o),
    m = s.getElementsByTagName(o)[0]; a.async = 1; a.src = g; m.parentNode.insertBefore(a, m)
})(window, document, 'script', '//www.google-analytics.com/analytics.js', 'ga');

ga('create', 'UA-XXXXXXXX-1', 'auto');
ga('send', 'pageview');

if (typeof jQuery != 'undefined') {
    jQuery(document).ready(function ($) {
        var filetypes = /\.(zip|exe|dmg|pdf|doc.*|xls.*|ppt.*|mp3|txt|rar|wma|mov|avi|wmv|flv|wav)$/i;
        var baseHref = '';
        if (jQuery('base').attr('href') != undefined) baseHref = jQuery('base').attr('href');

        jQuery('a').on('click', function (event) {
            var el = jQuery(this);
            var track = true;
            var href = (typeof (el.attr('href')) != 'undefined') ? el.attr('href') : "";
            var isThisDomain = href.match(document.domain.split('.').reverse()[1] + '.' + document.domain.split('.').reverse()[0]);
            if (!href.match(/^javascript:/i)) {
                var elEv = []; elEv.value = 0, elEv.non_i = false;
                if (href.match(/^mailto\:/i)) {
                    elEv.category = "email";
                    elEv.action = "click";
                    elEv.label = href.replace(/^mailto\:/i, '');
                    elEv.loc = href;
                }
                else if (href.match(filetypes)) {
                    var extension = (/[.]/.exec(href)) ? /[^.]+$/.exec(href) : undefined;
                    elEv.category = "download";
                    elEv.action = "click-" + extension[0];
                    elEv.label = href.replace(/ /g, "-");
                    elEv.loc = baseHref + href;
                }
                else if (href.match(/^https?\:/i) && !isThisDomain) {
                    elEv.category = "external";
                    elEv.action = "click";
                    elEv.label = href.replace(/^https?\:\/\//i, '');
                    elEv.non_i = true;
                    elEv.loc = href;
                }
                else if (href.match(/^tel\:/i)) {
                    elEv.category = "telephone";
                    elEv.action = "click";
                    elEv.label = href.replace(/^tel\:/i, '');
                    elEv.loc = href;
                }
                else if (href.match(/^call\:/i)) {
                    elEv.category = "telephone";
                    elEv.action = "click";
                    elEv.label = href.replace(/^call\:/i, '');
                    elEv.loc = href;
                }
                else track = false;

                if (track) {
                    TrackEvent(elEv.category.toLowerCase(), elEv.action.toLowerCase(), elEv.label.toLowerCase(), elEv.value);
                    if (el.attr('target') == undefined || el.attr('target').toLowerCase() != '_blank') {
                        setTimeout(function () { location.href = elEv.loc; }, 400);
                        return false;
                    }
                }
            }
        });
    });
}

/* Tracking events */
function TrackEvent(category, action, label, value) {
    // add to Google Analytics
    ga('send', 'event', category, action, label, value);
    // add to Westhill Analtics
    _paq.push(['trackEvent', category, action, label, value]);
}

In attach the code trackingGenerator.js (4KB)

Templated Razor Delegates

What’s that? I’ll let the code do the speaking.


@{
  Func<dynamic, object> b = @<strong>@item</strong>;
}
<span>This sentence is @b("In Bold").</span>

That could come in handy if you have friends who'll jump on your case for using the bold tag instead of the strong tag because it’s "not semantic".

Note that the delegate that's generated is a Func<T, HelperResult>. Also, the @item parameter is a special magic parameter. These delegates are only allowed one such parameter, but the template can call into that parameter as many times as it needs.

The example I showed is pretty trivial. I know what you’re thinking. Why not use a helper? Show me an example where this is really useful. Ok, you got it!

Suppose I wrote this really cool HTML helper method for generating any kind of list.

public static class RazorExtensions {
    public static HelperResult List<T>(this IEnumerable<T> items, 
      Func<T, HelperResult> template) {
        return new HelperResult(writer => {
            foreach (var item in items) {
                template(item).WriteTo(writer);
            }
        });
    }
}

This List method accepts a templated Razor delegate, so we can call it like so.


@{
  var items = new[] { "one", "two", "three" };
}

<ul>
@items.List(@<li>@item)
</ul>

As I mentioned earlier, notice that the argument to this method, <span class="asp">@</span>&lt;li><span class="asp">@</span>item&lt;/li> is automatically converted into a Func&lt;dynamic, HelperResult> which is what our method requires.

Now this List method is very reusable. Let’s use it to generate a table of comic books.


@{
    var comics = new[] { 
        new ComicBook {Title = "Groo", Publisher = "Dark Horse Comics"},
        new ComicBook {Title = "Spiderman", Publisher = "Marvel"}
    };
}

<table>
@comics.List(
  @<tr>
    <td>@item.Title
    <td>@item.Publisher
  </tr>)
</table>

Creating reusable HTML components in ASP.NET MVC using Razor

Whilst working on a side project I started to notice I was using a lot of the same HTML to create floating boxes. Whilst these boxes looked the same, the content of them changed quite dramatically; for instance, some of the content boxes contained forms, others contained straight text, like so:

<div class="panel">
         <div class=panel-inner">
             <h2 class="panel-title">Panel Title</h2>
             <div class="panel-content">
                 /* Can I pass content to be rendered in here here? */
             </div>
         </div>
     </div>
</div>

 

As my side project progressed and grew, I found myself making more and more modifications to these HTML components so I started to look how I can encapsulate the component for greater flexibility and extensibility as my project progressed.

The solution I ended up with was creating a HTML extension modelled off of the way the the Html.BeginForm() extension works, by writing directly to the view's context and then returning an instance of IDisposable, with the call to disposing of the context writing the closing HTML statements of my component to the view context - essentially wrapping the contents passed into the HTML extension in my HTML component.

Below is an example of what the code looks like:

namespace System.Web.Mvc
{
    public static class HtmlHelperExtensions
    {
        public static HtmlPanelComponent PanelComponent(this HtmlHelper html, string title)
        {
            html.ViewContext.Writer.Write(
            "<div class=\"panel\">" +
            "<div class=\"panel-inner\">" +
            "<h2 class=\"panel-title\">" + title + "</h2>" +
            "<div class=\"panel-content\">"
            );

            return new HtmlPanelComponent(html.ViewContext);
        }
    }

    public class HtmlPanelComponent : IDisposable
    {
        private readonly ViewContext _viewContext;
        public HtmlPanelComponent(ViewContext viewContext)
        {
            _viewContext = viewContext;
        }
        public void Dispose()
        {
            _viewContext.Writer.Write(
            "</div>" +
            "</div>" +
            "</div>"
            );
        }
    }
}

Using this new HTML extension I'm now able to reuse my HTML component and fill the panel's content in with whatever I please, like so:

@using (Html.PanelComponent("Panel Title"))
{
    <p>Welcome back, please select from the following options</p>
    <a href="#">Profile</a>
    <a href="#">My Defails</a>
}

Advertsing

125X125_06


Subscribe to our mailing list

* indicates required


TagCloud

MonthList

CommentList