2009.06.01

Some Resources for RSS Generation

Needing to generate RSS feeds from search results on a customer's ASP.NET web site, I sought out some info to assist and (re)discovered the following items, which I figured were worth sharing here, if for no other reason than to give this old blog a much needed kick in the pants.

Maybe this will get the old blog rolling again.

01:21 PM | Permalink | Comments (8) | TrackBack (0)

2008.01.29

FullTextSearch Normalization as SQL Server Function

Wanted to post this a week ago, but sickness and weather-related school closures have not been kind to my schedule. Also, I'm about done with a rewrite of my prior SQL Server Full Text Search condition normalization logic, to include some parsing improvements and noise word support, and hoped to post them simultaneously. Unfortunately, that's been delayed as well. So, rather than wait, I'm posting this now.

What this is is a kind contribution by Fabio Gava of Susegana, Italy explaining how he migrated my previously posted FTS condition normalization logic into SQL Server 2005 as a native function. This is possible, of course, because SQL Server 2005 includes support for hosting .NET assemblies, within certain limitations. This is a great solution for folks with a SQL Server/FTS back-end, but without support for hosting .NET logic on the front-end, whether for a web site or perhaps a client-based app.

I'm posting Fabio's instructions unaltered. I haven't actually tried this myself, but will with the new version when it's finished. It looks right to me, and he reports that it worked well for him. He also suggested that I "...please add also my email if someone needs help about this I could try to do it (since I'm not a C# developer)." So, if you have any questions or need some help, you can contact Fabio at fabio.gava AT bloomtech.it, or you can post a comment here and I'll try and take a look or will forward it to Fabio for you.

OK, I'll try to be as complete as possibile, let me know if you feel something is missing:

I begun with the this link:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

Using that instructions I was able to compile the FullTextSearch.cs, also if I had some troubles with the SAFE
options in creating the store function. 
Ah... I forgot to mention that I created a stored function, not procedure, since what I need is a returned value
(the normalized text), not a full dataset.

Since your class returns values as a standard DLL and it's not integrated with SQL Server, I added a class
"overwrapping" your class. This class has only one method, at the moment, who just call the NormalForm
method.
The code I added is:

 public sealed class btFullTextSearch {

 [Microsoft.SqlServer.Server.SqlProcedure]
    public static void NormalizzaRS(string condition)
    {
        FullTextSearch fts = new FullTextSearch(condition);

        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Normalizzato", SqlDbType.VarChar, 8000));
        SqlContext.Pipe.SendResultsStart(record);
                record.SetSqlString(0, fts.NormalForm);
  SqlContext.Pipe.SendResultsRow(record);
 SqlContext.Pipe.SendResultsEnd();
    }

    public static string NormalizzaVal(string condition)
    {
  FullTextSearch fts = new FullTextSearch(condition);
  return (string) fts.NormalForm;
    }
 }

Just before your class declaration.

Since I begun testing with recordset, I left both the method:
NormalizzaRS returns a dataset
NormalizzaVal returns a string

As you can see some SQL specific's units are required, so I added also:
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

That's all. I include also the T-SQL I used to declare external procedure and stored procedure and function:

CREATE ASSEMBLY NormalizzaFullText
FROM 'd:\fulltextsearch.dll' WITH PERMISSION_SET = UNSAFE

CREATE PROCEDURE sp_NormalizzaFullText
@testo NVARCHAR(500)
AS
EXTERNAL NAME FullTextSearchDLL.[Ewbi.btFullTextSearch].NormalizzaRS

CREATE FUNCTION sf_NormalizzaFullText(@testo NVARCHAR(500)) RETURNS
NVARCHAR(4000)
AS EXTERNAL NAME FullTextSearchDLL.[Ewbi.btFullTextSearch].NormalizzaVal

Thanks Fabio!

11:36 AM | Permalink | Comments (1) | TrackBack (0)

2007.12.13

Multi-line Regex String Literals in C#

It dawned on me after writing about the use of @-quoted string literal syntax for multi-line SQL string literals in C# that it's also very handy for complex regular expressions, like this:

private static Regex scrubPattern = new Regex(@"
  (?<quote>&quot;|\u201C|\u201D|&[lr]dquo;|&\#(?:8220|8221|34);)|
  (?<squote>&[lr]squo;|&\#(?:8216|8217|39);)|
  (?<win1252squote>\x92)|
  (?<amp>\&amp;)|
  (?<whitespace>\x09|\x0D|\x0A)|
  (?<ignoredEntities>&\#?[\w]{2,7};)|
  (?<html><[^>]+>)|
  (?<badxml>[\x01-\x08]|\x10|[\x0B-\x0C]|[\x0E-\x1F]|[\x80-\x9F])|
  (?<unisymbols>[\uE0AC-\uE0D5]|[\uF041-\uF07A]|[\uF0A0-\uF0FE])
", RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

Or this, from Wes Haggard's very interesting post Matching Balanced Constructs with .NET Regular Expressions:

Regex re = new Regex(string.Format(@"^
  {0}                       # Match first opeing delimiter
  (?<inner>
    (?>
        {0} (?<LEVEL>)      # On opening delimiter push level
      | 
        {1} (?<-LEVEL>)     # On closing delimiter pop level
      |
        (?! {0} | {1} ) .   # Match any char unless the opening   
    )+                      # or closing delimiters are in the lookahead string
    (?(LEVEL)(?!))          # If level exists then fail
  )
  {1}                       # Match last closing delimiter
  $", "<quote>", "</quote>"), 
  RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

re.Match("<quote>inner text</quote>").Groups["inner"].Value == "inner text"
re.Match("<quote>a<quote>b</quote>c</quote>").Groups["inner"].Value == "a<quote>b</quote>c"

This example from Mr. Haggard's post illustrates how to "retrieve the text between a set of tags when there is the possibility of the nesting." Good stuff.

Note that when initializing regular expressions with strings created using this type of multi-line syntax, it's necessary to include the RegexOptions.IgnorePatternWhitespace flag, because the resulting strings will include line breaks and indentation spaces preceding each line. And, as illustrated in the first example above, the RegexOptions.IgnorePatternWhitespace flag makes it necessary to escape #'s in the pattern to prevent them from being interpreted as x-mode comments.

10:37 AM | Permalink | Comments (0) | TrackBack (0)

2007.12.12

Google Does Numeric HTML Entities

Ran into an interesting Google feature today while researching some obscure entities found in some scraps of HTML that I had to scrub, store, and index. Maybe everyone knows this, but it was new to me.

If a Google search contains a numeric HTML entity, in the form &#xxx;, Google will convert it to its proper value. So, for instance, if you submit a search for "Air &#225; Danser", it will return "Air á Danser" and perform the expected search. It will not do the same thing for the equivalent named entity reference "Air &aacute; Danser".

So when faced with an unfamiliar numeric entity, like &#8734; or &#8501;, finding out what it looks like is as easy as a Google search:

http://www.google.com/search?q=%26%238734%3B  =  ∞
http://www.google.com/search?q=%26%238776%3B  =  ≈

Lest one think this is a mere byproduct of a web page taking a value in a POST and returning it as the value of a text input element, consider that neither of the other two major search engines provides this feature:

http://search.yahoo.com/search?p=%26%238734%3B
http://search.live.com/results.aspx?q=%26%238734%3B

Google is clearly evaluating the numeric entity, converting it to its proper character, and subsequently using the character in its search. Nice touch.

11:42 PM | Permalink | Comments (0) | TrackBack (0)

2007.11.28

SQL Server POWERSUM() in One Query

Update #2 11/29/2007
Oops. Robyn Page points out in a comment that I've more SELECTs than needed. I'm afraid that in my rush I did the first line and copied the remainder without thinking to pull the SELECTs and replace them with CASEs over a single SELECT. Here is Ms. Page's much cleaner solution:

select
 cast(
   left(
char(sum(distinct case when col/8=0 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=1 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=2 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=3 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=4 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=5 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=6 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=7 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=8 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=9 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=10 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=11 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=12 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=13 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=14 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=15 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=16 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=17 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=18 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=19 then power(2, Col%8) else 0 end))
+ char(sum(distinct case when col/8=20 then power(2, Col%8) else 0 end))
,max(col/8)+1)as varbinary)
from @data 

Btw, in a follow-up comment, Mr. Clarke suggests that his solution is different still than this. Can't wait to see.


Update 11/29/2007
A new comment-clue from Mr. Clarke suggests that the solution should work in SQL Server 2000 and that it will only handle input values beyond 127 with a little cut-and-paste. That led me to the following query:

select
  cast(
    left(
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 0), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 1), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 2), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 3), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 4), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 5), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 6), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 7), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 8), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 9), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 10), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 11), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 12), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 13), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 14), 0)) +
      char(COALESCE((select sum(distinct power(2, Col%8)) from @Data where (Col/8) = 15), 0))
    , (select max(Col/8)+1 from @Data))
  as varbinary(max))

Simple, but not as elegant as the recursive CTEs below. I think it satisfies the puzzle, though. Will await the solution.


In today's Daily Grind, Mike Gunderloy (curse his hide) linked to SQL Puzzle 8 by Red Gate Software engineer Lionel Clarke challenging readers to craft a one-query replacement for SQL Server's POWERSUM() function, which was apparently "removed" from SQL Server 2008 in the November CTP, as described by András, another Red Gate Software engineer. I'd never heard of nor used POWERSUM(), but I can't seem to leave challenges like this alone and so decided to take a look over lunch.

András' short explanation of what the undocumented POWERSUM() function does fell a bit short of the mark, but after a lot of testing I was able to get my head around it. And now, having done that, I'm not going to bother repeating here what I learned, unless someone really, really wants to know. After all, it's an undocumented and (now) dead function. I'll just say that it mostly does what András says, it just does more, and it does it in a really non-intuitive way.

Mr. Clarke's challenge was "to write a single select statement that will return the same results as POWERSUM when run on the supplied table variable", which in this case Mr. Clarke named @Data with a single INT column named Col. In a comment below his post, Mr. Clarke clarified that variables weren't allowed. And it's probably safe to assume that "single select statement" means no user-defined functions, too. However, Mr. Clarke never mentions whether he considers CTEs (Common Table Expressions) a legitimate extension of a "single select statement". I'm hoping he does, as I couldn't figure out how to do this without recursion, both for the production of a full range of bytes and for concatenation of the VARBINARY bytes. Perhaps in SQL Server 2008 there are new range-producing and/or aggregate-concatenating functions that would allow for the elimination of the CTEs, but with only SQL Server 2005 (and a lunch break) to work with, this was the best I could do.

The query below matches POWERSUM() for the values Mr. Clarke provides, as well as for every set of values I threw at it (with one exception, described below the query). It also satisfies Mr. Clarke's wish for a solution "that will scale to beyond" input values up to 127. As written below, the query will support input values to 807 before it runs into the default MAXRECURSION level of 100. If you need values greater than 807, add an "option (MAXRECURSION x)" hint to the end of the final SELECT, where "x" represents the number of levels you need. Or, just use a MAXRECURSION of 0 to allow recursion to any level, within the limits of your machine. I tested it with a MAXRECURSION of 0 and input values up to 20,000. My machine huffed and puffed, but after a few seconds did produce results that matched POWERSUM().

So, here's the query:

;with
psdata(value, byte) as (
  select distinct 
    Col, 
    (Col / 8) 
  from 
    @Data
),
psbyte(byte) as (
  select 
    max(byte) 
  from 
    psdata
 union all
  select 
    byte - 1 
  from 
    psbyte 
  where 
    byte > 0
),
psbytevalue(value, byte) as (
  select 
    cast(sum(b.bytevalue) as varbinary(1)), 
    b.byte 
  from (
    select 
      psdata.value, 
      case 
        when psdata.value is null then 0 
        else power(2, psdata.value - (8 * psdata.byte)) 
      end as bytevalue,
      psbyte.byte 
    from
      psbyte 
    left join 
      psdata on psdata.byte = psbyte.byte
  ) b 
  group by 
    b.byte
),
ps(value, byte) as (
  select 
    cast(value as varbinary(max)), 
    byte 
  from 
    psbytevalue 
  where 
    byte = 0
 union all
  select 
    cast(a.value + b.value as varbinary(max)), 
    b.byte 
  from 
    psbytevalue b 
  join 
    ps a on a.byte = b.byte - 1 
  where 
    b.byte > 0
)
select top 1 
  value 
from 
  ps 
order by 
  byte desc

So what's the one set of values that causes this query to produce a result different than POWERSUM()? An empty set. If zero rows are fed to this query it produces an empty result set. However, POWERSUM() somehow returns a zero-length VARBINARY value, which looks like "0x". There may be a way to do this with an expression, but I couldn't find it.

Anyhow, that's it. I can't wait to see what the "real" solution is. I have a feeling it will be significantly shorter and more succinct than what I've put together. Perhaps with more time I could collapse some of the CTEs out of this query and simplify it some, but there's no more time to spare.

09:37 PM | Permalink | Comments (2) | TrackBack (0)

2007.11.26

Why No Nz() Outside of Access?

Microsoft Access wires up its one DAO.DBEngine instance, its OLEDB-based ADO CurrentProject.Connection instance (starting with Access 2000), and its OLEDB-based ADO CurrentProject.AccessConnection instance (starting with Access 2002) with a reference to its Access.Application instance, as well to its opened and referenced VBA projects, thus making their properties, methods, and (for VBA projects) public functions available for use within SQL queries executed via these database vectors. This makes it possible to execute queries like the following from within Access, whether via the Query maintenance feature or in code using DAO via DBEngine or CurrentDb(), or ADO via CurrentProject.Connection or CurrentProject.AccessConnection:

select
  Version AS AccessVersion, 
  CBool(Visible) AS IsVisible, 
  CurrentUser() AS UserName, 
  CLng(DCount('1','MSysObjects','Type = 1')) AS NumberOfTables, 
  Eval('Date()') AS EvalDate, 
  GetOption('Default Font Name') AS DatasheetFontName, 
  Nz(Null,'was null') AS NzTest, 
  CLng(Forms.Count) AS NumberOfOpenForms;

In Access 2003 with one test form open, this query returns the following results:

Step out of Access, though, and try to execute a query like this using DAO or OLEDB via ADO or ADO.NET (with your own DBEngine and/or Connection instances) and you'll encounter an error - usually a 3085 "Application-defined or object-defined error" in VBA, or maybe a -2147217900 "Undefined function 'xxx' in expression" in .NET. There is no way to overcome this, short of newing up a full instance of Access via COM automation and executing your queries using the resulting Access.Application object's DBEngine property or CurrentDb() method (for DAO), or its CurrentProject property's Connection or AccessConnection properties (for ADO).

The only non-intrinsic SQL functions available in JET database queries outside of Access are those found in the VBA library, whose objects are exposed for use via the underlying JET database engine (with consideration for certain sandbox restrictions). You can perform some crazy-complex calculations in your queries using functions like VBA's IPmt() and PPmt(), and you can evaluate conditional logic using VBA's IIf(), but you cannot Nz() a column to overcome a Null value, because Nz() is a method of the Access.Application object.

So what are the alternatives to Nz()? Well, you could combine VBA's IIf() and IsNull() to do the same thing:

select
  IIf(IsNull(SomePossiblyNullField), '', SomePossiblyNullField)
from
  SomeTable

Or, if you're dealing with potentially Null string values, you can take advantage of the non-conforming JET SQL syntax's explicit concatenation operator (&), which it shares with VBA, and pound an empty string onto your value. The following query will produce the same result as the previous one:

select
  SomePossiblyNullField & ''
from
  SomeTable

I say non-conforming because in most cases combining Null with anything via an operator results in a Null; however, the concatenation operator works differently, treating Nulls like empty strings. Mathematical operators, like +, -, *, etc., do conform with standard tri-value Null logic in that their application to any Null value results in a Null expression, including the unfortunate case where + is applied to strings, which JET's SQL syntax and VBA still allow for historical reasons.

One more point regarding things you cannot do outside of Access. The ADO Connection exposed by the Access.Application object's CurrentProject property's AccessConnection property mentioned earlier is a multi-provider connection that relies on the new (and specially adapted) Microsoft.Access.OLEDB.10.0 OLEDB provider to support updateable bound-forms and other Access-specific activities (it relies on the Microsoft.JET.OLEDB.4.0 OLEDB provider for data access when targeting JET MDB databases; I assume other database-specific OLEDB providers when targeting other database types). As far as I can tell, it is not possible to directly instantiate an ADO Connection object using the Microsoft.Access.OLEDB.10.0 OLEDB provider outside of Access. This may be related to its being wired-up on initialization with a handle to the Access.Application object and VBA projects.

10:03 PM | Permalink | Comments (3) | TrackBack (0)

2007.11.21

Excel Text-to-Numbers: Method 8

There's a Microsoft Knowledge Base article that describes a number of different ways to convert text representing numbers into actual numbers in Excel. The KB article outlines a number of methods for doing this, where the applicability of each method depends on, among other things, how the data is sourced and how much data you're faced with:

  • Method 1: Use the Error Button
  • Method 2: Retype the Values in the Cells
  • Method 3: Edit Directly in the Cell
  • Method 4: Use the Paste Special Command
  • Method 5: Remove Hidden Characters and Spaces
  • Method 6: Use a Visual Basic for Applications Procedure
  • Method 7: Use the Text to Columns Command

I recently ran into a workbook whose particular number-as-text issue did not lend itself to any of these solutions directly. In this case, the rather large and complex workbook included numerous formulas that were, mid-formula, parsing the numerical portions out of some text values and then using them as the source for lookups in a range of numbers. The problem was that the parsed-out and concatenated number portions resulted in text values, not numbers, so they weren't being found in the range of numbers. Below is a screen-shot of a simplified example:

Notice how the RIGHT() function, a simple stand-in for the actual formulas' parsing and concatenation logic, results in a text value, even though the text represents a numerical value, and so isn't found with VLOOKUP() in the range of numbers.

Reviewing my options, it seemed Method 5 was the most likely solution. Contrary to its name, Method 5 doesn't actually suggest that you manually clean up the data by removing hidden characters and spaces, which is an obvious solution for some types of data issues, but instead describes how to use a formula to convert unclean textual representations of numbers into clean numeric values. The formula it provides by way of example is:

=VALUE(TRIM(CLEAN(A1)))

For my purposes, just the VALUE() function would suffice, but this presented a problem for this particular workbook and its troubled formulas. The problem was that the formulas were too long. Well, not too long yet, but the addition of seven more characters pushed a number of them over the 1,024 character limit. I might have mentioned that this was a terribly, and perhaps needlessly, complex workbook. And, unfortunately, it was not my workbook, so doing recon on some uncharted territory elsewhere in the workbook to lay down just the number parsing, concatenating, and now VALUE() converting, portions of the formulas was not an option.

The solution, which is the point of this post, was to eschew the VALUE() function and simply add 0 to the parsed values. Turns out that if you've got text that represents a valid number, adding 0 to it will convert it into an actual number. Try it yourself:

="1"+0

See? A number.

Below is a screen-shot of the same sample from above, but this time with the addition of 0 to the text-returning RIGHT()-parsed number values:

09:52 PM | Permalink | Comments (1) | TrackBack (0)

2007.11.19

SQL Server Left Anti Semi Join?

Irena Kennedy explains in the context of a "Good SQL Interview Question". You can add to her two examples the SQL Server 2005 EXCEPT operator, which, if you're keeping score at home, is SQL Server's answer to the old Oracle MINUS set operator. Btw, the SQL Server 2005 INTERSECT operator is a left semi join.

10:38 PM | Permalink | Comments (0) | TrackBack (0)

Funny Serialization Thread

I don't know why, but this kind of thing just slays me:

http://www.dotnet247.com/247reference/msgs/15/76044.aspx

To summarize: Sergiy Mesropyan asks how to circumvent the need for a public constructor on a class for which he wants to implement XML serialization. From the outset it's clear there's some confusion about whether Mr. Mesropyan wants binary/SOAP serialization or XML serialization, but everyone patiently explains basic serialization concepts over and over and over again, each time eliciting non-responsive responses from Mr. Mesropyan. The thread finally culminates in a frustrated full uppercase reply from Albert Cornejo exclaiming, "YOU CAN'T DO IT WITH THE XMLSERIALIZER!!!" To which Mr. Mesropyan replies, "That was the answer I was looking for! Thanks."

You just can't make this stuff up.

10:25 PM | Permalink | Comments (0) | TrackBack (0)

Multi-line SQL String Literals in C#

Back in September there were a couple of unrelated blog posts that used literal multi-line SQL strings as examples to illustrate two different points. The first by David Cumps investigated the performance aspects of string concatenation vs. memory allocation. The second by Chinh Do presented a number of Visual Studio 2005 IDE tips and tricks, one of which was the use of regular expressions in the find and replace dialog. In both cases, the syntax they chose to initialize their C# string variables with literal multi-line SQL statements were, in my opinion, more cumbersome than necessary. This may be due to their primary points being about something other than string initialization syntax; however, it seemed like something worth pointing out.

David Cumps uses the following syntax samples in his (very thorough) investigation of string concatenation performance:

string myString = "SELECT column1,"
                + "       column2,"
                + "       column3,"
                + "       column4,"
                + "       column5,"
                + "       column6,"
                + "  FROM table1 t1"
                + "  JOIN table2 t2"
                + "    ON t1.column1 = t2.column1";
return myString;

//---------

string myString = "SELECT column1,";
       myString += "       column2,";
       myString += "       column3,";
       myString += "       column4,";
       myString += "       column5,";
       myString += "       column6,";
       myString += "  FROM table1 t1";
       myString += "  JOIN table2 t2";
       myString += "    ON t1.column1 = t2.column1";
return myString;

//---------

string[] pieces = new string[] {
    "SELECT column1,",
    "       column2,",
    "       column3,",
    "       column4,",
    "       column5,",
    "       column6,",
    "  FROM table1 t1",
    "  JOIN table2 t2",
    "    ON t1.column1 = t2.column1"
};
return String.Concat(pieces);

//---------

StringBuilder builder = new StringBuilder();
builder.Append("SELECT column1,");
builder.Append("       column2,");
builder.Append("       column3,");
builder.Append("       column4,");
builder.Append("       column5,");
builder.Append("       column6,");
builder.Append("  FROM table1 t1");
builder.Append("  JOIN table2 t2");
builder.Append("    ON t1.column1 = t2.column1");
return builder.ToString();

//---------

StringBuilder builder = new StringBuilder();
builder.AppendFormat("SELECT {0},", "column1");
builder.AppendFormat("       {0},", "column2");
builder.AppendFormat("       {0},", "column3");
builder.AppendFormat("       {0},", "column4");
builder.AppendFormat("       {0},", "column5");
builder.AppendFormat("       {0},", "column6");
builder.AppendFormat("  FROM {0} t1", "table1");
builder.AppendFormat("  JOIN {0} t2", "table2");
builder.Append("    ON t1.column1 = t2.column1");
return builder.ToString();

And Chinh Do's regular expression-based formatting of a pasted SQL statement results in the following syntax, which is essentially the same syntax as in Mr. Cumps' first example:

string sql = "select "
+ "  employee_id "
+ "  , first_name "
+ "  , last_name "
+ "  , zip "
+ "from employees "
+ "where "
+ "  employee_id = :id ";

Using their examples, here is the syntax I use to initialize string variables with literal multi-line SQL statements in C#:

string myString = @"
  SELECT column1,
         column2,
         column3,
         column4,
         column5,
         column6,
    FROM table1 t1
    JOIN table2 t2
      ON t1.column1 = t2.column1";

string sql = @"
  select
    employee_id,
    first_name,
    last_name,
    zip
  from employees
  where
    employee_id = :id";

Very simple. No concatenation, no arrays, no StringBuilders, just a single string. The @-quoted string literal syntax does result in the string containing new-line characters at the line breaks, but I've never encountered an ADO.NET provider that had a problem with it. I just assumed that everyone did it this way.

01:17 PM | Permalink | Comments (11) | TrackBack (0)