CSQLQueryExpress

CSQLQueryExpress is a C# NuGet Library designed to compile TSQL code, providing developers with the utmost flexibility to write expressions in C# that closely resemble TSQL syntax.

Note that while CSQLQueryExpress handles the compilation of TSQL code, the execution is delegated to any ORM such as Dapper.

In addition to compiling TSQL code, CSQLQueryExpress offers a CSQLQueryExpress.Scaffolding tool and a test client for writing and executing queries.

Key Features

• TSQL Syntax Familiarity: Write C# code that mirrors TSQL syntax, making it easier for developers familiar with TSQL.
• Dynamic Code Compilation: Compile TSQL expressions dynamically within your C# applications, enhancing flexibility and reducing the need for pre-defined static queries.
• Comprehensive TSQL Support: Support for a wide range of TSQL commands and expressions, ensuring compatibility with various database operations and scenarios.
• Seamless Integration: Easily integrate CSQLQueryExpress into your existing C# projects with minimal configuration, enabling rapid development and deployment.
• Documentation: Examples to help you get started quickly and make the most of CSQLQueryExpress's capabilities.
• Database Scaffolding Tool: Automatically generate database schema and compile the corresponding data model in C#, simplifying database integration and development.
• Test Client: A dedicated test client for writing and executing queries using Dapper, facilitating quick and easy query testing and validation.

Getting Started

Compile and execute your queries

1. Write TSQL Expressions: Use CSQLQueryExpress's intuitive syntax to write TSQL expressions directly in your C# code.
2. Compile: Compile your TSQL code dynamically.
3. Execute with an ORM: Use an ORM like Dapper to execute the compiled TSQL code.

Example

Here's a simple example to demonstrate how to use CSQLQueryExpress with Dapper:

usingCSQLQueryExpress;usingDapper;usingSystem.Data.SqlClient;usingSystem.ComponentModel.DataAnnotations;usingSystem.ComponentModel.DataAnnotations.Schema;classProgram{staticvoidMain(){SQLQuerySelect<Users>query=newSQLQuery().From<Users>().Where(u =>u.Age.IsNotNull()&&u.Age>30).Select(u =>u.All());vartSqlQuery=query.Compile();varstatement=tSqlQuery.Statement;//"SELECT _t0.* FROM [dbo].[Users] AS _t0 WHERE ((_t0.[Age] IS NOT NULL) AND (_t0.[Age] > @p0))"varparameters=tSqlQuery.ParametersKeyValue;//@p0 = 30using(varconnection=newSqlConnection("YourConnectionString")){connection.Open();varresult=connection.Query<Users>(statement,parameters);foreach(varuserinresult){Console.WriteLine($"UserID:{user.UserID} - FirstName:{user.FirstName} - LastName:{user.LastName} - Age:{user.Age}");}}}}[Table("Users",Schema="dbo")]publicclassUsers:ISQLQueryEntity{[DatabaseGenerated(DatabaseGeneratedOption.Identity)][Column("EmployeeID")]publicintUserID{get;set;}[Required][Column("FirstName")]publicstringFirstName{get;set;}[Required][Column("LastName")]publicstringLastName{get;set;}[Column("Age")]publicint?Age{get;set;}}

Generate data model from your database schema

Example

Set your database connection string in app.config file of ScaffoldingDatabaseTool and execute program:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<addname="DBConnectionString"connectionString="YOUR DATABASE CONNECTION STRING" />
</connectionStrings>
<appSettings>
<addkey="OverwriteExistingDataModelClasses"value="true"/>
<addkey="DecorateWithDatabaseAttribute"value="false"/>
<addkey="DataModelClassNamespace"value="QueryExecution.Dal"/>
<addkey="OutputFolder"value="..\..\..\..\..\QueryExecution.Dal\Dal"/>
</appSettings>
</configuration>

You will find your database's data model in the Query Execution.Dal project in a folder named after your database.

Use TestClient to write and execute your queries

Example

Set your database connection string in app.config file of QueryExecution.TestClient.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<addname="NorthwindPubs"connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=NorthwindPubs;Integrated Security=SSPI;" />
<addname="Your_Database_Name"connectionString="..."/>
</connectionStrings>
</configuration>

The connection name must match the name of the folder in the project that will contain your queries.

Example of a query written for the TestClient

usingQueryExecution.Dapper.CommandFactory;usingQueryExecution.Dapper.CommandFactory.Commands;usingCSQLQueryExpress;usingCSQLQueryExpress.Fragments;usingQueryExecution.Dal.NorthwindPubs;usingCSQLQueryExpress.Statements;usingCSQLQueryExpress.Schema;namespaceQueryExecution.TestClient.Queries.NorthwindPubs{internalclassSelectMostShippedProductsByShipper:SQLSelectQueryCommand<ShippedProductsByShipper>{publicSelectMostShippedProductsByShipper(ISQLQueryCommandFactorycommandFactory):base(commandFactory){}protectedoverrideSQLQuerySelect<ShippedProductsByShipper>GetQuerySelect(){SQLQuerySelect<ShippedProductsByShipper>cte=newSQLQuery().From<dbo.Shippers>().InnerJoin<dbo.Orders>((sh,ord)=>sh.ShipperID==ord.ShipVia).InnerJoin<dbo.Order_Details>((sh,ord,ordDet)=>ord.OrderID==ordDet.OrderID).InnerJoin<dbo.Products>((sh,ord,ordDet,prod)=>ordDet.ProductID==prod.ProductID).GroupBy((sh,ord,ordDet,prod)=>sh.CompanyName,(sh,ord,ordDet,prod)=>prod.ProductName).Select<ShippedProductsByShipper>((sh,ord,ordDet,prod,res)=>sh.CompanyName,(sh,ord,ordDet,prod,res)=>prod.ProductName,(sh,ord,ordDet,prod,res)=>Count.All().As(res.ProductCount),(sh,ord,ordDet,prod,res)=>Row.Number().Over(n =>n.PartitionBy(sh.CompanyName).OrderBy(Count.All().Desc())).As(res.RowNumber)).ToCteTable();SQLQuerySelect<ShippedProductsByShipper>query=newSQLQuery().From(cte).Where(c =>c.RowNumber<=10).OrderBy(c =>c.RowNumber.Asc(), c =>c.CompanyName.Asc()).Select(c =>c.All());returnquery;}}classShippedProductsByShipper:ISQLQueryEntity{publicstringCompanyName{get;set;}publicstringProductName{get;set;}publicintProductCount{get;set;}publicintRowNumber{get;set;}}}

Setup

Test Client: The test client includes example queries for this data model and is configured to connect to the NorthwindPubs database on a localdb MSSQLServer instance. To execute the test queries, you need to create the NorthwindPubs database on your localdb MSSQLServer instance. The scripts for creating the NorthwindPubs database can be found here:
https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs.

Note

Compile and verify generated statements from the library: Compile and test the TSQL statements generated by the library. The library translates the query you write respecting the expressions used. Some expressions can be used in different contexts, the library is not able to discern the intention of the writer, therefore the control both in the writing and verification phases lies with the developer.