The tools (various small helper libraries) are now under http://svn.castleproject.org:8080/svn/castlecontrib/Tools/

what's in there:

1. Castle.Tools.StaticMapGenerator - That one creates a typed tree representing js/css/image files on the site's filesystem.
   more here:
   http://www.kenegozi.com/Blog/2008/01/17/staticmapgenerator-for-asp-dot-net-first-teaser.aspx
   Which reminds me that I need to add .swf files to the mix ...
2. Castle.Tools.SQLQueryGenerator -  That one is for building plain old SQL strings in a typed and intellisense friendly way.
   more here:
   http://www.kenegozi.com/Blog/2008/01/27/already-added-stuff-to-sql-query-generator.aspx
3. Castle.Tools.QuerySpecBuilder/ - The new kid in the block. That's a tool that is used to build specs programmatically, which would later be translated to a SQL string for your flavour of DAL. The generated SQL is mostly ANSI compliant, apart from the Paging syntax which is TSQL only currently, but at some point I'll add an extension point to allow other syntaxes.
   I also have an external thing to make it extremely useful with NHibernate's ISQLQuery.
   I'll blog about all that when I'll have a little time.
   

I feel so stupid.

Just spent almost two hours trying to figure out why a SQL query wasn't running.

I kept getting "Incorrect syntax near 'RowNumber'" error message.

I was under the impression that my SQL syntax was ok, and tried various changes to the way I was invoking the query, but alas, nothing worked.

Then I just copied the raw query into SQL Manager, just to see the next snippet (the colouring, or lack of, was pointing this out for me):

WEHRE RowNumber BETWEEN @First AND @Last

WEHRE the hell was my head?

Today I've given a little help to a friend, with a JET 4.0 (Ms-ACCESS) thing.

Situation

Given an existing schema:

  Customers (Id, Name, ..., Email)
  Ads (CustomerId, ...)

the client wanted to add a field name TargetEmail to Ads table.

Adding the field was simple enough:

ALTER TABLE Ads ADD COLUMN TargetEmail TEXT(255)

Now the client wanted to initialise the TargetEmail field for existing ads, based on the Customer's email.

A Naive and SQL Server jockey as I am, I gave him that little snippet:

UPDATE   Ads 
SET      Ads.TargetEmail = Customers.Email
FROM     Ads 
    JOIN Customers ON Ads.CustomerId = Customers.Id
WHERE   Ads.TargetEmail IS NULL

Problem

JET had refused that syntax.

Or rather, Jet is weird.

Solution

Google to the rescue. answer was here.

UPDATE  Ads, 
        Customers 
SET     Ads.TargetEmail = Customers.Email
WHERE	Ads.CustomerId = Customers.Id
  AND   Ads.TargetEmail IS NULL

the new stuff:

• Reusing clauses
• Operators (||, &&, !) on where clause
• OrderBy clause

Examples:

Reusing clauses:

FromClause from = new FromClause(SQL.Blogs);
WhereClause where = new WhereClause(SQL.Blogs.Id == 2); 
SQLQuery q1 = SQLQuery
    .Select(SQL.Blogs.Id)
    .From(from)
    .Where(where); 
SQLQuery q2 = SQLQuery
    .Select(SQL.Blogs.Name)
    .From(from)
    .Where(where); 
Console.WriteLine(q1);
Console.WriteLine(q2);

makes

SELECT
                [dbo].[Blogs].[Id]
FROM
                [dbo].[Blogs]
WHERE
                ([dbo].[Blogs].[Id] = 2)
SELECT
                [dbo].[Blogs].[Name]
FROM
                [dbo].[Blogs]
WHERE
                ([dbo].[Blogs].[Id] = 2)

Operators:

SQLQuery q1 = SQLQuery
                .Select(SQL.Blogs.Id)
                .From(SQL.Blogs)
                .Where(SQL.Blogs.Id > 2 || SQL.Blogs.Name == "Ken");
Console.WriteLine(q1);

makes

SELECT
                [dbo].[Blogs].[Id]
FROM
                [dbo].[Blogs]
WHERE
                (([dbo].[Blogs].[Id] > 2) OR ([dbo].[Blogs].[Name] = N'Ken'))

OrderBy Clause:

SQLQuery q = SQLQuery
    .Select(SQL.Blogs.Id)
    .From(SQL.Blogs)
    .Where(SQL.Blogs.Id > 2)
    .OrderBy(Order.By(SQL.Blogs.Id), Order.By(SQL.Blogs.Name).Desc);
Console.WriteLine(q);

makes

SELECT
                [dbo].[Blogs].[Id]
FROM
                [dbo].[Blogs]
WHERE
                ([dbo].[Blogs].[Id] > 2)
ORDER BY
                [dbo].[Blogs].[Id],
                [dbo].[Blogs].[Name] DESC

Didn't have time to upload a binary, but you can simply grab the source and build yourself. it has absolutely no dependencies but .NET 2.0

Where from?

http://svn.castleproject.org:8080/svn/castlecontrib/Castle.Tools.SQLQueryGenerator/trunk/

UPDATE (22/06/2008):
The source has slightly moved (to a sub folder):
http://svn.castleproject.org:8080/svn/castlecontrib/Tools/Castle.Tools.SQLQueryGenerator/

What is it?

A tool that generates a strongly typed representation of a relational database, to be used for generating SQL queries in a type-safe fashion, with the aid of intellisense.

Where to get it?

• Current Binaries: from here
• Source code: http://svn.castleproject.org:8080/svn/castlecontrib/Castle.Tools.SQLQueryGenerator/trunk/ 

UPDATE (22/06/2008):
The source has slightly moved (to a sub folder):
http://svn.castleproject.org:8080/svn/castlecontrib/Tools/Castle.Tools.SQLQueryGenerator/

Limitations:

• Currently works only with SQL Server 2005. Patches for more DB types would be welcomed.
• Currently only SELECT queries are implemented. Soon I'll add support for generating INSERT, UPDATE and DELETE, too.
• GroupBy, Order By and Having clauses didn't make it to this initial release. I hope to add those this week.

How to use it?

1. Generating the classes:
   Run Castle.Tools.SQLQueryGenerator.exe.
   Parameters:
   The mandatory flag is /db:DBNAME where DBNAME is your database name.
   By default, the server being looked for is (local). you can select another using /server:SERVER.
   By default, Integrated Security is used. You can supply /userid:USER and /password:PASS to override it.
   You can alternatively supply a /connectionstring:CONSTR parameter.
2. Add the generated file, named "SQLQuery.Generated.cs" to your project.
3. Add a reference to Castle.Tools.SQLQueryGenerator.Runtime.dll
4. Use and Enjoy

Usage sample (from Examples.cs in the test project:

SQLQuery q = SQLQuery
    .Select(SQL.Blogs.Id, SQL.Blogs.Name)
    .From(SQL.Blogs);
Console.WriteLine(q);

Would print out:

SELECT
                [dbo].[Blogs].[Id],
                [dbo].[Blogs].[Name]
FROM
                [dbo].[Blogs]

Not impressed? Well,

dbo_ForumMessages Message = SQL.ForumMessages.As("Message");
dbo_ForumMessages Parent = SQL.ForumMessages.As("Parent"); 
SQLQuery q = SQLQuery
    .Select(Message.Id, Message.ParentId, Message.Content)
    .From(Message)
        .Join(Parent, Message.ParentId == Parent.Id);
Console.WriteLine(q);

Will spit out

SELECT
                [Message].[Id],
                [Message].[ParentId],
                [Message].[Content]
FROM
                [dbo].[ForumMessages] AS [Message]
    JOIN        [dbo].[ForumMessages] AS [Parent] ON
                    ([Message].[ParentId] = [Parent].[Id])

Need parameters?

Parameter<int> blogId = new Parameter<int>("BlogId"); 
SQLQuery q = SQLQuery
    .Select(SQL.Blogs.Id, SQL.Blogs.Name)
    .From(SQL.Blogs)
    .Where(SQL.Blogs.Id == blogId);
Console.WriteLine(q);

would echo

SELECT
                [dbo].[Blogs].[Id],
                [dbo].[Blogs].[Name]
FROM
                [dbo].[Blogs]
WHERE
                ([dbo].[Blogs].[Id] = @BlogId)

How can YOU help?

1. Use it. Praise it. Use Paypal.
2. Or you can suggest improvements, spot bugs, create patches and buy me beer.

Situation:

1. My SQL Server 2005 Express has a default collation of HEBREW_CI_AI
2. I have a dev database with Latin_General_CI_AS

Problem:

Trying to rebuild the database from script fails due to collation errors.

Explanation:

A bit of lookup into those using my good pal google, have proved my suspicions. Somewhere along the script a StoredProc is being created, which joins to a temp table. It appear that temp tables are being created within the tempdb system database, which in turn has the collation of the server.

The answer usually is to create temp tables with explicit collation. However, those stored proces are generated by the ASP.NET membership thing, so it cannot be setup to use the proper collation on temp tables.

So, Solution 1 (if it's not happening on generated stored procs):

Explicitly set the collation on temp tables.

Solution 2 (for the other poor people with generated sprocs):

You'd need to rebuild the system databases, either by reinstalling SQL Server, or following these instructions

The list of collation options can be found here

Nothing fancy.

With a little help from Moran Benisty, here's the script I use to get the metadata I need for the SQLQueryGenerator:

SELECT   schemas.name AS [Schema],
         tables.name AS [Table],
         columns.name AS [Column],
         CASE 
             WHEN columns.system_type_id = 34    THEN 'byte[]'
             WHEN columns.system_type_id = 35    THEN 'string'
             WHEN columns.system_type_id = 36    THEN 'System.Guid'
             WHEN columns.system_type_id = 48    THEN 'byte'
             WHEN columns.system_type_id = 52    THEN 'short'
             WHEN columns.system_type_id = 56    THEN 'int'
             WHEN columns.system_type_id = 58    THEN 'System.DateTime'
             WHEN columns.system_type_id = 59    THEN 'float'
             WHEN columns.system_type_id = 60    THEN 'decimal'
             WHEN columns.system_type_id = 61    THEN 'System.DateTime'
             WHEN columns.system_type_id = 62    THEN 'double'
             WHEN columns.system_type_id = 98    THEN 'object'
             WHEN columns.system_type_id = 99    THEN 'string'
             WHEN columns.system_type_id = 104   THEN 'bool'
             WHEN columns.system_type_id = 106   THEN 'decimal'
             WHEN columns.system_type_id = 108   THEN 'decimal'
             WHEN columns.system_type_id = 122   THEN 'decimal'
             WHEN columns.system_type_id = 127   THEN 'long'
             WHEN columns.system_type_id = 165   THEN 'byte[]'
             WHEN columns.system_type_id = 167   THEN 'string'
             WHEN columns.system_type_id = 173   THEN 'byte[]'
             WHEN columns.system_type_id = 175   THEN 'string'
             WHEN columns.system_type_id = 189   THEN 'long'
             WHEN columns.system_type_id = 231   THEN 'string'
             WHEN columns.system_type_id = 239   THEN 'string'
             WHEN columns.system_type_id = 241   THEN 'string'
             WHEN columns.system_type_id = 241   THEN 'string'
         END AS [Type],
         columns.is_nullable AS [Nullable]


FROM              sys.tables tables
    INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
    INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id) 
 

WHERE     tables.name <> 'sysdiagrams' 
    AND   tables.name <> 'dtproperties' 

ORDER BY [Schema], [Table], [Column], [Type]

Quick, Dirty, Working.

Anyone up to contributing a similar thing for SQL 2000 / MySql / Oracle / Postgres  / MS-ACCESS ?

it's going to be subversion-ed really soon.

Imagine you could write that in your IDE:

SQLQuery q = SQLQuery
    .Select(SQL.Blogs.Id, SQL.Blogs.Name)
    .From(SQL.Blogs)
        .Join(SQL.Posts, Join.On(SQL.Blogs.Id == SQL.Posts.BlogId))
    .Where(SQL.Blogs.Name != "Ken's blog");

Console.WriteLine(q); 

and getting that output :

SELECT [Blogs].[Id], [Blogs].[Name]
FROM ([Blogs] JOIN [Posts] ON ([Blogs].[Id]=[Posts].[BlogId]))
WHERE ([Blogs].[Name]<>'Ken''s blog') 

Soon enough you would be able to to that.

After having fun creating the Static Sitemap Generator, today I've had a little free time (as my main machine is being reinstalled), so I came up with a SQL query generator.

It would be a tool to generate classes out of a database, that would make writing typed sql queries a breeze.

I have most of it working, except the part where I retrieve the metadata from the database ... No worries, my good friend and SQL guru Moran is about to send me the queries for that real soon.

First release would work with SQL Server 2005, and later on I'll add extension points to hook up other db engines.

Using SQL Server Management Studio (be it the 'full' version or the express one), you can sometime encounter the error:

in words:

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

It usually happens when you've created that DB by a script (which was a bit incomplete) or by restoring a DB created on another machine, with some different settings.

Instead of going to the bottom of it, I'll just write down the simple solution, for future reference.

open a new Query, and run the next stored procedure:

EXEC sp_changedbowner 'sa'

If you have another default owner on your machine, use it's login instead of 'sa'.

That's it.

As you already have probably noticed by now, I did some renovation on my blog.

Among other things, it is now being served from a SQL Server database, rather than form the daBlog xml files.

One caveat of this, is the fact that backuping the blog's content became much harder. Since I have no access to db backups, I neede to find a way to generate the INSERT scripts that will enable me recreationg the content if it would be needed.

My first try-out was the Microsoft SQL Server Database Publishing Wizard, that I saw at Scott Gu's blog

This tool is meant to create the script form a local dev db, in order to make it run on the remote one. Actually you can make it run on the remote one, nd save the generated sql file locally, for backup purposes.

I tried it up, but it send some nasty .NET break dialogs. It however managed to create A script, that I'm yet to check for it's usability.

Nice tool. But I'll look for something that is pure t-sql so it'd be easier to run (maybe automated every once in a while)