Is Linq really such a good idea? Are SQL Strings inside C# really such a bad idea?

Today, a friend at work asked me a question "how to sort in a stored procedure?" Of course there is a simple answer:

CREATE PROCEDURE [dbo].[Sorter]()

AS

SELECT * FROM Table ORDER BY Field

 

But... what if you want to order by any field of the table? That is a "dynamic order by"... well then:

CREATE PROCEDURE [dbo].[Sorter](@SortOrder tinyint = NULL

AS

SELECT Field1, Field2, Field3 FROM Customers ORDER BY CASE WHEN @SortOrder = 1 THEN Field1 WHEN @SortOrder = 2 THEN Field2 ELSE Field3 END

Great!... well not that much, because it only works as expected if all 3 fields are of the same type (of course we could convert them all to string... I mean "nchar")

My friend has all this problems because he is trying to avoid the dynamic slq approach (either creating his own dynamic sql generator, or using any of the available ORMs)

But this post is about LINQ... with LINQ, we will have relational extensions right there inside C#... that means no more language mixing (SQL inside strings inside C#)... but that also means... no more dynamic manipulation of SQL (AFAIK C# can not manipulate C# as a string, they way it does with SQL)... So... will LINQ really simplify development of data manipulation applications? or will it complicate it more by preventing us from easly and dinamically manipulate queries ? or is this just an SQL limitation (maybe SQL should allow as to have parametrized sorting?)?

I guess I'll have to do more research...

Handling Relative & Absolute URLs with System.Web.VirtualPathUtility

Until today, I translated between relative & absolute paths in ASP.NET "by hand". Example:

 

private string ToAbsolute(string url)
{
if (url.StartsWith("~"))

return (HttpContext.Current.Request.ApplicationPath +
url.Substring(1)).Replace("//", "/");
return url;
}

 

But now, it turns out the with ASP.NET 2.0... I can achieve this effect... by simply calling VirtualPathUtility:

 

VirtualPathUtility.ToAbsolute(url)

 

Much simpler... don't you think?

Autocomplete for ASP.NET Ajax Beta 2 is Ready!

Finally the autocomplete for ASP.NET Ajax Beta 2 is ready!

HttpUtility.UrlEncode is NOT equivalent to javascript "escape"

A few days ago, I posted about how to fix a "," problem in an very good autocomplete control.

As part of the solution I proposed to use "HttpUtility.UrlEncode" thinking that it was equivalent to javascript "escape" function, but.. it is not equivalent!

For example, HttpUtility.UrlEncode encodes the " " (whitespace) to "+", while javascript encode escapes the " " to "%20".

The same things goes for encodeURI in case you were wondering (and please don't even bother trying with HttpUtility.HtmlEncode, its purpose is completely different)

The answer? good old JScript.NET (in the server side):

Microsoft.JScript.GlobalObject.encode(string)

The documentation reads "This method supports the .NET Framework infrastructure and is not intended to be used directly from your code." but, since there is no other option, and I NOT going to implement this by myself, i think this is the best option available.

Do you know any other way to do it? would you please share it with me?

Objecthood!

According to the wikipedia, objecthood is: "is the state of being an object." I particularly liked that article, specially the part that says that:

"Theories of objecthood address two problems: the change problem and the problem of substance."

The answer to change problem is the answer to the following question, if object manifest themselves as cluster of properties... if you remove all the properties... what remains? According to substance theory, the answer is a substance (that which stands under the change)... if we apply this to Object Relational mapping... then.. the objectId (primary key) is the substance? Could this be used as a philosophical justification for the immutability of the primary key?

Then the text in the wikipedia continues: "Because substances are only experienced through their properties, a substance itself is never directly experienced." Is that a justification for never using the id (keeping it private)? Of course, there is another theory, the theory that since substance can not be experimented... then it doesn't exist, that is the "bundle theory".

In bundle theory all objects are merely a cluster of their properties and therefore... everything changes always... and no such thing as object ids/ primary keys really exist in the real world. This has an impact in relationships too, because we usually define a relationship as something that binds together 2 or more objects (at their substance level?)... but since object's substance doesn't actually exist then there isn't such thing as a "relationship" between two objects..

According to this relationships can only exist as something defined at the "property cluster level".. two (or more) objects aren't really related at the substance level... they simply "share a property" (is that why in SQL there isn't a way to make queries taking advantage of the integrity relationships? is "shared property" a better name for integrity relationships?)

And the thing gets worse: "Whether objects are just collections of properties or separate from those properties appears to be a strict dichotomy. That is, it seems that objects must be either collections of properties or something else." Is that the philosophical foundation for the "object/relational impedance mismatch"?

It seems that the conflict between a relational (bundle theory) and object (substance) worldview is there since way before a relational database or an object oriented language was invented...

I wonder what else could I learn from reading about this philosophical theories... (could there be a third one? is there a software programming paradigm for it?)

Okey, things I miss about NHibernate

Lately I have been working without a formal object relational mapping (you know, using custom "hand made" business objects, and fetching data from the database using stored procedures)

Here are the things I miss from NHibernate:

• Automatic caching of objects by primary key
• Easy support for primary keys with multiple fields
• Polymorphic Queries (and all the benefits of persistent inheritance)
• Queries with "." navigation "object.Relationship.Relationship.Field = :parameter"
• Prefecthing relationships: "load Products with their to one relationship with Vendor preloaded"
• Easy pagination (instead of trying to figure out how to do it in combination with stored procedures for a particular version of a particular database (that can be a real nightmare))
• Interactive single and multicolumn sorting (try to do it with stored procedures, and of course without cheating by using the execute command, remember if you use execute all the so called "security benefits" of stored procedures vs dynamic sql dissapear)
• Concurrency support (automatical increment of a "version" field when saving changes, even if those changes are not just field changes, but relationship changes, custom exception type for concurrency errors, optimistic locking)
  
• Query by example (no need to concatenate strings by hand based on whether a field is null, no need to worry about upper case or lower case comparision when comparing each field, no need to update a query definition if a column is added or dropped from a table)

And, if somehow you have to work with a database that doesn't have support for stored procedures (and you can't use NHibernate)... things get even more "interesting":

• Alias for ugly table names and ugly field names (instead of "select * from PDT where PCKT = 1" you write "select * from Products where PackageType = 1"
• Named Parameters: Instead of "select * from PDT where PCKT = ? and RQTY = ?" you can write: "select * from Products where PackageType= :packageType and RemainingQuantity = :remainingQuantity" this is specially a nightmare when one has to use OleDb, and it gets worse with Inserts or Updates: "INSERT INTO PDT (NN,PCKT,RQTY,DRP,PRNBR) VALUES (?,?,?,?,?)" instead of the simple: "session.save(product)"

I will update this list as it increases...

Fixed "," problem with SmartAutoComplete

Going back to the "," problem the solution seems to be modifing this
in the SmartAutoCompleteExtender.cs  (remember to add a reference to the Microsoft.JScript.dll to use GlobalObject.escape instead of HttpUtility.UrlEncode)

public string GetCallbackResult()
{
if (_completionItems == null)
return string.Empty;
for (int i = 0; i < _completionItems.Length; i++)
{
_completionItems[i] = (GlobalObject.escape(_completionItems[i]));

}
string join = string.Join(",", _completionItems);
return join;
}

And this in SmartAutoCompleteExtenderBehavior.js

this._onCallbackComplete = function(result, context)
{
var results = result.split(',');
var decodedResults = new Array();
for (var i=0; i<results.length; i++) {
decodedResults.push(unescape(results.getItem(i)));
}
if(_enableCache)
{
if(!_cache)
_cache = { };
_cache[context] = decodedResults;
}
_autoCompleteBehavior._update(context, decodedResults, false);
}

The trick is to "escape" things in C#... and "unescape" them in JavaScript (I hope this helps someone else with this issue, it works fine for me)