Feed on
Posts
Comments

In this blog, I will discuss about “one-to-one” postgresql transaction using

  • Plain SQL
  • CSharp .net & Npgsql
  • Visual Basic .net & Npgsql

A postgresql transaction will rollback the entire batch of transactions if any one of the transactions fails. To start off, I will create two tables ‘roles’ and ‘users’.

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE roles
(
    role_id        SERIAL        NOT NULL PRIMARY KEY,
    role_name    VARCHAR(24)    NOT NULL
);
 
CREATE TABLE users
(
    user_id        SERIAL        NOT NULL PRIMARY KEY,
    role_id        INTEGER        NOT NULL REFERENCES roles(role_id),
    user_name    VARCHAR(24)    NOT NULL
);

Plain SQL Transaction

Selec All Code:
1
2
3
4
5
6
7
8
9
BEGIN TRANSACTION;
 
INSERT INTO roles(role_id, role_name)
SELECT CAST(NEXTVAL('roles_role_id_seq') AS INTEGER), 'administrator';
 
INSERT INTO users(role_id, user_name)
SELECT CAST(CURRVAL('roles_role_id_seq') AS INTEGER), 'binod';
 
COMMIT TRANSACTION;

CSharp

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using NpgsqlTypes;
 
class OneToOneClass
{
	public static bool CreateUser(string roleName, string userName)
	{
		using (NpgsqlConnection pgConnection = new NpgsqlConnection("Server=localhost;User id=postgres;password=binod;Database=postgres;"))
		{
			try
			{
				pgConnection.Open();
				//Connection successful
 
				//Create a new transaction
				using (NpgsqlTransaction pgTransaction = (NpgsqlTransaction) pgConnection.BeginTransaction())
				{
					try
					{
						using (NpgsqlCommand roleCommand = new NpgsqlCommand("INSERT INTO roles(role_id, role_name) SELECT nextval('roles_role_id_seq'::regclass),@role_name;SELECT currval('roles_role_id_seq'::regclass) as role_id;", pgConnection, pgTransaction))
						{
							roleCommand.Parameters.AddWithValue("@role_name", roleName);
 
							//Get the newly inserted row_id of the current scope
							int roleId = Convert.ToInt32(roleCommand.ExecuteScalar());
 
							using (NpgsqlCommand userCommand = new NpgsqlCommand("INSERT INTO users(role_id, user_name) SELECT @role_id, @user_name;", pgConnection, pgTransaction))
							{
								userCommand.Parameters.AddWithValue("@role_id", roleId);
								userCommand.Parameters.AddWithValue("@user_name", userName);
								userCommand.ExecuteNonQuery();
							}
 
							//No exceptions encountered
							pgTransaction.Commit();
 
							System.Windows.Forms.MessageBox.Show("Task completed successfully.", "Success", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
							return true;
						}
					}
					catch (Exception ex)
					{
						//Transaction rolled back to the original state
						pgTransaction.Rollback();
						throw;
					}
				}
			}
			catch (NpgsqlException ex)
			{
				System.Windows.Forms.MessageBox.Show(ex.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
			}
			catch (Exception ex)
			{
				throw;
			}
		}
		return false;
	}
 
}

VisualBasic

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Imports Npgsql
 
Public Class OneToOneClass
    Shared Function CreateUser(ByVal roleName As String, ByVal userName As String) As Boolean
        Using pgConnection As NpgsqlConnection = New NpgsqlConnection("Server=localhost;User id=postgres;password=binod;Database=postgres;")
            Try
                pgConnection.Open()
                'Connection successful
 
                'Create a new transaction
                Using pgTransaction As NpgsqlTransaction = pgConnection.BeginTransaction
                    Try
                        Using roleCommand As NpgsqlCommand = New NpgsqlCommand("INSERT INTO roles(role_id, role_name) SELECT nextval('roles_role_id_seq'::regclass),@role_name;SELECT currval('roles_role_id_seq'::regclass) as role_id;", pgConnection, pgTransaction)
                            roleCommand.Parameters.AddWithValue("@role_name", roleName)
 
                            'Get the newly inserted row_id of the current scope
                            Dim roleId As Integer = Convert.ToInt32(roleCommand.ExecuteScalar)
 
                            Using userCommand As NpgsqlCommand = New NpgsqlCommand("INSERT INTO users(role_id, user_name) SELECT @role_id, @user_name;", pgConnection, pgTransaction)
                                userCommand.Parameters.AddWithValue("@role_id", roleId)
                                userCommand.Parameters.AddWithValue("@user_name", userName)
                                userCommand.ExecuteNonQuery()
                            End Using
 
                            'No exceptions encountered
                            pgTransaction.Commit()
 
                            MessageBox.Show("Task completed successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            Return True
                        End Using
                    Catch ex As Exception
                        'Transaction rolled back to the original state
                        pgTransaction.Rollback()
                        Throw
                    End Try
                End Using
            Catch ex As NpgsqlException
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Catch ex As Exception
                Throw
            End Try
        End Using
        Return False
    End Function
End Class

4 Responses to “PostgreSQL transaction and .net”

  1. singapore event photographer says:

    Thanks for that awesome posting. It saved MUCH time :-)

  2. James Jan says:

    Wow this is great, I am still on basic queries for SQL though. You are amazing.

  3. Grenet says:

    Great read, keep up the good work.

  4. Desiree says:

    Pretty nice post. I just stumbled upon your weblog and wanted to say that I have truly enjoyed surfing around your blog posts.
    In any case I will be subscribing to your rss feed and
    I hope you write again soon!

Leave a Reply