Implementing an Autosuggest Textbox in Silverlight

Showing suggestions from database for each user input in textbox.

Download Sample Project RadAutoSuggestBox

Introduction

The auto complete or can say autosuggest pattern is well-known, used from long and supported in almost all technologies. In Silverlight Toolkit you can see AutoCompleteBox it provides two type of suggestions one from already bound collection and another from database by hooking it with RIA service. But if you need to get suggestions from database by using WCF service or any other conventional way then you need to tweak the logic. Here I am going to demonstrate how you can tweak Telerik RadComboBox to use for this scenario. You can also try this type of logic for Silverlight toolkit AutoCompleteBox.

Background

To implement this application, I am going to use the following technologies:

• Silverlight5 for the client tier
• WCF services -HTTP Binding
• Telerik RadControls for Silverlight v2012.1.215.1050
• .NET Framework v4.0.30319

Silverlight Business Application

Create new Silverlight application. Name it to ‘RadAutoSuggestBox’.

WCF Service

Add WCF service to the solution. Name it ‘WcfService1’.

Write one method to return collection as per user input parameter in ‘Service1.svc.cs’

        

public List<string> SuggestOption(string x)

        {

            List<string> lst = new List<string>();//Make a call to database to get collection

            lst.Add("Omaha via webex");

            lst.Add("Celine Robinson");

            lst.Add("Vanessa Johnson");

            lst.Add("Susan Peter");

            lst.Add("Nedra Tracy");

            lst.Add("Diane Houston");

            lst.Add("Nancy McEvoy");

            lst.Add("San Francisco");

            var result = lst.Where(x1 => x1.ToLower().Contains(x.ToLower()));

            return result.ToList<string>();

        }

Add contract for this method.
        [OperationContract]        
        List<string> SuggestOption(string x);

To enable a Silverlight control to access a service in another domain, the service must explicitly opt-in to allow cross-domain access. I am also going to deploy WCF service and Silverlight application on different servers.

Add ‘CrossDomainService’ service and add following code to it.

public class CrossDomainService : ICrossDomainService

    {

        Message ICrossDomainService.ProvidePolicyFile()

        {

            FileStream filestream = File.Open(@"ClientAccessPolicy.xml", FileMode.Open);

            // Either specify ClientAccessPolicy.xml file path properly

            // or put that in \Bin folder of the console application

            XmlReader reader = XmlReader.Create(filestream);

            System.ServiceModel.Channels.Message result = Message.CreateMessage(MessageVersion.None, "", reader);

            return result;

        }

    }

Create contract for this service.

[ServiceContract]

    public interface ICrossDomainService

    {

        [OperationContract]

        [WebGet(UriTemplate = "ClientAccessPolicy.xml")]

        Message ProvidePolicyFile();

    }

Add ‘ClientAccessPolicy.xml’ file for policy description.

<?xml version="1.0" encoding="utf-8" ?>

<access-policy>

  <cross-domain-access>

    <policy>

      <allow-from http-request-headers="*">

        <domain uri="*"/>

      </allow-from>

      <grant-to>

        <resource path="/" include-subpaths="true"/>

      </grant-to>

    </policy>

  </cross-domain-access>

</access-policy>

Silverlight Client

Add service reference to Silverlight project.

Now add View model class named ‘MainViewModel’ to call WCF service model. We are implementing ‘INotifyPropertyChanged’ interface. Also defining following three properties:

RadComboBoxText – We shall bind this property to Text property of telerik combo box control so it can get user input text.  Further this user entered text is passed as a parameter to WCF service method.
ItemCollection – This is the collection which is bound to ItemSource of telerik combo box. We are going to change it for each character entered.
IsDropDownOpenState - this property is used to open the dropdown and bound to IsDropDownOpen property of Rad Combobox.

Set datacontext of existing view(created with project template) ‘MainPage.xaml’ to this view model.

           
               //ViewModel setting in constructor 
               var viewModel = new MainViewModel(); 
               this.DataContext = viewModel;

Add RadComboBox to MainPage.xaml and viewmodel properties as follows.

            

<telerik:RadComboBox x:Name="RadComboBox1" TextSearchMode="Contains" CanKeyboardNavigationSelectItems="False" EmptyText="Search Text" ItemsSource="{Binding ItemCollection, Mode=OneWay}" IsEditable="True" IsTextSearchEnabled="True" Text="{Binding RadComboBoxText, Mode=TwoWay}" IsDropDownOpen="{Binding IsDropDownOpenState, Mode=TwoWay}" IsFilteringEnabled="True" CanAutocompleteSelectItems="True" IsMouseWheelEnabled="True" Margin="0 10 0 10" VerticalAlignment="Center" Canvas.Left="149" Canvas.Top="102" Width="229" />

 

Now if you run the application it will look like this.

There is arrow icon which we are not going to use so need to be removed. Open this application in Expression Blend and modify the template to get rid of this arrow. I am not going in detail how to do in expression blend. After template modification UI will be as follows.

This implementation gets data from database for each user input. It supports keyboard navigation too.

Note

You can download source code attached with this article. Hope this article will help you in implementing AutoComplete textbox.

I would appreciate Vote for the Article and any comments or concerns or how things could be done better.

Thanks for reading.

Tag: Autosuggest dropdown, Autosuggest combobox, Autosuggest textbox, Keyboard support in autosuggest textbox, AutoComplete  with WCF, How to create Silverlight autosuggest control, Auto-suggest Control with WCF, Introducing the AutoCompleteBox, Implementing AutoComplete Text Functionality in Silverlight, AutoCompleteBox in Silverlight, Telerik autosuggest with WCF, Using WCF with Autosuggest textbox. MVVM with autosuggest.

SQL Query : Update row columns with another row

How to update row columns with another row if column values are null in SQL

Update row column with another row column in same table SQL only if it is null.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Queries

If you need to update one row columns from another row columns in the same table following approach can be useful.

Create sample table.

CREATE TABLE [dbo].[Employee](

      [Title] [varchar](50) NULL,

      [FirstName] [varchar](50) NULL,

      [Surname] [varchar](50) NULL,

      [DOB] [varchar](50) NULL,

      [Datestarted] [varchar](50) NULL,

      [EmployeeId] [int] IDENTITY(1,1) NOT NULL,

      [Interest] [varchar](50) NULL,

      [EmpAddress] [varchar](500) NULL

) ON [PRIMARY]

GO

Add some sample rows as follows.

I am just updating 2 columns. Of course you can update more in similar fashion. Here updating 2 columns of 1^st row with 2^nd row column values.

UPDATE n

   SET n.EmpAddress = p.EmpAddress

      ,n.[Interest] = p.Interest

      FROM [Employee] n inner join [Employee] p

ON n.employeeid = 1 AND p.employeeid = 2

GO

Select * from Employee

Please see next query if you wish to update only when value in the column is null.

Update column when value is null

I am going to use COALESCE. This is function similar to Case.

It returns the first nonnull expression among its arguments. Example

select coalesce(Interest,'FUN') from [Employee] where EmployeeId = 5

Updating two columns of row 4^th when they are null with column values of row 3^rd.

Precise query can look like

UPDATE n

   SET n.EmpAddress = coalesce(n.EmpAddress, p.EmpAddress)

      ,n.[Interest] = coalesce(n.Interest, p.Interest)

      FROM [Employee] n inner join [Employee] p

ON n.employeeid = 4 AND p.employeeid = 3

GO

Select * from Employee

Summary

In this article I have suggested queries to update row with anther row and how you can use coalesce to restrict modification to null fields only. I hope you can use these queries for other purposes too.

Tag: SQL, SQL SERVER, SQL Server 2008, COALESCE, Update column when value is null, How to update row with another row in SQL, update row with another row column in same table SQL, Update table column with data from other columns in same row, SQL UPDATE with sub-query that references the same table, SQL UPDATE from another row in the same table, Update column value based on other columns in same table, Update data in one table with data from another table, update when value is null sql, How can I update a value on a column only if that value is null, update a column value in case null, update a column value when null, SQL UPDATE, but only if the old value is null, Update columns if it is a null, Update only if null, IS NULL and IS NOT NULL, Update to 0 if NULL

SQL Server : Data sharing across stored procedures

Table data sharing across stored procedures

This article demonstrates how table data can be shared in two stored procedures. There are several requirements where we need to pass temp table data to called stored procedure for further processing and can return manipulated data to parent stored procedure.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Introduction

I want to evaluate some of the good ways to accomplish this. I shall test approach for concurrent calling.
Table Valued Function – This can be good approach. But limitation is you can’t call stored procedure inside function if you need to.
Using Temp Table - This approach looks promising. It works for in and out.
Passing table variable – By any chance if you are using insert into in both parent and child stored procedures. Then it fails.
Other approaches like Using Cursor Variables, CLR, Open query or XML are complex, non efficient or having other pitfalls.

Evaluation of data sharing using temp table

Lets evaluate most promising approach. Test - whether temp tables are call dependent and not, causing problem in other calls.
Create table named as mytable.
CREATE TABLE [dbo].[MyTable](
            [col1] [int] NOT NULL,
            [col2] [char](5) NULL
) ON [PRIMARY]
Lets insert some sample rows for test.

INSERT INTO [MyTable]

            ([col1],

             [col2])

VALUES      (1,

             A)

Go

INSERT INTO [MyTable]

            ([col1],

             [col2])

VALUES      (2,

             B)

GO

INSERT INTO [MyTable]

            ([col1],

             [col2])

VALUES      (3,

             C)

Go

INSERT INTO [MyTable]

            ([col1],

             [col2])

VALUES      (4,

             D)

Go

Create child stored procedure named as called_procedure. Here first I am checking whether temp table is existing or not. If exists then insert some data as per passed parameter @par1.

-- If Exist then drop and create

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = Object_id(N'[dbo].[called_procedure]')

                  AND type IN ( N'P', N'PC' ))

  DROP PROCEDURE [dbo].[called_procedure]

GO

CREATE PROCEDURE Called_procedure @par1 INT,

                                  @par2 BIT

AS

  BEGIN

      IF Object_id('tempdb..#mytemp') IS NOT NULL

        BEGIN

            INSERT INTO #mytemp

            SELECT *

            FROM   Mytable

            WHERE  col1 = @par1

        END

  END

Go

Create caller Stored procedure without creating temp table in scope.

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = Object_id(N'[dbo].[caller_procedure1]')

                  AND type IN ( N'P', N'PC' ))

  DROP PROCEDURE [dbo].[caller_procedure1]

GO

CREATE PROCEDURE Caller_procedure1

AS

  BEGIN

      --Testing for if temp table does not exists in scope

      EXEC Called_procedure

        1,

        0

      IF Object_id('tempdb..#mytemp') IS NOT NULL

        BEGIN

            SELECT *

            FROM   #mytemp

        END

  END

GO

Another caller stored procedure with temp table. This procedure can be called for different parameters.

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = Object_id(N'[dbo].[caller_procedure2]')

                  AND type IN ( N'P', N'PC' ))

  DROP PROCEDURE [dbo].[caller_procedure2]

GO

CREATE PROCEDURE Caller_procedure2 @par1 INT

AS

  BEGIN

      CREATE TABLE #mytemp

        (

           col1 INT NOT NULL,

           col2 CHAR(5) NULL

        )

      EXEC Called_procedure

        @par1,

        0

      SELECT *

      FROM   #mytemp

  END

go

Execute all following queries same time. You can also execute these procedures same time from different systems.

CREATE TABLE #mytemp (col1 int     NOT NULL,

                         col2 char(5) NULL

                        )

Exec caller_procedure2 2

Exec caller_procedure2 4

Exec caller_procedure2 2

Exec caller_procedure2 4

drop table #mytemp

Exec caller_procedure1

Caller_procedure2 takes it’s own temp table and drops when scope ends. It does not take temp table which we have created outside the proc.

Summary

This little interesting exercise can help you to understand how you can pass temp table to child stored procedures. Using this concept you can write multipurpose stored procedures to increase reusability of code.

If this article helps you in designing/coding SQL logic don’t forget to hit voting option. Please comment your suggestions and improvements.

Happy Coding!!

Search Tags:

SQL, DML, DDL, SQL SERVER 2008, SQL SERVER 2005, SQL SERVER 2000, SP, Stored Procedure, Function, Temp Table, Table Variable, Passing temp table to Stored Procedure, Table data sharing across stored procedures, Data sharing in SQL, Writing reusable Stored Procedures, Passing Rows to Stored Procedures