ASP.NET(C#) : Autocomplete TextBox - Part 2 (AjaxControlToolkit)
The source code for the following post can be downloaded here.
In the previous part of this post we had a look at how to create an autocomplete textbox from scratch using the scriptmanager, asp.net, c# and javascript.
Now in this post we're going to have a look at how to use a control created by microsoft - which should represent a more mature solution than the one we looked at in the first part of this post
I am of course talking about the open source autocomplete extender control thats part of the Ajax Control Toolkit.
First of all you'll need to download the latest AjaxControlToolkit assembly.
Next you will need to add the assembly to your toolbox, which will add the extender designer item (if you don't have other extenders already), like seen in the following image to the Visual Studio IDE:
When you click on "Add Extender", the following popup will appear that displays a list of available extenders:
(obviously you'll need to select the autocomplete extender ;))
Like in part 1 of this post, we've got two options that we can use as datasources, PageMethods or Webservices.
PageMethod:
<asp:AutoCompleteExtender ID="txtValue_AutoCompleteExtender" runat="server" DelimiterCharacters="" Enabled="True" TargetControlID="txtValue" ServiceMethod="Get" MinimumPrefixLength="1" CompletionSetCount="5" CompletionInterval="500"> </asp:AutoCompleteExtender>
public partial class _Default : System.Web.UI.Page { [WebMethod] public static string[] Get(String prefixText, Int32 count) { // Dummy data - don't do this string[] data = new string[] { "Christoff Trüter", "Eugene Stander", "Roland Cooper", "Alexander Mehlhorn", "Derek Campher", "Julie Trüter", "Hanno Coetzee", "Wayne Kleynhans", "Pieter Du Plooy", "Pam Nizar" }; return (from p in data where p.IndexOf(prefixText, StringComparison.OrdinalIgnoreCase) >= 0 select p).Take<String>(count).ToArray(); }
Quick things to remember about PageMethods:
- need to be public and static. (since its being called client-side it wont have server-side access to the current served page, therefore it would have been pointless to make it an instance method - among other reasons)
- lives on the page that calls it.
Webservice:
<asp:AutoCompleteExtender ID="txtValue_AutoCompleteExtender" runat="server" DelimiterCharacters="" Enabled="True" TargetControlID="txtValue" ServiceMethod="Get" ServicePath="~/Services/myService.asmx" MinimumPrefixLength="1" CompletionSetCount="5" CompletionInterval="500"> </asp:AutoCompleteExtender>
[System.Web.Script.Services.ScriptService] public class myService : System.Web.Services.WebService { [WebMethod] public string[] Get(String prefixText, Int32 count) { // Dummy data - don't do this string[] data = new string[] { "Christoff Trüter", "Eugene Stander", "Roland Cooper", "Alexander Mehlhorn", "Derek Campher", "Julie Trüter", "Hanno Coetzee", "Wayne Kleynhans", "Pieter Du Plooy", "Pam Nizar" }; return (from p in data where p.IndexOf(prefixText, StringComparison.OrdinalIgnoreCase) >= 0 select p).Take<String>(count).ToArray(); } }
Here is a quick description of some of the most important attributes used.
- TargetControlID : The texbox that must be autocompleted.
- ServiceMethod : The PageMethod or Service method (if ServicePath defined)
- ServicePath : The path to the service thats used for consumption.
- MinimumPrefixLength : The minimum amount of letters that must be typed within the textbox before a query is attempted to the server.
- CompletionSetCount : The number of results to return.
- CompletionInterval : The number (in milliseconds) to wait until a query to the server is attempted.
You can read more about other attributes of this extender control here.
Posted by - Christoff Truter
Date - 2011-04-10 19:39:39
Comments - 3
Date - 2011-04-10 19:39:39
Comments - 3
MS SQL Basics : scope_identity(), @@identity, IDENT_CURRENT
Retrieving the last inserted identity(ID) on a table is a very common task when working with pretty much any relational database. There are a number of ways to do this, most of these "ways" are unfortunately potentially dangerous and must be avoided if possible.
Instead of simply giving you definitions on some of these methods, lets take a more practical approach in order to see the potential pitfalls (it also makes for a longer post at least, hehe).
Firstly we're going to create two tables (for now at least) for testing purposes:
CREATE TABLE [dbo].[x] ( [xID] [int] IDENTITY(1,1) NOT NULL, [ID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_x] PRIMARY KEY CLUSTERED ( [xID] ASC )) GO CREATE TABLE [dbo].[y] ( [yID] [int] IDENTITY(1,1) NOT NULL, [xID] [int] NOT NULL, [ID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_y] PRIMARY KEY CLUSTERED ( [yID] ASC )) GO
We're also going to use the following stored procedure to help us to do some "damage":
CREATE PROCEDURE [dbo].[Test] AS BEGIN DECLARE @xID INT, @ID UNIQUEIDENTIFIER SET @ID = NEWID() INSERT INTO x(ID) VALUES(@ID) --SET @xID = -- Some Retrieval Method INSERT INTO y(xID, ID) VALUES(@xID, @ID) END
And we'll use the following query to do a quick validation on our data:
SELECT count(*) FROM x JOIN y ON x.xID = y.xID AND x.ID = y.ID
We're also going to write a little C# console app to simulate users using this stored procedure within an application:
using System.Data.SqlClient; using System.Configuration; using System.Threading; class Program { static void Main(string[] args) { for (int i = 0; i < 10; i++) { new Thread(() => { using (SqlConnection connection = new SqlConnection("some connection")) { connection.Open(); using (SqlCommand command = new SqlCommand("Test", connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.ExecuteNonQuery(); } } }).Start(); } } }
The first function we're going to look at is @@identity - we alter the test stored procedure with the following:
SET @xID = @@IDENTITY
We run the console application, run the validation query, mmm 10 rows, seems like everything is in order... or is it? Things get interesting as soon as we add another table and a trigger to the equation:
CREATE TABLE [dbo].[z]( [zID] [int] IDENTITY(1,1) NOT NULL, [xID] [int] NOT NULL, [ID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_z] PRIMARY KEY CLUSTERED ( [zID] ASC )) CREATE TRIGGER [dbo].[triggerHappy] ON [dbo].[x] AFTER INSERT AS INSERT INTO z(xID, ID) SELECT xID, ID FROM inserted
Okay, so after we amended our database we run the console app again and do validation, mmm 10 rows... uhhh that can't be right? Surely we've got 20 rows now? A look at the data reveals that everything is quite messed up! We do infact have 20 rows, but all the relationships are mucked up...
What happend here? Well, instead of inserting the last identity from table x, we're actually busy inserting identities from table z (thanks to the trigger) which basically gives us the clue that @@identity is not limited to the current scope (scope being our stored procedure), any new identity inserted regardless of table in our session will be returned - not ideal at all.
We can obviously use the IDENT_CURRENT function if we need to get a table specific identity, but unfortunately that also suffers from the same issues as @@identity - out of scope influences.
How do we get around all of these issues?
Well... This is where the scope_identity function (introduced in SQL 2000) comes into play.
As soon as you update the test stored procedure with this:
SET @xID = scope_identity()
You will notice that after you've run the console app, that everything will work like expected - scope_identity only returns the last inserted identity within the current scope - making this this our preferred method.
Posted by - Christoff Truter
Date - 2011-03-31 23:04:45
Comments - 0
Date - 2011-03-31 23:04:45
Comments - 0
First 6 7 8 9 10 11 12 13 14 15 Last / 65 Pages (129 Entries)