I have posted the following to the SQL Server forums as well, but no luck so far :

I am using SQL Server full text search with SQL 2008. The database we use is mostly read-only. It is updated by an external process on a weekly basis. The database has 4 tables on which full text index has been defined. I am only using the CONTAINS clause in my SQL to utilize the full text search, Nothing fancy.

The external process mentioned above, truncates the tables of the database and imports data to them from CSV files. After the external process completes its task, I am rebuilding the search index. This seems to work fine, for after a few minutes of the external process completion, I am able to get search results correctly. However the full text index seems to get empty for some reason after a few hours/days (I have not been able to find any pattern to when it happens). When this happens, I sometimes get either no search results, or partial results. If I manually rebuild the index from SQL Server Management tool, it works fine after that. Again the tables that have the index defined really do not have any data change, but I still have setup a sql server task to rebuild the search index both on the catalog as well as the individual tables. But this has not helped much

A few things I would like to understand:

1. What is causing the index to be empty (or partially filled)? And how to fix it?
2. Is there any way to monitor the status of the index to know how much has been rebuild?
3. The sql server jobs mentioned above : where can I see the history for them?

Please let me know if any other information is needed to help me solve this issue. It has been driving me nuts.

After learning few user controls and ASP.NET Master Pages, let us look at how menu handling can be done with ASP.NET. It is really difficult to maintain the website menu of a large website and it also takes more time. A menu is generally stores inside a file in ASP.NET to make its handling easier. The file where the menu is stores is known as web.sitemap file and it is kept under the root directory on the website.

Apart from the menu, ASP.NET also contains three different navigation controls as well.

1.    Sitemap Path

2.    Tree Views

3.    Dynamic Menus

Sitemap is actually an XML file and it can be created like a regular xml file with few special tags. Let us first look at few rules to create the web.sitemap file.

1.    The sitemap file has to have <sitemap> tag that surrounds the file content.

2.    The tag <sitemap> can have only one child node that is <siteMapNode> which actually is for home page.

3.    However, <siteMapNode> can have more than one child nodes which represents web pages of the website.

4.    Each of the <siteMapNode> has at least two basic attributes of page URL and page Title.

5.    A valid web.sitemap file cannot contain a duplicate URL and URL attribute can be left empty as well.

It is important to note again that a sitemap file must be placed under the root folder and the URL attributes must be assigned with the relative path of root folder.

Here is an example of a sitemap file.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<siteMap>
  <siteMapNode title="Home" url="/aspnet/providern_home.aspx">
    <siteMapNode title="Article Writing" url="/aspnet/ providern_article_writing.aspx">
      <siteMapNode title="SEO" url="/aspnet/providern_SEO.aspx"/>
      <siteMapNode title="Link Building" url="/aspnet/providern_link_building.aspx"/>
    </siteMapNode>
  </siteMapNode>
</sitemap>

It is also possible to use more than one sitemap files in order to portray the navigation structure of the website. For an example, a sitemap file in a root folder can be linked to a child sitemap file by referencing child sitemap file in siteMapNode element of the file.

After learning a web form and few of the ASP.NET controls, let us look at one of the prime feature of ASP.Net known as View-State. If you have been as ASP developer, you must know that once you submit the form, all the values from the form are cleared. Now, suppose the server comes up with error! Haah, that’s really a bad situation for user and has to refill all the information on the form, and that’s something you want to avoid as a developer.

ASP.NET View-State feature provides exactly the same. As a user if you find that the value you submitted still remains there even if the server gives an error and you press back button, think blindly that the web page is developed with ASP.NET View-State feature.

Basically, ASP.NET View-State is a method used by the ASP.NET pages to persevere the changes of state of a web form across the postbacks. Actual thing is that the ASP.NET maintains the view-state of your form. View-State defines the value of the web page at the time of submission to server. The current status of the web form is maintained with a hidden element places on each of the page with a form control.

It’s a default setting of ASP.NET to maintain the View-State of ASP.NET. In case, if you do not want to maintain a View-State of a web form, you shall include directive, <%@ Page EnableViewState="false" %> on top of the web form. Or else you can also add the element EnableViewState = “false” to any of the controls on the page.

A developer shall use ASP.NET View-State if he/she wants to achieve one of the below features in web page:

·         Save the form values between postbacks even without saving it in a user profile.

·         Save page control property values defined by a developer.

·         If a developer wants to store the view-state information in any data storage.

For Step 6 please click here.

ASP.Net Master Pages are one of the recent features added in ASP.Net. This feature allows a user to apply same kind of page layout to all the pages of the web application or a website. A web page which uses the master page is also known as the content page. Previously, there was consistent issue faced by users regarding not to have a feature that allow users to give a consistent look across multiple web pages of a single website. ASP.Net master pages are created to take care of deficiency in .NET.

In a website, you can find number of features same across multiple web pages of a single website such as copyright notices, menus, logos etc. Also, a website has a single layout design. Sometimes, even the control buttons across the web pages remains same. In such condition, Master pages take the control to apply same features across the multiple web pages.

Usually, the master page is differentiated by @Master directive which takes place of the directive @page which is used in the pages which are not the part of master pages. The master directive looks something like below:

<%@ Master Language="VB" %>

The directive @Master can contain almost the same directives which @Control directive can. I.e. below master page directive contains the name of the code file and allocates a class name for the master page.

<%@ Master Language="VB" CodeFile="MasterPageexam.masterexam.vb" Inherits="MasterPageexam" %>

In addition to above elements, @Master directive can also contains the top level HTML elements such as head, html tag, form tag etc. for a web page. I.e. user can use the html table tag and image tag to include table and image in the web page. User is allowed to use all the elements of ASP.NET and HTML as a part of the master page. This means ASP.NET master page is a well organized feature for the ASP.NET users to have same look and feel across multiple web pages with a little effort.

For Step 5 please click here.

We will learn about new control which is known as web user controls in step 3. Web User controls are the controls which are created and added to the web pages. To create a web user control on the web page is pretty much similar to creating a control for the desktop applications. The user control is ASP.NET is based in the class System.Web.UI.UserControl.

Adding a Web User Control on a web page is pretty simple. You can add user controls from the user Add Controls Windows of ASP.NET and by default they come with the .ascx extension. Instead of having @page directives, ASP.NET user controls contain @control directive which defines the configuration of the user control. The limitation of Web User Control is that they cannot be used stand alone and user must have to add it into the ASP.NET webpage just like other controls. ASP.NET web user control differs from the traditional web user control in a way that they shall have the elements like html, body or form only on the hosting page.

Add Web User Controls to Page

In order to add ASP.NET web user controls to the page, user must have to register it on hosting page. While registering, user has to denote the.ascx file containing web user control, a tag name and a tag prefix.

Once done with registering the control, user has to add the code for putting the control on the host page. In this case, below line of code is useful.

<uc1:links id="links1" runat="server"/>

Just like other controls on the page, you can see that even web user controls contains the property of runat = “server” which defines its dynamic nature on ASP.NET unlike traditional web user controls. If a web user control has the Web Server Control, you can also code a web user control to handle events requested by other child web user controls.

For Step 4 please click here.

Let us look at the second step of learning ASP.NET which is about learning HTML Server Controls. HTML Server Controls are just like HTML tags of old time with an additional attribute of “runat” which provides the functionality to run the control at server. This attribute gives ASP.NET control an advantage over the old age HTML controls, which allows the programmers to use it programmatically. i.e. If there exist <a> in a web page and it needs to be used dynamically with changing the address, the attribute runat = “server” can help to achieve this.

For almost all HTML elements, the server side HTML control exists. It’s pretty easy to create the HTML server control. It just requires applying the attribute runat=”server” in the regular HTML tag and it will create HTML controlled version for this tag. The HTML server controls have been contained inside the namespace System.Web.UI.HtmlControls. Because of the fact that the HTML server controls have been handled at the server side in the ASP.NET runtime, their properties can also be accessed somewhere else in the webpage through the programming. If you might have worked either with CSS, HTML or JavaScript, you might know how error-prone and cumbersome it can be to manipulate the inline styles and text within the HTML tags. HTML Server controls on the ASP.NET aims to resolve this issue by allowing to manipulate the data quickly with any of the NET languages.

Let us look at few control classes of HTML that uses the “runat” server attribute.

1.     HTMLForm
2.     HTMLButton
3.     HTMLAnchor
4.     HTMLImage
5.     HTMLInputButton
6.     HTMLInputImage
7.     HTMLTable
8.     HTMLInputText
9.     HTMLTextArea
10.   HTMLGenericControl

So, as described in this page, it’s really easy to use the HTML Server controls with the ASP.NET which will provide user an advantage of accessing HTML Server Controls through the programming.

For Step 3 please click here.

Just like any other technology, you will also find new terminologies in the ASP.NET as well. For a practical example, the word used to mention the web page is ASP.NET is known as a web form and it is the most important terminology of ASP.NET as it’s a central object in the language of ASP.NET. At a first look, the web forms look just like static HTML pages. However, they also contain .NET specific components in addition to the HTML elements as well as some code which executes on the server’s machine.

Web Forms

Each web form contains a tag <form runat = “server”>, which holds the ASP.NET specific elements in it which makes the page. However, there is no support for multiple forms. The actual structure of a simple web form is given below:

<%@ Page Language="language" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
     "http://www.abc.com/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 

<script runat="server"> 
… some code …

</script>

<html xmlns="http://www.abc.com/2003/xhtml"> 
<head runat="server"> 
 <title> Title of the Page</title> 
</head> 
<body> 
 <form id="form id" runat="server"> 
   … UI elements… 
 </form> 
</body> 
</html>

In order to programmatically access and change the data, the classs System.Web.UI.Page is being used. It is mandatory to define this class in the code file of a web form. In case, user is not generating a specific code file for a web form (when all the code is written in the file .aspx), the class is still gets generated, it is just that user is not able to see that.

In ASP.NET, wide ranges of UI elements are available to use in a web form which also includes the static HTML code. But, a user shall use the elements for which either values can be generated or changed. Such elements are known as controls in ASP.NET. There are numerous controls available in the ASP.NET as listed below:

1. Master Pages
2. Web User Controls
3. Web Server Controls
4. HTML Server Controls

We will learn about such controls in the coming chapters.

For Step 2 please click here.

Recently I was doing some code review and saw that one of the developers called a new SQL Server stored procedure sp_GetSomething. I explained to her that it is not a good practice to do so but thought I might write a blog so it is useful to other programmers out there as well.

So why should you not name your stored procedure with sp_? Here is why :

This is from SQL Server Books OnLine (BOL) – search for "Creating a Stored Procedure""

“System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

1. The stored procedure in the master database.
2. The stored procedure based on any qualifiers provided (database name or owner).
3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.”

See that ? So it has the following disadvantages:

1. There is a little performance hit since the sql server processor needs to go through the above list before it arrives at your stored procedure
2. There may be a stored procedure in the master database by the same name as yours and hence your stored procedure will not execute
3. Say you named your stored procedure something generic like sp_BackupData. Microsoft may choose the same name in their future SQL Server release and then the disadvantage mentioned in #2 above will materialize

As a general guidance give your stored procedures meaningful names and if you have to add a prefix, add the prefix based on the functionality group that the SP belongs to. Example : CUST_GetCustomerDetails. We will cover SQL Server naming conventions in one of the future blog posts

Hopefully this was helpful.

Technorati Tags: SQL Server,Stored procedure,naming conventions

So you (or your client) moved your email to Google Apps and now you want to use Google SMTP server to send emails from ASP.NET ? It is pretty straight forward. Read on…

• The System.Net contains the class SmtpClient which can be used to send the email from your ASP.NET page. Let us jump in the code (C#)

SmtpClient mailSender = new SmtpClient(“smtp.gmail.com”);

You can pass the smtp server location in the SmtpClient class’ constructor.

• Next specify the port:

mailSender.Port = 587;

• You can send the email using Google’s SMTP only by using authentication. So we will need to send the user name and password for the account from which you want to send the email (i.e your “From” email address):

System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(emailUserName, emailPassword);

where emailUserName is obviously your email address and emailPassword its password. Next use these credentials to send the email :

mailSender.Credentials = credentials ;

• Next tell the SMTP server NOT to use the default credentials and to use SSL

mailSender.EnableSsl = true;

mailSender.UseDefaultCredentials = false;

• Now send the email as you would normally in ASP.NET:

MailAddress toAddress = new MailAddress(“to@domain.com”);

MailAddress fromAddress = new MailAddress(“from@domain.com”);

MailMessage message= new MailMessage(fromAddress , toAddress );

message.Subject = “Email Subject”;

message.Body = “Email Message”;

mailSender .Send(message);

Of course as always recommend putting the smtp client (“smtp.gmail.com”) , port number. emailUserName and emailPassword in some kind of configuration (ASP.NET web.config file, app.config , SQL Server database etc)

This means you need to know the email user name and password of the account that the email is sent from. I have not found any other solution than physically creating this email account and using it. If anyone has found a better solution for this, please let me know

In the first part of this series we looked into Output Caching. In this part, we will see the options available with partial page caching. Just like output caching you can cache specific sections of a web page using partial page caching. This is achieved by caching user controls used for building the ASP.NET page.

User Controls are typically designed to be shared across multiple pages to enable reuse of their functionality. To achieve partial page caching, you can use the @OutputCache directive : using its default attributes will enable to cache the user control on a per page basis. By setting the Shared = “true” attribute, you can share the user controls among multiple pages. This leads to memory savings on the server, especially if your web site is made up of large number of pages (it always is, isn’t it ?:))

<%@ OutputCache Duration=”30” VaryByParam=”*” Shared=”true” %>

The above directive tells the .NET framework that a copy of the user control is cache and shared amongst all the pages using it for 30 seconds and to vary the cache by any and all parameters