If you have ben working with Linq, one of the barriers you may have encountered is the "2100 parameter limit" when attempting to build an "IN" statement through Linq. A common scenario is that you are given a large list of ID's that you have need to select from. A common way of implementing this could be reviewed here:
http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/
However, if you implement this method and have greater than 2100 parameters, your going to need a different option. Critics will probably argue that "you shouldn't be doing that in the first place" and suggest you examine your design. But sure enough, the scenarios exist and in some cases, your just going to need to get creative.
Recently I went through this myself and came up with a pretty neat extension for IQueryable objects.
public static List<T> In<T>(this IQueryable<T> q, Database Context, String ColumnName, ArrayList Values)
{
String SqlCmd = Context.GetCommand(q).CommandText;
StringBuilder SQL = new StringBuilder();
// First make sure there are no joins - which would mean multiple "where's"
if (SqlCmd.ToLower().Contains(" join "))
throw new NotSupportedException("\"In\" does not support join clauses.");
// Next make sure there are not more than 1 "select's" - which could mean multiple "where's"
if (SqlCmd.ToLower().IndexOf("select ") != SqlCmd.ToLower().LastIndexOf("select "))
throw new NotSupportedException("\"In\" does not support sub queries.");
// Append everything up until the "where" part of the query.
// If there is no where part, just add everything.
if (SqlCmd.ToLower().Contains("where"))
throw new NotSupportedException("\"In\" does not support additional where clauses.");
else
SQL.AppendFormat(SqlCmd.ToString());
SQL.AppendFormat(" where [{0}] IN (", ColumnName);
for (int i = 0; i < Values.Count; i++)
{
if (i < Values.Count - 1)
SQL.AppendFormat("'{0}',", Values[i]);
else
SQL.AppendFormat("'{0}')", Values[i]);
}
return Context.ExecuteQuery<T>(SQL.ToString()).ToList<T>();
}
The limitations are:
- The "IN" statement must be your first statement in the clause.
- You cannot use joins (although you could use a view to get around this)
- You cannot use subqueries (again, you could use a view to get around this)
Everything else is fair game and it makes it easy to share accross your other object.
A sample query then looks like:
List<MyTable> returned = Context.MyTable.In(Context, MyTable.COLUMNS.SomeColumnName, MyItemsAsArrayList);
A quick look at resolving an issue that can arrise when you use the SqlCacheDependency class in conjunction with a database restore job.
Chances are, you've seen a
Captcha. You probably had to fill one out to sign up for an account somewhere. Chances are less likely that you've had to implement a Captcha. Chances are even more less than likely that you've actually looked into whether the Captcha can be defeated or not.
The answer is yes,
Captchas can be defeated. Even the best of them have flaws. They are being defeated by using the same algorithms that are used to track people in video cameras. For those of you who are 3D vector mathematics wizards, if that's even the proper name for it, I direct you to
research done at the University of Berkeley. For the rest of us, it is enough to understand that it can be done.
The Human Barrier
In Berkeley's research, they used the "Gimpy" Captcha engine, which is used over on
Yahoo. They claim that their algorithm has been able to
break the Captcha 92% of the time. Another group,
PWNtcha, has been able to break many other Captcha mechanisms in practice and have made their code publicly available.
The underling theme is that
it is only a matter of time before all of the Captchas are broken. The problem is that a Captcha must be able to be solved by a Human. This puts a limit on the level of difficulty possible and, in my opinion, renders Captchas a poor choice for defense against bots in the future.
A Better Captcha
A great alternative to Captchas is a similar idea but using pictures of objects and requiring a human to interpret them. A good example is
Better Than Captcha (BTC). While this may not be perfect and may be slightly more trouble for the end-users to figure out (as it did me), it is sure to be a much better system then text based Captchas.
A follow up the previous article of 6 six things every ASP.NET developer should know.