Binod's Blog

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
);

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;

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;
	}
 
}

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