THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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.

Published Thursday, October 31, 2013 4:01 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement