Geek Daily

Here is what I liked this week. Enjoy!

Comparing Popular JavaScript/Ajax Frameworks
After four days of ASP.NET AJAX training with Stephen Walther I set out to learn more about my options in choosing a solution for a JavaScript/Ajax framework. If I realized days later I would be writing this comprehensive post on 7 of the most popular frameworks, I may have just went with the “Inny-Minny-Miney-Moe” method!

jQuery AJAX calls to a WCF REST Service
Since I’ve posted a few jQuery posts recently I’ve gotten a bunch of feedback to have more content on using jQuery in Ajax scenarios and showing some examples on how to use jQuery to cut out ASP.NET Ajax. In this post I’ll show how you can use jQuery to call a WCF REST service without requiring the ASP.NET AJAX ScriptManager and the client scripts that it loads by default. Note although I haven’t tried it recently the same approach should also work with ASMX style services.

SQL SERVER - Better Performance - LEFT JOIN or NOT IN?
First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing query? Answer is : It depends!

Video: Write Your First Silverlight Game
In this video, I demonstrate how to start writing your first Silverlight game. I show how to create a dramatic space scene, add a soundtrack, and associate movement with the mouse wheel. This is the first part of a two-part series.

Reading binary files using Ajax
But when it comes to binary files, helping hands from server-side technologies are often necessary.

So I googled around to see what I can do about binary files with Ajax and found this Marcus Granado’s post at http://mgran.blogspot.com/2006/08/downloading-binary-streams-with.html

What he posted there worked like a charm for FireFox and Safari but I couldn’t get it to work for IE.

But luckily, within the same page, someone had posted up a solution for IE as a comment, which is written in VBScript.

Safari CSS Masks
Webkit continues to impress with it’s early implementations of new standards. WebKit now supports alpha masks in CSS. Masks allow you to overlay the content of a box with a pattern that can be used to knock out portions of that box in the final display. In other words, you can clip to complex shapes based off the alpha of an image.

Popularity: 36% [?]

I talked about an issue I ran into between DateTime.MinValue and SqlDateTime.MinValue in a previous post. One other point I noticed while researching the topic was that, unlike almost all other types, SqlDateTime doesn’t have a TryParse method.

I really like the TryParse method because it helps me write code that is more terse and clean. I can easily use the TryParse methed as the condition for an if statement to handle the outcome without have to resort to ugly try catch blocks all over the place.

On Ian’s suggestion in the comments to my previous article I decided to take a stab at writing an extension method to implement this functionality. Now I said almost in the post title because I couldn’t get the TryParse method attached to the SqlDateTime native class. Now this could be due to this being my first attempt at an extension method so if anyone has any advice or suggestions on making this solution better feel free to leave them in the comments and I’ll update the code.

The solution

As I said I couldn’t get the method attached to the native type so this is the next best thing. I have declared a XSqlDateTime type that contains the TryParse method.

public static class XSqlDateTime {
	public static bool TryParse(string str, out SqlDateTime output) {
		try {
			output = SqlDateTime.Parse(str);
			return true;
		}
		catch {
			output = SqlDateTime.MinValue;
			return false;
		}
	}
}

This is easily used like so.

SqlDateTime date;
XSqlDateTime.TryParse("1/1/1953 12:00 AM", out date);

Just like all other implementations of TryParse on other types, if this method returns false it will assign the default type value to the output variable. In this case it is SqlDateTime.MinValue.

Hope this is useful to someone. I know it is for me. Cheers.

Popularity: 18% [?]

There are many arguments to whether stored procedures or dynamic sql is best when developing applications. I don’t think there is a cut and dry “best”. As with most things in programming, it boils down to what is best in your current situation.

A case for stored procedures

I have worked with stored procedures and dynamic sql extensively on several different platforms. When working in C# or ASP.Net I like to use stored procedures for most of my database access. However with all technologies there are limitations and times when a different approach is required. When a stored procedure cannot easily complete the task I look to dynamic sql.

I like stored procedures for a couple of reasons. Firstly, any values I pass in are automatically parameterized and sanitized for me so I don’t have to worry about special characters (like ‘) or sql injections (for the most part). Second, all the sql code is in one repository and I know exactly where to look when faced with an sql error and the procedures are easily reused saving on duplication if I had used dynamic sql.

A case for dynamic sql

When I am working in PHP and MySQL I always use dynamic sql. I do this also for a couple of reasons. My PHP applications tend to be on a much smaller scale then when I work in C#. This is because I use C# at work where we build enterprise applications and I use PHP at home for personal projects. I also use dynamic sql when working in PHP because my hosting is on CPanel which gives me access to my MySQL database with phpMyAdmin which does have a nice interface for stored procedures like SQL Server. Dynamic sql is just easier here. Although there are a few more things to deal with when generating dynamic sql it is very flexible and is only limited by your platforms implementation of SQL. Flexibility is always a huge plus.

Conclusion

So I stand currently on stored procedures as my favorite method right now. Perhaps I’ll get ambitious and look more closely at stored procedures in MySQL. I have played a bit with NHibernate and LINQ for C# which have solid benefits that may sway my opinion once I have used them more.

Popularity: 15% [?]

Working with non-nullable types in C# can be a bit of a pain. For instance when I have a date as a string and need to parse it into a DateTime what should the value be if the parse fails? I can’t use null because DateTime is not a nullable type.

This is exactly the dilema I encountered today. No worries, I’ll use DateTime.MinValue that way it is constant and I don’t have to worry about being consistent if I had chosen an arbitrary value of my own.

Well as it turns out I did have some worries. Sql Server 2000’s minimum DateTime value is not the same, in fact it is quite different. This kept causing errors.

For your reference here are some values you should take note of.

DateTime myDate = DateTime.MinValue; //=> 1/1/0001
SqlDateTime mySqlDate = SqlDateTime.MinValue; //=> 1/1/1753
//also note that SQL Server's smalldatetime min value is 1/1/1900

So my problem was easily averted, after a quick Google search. I just had to use SqlDateTime.MinValue instead of DateTime.MinValue.

Cheers

Popularity: 29% [?]

Funny cartoon I read today. If only…

Click to see whole strip…

Popularity: 13% [?]

ASP Free has written a great article on how to update a database using a SqlDataSource programmatically which is much better, in my opinion, than embedding it in the ASPX file.

In the previous article we talked about using the SqlDataSource control programmatically to obtain a result set from the database using a Data Reader object and a Data View object. We used an HtmlTable control to create a visual representation to hold the returned result set. Today we are going to use the SqlDataSource control to insert data into the Employees table of the Northwind database.

In case you haven’t read the previous article I will show you the relevant code from it again, but I advise you to read the complete article when you can. Meanwhile, before we learn how to insert data, let’s run a similar version of the previous article’s code again. Next is the complete code for the code behind the Default.aspx.cs file…

Popularity: 9% [?]

A very amusing example of why you need to sanitize user input.

Click to enlarge

Popularity: 10% [?]

By: lucasgreen.com

Whenever you purchase or acquire a MySQL database (such as from a website like WebContents.org), chances are it will come as a MySQL injection script. An injection script is simply a SQL script that, when executed, creates the appropriate database tables and inserts the data using INSERT statements (one statement per record of data). Because of this, a SQL injection script is usually very large, but this is the easiest way to export and import a database so this is how it is usually done.

Once you have the injection script, installing it is usually very easy. Of course, you must already have MySQL Server (mysql.org) installed on your server, so if you haven’t done that yet (such as if this is a new server), do it first, then put the script file in an easily accessible location (like the root folder of the server’s main hard drive, usually drive C, or in the temp folder).

The process for installing a MySQL database using a MySQL injection script is fairly trivial, but there are a couple of snags you might run into along the way. The only way to find out if these apply to you, however, is to attempt to install the database using the script normally, and deal with exceptions as they occur. To execute the injection script, you first need to create a database to hold the data. Open a command prompt and type in the following command to start the MySQL command-line interface: “mysql -u root -p” (without the quotes). It will prompt you for the root password, then you will be logged into the MySQL instance.

To create the database, use the command “create database imported;” (without the quotes). Substitute the word imported for whatever database name you would like to use, and make sure you put in the semicolon at the end. If successful it should say “Query OK, 1 row affected”. Now you are ready to run the injection script itself.

To execute the injection script, use the command “source c:\temp\vegrecipes.sql;” (without the quotes). Use whatever path and filename points to your injection script, and don’t forget the semicolon at the end! It may take a while to run depending on how large the script is, but it should say “Query OK, 1 row affected” for each record that it inserts into the database.

That’s it! The database should be installed, and you can find the data in whatever table is named at the beginning of the MySQL injection script (open it in notepad to see). The two most likely problems you might run into are unicode translation and script size. Depending on where you got the script, it may be formatted for a different Unicode set than your system normally uses — if the script won’t execute at all, open the script up in notepad and look for strange characters at the beginning of the file (delete them if you find any), then re-run the script. The other problem that might occur is if the injection script is VERY large — your system may run out of memory or hang while it is trying to load/run the script. In this case, you might want to split the injection script into multiple files that can be executed in sequence. If you need to do this, you can use any program that will split a file BY LINE (not by characters or bytes because you don’t want half a command to be in one file and the other half in the next), but a good one to use is SplitFile from rethinkit.com.

Now that you have the capability to install externally obtained MySQL databases using MySQL injection scripts, you may find that it is much easier to acquire databases this way rather than building them yourself from scratch. A good place to get MySQL injection script databases is www.WebContents.org. This is a great way to add large amounts of fresh content to your website, or even content that is not necessary but could be a nice additional feature for your website such as a joke or quote of the day, or food recipes, or rss feeds… the possibilites are endless!

Popularity: 9% [?]

By: lucasgreen.com

MySQL Server is the most widely used database management system in the world, primarily because it is open source and free. Hence, most databases you may get from outside sources will probably be in the form of a MySQL injection script. This is fine if you use MySQL for your own website databases, but if you use Microsoft SQL Server the script will require a little editing before it will work.

The first thing you’ll need to do is remove any comment lines from the script. MySQL comment lines begin with a pound character (”#”) and MSSQL comment lines begin with a double dash (”–”), which makes them completely incompatible and will product a syntax error if you try to import a MySQL injection as-is into MSSQL Server. So to get started, open up Query Analyzer if you haven’t already (the easiest way to run scripts in MSSQL Server), load up the injection script you are working with, and remove any comment lines (look for the pound symbol). It is easier just to remove them than it is to try and convert them to propery MSSQL syntax, and they are just comment lines anyway so it won’t affect anything.

The bulk of your script will most likely be a series of INSERT statements, and these aren’t very different in MSSQL as compared to MySQL. However, your script may also include at the beginning a small section that creates the database table where the data will be inserted, and this CREATE TABLE statement is likely to be VERY different in MSSQL, depending on how complicated it is (there could be primary and secondary keys, constraints, even triggers — the more of these the more the syntax changes from MySQL to MSSQL). Since this is likely to give you the most trouble, it is recommended that you create the database tables manually in Enterprise Manager rather than trying to convert the syntax of the script snippet. Looking at the code, you should be able to easily identify the fields and their types (such as int, varchar, text, etc). Once you have the database table created in Enterprise Manager, delete the snippet of code from the injection script that deals with the creation of the table.

Now all that remains is to convert the INSERT statements to the proper syntax for MSSQL Server. There are a few different steps to accomplish this, but none of them are very complicated. The first difference in syntax between MySQL and MSSQL is that in MySQL, all statements must end with a semicolon (”;”). In MSSQL, this is a syntax error. The easiest way to remove these semicolons is to do a search and replace, and since the INSERT statements should be passing a series of values for each record of data, each line of the MySQL script will most likely end with a paranthesis and semicolon (”);”). So, do a search and replace and replace all instances of “);” with just the parenthesis “)”.

Another difference that you will have to correct for is that your MySQL injection script will most likely use an acute accent / reverse apostrophe (ANSI character 180) around the table name on each line. In MSSQL Server, you can encapsulate an object’s name (such as a table’s) with either square brackets (”[” and “]”) or nothing at all. However, you probably don’t want to do a blanket search-and-replace of the reverse apostrophe character, because that character might be used in the data of each record (especially if the data contains text, such as an article body). The easiest way to correct for this difference in syntax, then, is to do another search and replace, and replace all instances of the reverse apostrophe AND the table name, for example “`articles`” with just the table name “articles”.

Finally, there will also be numerous occurrences of apostrophes throughout the text fields of the data, and the apostrophe character is used to encapsulate strings in the script. In MySQL, the way to escape an apostrophe so that the script knows it is part of the text and not the end of the string, is to use a backslash followed by the apostrophe (”\’”). In a MSSQL Server script, the proper way to escape an apostrophe is to use a double apostrophe (”””). So, one more search and replace is called for — this time, replace all instances of [\’] with [”] (double apostrophe, NOT an actual quotation mark).

Once these steps are all complete, you are ready to run the script! There shouldn’t be any other syntax changes you’ll have to make, but don’t worry if there are because when you execute the injection script it will tell you if there are any errors. If everything was corrected properly and there are no errors, you should get a series of “1 row(s) affected” responses — one for each INSERT statement in the script. If you want to verify that the proper number of records are in the database table, you can execute a “select count(*) from tablename” statement to count the rows of the table — it should match the number of lines in the injection script, give or take a few for blank lines, etc.

That’s it! Your options are now increased tremendously, because now you can use either MySQL or MSSQL injection scripts to import acquired databases into your database system. If you use MySQL as your dbms, you can do this process in reverse to convert a MSSQL injection script into a MySQL one. Either way, you now can import data using an injection script from either of the two most popular database management systems in the world. Now, where to obtain such databases or injection scripts is another question entirely, and beyond the scope of this article. Suffice it to say that there are numerous sources on the internet where you can purchase or acquire databases — a good one is www.WebContents.org. I think you will find that not only is it much easier to acquire content databases for your users than it is to build them from scratch, but it also is an easy way to add a lot of new, fresh content for your users with a minimal amount of time and effort. Using this method, you can get databases of articles, jokes, quotes, recipes, etc, and put them right on your website or any other database-integrated application, with very little work. Good luck!

Popularity: 11% [?]

I came across this great article by Jon Galloway on why you should not use SELECT * style queries.

Are SELECT * queries bad? Sure, everyone know that. But, why?
It’s returning too much data, right?

That’s the common answer, but I don’t think it’s the right one. If you’re working with a reasonably normalized database, the actual network traffic difference is pretty small.

Popularity: 8% [?]