No longer updated: new and updated blog (including old content) at http://roy-t.nl

Archive for http://roy-t.nl, please update your bookmarks

Posts Tagged ‘Stored Procedure’

Fun with stored procedures: insert only if unique. (Transact-SQL/MS SQL)

Posted by Roy Triesscheijn on Tuesday 10 March, 2009

As I’ve said in my previous post stored procedures are allot more powerful than I thought. And really have their own scripting (compiled scripts that is) access to the database.

Stored procedures are often used to enhance the speed of queries that are performed very often, however modern database systems nowadays track queries and store/compile queries that are performed allot of times. So what are the modern day uses for stored procedures?

Stored procedures:

-Save roundtrips  (if you want to do a query based on the result of another query, you can compact those two queries in one).

-Can batch work (if you always do 2 queries at the same time you can call them in only one trip to the database).

-Are Save (in most modern database systems stored procedures are called using parameterized queries or special objects that already wrap/escape dangerous user input).

Can put extra constrains on data (first check if some other value in the database is not interfering with the soon to be executed query)

Can synchronize across multiple applications (stored procedures can lock fields/rows/columns/tables for a short amount of time for better concurrency support)

Today I’m going to show a very simple stored procedure written in Transact SQL Microsoft and Sybase’s proprietary SQL dialect, and C# that hopefully demonstrates all except for the last of the previous points.

First I’ve created a small class that represents a user. The class has the following standard props:

public String UserName { get; set; }
public String FirstName { get; set; }
public String LastName { get; set; }
public String Email { get; set; }

And a special prop that handles password hashing (never save a password as plain text in a database, always save a hash) (note: be sure to add a using directive for System.Security.Cryptography;)

public String Password
 {
 get
 {
 return passwordHashed;
 }
 set
 {
 UTF8Encoding encoder = new UTF8Encoding();
 MD5CryptoServiceProvider hasher = new MD5CryptoServiceProvider();
 passwordHashed = encoder.GetString(hasher.ComputeHash(encoder.GetBytes(value)));
 }
 }
 private string passwordHashed = "";

Now start up SQL Server Management Studio (Express/Basic) and navigate to your database, then select the folder programmability->stored procedures and right click to insert a new stored procedure. (Also make a user table if you haven’t got a table yet where you want to try this out on, I’m using a simple table with all the values as nvarchar(50)’s and the id as an int.)

Normally before you insert a user you first verify that the input is legit (a valid email address, strong enough passwords and a username exceeding some minimal length). That can all be handled client and server side using .Net’s validation controls.  Once you’ve done that you would query the database if the username and email address already exist. If not you would run another query (insert) to input the actual data.  Because when inserting a user you will always have to do the ‘check if exists’ query first, and inserting users might be a common task, there is room for improvement here.

I’ve done that using the following stored procedure:

Note: when multiple pages/threads call this sp it might suffer from race conditions, for more information about race conditions see the comments section. (Especially the first comment by Alister).

CREATE PROCEDURE InsertUniqueUser
@Username nvarchar(50),
@Password nvarchar(50),
@LastName nvarchar(50),
@FirstName nvarchar(50),
@Email nvarchar(50),
@UserID int OUTPUT
AS
IF NOT EXISTS(SELECT Username FROM Users WHERE Username=@Username OR Email=@Email)
 BEGIN
 INSERT INTO Users
 (Username, Password, LastName, FirstName, Email)
 VALUES (@Username, @Password, @LastName, @FirstName, @Email)
 SET @UserID = @@IDENTITY
 END
ELSE
 BEGIN
 SET @UserID = -1
 END

Be sure to execute this procedure to insert and compile the actual stored procedure.

As you can see this stored procedure has 5 inputs (Username etc..)  and one output: the UserID.  The syntax is pretty simple. First an internal (thus fast) query is performed to check if there exists a record that either has a username same as the input username or an email address same as the input email address. If this is not the case the actual work begins and we do a normal insert query. At the end of the query the UserID output value is set to either the identifier of the inserted record or –1 if there was no record inserted.

This procedure saves a round trip to the database, batches work, put’s extra constrains (and validation) on the data in the database and is safe (as we will see shortly after)

To use this fine stored procedure we will have to create a special form of a parameterized query in C# that looks like this:

int newID;
 SqlConnection connection = new SqlConnection(connectionString);
 SqlCommand command = new SqlCommand("InsertUniqueUser", connection);
 command.CommandType = System.Data.CommandType.StoredProcedure;
 command.Parameters.Add(new SqlParameter("@Username", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Username"].Value = user.UserName;
 command.Parameters.Add(new SqlParameter("@Password", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Password"].Value = user.Password;
 command.Parameters.Add(new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@LastName"].Value = user.LastName;
 command.Parameters.Add(new SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@FirstName"].Value = user.FirstName;
 command.Parameters.Add(new SqlParameter("@Email", System.Data.SqlDbType.NVarChar, 50));
 command.Parameters["@Email"].Value = user.Email;
 command.Parameters.Add(new SqlParameter("@UserID", System.Data.SqlDbType.Int, 4));
 command.Parameters["@UserID"].Direction = System.Data.ParameterDirection.Output;

try
 {
 connection.Open();
 command.ExecuteNonQuery();
 newID = (int)command.Parameters["@UserID"].Value;
 }
 catch (Exception ex)
 {
 //trace error
 string log = ex.Message;
 }
 finally
 {
 connection.Close();
 }
 return newID;

As you can see we are building a pritty normal stored procedure. be sure to System.Data.SqlDbType values for the SqlParameters. Also note that in the last line before the try we set the Direction of the “@UserID” parameter to Output. This way the stored procedure can store data in UserID.

If you build a small webpage/winforms app around this you will see that the newID returned is the ID value of the the newly inserted record in the database if there where no duplicates, or that the newID was –1 and that there haven’t been made new records.

Posted in Blog, Databases | Tagged: , , , , , , | 10 Comments »

Book review: Pro ASP.NET 3.5 in C# 2008 and my reasons for reading it!

Posted by Roy Triesscheijn on Tuesday 10 March, 2009

Well instead of studying I’ve lately become obsessed with ASP.Net and started reading the book named in the title! Although the book is written by a different author than the Pro C# 2008 and the .Net 3.5 Framework, the style is quite similar, and the quality is very high! I’m one of those weird freaks who tries to read books like these from the beginning to end as to learn all the tips and tricks contained in those 1500 pages.

I’m a bit surprised by the ordering of the chapters, of course allot of pages are dedicated to the ever important techniques for the DAL (data access layer). I’m a bit struck that authentication, security and log-ins/profiles are pushed back to the end of the book and complicated techniques like caching are in the first third of the book. (Btw I really love how ASP.Net makes caching easy!).

The authors explain everything brilliantly simple and there are allot of code examples (and a big plus here is that each code sample really provides you with a new technique or thought pattern, where the Pro C# book would some times have useless code examples). I’m also pretty sure that after reading this book you know how to write a good webpage. Many best practices are explained (in code and text) and each new technique starts with an overview of how was it done before. Of course every (new) technique has drawbacks or scenarios where it’s not the best tool for the job. Matthew MacDonald and Maria Szpuszta save you the time of weighting each technique by providing nice tables with pro’s and cons.

 

Anyway (not sure if I’ve told this in my Umbraco rant), I’ve started becoming interested in ASP.NET because Argeweb, in cooperation with Microsoft, provides all Dutch students entitled to SurfSpot a free .nl domain name and adequate Windows Server 2008 hosting (IIS7, ASP.NET.3.5, SQL Server 2005 and even PHP and MySQL support). So I’ve registered a nice domain name and started to joke around.

I sure hope I can show you guys something soon!

5 out of 5 stars 🙂

Oh did I mention they also give a pretty thorough explanation of stored procedures, damn I had no idea you could do so much in DB code. The follow up to this article is a small stored procedure sample for inserting users only if they have a unique username and e-mail address without additional round trips.

Posted in Blog, Webdesign | Tagged: , , , , , , | 1 Comment »