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 ‘ASP.Net’

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 »

The horrors of Umbraco 4.0.0

Posted by Roy Triesscheijn on Thursday 5 March, 2009

Examining the title of this blog entry will reveal two things:

  • I tried to install the CMS Umbraco 4.0.0 (check it out here )
  • I wasn’t successful

Lately I’ve gained access to a ASP.Net 3.5 shared hosting solution provided by http://www.argeweb.nl and sponsored by Microsoft via Surfspot (a kind of dreamspark for Dutch students, not only for Microsoft products).

Alright it all seemed great. Windows Server 2008, IIS7, ASP.Net 3.5. All very much up to date technologies.

I  was first thinking about building my own CMS in ASP.Net/C#. But as you might know that is quite allot of work. So I went searching for a free .Net based CMS to start with, that I could later extend.

Quickly I found Umbraco, and after trying the demo site I was sold. The used techniques seemed solid, adding new features seemed easy, the source code was freely available and everything was written in ASP.Net 2.0.

I was sure installing it would be a breeze. I created a new database at my management panel at Argeweb and started uploading Umbraco to my websites root folder.

Quickly I encountered quite a few problems with the Web.config file that Umbraco provided. Apparently the Web.config file wasn’t compatible with IIS7’s default application pool, and since my hosting is shared I can’t change IIS’s settings. After allot of testing/googling I finally found 3 tweaks to make this config file work, but quickly NullReference and PermissionDenied exceptions where thrown.

After spending allot of time on the Umbraco forums I found out about a better config file that was IIS7 /ASP.Net3.5 compatible and installed it. All I now got was a permission exception. This seemed to be common in Medium Trust environments and I could simply delete the accused file. I also spoke to Argeweb and the agreed to check out the permissions. A few hours later they mailed that the permissions should be good enough for Umbraco. And they told me that Umbraco 3 was running on their shared host solutions. Of course I wanted Umbraco 4!

After allot more fiddling I finally got the installation screen to show up. I quickly accepted the licence and input my database settings.  A new error occurred stating that Umbraco couldn’t change the Web.config file (if it could that would be one hell of a security problem!) and that I had to change the connection string manually.  I quickly did so and restarted the installation. In the database step all fields where already filled in with the settings I put in the Web.config file, but it still wouldn’t let me pass because it couldn’t save my settings again!

After some searching I found a modified version of the database step *.asx file uploaded it and I could finally choose install.

Umbraco would install fine into my database (so the settings where correct). Great I thought! Now I should be getting somewhere. However the next step I had to change my admin password, ok great, Umbraco has DB access so this couldn’t possibly be a problem. Of course after pressing OK I was proved wrong, and another error occurred. This time in one of the role providers. Unfortunately I still haven’t found a way to work around this. I even edited the Web.Config file to think that the installation was done (yes this too was in the Web.config file and thus totally un-settable for Umbraco). But all I got was a redirect stating that my nodes are empty and that I should add a page. A fancy “start Umbraco” button was provided, but unfortunately this button did absolutely nothing…

After spending two days I still haven’t been able to install Umbraco. In the forums not even the developers know what permissions Umbraco exactly needs and they just state “Full Trust will do the trick.” or “Better change hosts.” But I don’t want to change hosts! Argeweb was very helpful and did all they could to get this working, and I understand that they wont elevate my account to Full Trust, which is quite a security hole.

I wasn’t the only one with all these problems, the forums are swarmed with it, and frankly it seems impossible to install Umbraco at a server you don’t control directly. (I tried to install in the root folder, which gave me all these problems. Installing Umbraco in a nice virtual folder like  http://mywebsite.com/umbraco would cause even more problems according to some users (and probably the end of the world as we know it).

To bad that Umbraco is the only real free solution that had the features that I wanted. At the moment it seems that I’m going to delve into ASP a bit more and see if I can come up with a very very very very very simple CMS.

 

I hope some one out there has more luck with Umbraco than me, it really seems to be awesome technology!

 

Art

(if you even dare to try to reinstall Umbraco you’ll get a nice NullReferenceException)

Posted in Webdesign | Tagged: , , , , , | 15 Comments »