Get T-SQL for Linq to Sql expression [draft]

Generate sql query for your linq to sql query expression. Doesn't support all dbtypes.

321

Usage:

void Main()
{
    var query = dataContext.Domains.Where(d => d.Name.Contains("test")).OrderBy(d => d.Name).Skip(100).Take(100);
    Console.WriteLine(this.GenerateToExecSql(query));
    Console.WriteLine(this.GenerateToPlainSql(query));
}

Code:

public static class Extensions
{
    public static string GenerateToPlainSql(this DataContext dc, IQueryable query)
    {
    	return generateSql(dc, query, false);
    }
    public static string GenerateToExecSql(this DataContext dc, IQueryable query)
    {
    	return generateSql(dc, query, true);
    }

    private static string generateSql(this DataContext dc, IQueryable query, bool isExecSqlQuery)
    {
    	string separator = isExecSqlQuery ? "," : Environment.NewLine;
    	string declareWord = isExecSqlQuery ? string.Empty : "DECLARE ";
    	string setWord = isExecSqlQuery ? string.Empty : "SET ";
    	StringBuilder sb = new StringBuilder();
    	StringBuilder sbParams = new StringBuilder();
    	StringBuilder sbValues = new StringBuilder();
    	var command = dc.GetCommand(query);
    	foreach (var param in command.Parameters.OfType<SqlParameter>())
    	{
    		if (param.SqlDbType == SqlDbType.VarChar || param.SqlDbType == SqlDbType.NVarChar)
    		{
    			sbParams.AppendFormat("{0}{1} {2}({3}){4}", declareWord, param.ParameterName, param.SqlDbType, param.Size, separator);
    			sbValues.AppendFormat("{0}{1} = '{2}'{3}", setWord, param.ParameterName, param.SqlValue, separator);
    		}
    		else if (param.SqlDbType == SqlDbType.Date || param.SqlDbType == SqlDbType.DateTime || param.SqlDbType == SqlDbType.DateTime2)
    		{
    			sbParams.AppendFormat("{0}{1} {2}{3}", declareWord, param.ParameterName, param.SqlDbType, separator);
    			sbValues.AppendFormat("{0}{1} = '{2}'{3}", setWord, param.ParameterName, param.SqlValue, separator);
    		}
    		else
    		{
    			sbParams.AppendFormat("{0}{1} {2}{3}", declareWord, param.ParameterName, param.SqlDbType, separator);
    			sbValues.AppendFormat("{0}{1} = {2}{3}", setWord, param.ParameterName, param.SqlValue, separator);
    		}
    	}
    	if (sbParams.Length > 0)
    		sbParams.Remove(sbParams.Length-1, 1);
    	if (sbValues.Length > 0)
    		sbValues.Remove(sbValues.Length-1, 1);

    	if (isExecSqlQuery)
    	{
    		sb.Append("EXECUTE sp_executesql N'").Append(query.Provider).Append("', ");
    		sb.Append(" N'").Append(sbParams).Append("', ");
    		sb.Append(sbValues).Append("");
    	}
    	else
    	{
    		sb.Append(sbParams).Append(sbValues);
    		sb.Append(query.Provider.ToString());
    	}

    	return sb.ToString();
    }

}