Click here to Skip to main content
15,867,453 members
Articles / Database Development / PostgreSQL
Tip/Trick

SQL Query with C# Typed Library - CooQ

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
2 Nov 2015MIT1 min read 8.3K   6  
Mapping your database with C# object class, then make typed-safe query like SQL syntax

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.

C#
//for PostgreSQl
CooQ.Query.Init(new CooQ.ConnectionSetting()
  {
    Server = "localhost",
    Database = "database_name",
    Username = "postgres",
    Password = "root",
    Port = 5432,
    Type = CooQ.Types.DatabaseType.POSTGRESQL
});
C#
//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:

C#
//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.

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

Then, you make a first query as below:

  1. Select Query

    C++
    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

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

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

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

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Software Developer (Senior) QAS
Vietnam Vietnam
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --