How can setting timeout value of Npgsql?

user_1497597 0
Viewed 4694

I using CommandTimeout=5 to setting timeout, but it is not ok.

This is my code:

Dim con As New NpgsqlConnection
        Dim cmd As New NpgsqlCommand
        Dim conStr As String
        Try
            Dim sql = "Select * from dat_extract_text a join dat_replace_text b on a.tm_id=b.tm_id 
join dat_base_text_dt c on c.tm_id=a.tm_id
join dat_base_text_dt_font d on d.tm_id=c.tm_id
limit 250000"
            conStr = "Server=192.168.1.10;Port=5434;UserId=testuser;Password=1234;Database=testdb;CommandTimeout=5;"
            con.ConnectionString = conStr
            con.Open()

            cmd.Connection = con
            cmd.CommandText = sql

            Dim st = DateTime.Now
            Dim rs = cmd.ExecuteNonQuery
            Dim et = DateTime.Now
            Dim t = (et - st).TotalSeconds           
        Catch ex As Exception
        End Try

Time of my query is about 40s, but it not occur timeout.

How can setting timeout value of Npgsql?

npgsql postgresql vb.net
3 Answers

Let us get your code straightened out a bit.

Connections and commands need to be closed and disposed. Using...End Using blocks take care of this for you.

You can pass the connection string directly to the constructor. Likewise the command text and connection can be passed to the constructor of the command.

If you want to time something use the Stopwatch class available in .net.

A Select statement is not a NonQuery. It will return a result set. Using a DataTable will allow you to use the data.

An empty Catch is of no help because it just swallows errors an you don't know what has gone wrong.

Private Sub OPCode()
    Dim sql = "Select * from dat_extract_text a join dat_replace_text b on a.tm_id=b.tm_id 
                join dat_base_text_dt c on c.tm_id=a.tm_id
                join dat_base_text_dt_font d on d.tm_id=c.tm_id
                limit 250000"
    Dim dt As New DataTable
    Using con As New NpgsqlConnection("Server=192.168.1.10;Port=5434;UserId=testuser;Password=1234;Database=testdb;"),
        cmd As New NpgsqlCommand(sql, con)
        'cmd.CommandTimeout = 5
        con.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
End Sub

Test the code this way, without the timeout and without the Try...Catch to see what line the error occurs on and what the error is.

user_83676260
user_92982480

I've never used that particular provider but, like other ADO.NET providers, the command object, i.e. the NpgsqlCommand instance, has a CommandTimeout property. You should presumably be setting that.

user_5841830
Related