SQL Query with C# Typed Library - CooQ

Introduction

• The main idea started from: http://www.jooq.org, http://typedquery.sourceforge.net
• A library useful to make typed-safe query to your database, currently support PostgreSQL and MSSQL.
• A UI tool to generate mapped class from your database.
• Sometimes, you work with ORM and you want to make some SQL query or get only some columns which you need. You can use CooQ with your existing ORM project, the only thing you must to do is generate your classes and make query with syntax as below.
• You can find the code in this link: https://sourceforge.net/projects/cooq

Background

• Already work with ADO.NET

Generate Database Objects Class

• Currently supports 2 database types - MSSQL and PostgreSQL. I using velocity to generate class by template. You can extend as you want.
• To generate template, you should open file CooQGenerate.exe then fill specific required information to connect to your database, then click Generate button.

Init CooQ with Connection String

You must init some information for CooQ once.

//for PostgreSQl
CooQ.Query.Init(new CooQ.ConnectionSetting()
  {
    Server = "localhost",
    Database = "database_name",
    Username = "postgres",
    Password = "root",
    Port = 5432,
    Type = CooQ.Types.DatabaseType.POSTGRESQL
});

//for MSSQL
CooQ.Query.Init(new CooQ.ConnectionSetting()
  {
    Server = @".\SQLEXPRESS",
    Database = "database_name",
    Type = CooQ.Types.DatabaseType.MSSQL
});

Make Query.

Note that your database naming convention should be lowercase with _, for example your table name is TableTest then it should be table_test in your database.

Suppose you create a table in your database named table_test and your generated class is:

//we have 2 mapped class after generated:
Table.TableTest.cs
Record.TableTestRecord.cs

Supposed that table_test have 2 columns named column_a, column_b.

//We have two mapped columns after generated:
TableTest.TABLETEST.COLUMNA
TableTest.TABLETEST.COLUMNB

Then, you make a first query as below:

1. Select Query

   IQueryable<Record> res = CooQ.Query.Select(TableTest.TABLETEST)
          .From(TableTest.TABLETEST)
          .Execute()
          .GetRows();
   
   foreach (TableTestRecord record in res)
    {
      //your code here...
      Debug.Print("Column A is:" + record.ColumnA);
    }
   

2. Insert Query

   Query.Insert(TableTest.TABLETEST)
         .Set(TableTest.TABLETEST.COLUMNA, "value 1")
         .Set(TableTest.TABLETEST.COLUMNB, "value 2");
   

3. Update Query

   Query.Update(TableTest.TABLETEST)
         .Set(TableTest.TABLETEST.COLUMNA, "value 1")
         .Set(TableTest.TABLETEST.COLUMNB, "value 2");
   

4. Delete Query

   Query.Delete(TableTest.TABLETEST)
           .Where(TableTest.TABLETEST.COLUMNA = "value 1");