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?
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.
Use Timeout instead of CommandTimeout. Refer this - https://www.npgsql.org/doc/connection-string-parameters.html
conStr = "Server=192.168.1.10;Port=5434;UserId=testuser;Password=1234;Database=testdb;Timeout=5;"
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.