Home

Popsql

Popsql (pronounced 'popsicle') is a library eliminating the need for magic SQL strings in your code. It works by providing an API that allows you to build complex SQL expression trees from code and later render them into valid SQL text that goes right into your ADO.NET code or your favorite micro-ORM.

License

Popsql is Open Source software and is released under the MIT license. This license allow the use of Popsql in free and commercial applications and libraries without restrictions.

Getting Started

Installation

You can just add a reference to Popsql from NuGet using the Visual Studio UI or by running the following command in the package manager console.

Install-Package popsql

Your First Query

In the Popsql framework, everything starts with the Sql class, which provides static factory methods to get started with building your queries. Let's start with a simple example:

var sql = Sql
    .Select("Id", "UserName", "Email", "Age")
    .From("Users")
    .Where(SqlExpression.GreaterThanOrEqual("Age", 30))
    .OrderBy("Id", SqlSortOrder.Descending)
    .OrderBy("Name")
    .ToSql();

This sample generates the following SELECT query, which fetches all users older than 30 from the Users table.

SELECT [Id], [UserName], [Email], [Age] FROM [Users] WHERE ([Age] >= 30) ORDER BY [Id] DESC, [Name] ASC

Of course you can also use JOIN clauses and other more complex SQL syntax. As you can see, Popsql provides some syntactic sugar to simplyfy selecting the right tables for your columns as well:

SqlTable u = new SqlTable("Users", "u");
SqlTable p = new SqlTable("Profiles", "p");
var sql = Sql
    .Select(u + "Id", u + "UserName", p + "Email", p + "Age")
    .From(u)
    .LeftJoin(p, SqlExpression.Equal(u + "Id", p + "UserId"))
    .Where(SqlExpression.GreaterThanOrEqual(p + "Age", 30))
    .OrderBy(u + "Id", SqlSortOrder.Descending)
    .OrderBy(p + "Name")
    .ToSql();

Which yields the following masterpiece of selective power:

SELECT [u].[Id], [u].[UserName], [p].[Email], [p].[Age] 
FROM [Users] [u]
LEFT JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId])
WHERE ([p].[Age] >= 30) 
ORDER BY [u].[Id] DESC, [u].[Name] ASC

Inserting, Updating and Deleting

Insert

Of course, database-driven applications don't run very well when you can only read stuff from them, so you can also express INSERT, UPDATE and DELETE statements with Popsql. Let's start inserting some data in our fictional Users table:

var sql = Sql
    .Insert()
    .Into("Users", "Id", "UserName", "Email")
    .Values(SqlConstant.Null, "JohnD", "john.doe@popsql.local")
    .ToSql();

Will generate the following INSERT statement:

INSERT INTO [Users] ([Id], [UserName], [Email]) VALUES (NULL, 'JohnD', 'john.doe@popsql.local')

Multi-row Insert

Now, inserting data row-by-row is so last century and dead slow, so Popsql will also generate SQL multi-row insert statements if you ask it to:

var sql = Sql
    .Insert()
    .Into("Users", "Id", "UserName", "Email")
    .Values(SqlConstant.Null, "JaneD", "jane.doe@popsql.local")
    .Values(SqlConstant.Null, "JoeD", "joe.doe@popsql.local")
    .Values(SqlConstant.Null, "JimyD", "jimmy.doe@popsql.local")
    .ToSql();

Now we're talking. This will work if your RDBMS supports standard multi-row insert statements (most do!):

INSERT INTO [Users] ([Id], [UserName], [Email]) VALUES 
    (NULL, 'JaneD', 'jane.doe@popsql.local'),
    (NULL, 'JoeD', 'joe.doe@popsql.local'),
    (NULL, 'JimyD', 'jimmy.doe@popsql.local')

Update

We seem to have misspelled Jimmy's name in the previous statement, let's correct it:

var sql = Sql
    .Update("Users")
    .Set("UserName", "JimmyD")
    .Where(SqlExpression.Equal("Id", 4)
    .ToSql();

There, that's better.

UPDATE [Users] SET [UserName] = 'JimmyD' WHERE [Id] = 4

Delete

And finally, this is how you can express DELETE statements:

var sql = Sql
   .Delete()
   .From("Users")
   .Where(SqlExpression.GreaterThan("Age", 30))
   .ToSql();

Parameters?

Until now we have only used constants in our SQL, which is of course not exactly a safe to go. Luckily, with Popsql you can easily work with parameterized queries as well:

var sql = Sql
    .Insert()
    .Into("Users", "Id", "UserName", "Email")
    .Values(
        "Id" + SqlConstant.Null, 
        "UserName" + (SqlConstant)"JillD", 
        "Email" + (SqlConstant)"jill.doe@popsql.local")
    .ToSql()

And you will use parameters instead of constant values:

INSERT INTO [Users] ([Id], [UserName], [Email]) VALUES (@Id, @UserName, @Email)

Database Commands

Until now, we have only demonstrated the use of the ToSql() method. However, Popsql also provides an exentension method on each SQL statement that allows you to generate and IDbCommand directly from the expression tree. You only have to give it an instance of the IDbConnection interface of your bidding:

using (var connection = CreateDbConnection())
{
    var query = Sql
        .Select("Id", "Title", "Content", "PublishedOn", "PublishedBy")
        .From("Posts")
        .Where(SqlExpression.Id, "Id" + (SqlConstant)42);
    var command = query.ToCommand(connection);
    
    // Execute the command and do something awesome!
}