Basic CRUD using C# and PostgreSQL using functions

 Blog author: Niladri.Biswas | Posted on: 7/8/2012 | Category: Others Blogs | Views: 2554 | Status: [Member] | Points: 75 | Alert Moderator   Tweet  Download source file

Submit Blog | Blogs Home |

Basic CRUD using C# and PostgreSQL using functions

PostgreSQL does not support the traditional way of creating stored procedure found in other databases like Sql Server, Oracle etc. It lacks the famous Create Procedure syntax.

However,we can do so by the help of functions.We know that stored procedures can return multiple result sets which a functions can not. In PostgreSQL we can do so by returing a set of refcursors. In this article we will look into how to do so by extending our previous example

We will first create the environment.Let us first create the Player table(tblPlayers) found in the earlier example

CREATE TABLE tblPlayers (
        PlayerName Varchar(50)
		,Salary Int
		,BelongsTo Varchar(50)
		,Age Int
);

Next let us create the functions as under

Create Select All Record Function

CREATE OR REPLACE FUNCTION fnFetchPlayerRecord() RETURNS SETOF refcursor AS

'DECLARE 
  recordSet  refcursor;  
BEGIN

OPEN recordSet FOR 
 Select * from tblPlayers;
RETURN NEXT recordSet ;

RETURN;
END;'
LANGUAGE plpgsql;

We can find out that the return type is refcursor. In this way we can send the record sets back to the client.Also the language type is plpgsql

Create Insert Function

CREATE OR REPLACE FUNCTION fnInsertRecord(varchar,int,varchar,int) RETURNS void AS  
'BEGIN
		Insert Into tblPlayers(playername,age,belongsto,salary) values ($1,$2,$3,$4);
END;'
LANGUAGE plpgsql;

This function accepts 4 parameters.The first and third being of Varchar type while the second and fourth is integer type and returns void. Observe that the values being inserted by using the positional parameter (e.g. $1,$2 etc.). It is different from Oracle or SQL Server where we do so by using the parameter name.Also at the time of declaring the CREATE PROCEDURE statement in those databases, we name the Parameters and their type.But in PostgreSQL, mentioning only the datatype is enough

Create Update Function

CREATE OR REPLACE FUNCTION fnUpdateRecord(varchar,int) RETURNS void AS  
'BEGIN		
		Update tblPlayers Set salary  = salary + $2 Where playername = $1;		
END;'
LANGUAGE plpgsql;

Create Delete Function

CREATE OR REPLACE FUNCTION fnDeleteRecord(varchar) RETURNS void AS  
'BEGIN		
		Delete From tblPlayers Where playername = $1;		
END;'
LANGUAGE plpgsql;

So, we have written all kind of basic scripts for our operation

Now we will look into the interfacing code in our DAL layer.We first need to add a reference to

1. Mono.Security.dll
2. Npgsql.dll

We will now look into as how we can invoke these functions from DAL layer

First let us look into how we are invoking the fnFetchPlayerRecord function(1st function)

public DataTable GetAllRecords()
{

	DataTable dtRecord = new DataTable();           

	try
	{
		using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
		{
			// Open the PgSQL Connection.                
			pgsqlConnection.Open();

			string selectCommand = "fnFetchPlayerRecord";                    

			using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(selectCommand, pgsqlConnection))
			{
				using (NpgsqlTransaction tran = pgsqlConnection.BeginTransaction())
				{
					pgsqlcommand.CommandType = CommandType.StoredProcedure;

					using (NpgsqlDataAdapter Adpt = new NpgsqlDataAdapter(pgsqlcommand))
					{
						Adpt.Fill(dtRecord);
					}
					tran.Commit();
				}
			}
		}
	}
	catch (NpgsqlException ex)
	{
		throw ex;
	}
	catch (Exception ex)
	{
		throw ex;
	}
	return dtRecord;
}

It is preety much the same code as we found while interfacing with other databases.Since we know that, our function returns only one record set we are using Datatable else we can use DataSet.We are using transaction for preventing cursors returned by refcursor function from closing after the implicity transaction is finished.In the CommandText of the NpgsqlCommand class , we are specifying the function name.

Next we will see the way to invoke the Insert function (fnInsertRecord)

public void InsertRecord(string PlayerName, int Age, string BelongsTo, int Salary)
{

	try
	{
		using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
		{
			// Open the PgSQL Connection.                  
			pgsqlConnection.Open();

			string insertCommand = "fnInsertRecord";

			using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
			{
				using (NpgsqlTransaction tran = pgsqlConnection.BeginTransaction())
				{
					pgsqlcommand.CommandType = CommandType.StoredProcedure;

					pgsqlcommand.Parameters.Add(new NpgsqlParameter("playername", NpgsqlDbType.Varchar));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("age", NpgsqlDbType.Integer));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("belongsto", NpgsqlDbType.Varchar));
					pgsqlcommand.Parameters.Add(new NpgsqlParameter("salary", NpgsqlDbType.Integer));

					pgsqlcommand.Parameters[0].Value = PlayerName;
					pgsqlcommand.Parameters[1].Value = Age;
					pgsqlcommand.Parameters[2].Value = BelongsTo;
					pgsqlcommand.Parameters[3].Value = Salary;

					pgsqlcommand.ExecuteNonQuery();
					
					tran.Commit();
				}
			}                   
		}
	}
	catch (NpgsqlException ex)
	{
		throw ex;
	}
	catch (Exception ex)
	{
		throw ex;
	}
}

This follows the similar concept as the previous one

The other DML functions follows the same pattern.

The output will be as under

References Npgsql: User's Manual

Hope this will help those who want's start their dotnet client interfacing with PostgreSQL DB.Thanks for reading the article.Attached is the zipped file that contains a sample windows application and the PostgreSQL script