Already Added Stuff To SQL Query Generator

on January 27th, 2008 at 10:52pm , 2 responses

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/

SQL Query Generator - First Release

on January 27th, 2008 at 4:01pm , 12 responses

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?

 

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.

Cannot Recreate Database With ASP.NET Membership Due To Collation Problems

on January 24th, 2008 at 10:44am 2 responses

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

Retrieving All Column Names And Types From SQL Server 2005 For .NET

on January 19th, 2008 at 6:41pm , 11 responses

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.

SQL Query Generator

on January 19th, 2008 at 2:33pm , 4 responses

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.

I CAN HAS SITE - My LOL Blog, And Accessibility

on January 18th, 2008 at 8:07am , , 0 responses

There's this cool little site that LOL-ify any web page.

This is how my blog would've looked like had I been a kitten. It would also most probably be written in LOLCODE.NET

 

Now, the interesting part.

 

I got that through Roy Osherove's blog. That's how his blog looks like when LOL-fied.

 

Can you see the difference?

 

When designing my blog's markup, I paid good attention to the fact that the actual content (posts) should come before the side-bar with links, archive, blogroll, ads or whatever.

 

The more 'legacy' kind of web design (usually with tables, but can also be "achieved" with div/css) is to box everything around the content, and having the ViewContents (or ContentPlaceholder) as the last thing on the Layout (or MasterPage).

 

So when my blog is being read by a machine (that parses html), the important things is first.

You might say - I don't give a crap about LOL sites, and my site is for humans, not machines.

But what about the blind who 'reads' helped by a machine that reads the page and say it out loud? must they get the whole links part on every page before they get to the content?

What about search index bots? we should help them get to the content.

YAGNI - My Tiny IoC Feels Lonely

on January 17th, 2008 at 11:23pm , 0 responses

It's funny. At the end of the day, I didn't use the tiny IoC in the StaticSiteMap for the testing.

It was fun however.

StaticMapGenerator Source Is Available

on January 17th, 2008 at 11:19pm , , 0 responses

The Static SiteMap Generator's home is in Castle Contrib, and it's named Castle.Tools.StaticMapGenerator

 

I've just commited it to the repository, so it's at http://svn.castleproject.org:8080/svn/castlecontrib/Castle.Tools.StaticMapGenerator/trunk/

 

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

 

I hope to have time soon to blog about the creation of this little tool, and of the usage. Also, expect a binary soon.

StaticMapGenerator for ASP.NET, First Teaser

on January 17th, 2008 at 12:01pm , , 1 responses

Last night I got frustrated with the fact that I have no intellisense (nor compile time check) for locating static files like .js, .css and image files.

So I sat up and created a simple console application that can generate exactly that, out of the site's filesystem.

 

usage:

D:\MyTools\StaticMapGenerator /site:D:\Dev\MySite

it generates a file called Static.Site.Generated.cs within the site's root folder, and then I go and include that file in my web project.

No I can do stuff like:

<script type="text/javascript" src="<%= Static.Site.Include.Scripts.myscript_js %>"> </script>
 
<link rel="stylesheet" href="<%= Static.Site.Include.CSS.master_css %>" />
 
<img alt="Ken Egozi" title="My Logo" src="<%= Static.Site.Include.Images.Logos.my_logo_png" />

 

How cool is that?

It works in every ASP.NET compatible web framework (MonoRail, ASP.NET MVC, even WebForms ...)

The only prequisite is .NET 2.0 runtime.

 

Sorry for keeping it out of reach for the moment. I need a little bit of time to setup a svn repository to make the source public (it would of course be BSD/Apache2/MIT thing) and to upload a binary. No promises given, I'll try to make it in the coming weekend, or even tonight, so stay tuned.

The code is somewhat naive, and certainly does not cover any edge cases, however it's enough to work cleanly on the largest project I'm currently involved in (Music Glue). Patches to make it more configurable and able to handle more edge cases would be gladly accepted once it's out.

 

One cool spot - as part of this, I have also implemented my tiny IoC container in 33 LoC.

It's My Turn To Build An IoC Container In 15 Minutes and 33 Lines

on January 17th, 2008 at 9:16am , , 11 responses

Last night I've built a nice new tool called StaticMapGenerator which is used to generate a typed static resources site-map for ASP.NET sites (works for MonoRail, ASP.NET MVC and even WebForms).

I'll blog about it on a separate post in details.

Since I didn't want any dependency (but .NET 2.0 runtime) for the generator and the generated code, I couldn't use Windsor to IoC. That calls for a hand rolled simple IoC implementation

Ayende has already done it in 15 lines, but I wanted also to automagically set dependencies and have a simpler registration model.

so I've quickly hacked together a configurable DI resolver (a.k.a. IoC container) in 15 Minutes and 33 Lines Of Code. Call me a sloppy-coder, call me whadever-ya-like. It just works.

static class IoC 
{
   static readonly IDictionary<Type, Type> types = new Dictionary<Type, Type>();
  
   public static void Register<TContract, TImplementation>()
   {
      types[typeof(TContract)] = typeof(TImplementation);
   }
  
   public static T Resolve<T>()
   {
      return (T)Resolve(typeof(T));
   }

  
   public static object Resolve(Type contract)
   {
      Type implementation = types[contract];
  
      ConstructorInfo constructor = implementation.GetConstructors()[0];
  
      ParameterInfo[] constructorParameters = constructor.GetParameters();
  
      if (constructorParameters.Length == 0)
         return Activator.CreateInstance(implementation);
  
      List<object> parameters = new List<object>(constructorParameters.Length);
  
      foreach (ParameterInfo parameterInfo in constructorParameters)
         parameters.Add(Resolve(parameterInfo.ParameterType));
  
      return constructor.Invoke(parameters.ToArray());
   }
}

Ok, I've cheated. You'd need using statements too, but you can see that I was generous enough with newlines ...

Usage:

Given those:

public interface IFileSystemAdapter { }
 
public class FileSystemAdapter : IFileSystemAdapter { }
 
public interface IBuildDirectoryStructureService { }
 
public class BuildDirectoryStructureService : IBuildDirectoryStructureService
{
   IFileSystemAdapter fileSystemAdapter;
   public BuildDirectoryStructureService(IFileSystemAdapter fileSystemAdapter)
   {
      this.fileSystemAdapter = fileSystemAdapter;
   } }

You can do that:

IoC.Register<IFileSystemAdapter, FileSystemAdapter>();
 
IoC.Register<IBuildDirectoryStructureService, BuildDirectoryStructureService>();
 
IBuildDirectoryStructureService service = IoC.Resolve<IBuildDirectoryStructureService>();
You need not worry about supplying the BuildDirectoryStructureService with an implementation for the service it depends on, but only to register an implementation for that service.

Conditional Rendering, or I Do Not Want Analytics Code On Dev Machine

on January 12th, 2008 at 2:27pm , , 0 responses

From time to time you'd want some of your markup rendered only on 'real' scenarios. For example, you wouldn't want google analytics to track visits you do on your dev machine. Sometime you'd even develop while your machine is not even connected to the internet, and every page would try get the analytics script and will behave strangely.

 

In Monorail, the Request has a property named IsLocal, just for that. I've wrapped it in a nice ViewComponent.

 

public class GoogleAnalyticsComponent : ViewComponent
{

    public override void Render()
    {
        if (Request.IsLocal)
            return;
        RenderView("AnalyticsCode");
    }
}

 

Accompanied by the AnalyticsCode view template:

<%@ Page Language="C#" Inherits="Castle.MonoRail.Views.AspView.ViewAtDesignTime" %>
<script src="https://ssl.google-analytics.com/urchin.js" type="text/javascript"></script>
<script type="text/javascript">
    _uacct = "MY_URCHIN_CODE";
    urchinTracker();
</script>

, that can easily be extensible to set the urchin code with a parameter.

Yet Another Supercool Macbook Edition

on January 11th, 2008 at 6:43am 0 responses

I wonder. Would this babe come with a T7700, 4GB and a 7200RPM?

AspView and the Latest Castle Trunk

on January 7th, 2008 at 8:11pm , , 2 responses

Lately there has been a major refactoring work being done on MonoRail, as part of the effort toward a release hopefully later this year. As of today, AspView's trunk is once again compatible with Castle's trunk.

As usual, code's here, binaries are here.

Good News For Web Developers - IE7 Made It To Automatic Updates

on January 4th, 2008 at 7:04pm 2 responses

The title say it all.

I really hope that IE6 would be obsolete as soon as possible. It would make writing decent markup much easier, not needing to look out for weird IE6 quirks. Also, won't need to hack my way into look at pages in IE6 (since my machine has IE7 for some time now)

Last month's IE visitors to my site (according to google analytics):

  1. IE7 - 64.46%
  2. IE6 - 35.47%
  3. IE5.01- 0.06% (WTF? who uses that?)

I really hope that soon enough IE6 would join IE5 in the not-important area.

 

btw, IE visitors in total are 45.51%, so IE6 visitors are only 16%. wow. that's good, especially since the DotNetKick widget does not render well on IE6 and I never found time to fix that. I guess I'm not going to ...


Follow

Statistics

Posts count:
447
Comments:
951