PostgreSQL and VB.NET

[Vladamir]

I run PostgreSQL on a FreeBSD server. Why, because I'm a cheap lazy hack with no money to invest in M$ and SQL Server license. I only started programming with VB 2010 Express a few weeks ago. And trying to find information on how to get started connecting to a PostgreSQL server through .NET.

This may be something trivial but for newbies like me I thought I'd share this code snippet. It's nothing fancy but it will make a connection to the database and insert some new data. You can build on it from there.

First, you will need a file called Npgsql.dll. There are several locations for getting this, just do a search for it and you'll find one of them. Open a new project in VB.NET and add a reference to it. Click on the browse button and point to the location where you have this .dll file. Once it's added you can copy this code into your project. Make the necessary changes to the server name, and database name.

Imports Npgsql

Public Class Form1

    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
        Dim myConnection As NpgsqlConnection = New NpgsqlConnection()
        Dim myCommand As NpgsqlCommand
        Dim mySQLString As String

        myConnection.ConnectionString = "Server=10.0.0.254;Port=5432;Database=vbnet01;User Id=postgres;Password=postgres;"
        myConnection.Open()
        mySQLString = "INSERT INTO DRAWINGS (id, QuoteNum, DWGNo, DrawnBy) VALUES (18, 213317, 77692, 'FLG');"

        myCommand = New NpgsqlCommand(mySQLString, myConnection)
        myCommand.ExecuteNonQuery()

        myConnection.Close()
    End Sub
End Class

[Darshiva]

Hi Vladamir,
I am an absolute beginner when it comes to programming and I usually worked with sql and access. Now my boss wants me to write a programm and run the data with postgresql. Well, I don't wanna bore you with details I have my postgre-db and I can actually connect to it. I can get my tables into a dataset and connect the datagridview to the dataset/datatable. Now, I have the problem that I don't get it to update/insert/delete. Originally I wrote it for Access and even tested it on a Firebird. Everything was working fine. But then I had to switch to Postgresql ... I use Nqgsql like you. Do you have any tips? Thanks for your time.

[Vladamir]

Are you sure you're connecting correctly with the correct password, etc... PostgreSQL uses standard SQL syntax so what works with Access should work with pgsql. I know it's a pain but debugging is usually 50% of any code project. So get out those log files and see what may be happening behind the scenes. PostgreSQL has some good resources at their website. Check that out as well.

[Darshiva]

Thank you for your quick reply. Yes, it is connecting alright. I can open my application which I couldn't without the db-connection and get all of my tables into datagridviews. It just won't take my updates and it absolutely refuses to write it back into the db. I will see what I can find at the PostgreSQL website. Thanks again for your tips