MS-SQL - Send Mail from SQL Server 2008

In this article i will show you how to configure and send mail through SQL Server 2008.

Database Mail was introduced in SQL Server 2005 and we can say it is a complete replacement of SQL Mail of SQL Server earlier version. Database Mail is designed for reliability, scalability, security, and supportability.

Advantages of using Database Mail.

• It's easy to configure, fast and reliable.
• Auditing and Maintaining Logs of every mail.
• Can send HTML messages, attachments,Attach Query ResultSet as TextFile.

Let See How to configure Database mail in SQL server 2008.

Step 1

We need to create a profile and account using the Configure Database Mail Wizard.This wizard is used to manage accounts, profiles, and Database Mail global settings.

Open Management Nod, right click on Database Mail and click Configure Database Mail.it is look like this

Click on Image for better View

Step 2

Database Mail Wizard will Open,it is look like this

Click on Image for better View

Step 3

Setup Database Mail,Click Next Button,it is look like this

Click on Image for better View

Step 4

Add profile name and profile description and click Add button,it is look like this

Click on Image for better View

Step 5

Enter Database Mail Account details and click OK button,it is look like this

Click on Image for better View

All the information needed for SQL Server to send an email to a Simple Mail Transfer Protocol (SMTP) server.

• Account Name: Name that will be used to identify the database mail account.

• Description: Optional description used to identify the database mail account.

• E-mail Address: Email address that the mail is being sent from.

• Display Name: Optional name that is displayed on email messages to indicate who the email is from.

• Reply E-mail: Optional email address that will be used when someone replies to an email sent by this account.

• Server Name: Server name or IP address of the SMTP server that is used to send emails for this account.

• Port Number: Port number that is used to connect to the SMTP server.

• This Server Requires a Secure Connection (SSL): This option will encrypt communication between SQL Server and the SMTP server. You must have a certificate installed for SQL Server in order to use this option.

• Windows Authentication Using Database Engine Service Credentials: This option will use the MSSQLServer service credentials to connect to the SMTP server.

• Basic Authentication: This option allows you to specify a username and password in order to connect to the SMTP server.

• Anonymous Authentication: This option connects to SMTP servers without passing any login credentials and is used for SMTP servers that do not require authentication.

Step 6

Now New Account Profile Create,Click Next Button,it is look like this

Click on Image for better View

Step 7

Manage Profile Security, Click Next Button

Click on Image for better View

We can configure Database Mail profiles as public profiles or private profiles. If you configure the profile as public, anyone who can access the msdb database is allowed to use the profile to send mail. If you configure the profile as private, the profile will be limited to only specific users. You can also have one default public profile and one default private profile. A default profile allows you to send mail without specifying a profile name. When an account has access to both a default private profile and a default public profile, the default private profile will be used. If you would like to make the profile public, select the Public check box next to the profile name; if you would like to make the profile private, select the Private Profiles tab

Note: No need to select any CheckBox just Click Next button.

Step 8

Configure System Parameter,Click Next Button,it is look like this

Click on Image for better View

We can use the Configure System Parameters screen to define the system parameters for an entire instance of SQL Server; any changes you make here will apply to all Database Mail profiles and accounts. 

• Account Retry Attempts: Number of times Database Mail will retry to send mail using each account in a profile. For example, if you set the Account Retry Attempts parameter to three and you have two accounts in a profile, each account will retry to send mail three times.

• Account Retry Delay (Seconds): Number of seconds Database Mail will wait between retries. This delay is not the delay between each account in a profile. Database Mail will attempt to use all accounts in a profile, and then wait the defined number of seconds before trying all accounts again.

• Maximum File Size (Bytes): Limits the size of an attachment.

• Prohibited Attachment File Extensions: Comma-delimited list of file extensions that are not permitted as attachments when sending Database Mail.

• Database Mail Executable Minimum Lifetime (Seconds): The minimum time the external mail process will remain active even if there are no items in the mail queue. If items are in the queue, the external mail process will remain active until all items are processed.

• Logging Level: There are three logging levels you can use to send events to the Database Mail log: Normal, Extended, and Verbose. The Normal logging level will log errors. The Extended logging level will log errors, warnings, and informational messages. (Extended is the default logging level.) The Verbose logging level will log errors, warnings, informational messages, success messages, and internal messages. You should generally only use the Verbose logging level for troubleshooting purposes.

Step 9

Complete the Wizard,Click Finish Button,it is look like this

Click on Image for better View

Click on Image for better View

Click on Close Button.

Step 10

Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure

USE msdb
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 11
All configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters.

1. Send Basic Email

-- Send Basic Email
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Test message from Sql Server',
@body='Sending mail from SQL Server 2008'

Output

Click on Image for better View

2. Send Select Query ResultSet

-- Send Select Query ResultSet
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Query Result Set',
@body='Select Query Result Set',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
'

Output

Click on Image for better View

3. Send mail attach the Select Query Resultset as Text File

-- Send mail attach the Select Query Resultset as Text File.
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Attach the Result as Text File',
@body='Find the attachment.',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
',
@attach_query_result_as_file = 1,
@query_attachment_filename ='EmployeeResults.txt'

Output

Click on Image for better View

4. Send mail with Attachment

-- Send mail with Attachment
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Send Mail with Attachment',
@body='Find the attachment.',
@file_attachments ='D:\Koala.jpg'

Output

Click on Image for better View

5. Send mail that uses HTML Formatting

--Send mail that uses HTML Formatting
--Send mail that uses HTML Formatting
DECLARE @HtmlBody Nvarchar(MAX)

 SET @HtmlBody='<table border=1 cellpadding="5" cellspacing="5">'
 SET @HtmlBody=@HtmlBody+'<tr>'
 SET @HtmlBody=@HtmlBody+'<th>First Name</th>'
 SET @HtmlBody=@HtmlBody+'<th>Last Name</th>'
 SET @HtmlBody=@HtmlBody+'</tr>'
 
 SELECT @HtmlBody=@HtmlBody+'<tr><td>'+Northwind..Employees.FirstName+
     '</td>'+'<td>'+Northwind..Employees.LastName+'</td></tr>' 
     FROM Northwind..Employees
     
 SET @HtmlBody=@HtmlBody+'</table>'

EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='HTML Formatting',
@body=@HtmlBody,
@body_format = 'HTML'

Output

Click on Image for better View

Step 11
Status of mail sent,it is look like this 

SELECT * FROM sysmail_mailitems

Output

Click on Image for better View

Step 12
Mail Logs

SELECT * FROM sysmail_log

Output

Click on Image for better View

Download
Scripts

WPF - ProgressBar in DataGrid Column in WPF

In this article i will show you how to add Progressbar in DataGrid Column in WPF 4. 

Step 1
Download Northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a Northwind database into MS-SQL server.

Step 3
Create a WPF Application and give solution name as SolProgressBarinDataGrid.

Step 4
Create a New Folder in Solution and give the Folder Name as ORD,it is look like this

Click on Image for Better View

Step 5
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this

Click on Image for Better View

Step 6

Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

Click on Image for Better View

Click on Image for Better View

Visual studio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 7

Create a Employee and Order object that will use LINQ to SQL to map to this table.go to the Server Explorer,select Northwind database,go to the Tables and select Employees and Orders table,it is look like this

Click on Image for Better View

Drag and drop Employees and Orders table from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on Image for Better View

Step 8

Create a OrderCount Static Class in Solution for retrieving total number of Count Orders of each Employee Using Linq or Lambda Expression.it is look like this 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SolProgressBarinDataGrid
{
    public static class OrderCount
    {
        #region Methods

        /// <summary>
        /// Get Order Count Data of Each Employee
        /// </summary>
        /// <returns>IList</returns>
        public static IList GetOrderCountData()
        {
            try
            {
                // Create Instance of Northwind DataContext 
                ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext();

                // Using Linq Query
                var Query = (from E in DC.Employees
                             join O in DC.Orders
                             on E.EmployeeID equals O.EmployeeID
                             into EmployeeOrder
                             orderby E.FirstName ascending
                             select new
                             {
                                 EmployeeName = E.FirstName + " " + E.LastName,
                                 OrderCounts = EmployeeOrder.Count()
                             }
                               );

                // Using Lambda Expression
                //var Query = (DC.Employees.GroupJoin(DC.Orders,
                //              Employee => Employee.EmployeeID,
                //              Order => Order.EmployeeID,
                //              (Employee, EmployeeGroup) => new
                //              {
                //                  EmployeeName = Employee.FirstName + " " + Employee.LastName,
                //                  OrderCounts = EmployeeGroup.Count()
                //              }

                //              ));

                return Query.ToList();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message); 
            }
        }

        #endregion
    }
}

Output of Result Using LinqPad

Click on Image for Better View

Download LinqPad
http://www.linqpad.net/

Step 9
Now add DataGrid Control on window,it is look like this

<Grid>
  
      <DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">

      </DataGrid>
  
</Grid>

Step 10
Select DataGrid Control and Add DataGridTextColumn in DataGrid where we bind Employee Full Name,it is look like this

<DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
                        </DataGrid.Columns>
  </DataGrid>
  
 </Grid>

Click on Image for Better View

Step 11

Select DataGrid and Add DataGridTemplateColumn in DataGrid where we add ProgreeBar control in Column and Bind Order Count value to Progressbar control and TextBlock control,it is look like this

<DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
    <DataGridTemplateColumn Header="Orders Count" Width="380">
     <DataGridTemplateColumn.CellTemplate>
      <DataTemplate>
       <Grid>
        <ProgressBar Grid.Row="0" Grid.Column="0"  Minimum="0" Maximum="200" Value="{Binding OrderCounts,Mode=OneWay}" ToolTip="{Binding OrderCounts,Mode=OneWay}">
        </ProgressBar>
       <TextBlock Grid.Row="0" Grid.Column="0" Text="{Binding OrderCounts,Mode=OneWay}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
       </Grid>
      </DataTemplate>
     </DataGridTemplateColumn.CellTemplate>
    </DataGridTemplateColumn>
   </DataGrid.Columns>
  </DataGrid>

Click on Image for Better View

Full Code of XAML

<Window x:Class="SolProgressBarinDataGrid.MainWindow"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  Title="Progressbar in DataGrid Column" Height="350" Width="525" Loaded="Window_Loaded_1">
 
 <Grid>
  
  <DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
    <DataGridTemplateColumn Header="Orders Count" Width="380">
     <DataGridTemplateColumn.CellTemplate>
      <DataTemplate>
       <Grid>
        <ProgressBar Grid.Row="0" Grid.Column="0"  Minimum="0" Maximum="200" Value="{Binding OrderCounts,Mode=OneWay}" ToolTip="{Binding OrderCounts,Mode=OneWay}">
        </ProgressBar>
       <TextBlock Grid.Row="0" Grid.Column="0" Text="{Binding OrderCounts,Mode=OneWay}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
       </Grid>
      </DataTemplate>
     </DataGridTemplateColumn.CellTemplate>
    </DataGridTemplateColumn>
   </DataGrid.Columns>
  </DataGrid>
  
 </Grid>
</Window>

Step 12
Now Bind the data in DataGrid Control in Code Behind on Window Load Event,it is look like this

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace SolProgressBarinDataGrid
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

           
        }

        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            try
            {
                // Get Order Count Data In IList
                IList OrderCountList = OrderCount.GetOrderCountData();

                // Check the IList is Null or Not
                if (OrderCountList != null)
                {
                    // Check the IList Count is Grether than 0 or not
                    if (OrderCountList.Count > 0)
                    {
                        // Bind Data to DataGrid Control
                        DgOrderCount.DataContext = OrderCount.GetOrderCountData();
                    }
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); 
            }
        }
    }
}

Output

Click on Image for Better View

Download
Download Source Code

WP7 - Send SMS in Window Phone 7

In this article i will explain you how to send SMS in window phone 7.Sending a SMS is a feature of the Windows Phone operating system and can be used by the Launcher API. it uses the SmsComposeTask Launcher to open the native device SMS editor and give the user an option to send the SMS or discard it.


Step 1
To Develop application for Windows Phone 7 devices, you need to install Windows Phone 7.1 SDK.You can download latest SDK for Windows Phone
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=27570

SDK 7.1.1 Update
https://dev.windowsphone.com/en-us/downloadsdk


Step 2
Create a Window Phone Application and give the solution name as SendSMSinWP7.
To start creating a new Windows Phone application, start Microsoft Visual Studio then create a new Project and select Windows Phone Application Template,it is look like this

Click on Image for better View

Step 3
Select the Window Phone Platform,it is look like this

Click on Image for better View

Step 4
Now design page of sending SMS,it is look like this

<Grid x:Name="LayoutRoot" Background="Transparent">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="MY APPLICATION" Style="{StaticResource PhoneTextNormalStyle}"/>
            <TextBlock x:Name="PageTitle" Text="Send SMS" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
        </StackPanel>

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
         <Grid.RowDefinitions>
          <RowDefinition Height="0.072*"/>
          <RowDefinition Height="0.124*"/>
    <RowDefinition Height="0.066*"/>
          <RowDefinition Height="0.61*"/>
          <RowDefinition Height="0.129*"/>
         </Grid.RowDefinitions>
   
   <TextBlock Grid.Row="0" Grid.Column="0" Text="Enter Mobile No" FontSize="24"/>
   <TextBox x:Name="txtMobileNo" Grid.Row="1" Grid.Column="0"></TextBox>
   <TextBlock Grid.Row="2" Grid.Column="0" Text="Enter Message" FontSize="24"></TextBlock>
   <TextBox x:Name="txtMessage" Grid.Row="3" Grid.Column="0" TextWrapping="Wrap" AcceptsReturn="True"></TextBox>
   <Button x:Name="btnSendSMS" Grid.Row="4" Grid.Column="0" Content="Send SMS" Click="btnSendSMS_Click"></Button>
   
   </Grid>
    </Grid>

Click on Image for better View

Step 5
On Button Send SMS click event,add the following Code,it is look like this

private void btnSendSMS_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //Create Instance of SmsComposeTask Launcher
                SmsComposeTask smsComposeTask = new SmsComposeTask();

                // Specify Mobile phone number to whom the sms is to be sent
                smsComposeTask.To = txtMobileNo.Text.Trim();

                // Body of Message
                smsComposeTask.Body = txtMessage.Text.Trim();

                //Invoke the native sms edtior
                smsComposeTask.Show(); 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); 
            }

        }

Run the Project.

Output

Click on Image for better View

When we click on send SMS button, it will open native SMS application.

Click on Image for better View
In here we can either edit the message or set other recipients.
Once the user clicks on Send Button on native SMS application, SMS will be sent.

Click on Image for better View

Full Code of XAML

<phone:PhoneApplicationPage 
    x:Class="SendSMSinWP7.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    shell:SystemTray.IsVisible="True">

    <!--LayoutRoot is the root grid where all page content is placed-->
    <Grid x:Name="LayoutRoot" Background="Transparent">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="MY APPLICATION" Style="{StaticResource PhoneTextNormalStyle}"/>
            <TextBlock x:Name="PageTitle" Text="Send SMS" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
        </StackPanel>

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
         <Grid.RowDefinitions>
          <RowDefinition Height="0.072*"/>
          <RowDefinition Height="0.124*"/>
    <RowDefinition Height="0.066*"/>
          <RowDefinition Height="0.61*"/>
          <RowDefinition Height="0.129*"/>
         </Grid.RowDefinitions>
   
   <TextBlock Grid.Row="0" Grid.Column="0" Text="Enter Mobile No" FontSize="24"/>
   <TextBox x:Name="txtMobileNo" Grid.Row="1" Grid.Column="0"></TextBox>
   <TextBlock Grid.Row="2" Grid.Column="0" Text="Enter Message" FontSize="24"></TextBlock>
   <TextBox x:Name="txtMessage" Grid.Row="3" Grid.Column="0" TextWrapping="Wrap" AcceptsReturn="True"></TextBox>
   <Button x:Name="btnSendSMS" Grid.Row="4" Grid.Column="0" Content="Send SMS" Click="btnSendSMS_Click"></Button>
   
   </Grid>
    </Grid>
 
    <!--Sample code showing usage of ApplicationBar-->
    <!--<phone:PhoneApplicationPage.ApplicationBar>
        <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button1.png" Text="Button 1"/>
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Button 2"/>
            <shell:ApplicationBar.MenuItems>
                <shell:ApplicationBarMenuItem Text="MenuItem 1"/>
                <shell:ApplicationBarMenuItem Text="MenuItem 2"/>
            </shell:ApplicationBar.MenuItems>
        </shell:ApplicationBar>
    </phone:PhoneApplicationPage.ApplicationBar>-->

</phone:PhoneApplicationPage>

Download
Download Source Code

JQuery - Custom Autocomplete TextBox in JQuery

In this article i will explain how to display Image with Auto Complete search in Asp.net Using JQuery.

here we can use our own custom data formats and displays by simply overriding the default focus and select actions.

Declare web methods in Code behind and call those methods in our page using JQuery.

Step 1
Download UI Autocomplete Plugin from the following Link
JQuery UI 1.9.2 

Step 2
First we need to create a database,give the database name as AppTest and create a single table such as Developer, where we can store developer information.,it is look like this

USE AppTest

CREATE TABLE Developer
(
 Name Nvarchar(50) NULL,
 Speciality Nvarchar(50) NULL,
 [Image] Nvarchar(50) NULL
)

Click on Image for better View

Step 3
Insert a Data in Developer table,it is look like this

INSERT INTO Developer 
(Name,Speciality,Image)
VALUES
('Bhushan Pawar','SEO & Sql Database','Bhushan.jpg')

INSERT INTO Developer 
(Name,Speciality,Image)
VALUES
('Ramdas Bhosale','Web Application','Ramdas.jpg')

INSERT INTO Developer 
(Name,Speciality,Image)
VALUES
('Kishor Naik','Window Application','Kishor.jpg')

Click on Image for better View

Step 4
Create a Web Application and give the solution name as SolCustomAutoCompleteBoxJquery.

Step 5
Add CSS and Script files which we downloaded from Above Link,it is look like this

CSS

SCRIPT

Click on Image for better View

Step 6
Add images in Folder,Create a New Folder in the Solution and give folder name as Images,it is look like this

Click on Image for better View

Step 7
Add App_Code folder in the solution and add a New Folder inside the App_Code folder and give folder name as ORD,it is look like this

Click on Image for better View

Step 8
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it DeveloperDC and click on add button,it is look like this

Click on Image for better View

Step 9
Open a O/R Designer by double click on DeveloperDC.dbml,it is look like this

Click on Image for better View

Click on Image for better View

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 10
Create a Developer object that will use LINQ to SQL to map to this table.go to the Server Explorer,select AppTest database,go to the Tables and select Developer table,it is look like this

Click on Image for better View

Drag and drop Developer table from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on Image for better View

Step 11
Create a Developer static class in App_Code folder for retriving an Developer data from database,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for Developer
/// </summary>
public static class Developer
{
    #region Methods

    /// <summary>
    /// Retriving Developer Data from Table
    /// </summary>
    /// <param name="SearchText">Specify the Search text</param>
    /// <returns>List</returns>
    public static List<ORD.Developer> GetdeveloperData(String SearchText)
    {
        try
        {
            ORD.DeveloperDCDataContext DC = new ORD.DeveloperDCDataContext();

            // Using Linq
            var Query = (from Q in DC.Developers
                         where Q.Name.Contains(SearchText)
                         select Q).ToList<ORD.Developer>();

            // Using Lambda Expression
            //var Query = DC.Developers.Where(LE => LE.Name.Contains(SearchText)).Select(LE => LE).ToList<ORD.Developer>();

            return Query; 
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }

    #endregion
}

Step 12
Create a Web Method in Default.aspx.cs for call Web Method from JSON function.it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }

    #region Methods

    /// <summary>
    /// Bind Data
    /// </summary>
    /// <param name="SearchText">Specify the Search Text Box</param>
    /// <returns>Array of ORD.Developer</returns>
    [WebMethod]
    public static ORD.Developer[] BindAutoCompleteBox(String SearchText)
    {
        try
        {
            List<ORD.Developer> List = Developer.GetdeveloperData(SearchText);

            return List.ToArray();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }

    #endregion
}

Generally we will create static web methods in webservice and we will use those methods to call it from JQuery instead of that directly we can create static methods with [WebMethod] attribute in our code behind file and use those methods from JQuery.

Now Server side code part done,let move to the Client Side.

Step 13
 Add a TextBox in Page,it is look like this

<body>
    <form id="form1" runat="server">
        <div>
            <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
        </div>
    </form>
</body>

Step 14
Add CSS file Reference inside the head tag of the page,it is look like this

<link type="text/css" href="css/ui-lightness/jquery-ui-1.9.2.custom.min.css" rel="Stylesheet" rev="Stylesheet" />

Step 15
Add JQuery and UI file Reference inside the head tag of the page,it is look like this

<script language="javascript" type="text/javascript" src="Scripts/jquery-1.8.3.js"></script>
    <script language="javascript" type="text/javascript" src="Scripts/jquery-ui-1.9.2.custom.min.js"></script>

Step 16
Call a Page Web Method in JQuery for binding Custom Data,it is look like this

<script language="javascript" type="text/javascript">

        $(document).ready(function () {

            $('#<%=txtSearch.ClientID %>').autocomplete({
                minLength: 0,
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        // Specify the Full Path of Page and Exact Fucntion Name 
                        url: "Default.aspx/BindAutoCompleteBox",
                        // Specify the Exact Parameter Name which you specified in Function 
                        data: "{'SearchText':'" + $('#<%=txtSearch.ClientID %>').val() + "'}",
                                    dataType: "json",
                                    success: function (data) {
                                        response(data.d);
                                    },
                                    error: function (result) {
                                        alert("Issued in Database.");
                                    }
                                });
                },
                //Triggered when focus is moved to an item
                focus: function (event, ui) {
                    $("#txtSearch").val(ui.item.Name);
                    return false;
                },
                //Triggered when an item is selected from the menu.
                select: function (event, ui) {
                    $("#txtSearch").val(ui.item.Name);
                    return false;
                },

                // Create a Custome Format to dispaly Data in TextBox
            }).data('autocomplete')._renderItem = function (ul, item) {
                return $("<li></li>")
                        .data("item.autocomplete", item)
                        .append("<a style = 'vertical-align:top'>" +
                        "<img style = 'vertical-align:middle;width:60px;height:60px' src='Images/" + item.Image + "'/>" +
                        "Developer Name :" + item.Name +
                        "<br/>"+
                        "Speciality :" + item.Speciality +
                        "<hr/>" +
                        "</a>"
                        ).appendTo(ul);
            };
           

        });

    </script>

This is the function declaration of JSON format we are using this JSON function to call web methods using JQuery $.ajax() whenever we need to make Ajax call with JQuery then we will use JSON functions like as we mentioned in above format. Here type, ContentType  are same for all functions only dataType, url, data and success and error parameters will vary based on our requirement.

• Path : Path of our WebMethod.
• data : Pass Parameter.
• success : Once our web method execution completed then success function will execute and return required data.
• error : This parameter is used to display required error message whenever we get problem.

• Focus Event : 
• Triggered when focus is moved to an item (not selecting). The default action is to replace the text field's value with the value of the focused item, though only if the event was triggered by a keyboard interaction.
• Canceling this event prevents the value from being updated, but does not prevent the menu item from being focused.
• Select Event :
• Triggered when an item is selected from the menu. The default action is to replace the text field's value with the value of the selected item.u. The default action is to replace the text field's value with the value of the selected item.
• Canceling this event prevents the value from being updated, but does not prevent the menu from closing.

• Data : Create Custom Format to display data. 

Full Code of .ASPX

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Custom AutoCompleteTextBox in Jquery</title>

    <link type="text/css" href="css/ui-lightness/jquery-ui-1.9.2.custom.min.css" rel="Stylesheet" rev="Stylesheet" />

    <script language="javascript" type="text/javascript" src="Scripts/jquery-1.8.3.js"></script>
    <script language="javascript" type="text/javascript" src="Scripts/jquery-ui-1.9.2.custom.min.js"></script>

    <script language="javascript" type="text/javascript">

        $(document).ready(function () {

            $('#<%=txtSearch.ClientID %>').autocomplete({
                minLength: 0,
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        // Specify the Full Path of Page and Exact Fucntion Name 
                        url: "Default.aspx/BindAutoCompleteBox",
                        // Specify the Exact Parameter Name which you specified in Function 
                        data: "{'SearchText':'" + $('#<%=txtSearch.ClientID %>').val() + "'}",
                                    dataType: "json",
                                    success: function (data) {
                                        response(data.d);
                                    },
                                    error: function (result) {
                                        alert("Issued in Database.");
                                    }
                                });
                },
                //Triggered when focus is moved to an item
                focus: function (event, ui) {
                    $("#txtSearch").val(ui.item.Name);
                    return false;
                },
                //Triggered when an item is selected from the menu.
                select: function (event, ui) {
                    $("#txtSearch").val(ui.item.Name);
                    return false;
                },

                // Create a Custome Format to dispaly Data in TextBox
            }).data('autocomplete')._renderItem = function (ul, item) {
                return $("<li></li>")
                        .data("item.autocomplete", item)
                        .append("<a style = 'vertical-align:top'>" +
                        "<img style = 'vertical-align:middle;width:60px;height:60px' src='Images/" + item.Image + "'/>" +
                        "Developer Name :" + item.Name +
                        "<br/>"+
                        "Speciality :" + item.Speciality +
                        "<hr/>" +
                        "</a>"
                        ).appendTo(ul);
            };
           

        });

    </script>

</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
        </div>
    </form>
</body>
</html>

Output

Download
Download Source Code