How to connect NpgsqlDatabaseInfo for crate database in C#

I am very new to crate db. I created an REST API application in C# which connected to crate db and executing the database query. I’m using the https://crate.io/docs/npgsql/en/latest/connect.html#connect
documentation. The datafactory related link is not working.https://www.npgsql.org/doc/api/Npgsql.NpgsqlDatabaseInfo.html

When multiple API calls are came, the time out exception occurred in
NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory()) connection.

Below is my code .
my function is below.

    public async Task<List<AggregationModel>> datafetch(QueryModelRawData model,string tablename)
    {
      var result = new List<AggregationModel>();
      NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory())
      using (NpgsqlConnection sqlCon = new NpgsqlConnection(SqlConnectionString))
      {
       var tableName = "testtable";
 sqlCon.Open();
       var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";
       using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, sqlCon))
        {
         sqlCmd.Parameters.AddWithValue("s", model.id);
         sqlCmd.Parameters.AddWithValue("st", model.starttime);
         sqlCmd.Parameters.AddWithValue("et", model.endtime);
        using (var reader = await sqlCmd.ExecuteReaderAsync())
         {
           while (reader.HasRows && await reader.ReadAsync())
           {
         double readingValue = readingToDouble(reader);
         AggregationModel item = new AggregationModel
         {
         reading = readingValue,
         unixtime = (double)reader["unixtime"]
         };
         result.Add(item);
      }
     }
       }
      }
                   
     return result;
     }

i called the same function every time for each query execution and is this correct way ? Any example is available ?

The CrateDB specific DataFactory should not be needed anymore from CrateDB v4.2 and higher. You should be able to connect following standard npgsql guides for Postgres.

Thank you for the response . I am using crate:4.5.0 docker image. Without specificy DataFactory got an error of Read
mismatched input ‘READ’
That mentioned in this post https://community.crate.io/t/sqlparseexception-when-opening-cratedb-connection-in-c/436
Is there any reference document ?

Could you share the connection string (without credentials of course)?
Did you adjust the configuration at all?
How did you start the container?

I am deployed the crate db in kubernetes . Using
https://crate.io/docs/crate/howtos/en/latest/deployment/containers/kubernetes.html#internal-service

No loadbalancer is added. connection is established through the internal service communication.

“cratedbconnectionstring”: “Host=internal-service;Username=crate;SSL Mode=Prefer;Database=doc”,

can you connect to the AdminUI ?

is unixtime a timestamp?
If yes, I don’t think your cast (double)reader["unixtime"] works


I tried to replicate it

CREATE TABLE testtable (id TEXT, timestamp timestamp ,reading double);
INSERT INTO testtable VALUES ('a',now(),random());
using System;
using Npgsql;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = "Host=localhost;Username=crate;SSL Mode=Prefer;Database=doc";

            using (var conn = new NpgsqlConnection(connString))

            {
                conn.Open();

                var tableName = "testtable";
                var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";

                using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, conn))
                {
                    sqlCmd.Parameters.AddWithValue("s", "a");
                    sqlCmd.Parameters.AddWithValue("st", 1622716474405);
                    sqlCmd.Parameters.AddWithValue("et", 1622716474837);

                    using (var reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            Console.WriteLine(reader["unixtime"]);
                        }
                    }
                }
            }
        }
    }

}

Outputs

6/3/2021 12:34:34 PM
6/3/2021 12:34:34 PM
6/3/2021 12:34:34 PM
6/3/2021 12:34:34 PM
2 Likes

thank you for the reply…
My problem is connection with crate db. just leave the other part.
Now i remove the RegisterFactory and using Npgsql;

Then i got an error

> Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
>  ---> System.TimeoutException: Timeout during reading attempt
>    at Npgsql.NpgsqlReadBuffer.<Ensure>g__EnsureLong|40_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
>    at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
>    at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
>    at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
>    at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<<Rent>g__RentAsync|0>d.MoveNext()
> --- End of stack trace from previous location where exception was thrown ---
>    at Npgsql.NpgsqlConnection.<>c__DisplayClass41_0.<<Open>g__OpenAsync|0>d.MoveNext()
> --- End of stack trace from previous location where exception was thrown ---
>    at Npgsql.NpgsqlConnection.Open()
at Implementation/crateRepository.cs:line 15

My code is

using System;
using Npgsql;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = "Host=localhost;Username=crate;SSL Mode=Prefer;Database=doc";

            using (var conn = new NpgsqlConnection(connString))

            {
                conn.Open();

                var tableName = "testtable";
                var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";

                using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, conn))
                {
                    sqlCmd.Parameters.AddWithValue("s", "a");
                    sqlCmd.Parameters.AddWithValue("st", 1622716474405);
                    sqlCmd.Parameters.AddWithValue("et", 1622716474837);

                    using (var reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            Console.WriteLine(reader["unixtime"]);
                        }
                    }
                }
            }
        }
    }

}

The error is:

error at                 conn.Open();

Have you forwarded the port to localhost from the pod or are you using any load balancer? To me this now seems most likely like a war one kubernetes setup :thinking:

Which version of npgsql are you using?
How big is the table queried?
Did you have succes connecting with any other client (e.g. crash or psql)?

1 Like

Yes. I deployed this crate db in kubernetes. not deployed any load balance for crate db. the c# application connecting the DB through the internal service. check the crate Internal service documentation. Both crate db and c# web api connection deployed in same kubernetes cluster. (connection between c# application and crate db are in same network. so no need any load balancer or port forward to connecting it )
Npgsql version ==> Include=“Npgsql” Version=“5.0.5”/>.
Only 50000 data in table.But api request (multiple db query execution call happens at the same time). I didn’t try any other client for c# application. I found only Npgsql documentation for c#.

Could you please help me?

You said, that you only set up the internal service.
Did you expose port 5432 / 4200 in the internal service or only port 4300?


using 4300 portl may be…i didn’t specify the port in code anywhere?

this is created by using below link

Hey awesome that you look into cratedb. I gave it a run and set it up on GKE. Could you please verify and re-produce my steps.


kubectl get services
NAME                     TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)             AGE
crate-external-service   ClusterIP   10.8.154.146   <none>        4200/TCP,5432/TCP   54m
crate-internal-service   ClusterIP   10.8.153.70    <none>        4300/TCP            55m

kubectl get pods -o wide  
NAME      READY   STATUS    RESTARTS   AGE    IP          NODE                                       NOMINATED NODE   READINESS GATES
box       1/1     Running   1          6m4s   10.4.2.13   gke-cluster-1-default-pool-fb5465a3-862k   <none>           <none>
crate-0   1/1     Running   0          58m    10.4.2.8    gke-cluster-1-default-pool-fb5465a3-862k   <none>           <none>
crate-1   1/1     Running   0          58m    10.4.0.3    gke-cluster-1-default-pool-fb5465a3-hwcd   <none>           <none>
crate-2   1/1     Running   0          57m    10.4.2.9    gke-cluster-1-default-pool-fb5465a3-862k   <none>           <none>

kubectl exec box -it bash
root@box:/# curl 10.8.154.146:4200
{
  "ok" : true,
  "status" : 200,
  "name" : "crate-2",
  "cluster_name" : "my-crate",
  "version" : {
    "number" : "4.2.4",
    "build_hash" : "62b009206df6e916623368905c7fc7c1d776b947",
    "build_timestamp" : "2020-08-26T14:15:09Z",
    "build_snapshot" : false,
    "lucene_version" : "8.5.1"
  }
}

root@box:/# psql --host=10.8.154.146 -U crate
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1), server 10.5)
Type "help" for help.

crate=> select * from sys.jobs;
                  id                  |                       node                       |          started           |          stmt           | username
--------------------------------------+--------------------------------------------------+----------------------------+-------------------------+----------
 45d6648e-b32d-4463-9076-f0514de76ac6 | {"name":"crate-2","id":"npIPBNVjTQKjA-MK5oK9gQ"} | 2021-06-08 08:46:13.007+00 | select * from sys.jobs; | crate
(1 row)


1 Like
NAME                     TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)                         AGE
crate-external-service   LoadBalancer   10.0.174.178   <none>          4200:30895/TCP,5432:30819/TCP   3m
crate-internal-service   ClusterIP      10.0.32.59     <none>          4300/TCP,5432/TCP,4200/TCP      11d
NAME                                     READY   STATUS    RESTARTS   AGE    IP             NODE                                NOMINATED NODE   READINESS GATES
crate-0                                  1/1     Running   0          32d    10.244.0.112   aks-agentpool-37458998-vmss000000   <none>           <none>
crate-1                                  1/1     Running   0          7d8h   10.244.1.9     aks-agentpool-37458998-vmss000001   <none>           <none>
crate-2                                  1/1     Running   1          32d    10.244.0.113   aks-agentpool-37458998-vmss000000   <none>           <none>
[ root@curl:/ ]$ curl 10.0.174.178:4200
{
  "ok" : true,
  "status" : 200,
  "name" : "crate-2",
  "cluster_name" : "my-crate",
  "version" : {
    "number" : "4.5.0",
    "build_hash" : "fb7a5337ef75d60c5bdf26c1f54a9278f5fe03a6",
    "build_timestamp" : "2021-03-23T14:12:58Z",
    "build_snapshot" : false,
    "lucene_version" : "8.7.0"
  }
}
root@ubuntu:/# psql --host=10.0.174.178 -U crate
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1), server 10.5)
Type "help" for help.

crate=> select * from sys.jobs;
                  id                  |                       node                       |          started           |          stmt           | username 
--------------------------------------+--------------------------------------------------+----------------------------+-------------------------+----------
 e85eaf7e-6d04-4f0c-af95-4d507c697642 | {"name":"crate-1","id":"gYErxrB2TlC0u6sciqdsyw"} | 2021-06-08 17:05:08.480+00 | select * from sys.jobs; | crate
(1 row)


Dear @testjob_tyu,

thank you for writing in.

On behalf of Add basic demonstration program for using CrateDB with vanilla Npgsql by amotl · Pull Request #3 · crate/cratedb-examples · GitHub, I was able to verify that the connection with the vanilla Npgsql driver works successfully, both for a CrateDB instance running on Docker on my machine as well as when using CrateDB Cloud.

Sorry about that one, with recent versions of CrateDB >= 4.2, it is advised to just use the vanilla Npgsql driver from https://www.npgsql.org/. So, along the lines, the patch Documentation: Improve notes about vanilla Npgsql driver by amotl · Pull Request #58 · crate/crate-npgsql · GitHub improves the situation on the documentation of CrateDB’s Npgsql fork by adding improved admonitions.

Do you still observe issues when connecting to CrateDB or did your problem go away?

With kind regards,
Andreas.

1 Like

Still i have same problem .Also some other error raised

How often are you calling the function in parallel?
It opens a new database connection for every request.

Hi @testjob_tyu,

We are talking about the connectivity issue outlined within How to connect NpgsqlDatabaseInfo for crate database in C# - #7 by testjob_tyu, right?

Can we take a step back together and evaluate whether everything works fine on a vanilla instance of CrateDB, e.g. invoked on your workstation like

docker run -it --rm --publish=4200:4200 --publish=5432:5432 crate:4.5.1

If we can verify this works, for example together with the example program from https://github.com/crate/cratedb-examples/pull/3, we might be able to work up from there.

With kind regards,
Andreas.

There is a 500 data write into database in every 1 minute and 10 parallel get query executed parallel(this get call happens once in a every 15 minute).

yes. same connectivity issue