Feed on
Posts
Comments

In my previous blog, I showed an example of one-to-one PostgreSQL transaction support in .net. In this blog, we will try to enter one to many PostgreSQL transaction from .net using Npgsql. To start off, we need some tables:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE SEQUENCE orders_order_id_seq;
CREATE TABLE orders
(
	order_id 	INTEGER NOT NULL PRIMARY KEY DEFAULT(NEXTVAL('orders_order_id_seq')::INTEGER),
	order_date	TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(NOW()),
	customer_name	character varying(40) NOT NULL
);
 
CREATE SEQUENCE ordered_products_ordered_product_id_seq;
CREATE TABLE ordered_products
(
	ordered_product_id	INTEGER NOT NULL PRIMARY KEY DEFAULT(NEXTVAL('ordered_products_ordered_product_id_seq')::INTEGER),
	order_id		INTEGER NOT NULL REFERENCES orders(order_id),
	product_name		character varying(40) NOT NULL
);
 
CREATE UNIQUE INDEX ordered_products_product_name_uix
ON ordered_products(order_id, UPPER(product_name));

Transaction in plpgsql

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
DO
$$
DECLARE _order_id INTEGER;
BEGIN
	INSERT INTO orders(customer_name)
	SELECT 'Rudolf Schenker';
 
	_order_id:=CURRVAL('orders_order_id_seq')::INTEGER;
 
	INSERT INTO ordered_products(order_id, product_name)
	SELECT _order_id, 'Holiday' UNION ALL
	SELECT _order_id, 'Wind of Change' UNION ALL
	SELECT _order_id, 'Under the Same Sun' UNION ALL
	SELECT _order_id, 'When You Came into My Life' UNION ALL
	SELECT _order_id, 'Rock You Like a Hurricane';
	RAISE NOTICE 'The transaction was successfully processed.';
EXCEPTION
	WHEN OTHERS THEN
	RAISE NOTICE 'The transaction was rolled back, nothing was changed.';
END
$$
LANGUAGE plpgsql;

Transaction in CSharp (Npgsql)

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
public bool SaveOrder(string customerName, List products)
{
	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 = pgConnection.BeginTransaction())
			{
				try
				{
					using (NpgsqlCommand roleCommand = new NpgsqlCommand("INSERT INTO orders(customer_name) SELECT @customer_name;SELECT CURRVAL('orders_order_id_seq')::integer as order_id;", pgConnection, pgTransaction))
					{
						roleCommand.Parameters.AddWithValue("@customer_name", customerName);
 
						//Get the newly inserted row_id of the current scope
						int orderId = Convert.ToInt32(roleCommand.ExecuteScalar());
 
						foreach (string product in products)
						{
							using (NpgsqlCommand userCommand = new NpgsqlCommand("INSERT INTO ordered_products(order_id, product_name) SELECT @order_id, @product_name;", pgConnection, pgTransaction))
							{
								userCommand.Parameters.AddWithValue("@order_id", orderId);
								userCommand.Parameters.AddWithValue("@product_name", product);
								userCommand.ExecuteNonQuery();
							}
						}
 
						//No exceptions encountered
						pgTransaction.Commit();
 
						//System.Windows.Forms.MessageBox.Show("Task completed successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
						return true;
					}
				}
				catch
				{
					//Transaction rolled back to the original state
					pgTransaction.Rollback();
					throw;
				}
			}
		}
		catch (NpgsqlException ex)
		{
			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
		}
		catch
		{
			throw;
		}
	}
	return false;
}

Using the CSharp Function 

Selec All Code:
1
2
3
4
5
6
7
8
List<string> products = new List<string>();
products.Add("Holiday");
products.Add("Wind of Change");
products.Add("Under the Same Sun");
products.Add("When You Came into My Life");
products.Add("Rock You Like a Hurricane");
 
SaveOrder("Rudolf Schenker",products);

Transaction in VisualBasic (Npgsql)

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
Function SaveOrder(ByVal customerName As String, ByVal products As List(Of 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 orders(customer_name) SELECT @customer_name;SELECT CURRVAL('orders_order_id_seq')::integer as order_id;", pgConnection, pgTransaction)
						roleCommand.Parameters.AddWithValue("@customer_name", customerName)
 
						'Get the newly inserted row_id of the current scope
						Dim orderId As Integer = Convert.ToInt32(roleCommand.ExecuteScalar())
 
						For Each product As String In products
							Using userCommand As NpgsqlCommand = New NpgsqlCommand("INSERT INTO ordered_products(order_id, product_name) SELECT @order_id, @product_name;", pgConnection, pgTransaction)
								userCommand.Parameters.AddWithValue("@order_id", orderId)
								userCommand.Parameters.AddWithValue("@product_name", product)
								userCommand.ExecuteNonQuery()
							End Using
						Next
 
						'No exceptions encountered
						pgTransaction.Commit()
 
						'System.Windows.Forms.MessageBox.Show("Task completed successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
						Return True
					End Using
				Catch
					'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
			Throw
		End Try
	End Using
	Return False
End Function

Using the VisualBasic Function

 

Selec All Code:
1
2
3
4
5
6
7
8
Dim products As List(Of String) = New List(Of String)
products.Add("Holiday")
products.Add("Wind of Change")
products.Add("Under the Same Sun")
products.Add("When You Came into My Life")
products.Add("Rock You Like a Hurricane")
 
SaveOrder("Rudolf Schenker", products)

6 Responses to “One to Many PostgreSQL transaction in .net”

  1. acid reflux says:

    Nice website, keep up the good work.

  2. sewa elf says:

    Nice article, thanks for the information.

  3. I wish Visual basic would just die. great postgreSQL post, though.

  4. Great post, I think people should larn a lot from this web site its very user friendly .

  5. Hello, just wanted to say, I loved this blog post. It was helpful. Keep on posting!

  6. Tom Maccabe says:

    Your blog is wonderful and quite informative. Keep writing the .net related articles more..

    I love visual basic. I started using from VB5.0 with windows 98 operating system. I love the way Visual studio 6 designed. Though the recent Visual studio 2010 is much attractive with all bells and whistles, I still like visual studio 6.0.

    Thank you so much for a great blog post.

Leave a Reply