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
ASP.NET(C#) : Autocomplete TextBox - Part 1 (From Scratch)
The source code for the following post can be downloaded here.
Back in 2007 I had the wonderful privilege to work at Web Africa, one of the largest internet service providers in South Africa.
One of my PHP projects (or at least a small part of the project) involved creating an autocomplete textbox when searching for domains located within the company mysql database.
If memory serves me correctly (it probably doesn't) thats around the same time google introduced google suggests at google labs - something that is standard to its search these days.
(subtle attempt at humour, ahhh.... ha ha)
Last month we needed the same functionality in one of our ASP.NET projects at my current company.
In this post we're going to have a look at how to create our very own autocomplete textbox using ASP.net (C#) (with a little bit of javascript of course).
Lets jump into some code...
We can retrieve our autocomplete results via PageMethods (ajax request, json response), which we need to enable in our scriptmanager at the top of our page like seen in the following snippet:
<asp:ScriptManager runat="server" ID="scriptmanager" EnablePageMethods="true"> <Scripts> <asp:ScriptReference Path="~/js/autocomplete.js" /> </Scripts> </asp:ScriptManager>
The server side method responsible for retrieving data will look something like this (you will obviously want to retrieve results via some database - this example is solely for demo purposes)
[WebMethod] public static string[] GetList(string prefixText, int 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(); }
Alternatively results can also be retrieved via webservice, we need to register the service using the scriptmanager - this makes its methods available to our javascript script. (retrieving our results using a webservice is a bit more reusable than the PageMethod option, since PageMethods are only available within the page they're created on)
<asp:ScriptManager runat="server" ID="scriptmanager"> <Services> <asp:ServiceReference Path="~/MyService.asmx" /> </Services> <Scripts> <asp:ScriptReference Path="~/js/autocomplete.js" /> </Scripts> </asp:ScriptManager>
The webservice code will look something like this:
using System; using System.Linq; using System.Web.Services; [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.Web.Script.Services.ScriptService] public class MyService : System.Web.Services.WebService { [WebMethod] public string[] GetList(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(); } }
Note the ScriptService attribute at the top of the MyService class, its important to set this attribute in order to make the service useable via our scriptmanager and expose it to javascript.
Okay, now that we've got an idea of where our data will be coming from, lets have a look at the javascript (js/autocomplete.js) script that will make this all possible.
Within our autocomplete.js file we've got a function called AutoComplete, now this function needs to be called as soon as the page is done loading - we can do this using the pageLoad function which will automatically be called by the scriptmanager as soon as the page finished loading, observe:
<script type="text/javascript"> function pageLoad() { AutoComplete('<%=txtTest.ClientID %>', 10, 500, PageMethods, 'GetList'); } </script> <asp:TextBox ID="txtTest" runat="server"></asp:TextBox>
The first argument to the AutoComplete function contains the ID of the textbox we wish to autocomplete, the second the maximum amount of results to return, third the timeout - how long the script needs to wait (in milliseconds) before it attempts to query the server.
The fourth argument is where it gets a little bit more interesting, when using PageMethods an object named PageMethods becomes available to our scripts, when using a webservice the scriptmanager will create an object thats got the same name as the class name we gave our webservice.
In this example the object will be named MyService (if you use namespaces you'll need to include that in your object as well e.g. MyNamespace.MyService), now instead of passing the PageMethods object to the function, you can alternatively pass the MyService object to the function (these objects are interchangeable).
The last argument obviously tells the function which method to call e.g. GetList.
Lets dig a bit deeper to see how all of this works.
(js/autocomplete.js)
function AutoComplete(targetId, count, timeout, service, method) { var timeoutId; var request; var target = document.getElementById(targetId); var list = CreateList(target); $addHandler(target, "keyup", function() { if (timeoutId != undefined) { clearTimeout(timeoutId); } if (request != undefined) { var executor = request.get_executor(); if (executor.get_started()) { executor.abort(); } } timeoutId = setTimeout(function() { request = service._staticInstance[method](target.value, count, success, function() { }); }, timeout); });
Notice the $addHandler function, this is function available courtesy of the scriptmanager - since browsers like IE and FireFox handle events differently, we need a crossbrowser friendly function to attach events.
Within our keyup eventhandler you will notice a few interesting things:
- We need to minimize the amount of requests to the
server therefore we clear our timer (set using setTimeout) callback with every keyup, if we don't do this every key pressed will do a request to the server like
seen in the following image:
-
We also abort requests already in progress, we ideally only want one active request at a time - especially since we're limited to the amount of
requests in our browsers - if a key is pressed and an active request is found, the script aborts it, like seen in the image
below:
The success function like seen below (nested within the AutoComplete giving it access to all its "private" fields), gets executed if the request successfully executes - populating the autcomplete listbox.
function success(result) { list.options.length = 0; // clear listbox if (result != '') { list.setAttribute("size", result.length + 1); list.style.display = "block"; for (var index = 0; index < result.length; index++) { list.options.add(new Option(result[index], result[index], false)); } } else { list.style.display = "none"; } }
In the following snippet we attach a handler to the page which assigns the selected item to the specified target and hides the active listbox.
$addHandler(document, "click", function(e) {
if ((e.srcElement == target) || (e.target == target)) {
return false;
}
list.style.display = "none";
if (list.value != '') {
target.value = list.value;
}
});
}
// Append the autocomplete listbox to the target textbox
function CreateList(sender) {
var div = document.createElement("div");
var list = document.createElement("select");
list.style.position = "absolute";
list.style.display = "none";
div.appendChild(list);
sender.parentNode.insertBefore(div, sender.nextSibling);
return list;
}
Other things one can look at is caching the ajax results in order to free the server up a bit more etc...
In the next part of this post we're going to have a quick look at how to use an autocomplete extender control that microsoft created (which should represent a more mature solution).
Additional Reading
AjaxControlToolkit : Autocomplete
Posted by - Christoff Truter
Date - 2011-03-27 20:50:07
Comments - 6
Date - 2011-03-27 20:50:07
Comments - 6
First 1 2 3 4 5 6 7 8 9 10 Last / 62 Pages (124 Entries)