Learning PostgreSql: Reading and Writing From .Net

In this post we shall do some setup tasks, save a few rows of data from a .Net client to PostgreSql, and read it back.

Setting up

We have set up a virtual machine running Red Hat Linux, installed PostgreSql 9.3 on it, and made sure there is enough disk space. 9.3 is a very recent version, released this September. Because PostgreSqlis not known for releasing before the full testing is complete, we did not have to wait for the next service pack or something like that.

Smoke test

On the client machine running Windows 7, we installed SQL Manager Lite, which is a lightweight GUI for simple tasks. We had no problems installing it and connecting to the server. Our first query was cut and pasted from the following article: "Postgres SQL Injection Cheat Sheet".

We ran this:

SELECT version()

followed by this

SELECT 1;

Creating a table

The following script was issued from SQL Manager Lite:

CREATE TABLE prices (
  ticker VARCHAR(20),
  asofdate TIMESTAMP WITHOUT TIME ZONE,
  price NUMERIC(6,2)
) 

Note: the default schema in PostgreSql is named public, not dbo.

Connecting from .Net

We have chosen to use a free open source .Net provider named  Npgsql, documented here:http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html. We have reviewed the source code (it was developed in C#) and built the binaries for our project.

The following code wrote a few rows into our table:

        private const string ConnString = "Server=myserver;Port=5432;Database=AkTest;User Id=myuser;Password=mypassword";

        [Test, Explicit]
        public void WriteTest()
        {
            using(var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
            {
                conn.Open();
                using(var command = conn.CreateCommand())
                {
                    command.CommandText =
                        "INSERT INTO Prices(Ticker, AsOfDate, Price)VALUES('GOOG', '2013-10-23',1001.00),('AAPL', '2013-10-23',517.49)";
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

We were able to select these rows from SQL Manager Lite. The following code read these rows from .Net:

       [Test, Explicit]
        public void ReadTest()
        {
            using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText =
                        "SELECT Ticker, AsOfDate, Price FROM Prices ORDER BY Ticker, AsOfDate;";
                    command.CommandType = CommandType.Text;
                    using(var dr = command.ExecuteReader())
                        while (dr.Read())
                        {
                            Console.WriteLine(string.Format("Ticker: {0}, AsOfDate: {1}, Price: {2}", dr.GetString(0), dr.GetTimeStamp(1), dr.GetDecimal(2)));
                        }
                }
            }
        } 

In the next post we shall create a stored procedure which reads data, and invoke it from our C# client.