Flepper Library

Flepper is a library to facilitate some database interactions that we need to perform in our applications. The initial idea of Flepper is to provide a query builder for writing SQL queries fluently, improving the readability of your code.

Authors

Nicolas Takashi, Gustavo Fontes, Alberto Monteiro

Download

You can download this project in either zip or tar formats.

You can also clone the project with Git by running: $ git clone https://github.com/Flepper/flepper

Installing

Packge Manager: Install-Package Flepper.QueryBuilder

.NET CLI dotnet add package Flepper.QueryBuilder After executing one of the commands above you will have the Flepper installed and ready to be used.

1. Using Flepper Query Builder

The FlepperQueryBuilder class contain all necessary implementation that can enable you make your commands.
Bellow, you going to see how to create a simple CRUD statements.

Create Select Queries

To create a simple select statement: Select().From("tableName");

The output query is: SELECT * FROM [TableName]

Select with Where

Select() .From("tableName") .Where("column1") .EqualTo("param") .And("column2") .NotEqualTo("param2");

The output query is: SELECT * FROM [tableName] WHERE [column1] = 'param' AND [column2] <> 'param2'

Create Update Queries

To create a simple update statement:
Update("dbo", "table") .Set("column", "value") .Where("column").EqualTo("value");

The output query is:
UPDATE [dbo].[table] SET [column] = 'value' WHERE [column] = 'value'

Create Delete Queries

To create a simple delete statement:
Delete().From("Table");

The output query is: DELETE FROM [Table]

Create Insert Queries

To create a simple insert statement:
Insert("table", "column1", "column2") .Values("value1", 2)

The output query is: INSERT INTO [table] ([column1],[column2] ) VALUES ('value1',2)"

Building queries

For All commands, after create a query, use FlepperQueryBuild.Build() method in order to generate the query

Example:
Select().From("Table") .Build()

2. Advanced Select Statement

There are a lot of select statement features that you can to compose your query with flepper. Here, We will to show to you how you can do select with inner join, left join, how you can use sql functions like Max, Min, Count and how you can use alias table at the columns and alias to columns

Creating Select statement with Joins


                        FlepperQueryBuilder

                        .Select(

                            "Name",

                            "Age",

                            "PostalCode")

                        .From("User").As("usr")

                        .InnerJoin("Address").As("adr")

                        .On("usr","AddressId").EqualTo("adr","Id")

                        .Build();

The code above generate the bellow query:

SELECT Name, Age, PostalCode FROM [User] AS usr INNER JOIN [Address] AS adr ON [usr].AddressId = [adr].Id

The sintax to create LEFT JOIN is not much diferent. See bellow an example:

FlepperQueryBuilder .Select( "Name", "Age", "PostalCode") .From("User").As("usr") .LeftJoin("Address").As("adr") .On("usr","AddressId").EqualTo("adr","Id") .Build();

The code above generate the bellow query:


                        SELECT

                        Name,

                        Age,

                        PostalCode

                    FROM [User] AS usr
 
                    LEFT JOIN [Address] AS adr
 
                    ON [usr].AddressId = [adr].Id

Creating select statement with column alias

To create column alias and sql function, like MAX, MIN SUM , you need Flepper.QueryBuilder.FlepperQueryFunction namespace.

Bellow, you going to see how create a column alias.

Select( As("Name","FirstName"), "Age") .From("User")

the code above generate the query:

SELECT Name As FirsName, Age, PostalCode FROM [User]

Creating select statement with table alias

Bellow, you going to see how create a table alias.

Select( "Age") .From("User").As("usr")

the code above generate the query:

SELECT Age FROM [User] AS usr

Creating select statement with Count, Max and Min sql functions

Bellow, you going to see how create sql function.

Select( Max("Age","OlderAge"), Min("Id","YoungerAge"), Count("Id","UserQuantity") .From("User") .GroupBy("Age","Id")

the code above generate the query:

SELECT MAX([Age])As OlderAge, MIN([Age])As YoungerAge, COUNT([Id]As UserQuantity) FROM [User] GROUP BY [Age], [Id]

Important! You need use GroupBy command Explicity in order sql function work correctly.