GridView column header merging in ASP.NET

Introduction
The ASP.NET GridView data control is a very rich tool for displaying tabular data in web applications and also it is easy to achieve a great degree of customization with this control. In this article, we will examine how to merge a grid view column header and highlight the grid cells for a timesheet application. There are several ways to achieve this, we are going to see one among them. Hope this will be useful for those who work with timesheet kind of applications.

Requirement  
We have a requirement to create an ASP.NET webpage to display the timesheet summary details in tabular format as shown in figure 1. Each Employee Name header should be split into three columns as In Time, Out Time, and Status. Personal leaves and weekend holiday cells should be highlighted with colors as shown below.

Figure 1.

Solution    

The above requirement can be achieved through the ASP.NET GridView data control. GridView row objects are created before the GridView control is rendered on to the page. Most cell formatting activities can be achieved by capturing the RowDataBound and RowCreated events. 

Using the code  

Let’s create a sample timesheet web page to explain a solution for this problem. To get this first we have to populate sample timesheet data using code. Please download the attached source code and go through the data manager class functionality to populate employee and timesheet data using data tables.

Next, we will bind timesheet data to a GridView control. Before binding we should customize the data source table as follows using employee and timesheet data. Refer to the following table and code. 

Figure 2.

Now we will see the code to populate the GridView. The following code reads the employee and timesheet data from the sample data tables and generates a new timesheet data table as shown in the above figure.    

private string _seperator = "|";

protected void btnShow_Click(object sender, EventArgs e)
{
  // Reading employee data
  DataTable employeeData = DataManager.GetEmployeeData();
  // Reading time sheet data for the employee for a data range
  DataTable timeSheetData = DataManager.GetTimeSheetData();

  // Creating a customized time sheet table for binding with data grid view
  var timeSheet = new DataTable("TimeSheet");

  timeSheet.Columns.Add("DENTRY" + _seperator + "");

  // creating colum header for each employee data
  foreach (DataRow item in employeeData.Rows)
  {
    string columnName = item["EMPLOYEENAME"].ToString().Trim();
    timeSheet.Columns.Add(columnName + _seperator + "InTime");
    timeSheet.Columns.Add(columnName + _seperator + "OutTime");
    timeSheet.Columns.Add(columnName + _seperator + "Status");
   }

   // setting start date
   DateTime currentDate = Convert.ToDateTime("05/21/2012");

   //creating 10 days time sheet data for each employee
   for (int i = 0; i < 9; i++)
   {
     var dataRow = timeSheet.NewRow();
     FillTimeSheetRow(timeSheetData, employeeData, currentDate, dataRow);
     timeSheet.Rows.Add(dataRow);
     currentDate = currentDate.AddDays(1);
   }

   //Binding time sheet table with data grid view
   timeSheetGrid.DataSource = timeSheet;
   timeSheetGrid.DataBind();
}

private void FillTimeSheetRow(DataTable timeSheetData, 
        DataTable employees, DateTime currentDate, DataRow dataRow)
{
     dataRow["DENTRY" + _seperator + 
             ""] = currentDate.ToString("dd-MMM-yyyy");

      foreach (DataRow row in employees.Rows)
      {
           string columnName = row["EMPLOYEENAME"].ToString().Trim();
           string employeeId = (row["EMPLOYEEID"]).ToString().Trim();

            var dayStatus = "";
            // updating status as holiday for week ends
             if (currentDate.DayOfWeek.ToString() == "Saturday" || 
                     currentDate.DayOfWeek.ToString() == "Sunday")
             {
                  dayStatus = "HDAY";
              }

              // Fetching time sheet entry for the current data from time sheet data
               DataRow[] result = timeSheetData.Select("EMPLOYEEID='" + employeeId + 
                     "' AND DENTRY='" + currentDate.ToShortDateString() + "'");

                if (result.Length != 0)
                {
                    string status = result[0]["STATUS"].ToString();
                    dataRow[columnName + "|InTime"] = result[0]["INTIME"].ToString();
                    dataRow[columnName + "|OutTime"] = result[0]["OUTTIME"].ToString();
                    dayStatus = status;
                }
                dataRow[columnName + "|Status"] = dayStatus;
            }
        }
    }
}

After modifying the timesheet data table, we have to capture two events (RowDataBound and RowCreated) for changing the cell background color and merging the column headers.

Changing the GridView cell background colors.
Before the timesheet GridView control can be rendered on to the page, we have to capture the RowDataBound event. And it is triggered whenever the GridViewRow object is bound to timesheet data. A GridviewRowEventArgs object is passed to the handler method, which will help us access properties of every row. After getting the row cells we can check for the cell text property and we can change the background color for that particular cell using the BackColor property. Refer to the following code snippet.

protected void timeSheetGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
  //number of cells in the row
  int cellCount = e.Row.Cells.Count;

  //iterating through every cells and check for the status for each employees
  for (int item = 3; item < cellCount; item = item + 3)
  {
    if (e.Row.Cells != null)
    {
      var cellText = e.Row.Cells[item].Text;
      switch (cellText)
      {
       case "WDAY"://Working Day
        e.Row.Cells[item].VerticalAlign = VerticalAlign.Middle;
       break;
       case "LEAVE"://Leave
        e.Row.Cells[item].VerticalAlign = VerticalAlign.Middle;
        e.Row.Cells[item].BackColor = Color.FromArgb(255, 255, 000);
        e.Row.Cells[item - 1].BackColor = Color.FromArgb(255, 255, 000);
        e.Row.Cells[item - 2].BackColor = Color.FromArgb(255, 255, 000);
       break;
       case "HDAY"://Holiday
        e.Row.Cells[item].VerticalAlign = VerticalAlign.Middle;
        e.Row.Cells[item].BackColor = Color.FromArgb(255, 0, 0);
        e.Row.Cells[item - 1].BackColor = Color.FromArgb(255, 0, 0);
        e.Row.Cells[item - 2].BackColor = Color.FromArgb(255, 0, 0);
       break;
      }
    }
  }
}

2. Merging GridView column headers
Just like RowDataBound, the RowCreated event gets fired whenever a row in the GridView is created. This helps us code custom functionality to a grid row and allows to implement code to merge cell headers depending on the RowType of the GridviewRowEventArgs object arguments. After customization we can hide the default header row by making the Visible property false. Refer to the following code snippet:

protected void timeSheetGrid_RowCreated(object sender, GridViewRowEventArgs e)
{
  //If row type= header customize header cells
  if (e.Row.RowType == DataControlRowType.Header)
  CustomizeGridHeader((GridView)sender, e.Row, 2);
}

private void CustomizeGridHeader(GridView timeSheetGrid, 
             GridViewRow gridRow, int headerLevels)
{
     for (int item = 1; item <= headerLevels; item++)
     {
        //creating new header row
        GridViewRow gridviewRow = new GridViewRow(0, 0, 
        DataControlRowType.Header, DataControlRowState.Insert);
        IEnumerable<IGrouping<string,>> gridHeaders = null;

        //reading existing header 
        gridHeaders = gridRow.Cells.Cast<tablecell>()
        .Select(cell => GetHeaderText(cell.Text, item))
        .GroupBy(headerText => headerText);

        foreach (var header in gridHeaders)
        {
            TableHeaderCell cell = new TableHeaderCell();

            if (item == 2)
            {
                cell.Text = header.Key.Substring(header.Key.LastIndexOf(_seperator) + 1);
            }
            else
            {
                cell.Text = header.Key.ToString();
                if (!cell.Text.Contains("DENTRY"))
                {
                    cell.ColumnSpan = 3;
                }
            }
            gridviewRow.Cells.Add(cell);
        }
        // Adding new header to the grid
        timeSheetGrid.Controls[0].Controls.AddAt(gridRow.RowIndex, gridviewRow);
    }
    //hiding existing header
    gridRow.Visible = false;
}

private string GetHeaderText(string headerText, int headerLevel)
{
    if (headerLevel == 2)
    {
        return headerText;
    }
    return headerText.Substring(0, headerText.LastIndexOf(_seperator));
}

Now we will see the rest of the code-behind for the GridView control:

<table>
<tbody><tr>
<td>
<asp:button id="btnShow" runat="server" 
      text="Show" onclick="btnShow_Click">
</asp:button></td>
</tr>
<tr>
<td><asp:panel id="pnlContent" scrollbars="Auto" 
    style="background-color: white; width: 980px; height: 500px; " runat="server">
<asp:gridview id="timeSheetGrid" runat="server" cellpadding="4" 
        forecolor="#333333" gridlines="Both" bordercolor="#738DA5" 
        cellspacing="1" width="100%" 
        onrowcreated="timeSheetGrid_RowCreated" 
        onrowdatabound="timeSheetGrid_RowDataBound">
   <editrowstyle backcolor="#999999">
   <footerstyle backcolor="#5D7B9D" 
          font-bold="True" forecolor="White">
   <headerstyle backcolor="#465c71" font-bold="False" 
      forecolor="White" wrap="false" font-size="Small">
   <pagerstyle backcolor="#284775" forecolor="White" 
          horizontalalign="Center">
   <rowstyle backcolor="white" forecolor="black" 
          wrap="false" font-size="Small">
   <selectedrowstyle backcolor="#E2DED6" font-bold="True" 
          forecolor="#333333">
   <sortedascendingcellstyle backcolor="#E9E7E2">
   <sortedascendingheaderstyle backcolor="#5216C8C">
   <sorteddescendingcellstyle backcolor="#FFFDF8">
   <sorteddescendingheaderstyle backcolor="#6F8DAE">
   </sorteddescendingheaderstyle>
   </sorteddescendingcellstyle>
   </sortedascendingheaderstyle>
   </sortedascendingcellstyle>
   </selectedrowstyle>
   </rowstyle>
   </pagerstyle>
   </headerstyle>
   </footerstyle>
   </editrowstyle>
   </asp:gridview>
   </asp:panel>
   </td>
</tr>
</tbody>
</table>

SQL SERVER – Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Here is the question – How to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter. Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code. When we have almost all of our code in Stored Procedure it is very common requirement that we have need of one stored procedure’s result to be passed as another stored procedure’s parameter.

Let us try to understand this with a simple example. Please note that this is a simple example, the matter of the fact, we can do the task of these two stored procedure in a single SP but our goal of this blog post is to understand how we can pass the result of one SP to another SP as a parameter.

Let us first create one Stored Procedure which gives us square of the passed parameter.

-- First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam
-- Additional Code
RETURN (@MyFirstParamSquare)
GO

Now let us create second Stored Procedure which gives us area of the circle.

-- Second Stored Procedure
CREATE PROCEDURE FindArea
@SquaredParam INT
AS
DECLARE @AreaofCircle FLOAT
SELECT @AreaofCircle = @SquaredParam * PI()
RETURN (@AreaofCircle)
GO

You can clearly see that we need to pass the result of the first stored procedure (SquareSP) to second stored procedure (FindArea). We can do that by using following method:

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
DECLARE @ParamtoPass INT, @CircleArea FLOAT
-- First SP
EXEC @ParamtoPass = SquareSP 5
-- Second SP
EXEC @CircleArea = FindArea @ParamtoPass
SELECT @CircleArea FinalArea
GO

You can see that it is extremely simple to pass the result of the first stored procedure to second procedure.

You can clean up the code by running the following code.

-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO

SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

SQL STORED PROCEDURE

“How do I insert the results of the stored procedure in my table?”

This question has two fold answers – 1) When the table is already created and 2) When the table is to be created run time. In this blog post we will explore both the scenarios together.

However, first let us create a stored procedure which we will use for our example.

CREATE PROCEDURE GetDBNames
AS
SELECT name, database_id
FROM sys.databases
GO

We can execute this stored procedure using the following script.

EXEC GetDBNames

Now let us see two different scenarios where we will insert the data of the stored procedure directly into the table.

1) Schema Known – Table Created Beforehand

If we know the schema of the stored procedure resultset we can build a table beforehand and execute following code.

CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
-- Select Table
SELECT *
FROM #TestTable;

The disadvantage of this code is that if due to any reason the stored procedure returns more or less columns it will throw an error.

2) Unknown Schema – Table Created at Runtime

There are cases when we do know the resultset of the stored procedure and we want to populate the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;

The disadvantage of this code is that it bit complicated but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

I will be interested to know which of the above method do you use in your projects? and why?

Sending e-mail in asp.net using smtp gmail server

public string SendMail(string toList, string from, string ccList, string subject, string body)
{

    MailMessage message = new MailMessage();
    SmtpClient smtpClient = new SmtpClient();
    string msg = string.Empty;
    try
    {
        MailAddress fromAddress = new MailAddress(from);
        message.From = fromAddress;
        message.To.Add(toList);
        if (ccList != null && ccList != string.Empty)
            message.CC.Add(ccList);
        message.Subject = subject;
        message.IsBodyHtml = true;
        message.Body = body;
        smtpClient.Host = "smtp.gmail.com";   // We use gmail as our smtp client
        smtpClient.Port = 587;
        smtpClient.EnableSsl = true;
        smtpClient.UseDefaultCredentials = true;
        smtpClient.Credentials = new System.Net.NetworkCredential("Your Gmail User Name", "Your Gmail Password");

        smtpClient.Send(message);
        msg = "Successful<BR>";
    }
    catch (Exception ex)
    {
        msg = ex.Message;
    }
    return msg;
}

Validate Date of Birth is not greater than current date using jQuery

jQuery, jQuery DatePicker, jQuery UI, jQuery UI DatePicker

This is a common situation where Date of birth needs to validated against the current or today's date. I have to implement the same functionality for my project. I have used jQuery UI datepicker control to select date and textbox was read only, it was pretty easy to implement.

All I needed to do is to stop datepicker control to disable dates greater than today's date. jQuery UI datepicker control provides an option to set the max date. If its value is set to "-1d" then all the dates after current date will be disabled.

Note: I have set the year range statically but this can be set programmatically.

$(document).ready(function(){
  $("#txtDate").datepicker(
  {
  yearRange:
  '<%=(System.DateTime.Now.Year - 150).ToString()%>:
  <%=System.DateTime.Now.Year.ToString() %>',
  changeMonth:true,
  changeYear:true,

  maxDate: '-1d'
  });
});

Validate email address using jQuery

jQuery, jQuery Code Examples, Jquery Code Snippets, jQuery Codes

This is a very basic functionality to validate the email address. In this post, I will show you how to validate the email address using jQuery. To validate the email address, I have created a separate function which based on email address, returns true or false. Email address validation is done using regular expression.

Earlier I had posted about Validate Date format using jQuery, Validate Date using jQuery and Validate Phone numbers using jQuery. And in this post, find jQuery way to Validate email address.

The validateEmail() functions (created below) will accept a parameter which is nothing but the email address and using regex it validates the email address. If it is correct then it returns true, otherwise false.

function validateEmail(sEmail) {
    var filter = /^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$/;
    if (filter.test(sEmail)) {
        return true;
    }
    else {
        return false;
    }
}​
One just need to make a call to this function to validate the email address. For demo, I have used on click event of button. But It can be used on blur event of textbox or any another event.
view sourceprint?

$(document).ready(function() {
   $('#btnValidate').click(function() {
        var sEmail = $('#txtEmail').val();
        if ($.trim(sEmail).length == 0) {
            alert('Please enter valid email address');
            e.preventDefault();
        }
        if (validateEmail(sEmail)) {
            alert('Email is valid');
        }
        else {
            alert('Invalid Email Address');
            e.preventDefault();
        }
    });
});

Validate phone numbers using jQuery

jQuery, jQuery Code Examples, Jquery Code Snippets, jQuery Codes, jQuery Validation

Now days registration forms have phone numbers as mandatory field. But how to validate the entered phone number is correct or not? In this post, I will show you how to validate the phone number using jQuery. To validate the phone number, I have created a function which based on input, returns true or false. It checks for the validity of phone number using regular expression.

//Code Starts

function validatePhone(txtPhone) {
    var a = document.getElementById(txtPhone).value;
    var filter = /^[0-9-+]+$/;
    if (filter.test(a)) {
        return true;
    }
    else {
        return false;
    }
}​
//Code Ends
The above regular expression allows only numerals and + or – signs. Based on your requirement you can modify this regular expression.

Now call this function to validate the phone number. For demo, I have used on blur event of textbox But It can be used on click on button or any another event. Based on the result, it updates the status in a span tag.
//Code Starts

$('#txtPhone').blur(function(e) {
   if (validatePhone('txtPhone')) {
       $('#spnPhoneStatus').html('Valid');
       $('#spnPhoneStatus').css('color', 'green');
   }
   else {
      $('#spnPhoneStatus').html('Invalid');
      $('#spnPhoneStatus').css('color', 'red');
   }
});
//Code Ends

jQuery code to allow only numbers in textbox

 jQuery, jQuery Code Examples, Jquery Code Snippets, jQuery Codes

Below jQuery code snippets will allow only numbers in the textbox. However backspace and delete keys are also allowed. 

$(document).ready(function(){
   $("#<%= txtNumbers.ClientID%>").keydown(function(e)
   {
       if (e.shiftKey)
           e.preventDefault();
       else
       {
           var nKeyCode = e.keyCode;
           //Ignore Backspace and Tab keys
           if (nKeyCode == 8 || nKeyCode == 9)
               return;
           if (nKeyCode < 95)
           {
               if (nKeyCode < 48 || nKeyCode > 57)
                   e.preventDefault();
           }
           else
           {
               if (nKeyCode < 96 || nKeyCode > 105)
               e.preventDefault();
           }
       }
   });
});

jQuery code to check if all textboxes are empty with jQuery, jQuery Codes, jQuery Tips, jQuery Validation

Find jQuery code to check if all the input type 'text' element or textboxes are empty or not. This is quite useful for validation. This jQuery code loops through all the textboxes and check their value. If value is empty then it adds a red color border to let user know that this field is required.

$(document).ready(function() {
    $('#btnSubmit').click(function(e) {
        var isValid = true;
        $('input[type="text"]').each(function() {
            if ($.trim($(this).val()) == '') {
                isValid = false;
                $(this).css({
                    "border": "1px solid red",
                    "background": "#FFCECE"
                });
            }
            else {
                $(this).css({
                    "border": "",
                    "background": ""
                });
            }
        });
        if (isValid == false)
            e.preventDefault();
        else
            alert('Thank you for submitting');
    });
});​

HTML 

<table>

<tr>

    <td>First Name:

    </td>

    <td><input type='text' id='txtFName'/ >

    </td>

</tr>

<tr>

    <td>Last Name:

    </td>

    <td><input type='text' id='txtLName'/ >

    </td>

</tr>

<tr>

    <td>Age:

    </td>

    <td><input type='text' id='txtAge'/ >

    </td>

</tr>

<tr>

    <td>Email:

    </td>

    <td><input type='text' id='txtEmail'/ >

    </td>

</tr>

<tr>

    <td colspan="2" style='text-align:center;'><input type="button" id="btnSubmit" value=" Submit ">

    </td>

</tr>

</table>

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

This question is asked many times to me. What is difference between DISTINCT and GROUP BY?

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SQL SERVER – Union vs. Union All – Which is better for performance?

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth

Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)

SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

Simple Example of Cursor - Simple Example of Cursor

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

Query to Find ByteSize of All the Tables in Database - SQL Server

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
   ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
   SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE

SQL SERVER – Check If Column Exists in SQL Server Table

A very frequent task among SQL developers is to check if any specific column exists in the database table or not. Based on the output developers perform various tasks. Here are couple of simple tricks which you can use to check if column exists in your database table or not.

Method 1

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'Name' AND OBJECT_ID = OBJECT_ID(N'[HumanResources].[Department]'))
BEGIN
PRINT 'Your Column Exists'
END

Method 2

IF COL_LENGTH('table_name','column_name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF COL_LENGTH('[HumanResources].[Department]','Name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END

Method 3

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'TableName'
AND [COLUMN_NAME] = 'ColumnName'
AND [TABLE_SCHEMA] = 'SchemaName')
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'Department'
AND [COLUMN_NAME] = 'Name'
AND [TABLE_SCHEMA] = 'HumanResources')
BEGIN
PRINT 'Your Column Exists'
END

jQuery Animations and Effects

You can slide elements, animate elements, and even stop animations in mid-sequence. To slide elements up or down: 

$("#myElement").slideDown("fast", function() {
 // do something when slide down is finished
}

$("#myElement").slideUp("slow", function() {
 // do something when slide up is finished
}

$("#myElement").slideToggle(1000, function() {
 // do something when slide up/down is finished
}

To animate an element, you do so by telling jQuery the CSS styles that the item should change to. jQuery will set the new styles, but instead of setting them instantly (as CSS or raw JavaScript would do), it does so gradually, animating the effect at the chosen speed: 

$("#myElement").animate(
 {
 opacity: .3,
 width: "500px",
 height: "700px"
 }, 2000, function() {
 // optional callback after animation completes
 }
);

Animation with jQuery is very powerful, and it does have its quirks (for example, to animate colors, you need a special plugin). It's worth taking the time to learn to use the animate command in-depth, but it is quite easy to use even for beginners.

Showing and Hiding Elements with jQuery

The syntax for showing, hiding an element (or toggling show/hide) is: 

$("#myElement").hide("slow", function() {
 // do something once the element is hidden
}

$("#myElement").show("fast", function() {
 // do something once the element is shown
}

$("#myElement").toggle(1000, function() {
 // do something once the element is shown/hidden
}

Remember that the "toggle" command will change whatever state the element currently has, and the parameters are both optional. The first parameter indicates the speed of the showing/hiding. If no speed is set, it will occur instantly, with no animation. A number for "speed" represents the speed in milliseconds. The second parameter is an optional function that will run when the command is finished executing.

You can also specifically choose to fade an element in or out, which is always done by animation:

$("#myElement").fadeOut("slow", function() {
 // do something when fade out finished
}

$("#myElement").fadeIn("fast", function() {
 // do something when fade in finished
}

To fade an element only partially, either in or out:

$("#myElement").fadeTo(2000, 0.4, function() {
 // do something when fade is finished
}

The second parameter (0.4) represents "opacity", and is similar to the way opacity is set in CSS. Whatever the opacity is to start with, it will animate (fadeTo) until it reaches the setting specified, at the speed set (2000 milliseconds). The optional function (called a "callback function") will run when the opacity change is complete. This is the way virtually all callback functions in jQuery work.

Dealing with Events in jQuery

Specific event handlers can be established using the following code: 

$("a").click(function() {
 // do something here
 // when any anchor is clicked
});

The code inside function() will only run when an anchor is clicked. Some other common events you might use in jQuery include: 

blur, focus, hover, keydown, load, mousemove, resize, scroll, submit, select. 

Adding, Removing, and Appending Elements and Content Using With Jquery

There are a number of ways to manipulate groups of elements with jQuery, including manipulating the content of those elements (whether text, inline elements, etc). 

Get the HTML of any element (similar to innerHTML in JavaScript): 

var myElementHTML = $("#myElement").html();
// variable contains all HTML (including text) inside #myElement
If you don't want to access the HTML, but only want the text of an element:
var myElementHTML = $("#myElement").text();
// variable contains all text (excluding HTML) inside #myElement

Using similar syntax to the above two examples, you can change the HTML or text content of a specified element: 
$("#myElement").html("<p>This is the new content.</p>");
// content inside #myElement will be replaced with that specified
$("#myElement").text("This is the new content.");
// text content will be replaced with that specified
To append content to an element:
$("#myElement").append("<p>This is the new content.</p>");
// keeps content intact, and adds the new content to the end
$("p").append("<p>This is the new content.</p>");
// add the same content to all paragraphs

jQuery also offers use of the commands appendTo(), prepend(), prependTo(), before(), insertBefore(), after(), and insertAfter(), which work similarly to append() but with their own unique characteristics that go beyond the scope of this simple tutorial.