Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

19
PostgreSQL in C#

This example demonstrates the PostgreSQL database in a C# application. If you skipped Chapter 18, which built a similar example in Python, go to that chapter and read the beginning and the first two main sections, which are described in the following list.

“Install PostgreSQL” explains how to install the PostgreSQL database.
“Run pgAdmin” tells you how to use the pgAdmin tool to build the database that this example uses.

When you reach the section “Create the Program” in Chapter 18, come back here and read the following sections.

CREATE THE PROGRAM

To create a C# program to work with the Brew Crew database, create a new C# Console App (.NET Framework) and then add the code described in the following sections.

Like the program described in Chapter 17, “MariaDB in C#,” this example's main method calls other methods to do the actual work. This time the main method creates the database connection and passes it to the other methods so that they don't need to open their own connections.

Install Npgsql

This example uses the Npgsql data provider to allow your program to communicate with the Postgres engine. To add Npgsql to your project, follow these steps.

Open the Project menu and select Manage NuGet Packages.
Click the Browse tab and in the search box enter Npgsql. This will bring up around 200 packages.
Find the package named Npgsql, click it, and then use the Install button on the right to install it. This shouldn't take too long, but you might still want a fast Internet connection.

If you later need to remove or update a package, open the NuGet Package Manager again and look at the Installed tab. After you select an installed package, you can use the Uninstall or Update button on the right.

Connect to the Database

The following code shows the example program's main method:

static void Main(string[] args)
{
    string connectString =
        @"Host=localhost;Username=brew_master;
        Password=brew_password;Database=BrewCrewDB";
 
    using (NpgsqlConnection conn = new NpgsqlConnection(connectString))
    {
        conn.Open();
 
        // Delete any existing records.
        DeleteRecords(conn);
 
        // Create customer records.
        int bobsId, clairesId;
        CreateCustomerRecords(conn, out bobsId, out clairesId);
 
        // Create order records.
        List<int> bobsOrderIds, clairesOrderIds;
        CreateOrderRecords(conn,
            bobsId, clairesId,
            out bobsOrderIds, out clairesOrderIds);
 
        // Create order_items records.
        CreateOrderItemRecords(conn, bobsOrderIds, clairesOrderIds);
 
        // Display some records.
        DisplayOrders(conn);
 
        // Let the user create an order.
        LetUserCreateOrder(conn);
 
 
        // Display some records.
        DisplayOrders(conn);
    }
 
    Console.Write("Press Enter to quit ");
    Console.ReadLine();
}

This code creates a connect string to identify the database's server computer, username, password, and database. The main method uses that string to create a database connection, opens the connection, and then passes it to other methods to do the real work.

Delete Old Data

The DeleteRecords method uses the following code to delete any old records that are gathering dust in the database:

// Delete the records to start from scratch.
static private void DeleteRecords(NpgsqlConnection conn)
{
    string deleteStatement = "DELETE FROM order_items";
    using (NpgsqlCommand deleteCmd = new NpgsqlCommand(deleteStatement, conn))
    {
        deleteCmd.ExecuteNonQuery();
    }
 
    deleteStatement = "DELETE FROM orders";
    using (NpgsqlCommand deleteCmd = new NpgsqlCommand(deleteStatement, conn))
    {
        deleteCmd.ExecuteNonQuery();
    }
 
    deleteStatement = "DELETE FROM customers";
    using (NpgsqlCommand deleteCmd = new NpgsqlCommand(deleteStatement, conn))
    {
        deleteCmd.ExecuteNonQuery();
    }
}

This code composes a SQL DELETE statement that removes the records from the order_items table. It then uses that statement and the database connection to create an NpgsqlCommand object. It then calls the command object's ExecuteNonQuery method to execute the statement. As I'm sure you can guess, it uses ExecuteNonQuery because this SQL statement is not a query.

The code repeats those steps to create two other command objects that delete records from the orders and customers tables.

Create Customer Data

The CreateCustomerRecords method uses the following code to add two records to the customers table:

static private void CreateCustomerRecords(NpgsqlConnection conn,
    out int bobsId, out int clairesId)
{
    using (NpgsqlTransaction transaction = conn.BeginTransaction())
    {
        string createCustomerStatement =
            @"INSERT INTO Customers (name)
            VALUES (@name) RETURNING customer_id";
        using (NpgsqlCommand insertCmd =
            new NpgsqlCommand(createCustomerStatement, conn))
        {
            insertCmd.Transaction = transaction;
 
            // Bob's Beans.
            Npgsql.NpgsqlParameter nameParameter =
                insertCmd.Parameters.AddWithValue("name", "Bob's Beans");
            bobsId = (int)insertCmd.ExecuteScalar();
            Console.WriteLine($"Bob's ID: {bobsId}");
 
            // Claire's Coffee.
            nameParameter.Value = "Claire's Coffee";
            clairesId = (int)insertCmd.ExecuteScalar();
            Console.WriteLine($"Claire's ID: {clairesId}");
        }
 
        transaction.Commit();
    }
}

This code first uses the connection object to create a transaction so it can commit or roll back actions.

It then composes a SQL INSERT statement that uses the placeholder @name for the customers record's name value. It uses the database connection and the SQL statement to create a new command object and then sets the command's Transaction property to the transaction object that it created earlier. That allows the transaction to commit or roll back actions performed by this command object.

Next, the code creates an NpgsqlParameter object to represent a parameter for the SQL statement. It sets the parameter's name to name and sets its value to Bob's Beans.

The code then calls the command object's ExecuteScalar method. That method executes a SQL statement that returns a single value. (In the database context, scalar means in a single value.)

In this example, the statement's RETURNING clause makes the command return the newly created customer ID value that is automatically generated by the database when the record is created. The code casts the returned result into an integer and stores it in the variable bobsId for later use.

Next, the program changes the parameter object's Value property to Claire's Coffee, executes the command, and saves the result in variable claireId.

After it finishes creating the customers records, the code calls the transaction object's Commit method to make the changes permanent. If you call the Rollback method or if you forget to call Commit before the transaction object goes out of scope and is destroyed, then the actions are undone.

TRANSITORY TRANSACTIONS

If you don't use transactions, then every command takes immediate effect with no undo option. The CreateCustomerRecords method uses a transaction so that you can see how it's done, but the rest of the program doesn't bother. There's no point because it won't ever roll back a transaction.

Transactions are more important for sets of actions that should either all happen or all not happen. For example, if the program transfers money from one bank account to another, you don't want the program to crash halfway through after taking the money from one account but before putting it in the other.

For another example (which foreshadows Exercise 19.2), suppose you're creating a new order record that has associated order items. If you create the order record and then the order item records fail, you'll have an orphaned order record sitting around with no items in it. To prevent that, you don't want to create any of those records unless you successfully create them all.

Create Order Data

The CreateOrderRecords method uses the following code to create order records:

static private void CreateOrderRecords(NpgsqlConnection conn,
    int bobsId, int clairesId,
    out List<int> bobsOrderIds, out List<int> clairesOrderIds)
{
    // Make lists to hold new order IDs.
    bobsOrderIds = new List<int>();
    clairesOrderIds = new List<int>();
 
    // Make the command.
    string createOrderStatement =
        @"INSERT INTO Orders (customer_id, date)
            VALUES (@customer_id, @date) RETURNING order_id";
 
    using (NpgsqlCommand insertCmd =
        new NpgsqlCommand(createOrderStatement, conn))
    {
        // Add the parameters.
        NpgsqlParameter customerIdParameter =
            insertCmd.Parameters.Add("customer_id",
                NpgsqlTypes.NpgsqlDbType.Integer);
        NpgsqlParameter dateParameter =
            insertCmd.Parameters.Add("date", NpgsqlTypes.NpgsqlDbType.Date);
 
        // Prepare the command.
        insertCmd.Prepare();
 
        // Create Bob's records.
        customerIdParameter.Value = bobsId;
        dateParameter.Value = new DateTime(2027, 4, 1);
        bobsOrderIds.Add((int)insertCmd.ExecuteScalar());
 
        dateParameter.Value = new DateTime(2027, 4, 1);
        bobsOrderIds.Add((int)insertCmd.ExecuteScalar());
 
        dateParameter.Value = new DateTime(2027, 4, 17);
        bobsOrderIds.Add((int)insertCmd.ExecuteScalar());
 
        dateParameter.Value = new DateTime(2027, 5, 1);
        bobsOrderIds.Add((int)insertCmd.ExecuteScalar());
 
        // Create Claire's records.
        customerIdParameter.Value = clairesId;
        dateParameter.Value = new DateTime(2027, 4, 10);
        clairesOrderIds.Add((int)insertCmd.ExecuteScalar());
 
        dateParameter.Value = new DateTime(2027, 4, 20);
        clairesOrderIds.Add((int)insertCmd.ExecuteScalar());
    }
}

This method creates two lists of integers (named bobsOrderIds and claireOrderIds) to hold the newly created order IDs.

It then composes a SQL INSERT statement that adds a record to the orders table. The statement represents the values with the placeholders @customer_id and @date. The RETURNING clause indicates that the statement should return the newly created order ID.

Next, the code uses the database connection and the SQL statement to create a command object. It then creates two parameter objects for the command to hold the customer_id and date values.

The program then calls the command object's Prepare method to ready the command for action.

The code then sets the customer_id and date parameter values for the first order. It then calls the command's ExecuteScalar method, converts the returned value (which is the newly minted order ID) into an integer, and saves the result in the bobsOrderIds list.

Next, the code sets the date parameter to a new date and inserts another record, again saving the order ID in the bobsOrderIds list. It repeats those steps two more times to create two more orders for Bob's Beans.

The method then sets the command's customer ID parameter to Claire's Coffee's ID. It then uses the same steps it used earlier to create two orders for Claire's Coffee.

Create Order Item Data

The CreateOrderItemRecords method uses the following code to add order items to the orders that the CreateOrderRecords method created:

static private void CreateOrderItemRecords(NpgsqlConnection conn,
    List<int> bobsOrderIds, List<int> clairesOrderIds)
{
    // Define the data.
    object[] orderItemsData =
    {
        new object[] { bobsOrderIds[0], 1, "Coffee cup", 10, 1.99 },
        new object[] { bobsOrderIds[0], 2, "Sulawesi", 5, 8.95},
        new object[] { bobsOrderIds[1], 1, "Tarrazu", 2, 19.95},
        new object[] { bobsOrderIds[1], 2, "Coffee filters", 10, 4.95},
        new object[] { bobsOrderIds[1], 3, "Napkins", 10, 4.99},
        new object[] { bobsOrderIds[1], 4, "Yirgacheffe", 5, 15.99},
        new object[] { bobsOrderIds[2], 1, "Digital scale", 1, 74.99},
        new object[] { bobsOrderIds[3], 1, "Espresso machine", 1, 2999.00},
        new object[] { clairesOrderIds[0], 1, "Grinder", 1, 299.00},
        new object[] { clairesOrderIds[1], 1, "Thermometer", 2, 15.49},
        new object[] { clairesOrderIds[1], 2, "Foamer", 1, 9.95},
    };
 
    // Make the command.
    string createItemStatement =
        @"INSERT INTO order_items
            (order_id, item_number, item_name, quantity, price:each) 
        VALUES
            (@order_id, @item_number, @item_name, @quantity, @price:each)";
    using (NpgsqlCommand insertCmd =
        new NpgsqlCommand(createItemStatement, conn))
    {
        // Add the parameters.
        NpgsqlParameter orderIdParameter =
            insertCmd.Parameters.Add("order_id",
                NpgsqlTypes.NpgsqlDbType.Integer);
        NpgsqlParameter itemNumberParameter =
            insertCmd.Parameters.Add("item_number",
                NpgsqlTypes.NpgsqlDbType.Integer);
        NpgsqlParameter itemNameParameter =
            insertCmd.Parameters.Add("item_name",
                NpgsqlTypes.NpgsqlDbType.Text);
        NpgsqlParameter quantityParameter =
            insertCmd.Parameters.Add("quantity",
                NpgsqlTypes.NpgsqlDbType.Integer);
        NpgsqlParameter priceEachParameter =
            insertCmd.Parameters.Add("price:each",
                NpgsqlTypes.NpgsqlDbType.Numeric);
 
        // Prepare the command.
        insertCmd.Prepare();
 
        // Create the records.
        foreach (object[] row in orderItemsData)
        {
            // Create this row.
            orderIdParameter.Value = row[0];
            itemNumberParameter.Value = row[1];
            itemNameParameter.Value = row[2];
            quantityParameter.Value = row[3];
            priceEachParameter.Value = row[4];
            insertCmd.ExecuteNonQuery();
        }
    }
}

This method uses the same basic approach used by the CreateOrderRecords method except it uses a loop to add records with data stored in an array.

The method first creates an array of objects, each of which is an array holding one record's values. Having defined the records' data, the code method composes a SQL INSERT statement with placeholders for its values as in earlier pieces of code. It uses the statement to create a command object, adds parameters to that object, and calls its Prepare method all as before.

Now the code loops through the record data array. For each row in the array, the program copies the row's field values into the appropriate parameters and then calls the command's ExecuteNonQuery method.

Logically, this approach is similar to the one used by the CreateCustomerRecords and CreateOrderRecords methods. They all create a command, give it some parameters, prepare it, fill in parameter values, and execute the command. Storing the data in an array and looping may make the code easier to read, particularly if you need to create many records.

Display Orders

The DisplayOrders method uses the following code to display the orders and their items:

// Display the orders.
static private void DisplayOrders(NpgsqlConnection conn)
{
    string customersQuery =
        @"SELECT date, name, orders.order_id, item_number, item_name,
            quantity, price:each, quantity * price:each
        FROM customers, orders, order_items
        WHERE
            customers.customer_id = orders.customer_id AND
            orders.order_id = order_items.order_id
        ORDER BY date, order_id, item_number";
    using (NpgsqlCommand cmd =
        new NpgsqlCommand(customersQuery, conn))
    {
        int lastOrderId = -1;
        double orderTotal = 0;
        NpgsqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (reader.GetInt32(2) != lastOrderId)
            {
                if (orderTotal> 0)
                {
                    Console.WriteLine("    Total: {0:C}
", orderTotal);
                    orderTotal = 0;
                }
 
                Console.WriteLine("{0,10}  {1,-15} Order ID: {2}",
                    reader.GetDateTime(0).ToShortDateString(),
                    reader.GetString(1),
                    reader.GetInt32(2));
                lastOrderId = reader.GetInt32(2);
            }
 
            Console.WriteLine("    {0}. {1,-20} {2,3} @ {3,9:C} = {4,9:C}",
                reader.GetInt32(3),
                reader.GetString(4),
                reader.GetInt32(5),
                reader.GetDouble(6),
                reader.GetDouble(7));
            orderTotal += reader.GetDouble(7);
        }
        if (orderTotal> 0)
        {
            Console.WriteLine("    Total: {0:C}
", orderTotal);
            orderTotal = 0;
        }
 
        // Close the reader. Important!
        reader.Close();
    }
}

This method creates a query that selects fields from the customers, orders, and order_items tables. It also selects the calculated value quantity * price:each.

The WHERE clause uses the records' IDs to link associated records. The ORDER BY clause sorts the results by date, order ID (if multiple orders have the same date), and item number. The final sort by item number makes the items in an order appear in their proper order. After it has created the query, the program uses it to create a command object.

The code then defines two variables. It uses the variable lastOrderId to track the current order's ID. It initially sets that variable to –1 so the first row will have a different ID.

The code uses the variable orderTotal to keep track of the current order's total cost. It initially sets this value to 0.

Having initialized the lastOrderId and orderTotal variables, the code calls the command's ExecuteReader method. That method performs the query and returns an NpgsqlDataReader object that can loop through the returned results.

The code then enters a while loop that executes as long as the reader's Read method returns true. That method advances to the next returned result row. (Initially, it points before the first row, so the first call to Read advances to the first row.) The Read method returns true if it successfully moved to the next row and false if it dropped off the end of the results.

Inside the loop, the program calls the reader's GetInt32 method to get the value in position 2 (numbered from 0) as an integer. (If you look at the query, you'll see that the returned value in position 2 is the order ID.) If the new order ID is different from the current one stored in the variable lastOrderId, then the program displays the previous order's total value.

If this is a new order, the code displays the new order date, customer name, and order ID. It then saves the new order's ID in the lastOrderId variable so that it will know when it sees the next order's records.

After it has dealt with the case of starting a new record, the program displays the new order item's data and adds its total cost to the orderTotal variable.

After it has finished looping through all the returned data, the program displays the final order's total cost.

The method then closes the data reader. If you don't do that, then the next time you try to use the connection to execute a command you'll get the following error:

System.InvalidOperationException: 'Connection is busy'

Instead of explicitly closing the reader, you can use it in a using block so that it closes automatically.

The following text shows this method's output:

  4/1/2027  Bob's Beans     Order ID: 633
    1. Coffee cup            10 @     $1.99 =    $19.90
    2. Sulawesi               5 @     $8.95 =    $44.75
    Total: $64.65
 
  4/1/2027  Bob's Beans     Order ID: 634
    1. Tarrazu                2 @    $19.95 =    $39.90
    2. Coffee filters        10 @     $4.95 =    $49.50
    3. Napkins               10 @     $4.99 =    $49.90
    4. Yirgacheffe            5 @    $15.99 =    $79.95
    Total: $219.25
 
 4/10/2027  Claire's Coffee Order ID: 637
    1. Grinder                1 @   $299.00 =   $299.00
    Total: $299.00
 
 4/17/2027  Bob's Beans     Order ID: 635
    1. Digital scale          1 @    $74.99 =    $74.99
    Total: $74.99
 
 4/20/2027  Claire's Coffee Order ID: 638
    1. Thermometer            2 @    $15.49 =    $30.98
    2. Foamer                 1 @     $9.95 =     $9.95
    Total: $40.93
 
  5/1/2027  Bob's Beans     Order ID: 636
    1. Espresso machine       1 @ $2,999.00 = $2,999.00
    Total: $2,999.00

These results are similar to those produced by the Python version of the program.

SUMMARY

PostgreSQL is a popular relational database system that in many ways is similar to MariaDB. The main differences between this example and the MariaDB example are due to the ways that the two programs' database adapters work.

For this example, you used pgAdmin to create an orders database. The example program then adds some records to the database's tables and fetches the data, linking related records together to reproduce the customers' orders. In a real application, you would want to add other features to let the user interactively work with the database to create, read, and edit orders.

The next chapter moves away from relational databases to demonstrate a NoSQL graph database. Before you move on to Chapter 20, however, use the following exercises to test your understanding of the material covered in this chapter. You can find the solutions to these exercises in Appendix A.

EXERCISES

The CreateCustomerRecords and CreateOrderRecords methods call a command object's ExecuteScalar method, but the CreateOrderItemRecords method calls ExecuteNonQuery. Why the difference?
Add a new LetUserCreateOrder method to the example program that allows the user to create a new order for one of the existing customers. It should do the following:
1. Prompt the user for a customer ID. (Be sure to enter a valid ID when you're testing.)
2. Prompt the user for an order date.
3. Enter an infinite loop that prompts for the order item information: item name, quantity, and price of each. It should keep track of the item number as the user enters items. When the user enters a blank line, break out of the loop.
4. Create the new records.
  1. Create a transaction.
  2. Create the orders record and save the autogenerated order ID in a variable. Make the command use the transaction.
  3. Create the order items records, making their commands use the transaction.
5. Rerun the DisplayOrders method to verify that the new order was created correctly.
  Here are some hints:
  - Don't worry about error handling. Assume that you'll enter valid values while testing.
  - It may help with debugging to print the SQL commands before executing them.
Set a breakpoint in the LetUserCreateOrder method on the statement that commits the transaction. Run the program and create a new order. To make recognizing the order easier, give it the distinctive date 1/2/2300. Give it at least one item with the distinctive item name Aardvark.
When it reaches the breakpoint, right-click the following statement and select Set New Statement so that the program skips the Commit statement. Then let the program finish.

Now use pgAdmin to verify that the transaction prevented the program from creating the new order. To do that, follow these steps:
1. Start pgAdmin.
2. In the tree view on the left, drill down to Servers ➪ PostgreSQL ➪ Databases ➪ BrewCrewDB ➪ Schemas ➪ Public ➪ Tables.
3. In the tree view, click the orders table. On the toolbar, click the View Data tool, which looks like a little spreadsheet. Look at the data and verify that there is no order with the date 1/2/2300.
4. In the tree view, click the order_items table. Click the View Data tool and verify that there is no order item named Aardvark.
What happens if you enter crème brûlée as an item name? (If you can't type those characters, search online for creme brulee and then copy and paste the words with the correct accents into the program's input box.)
What happens if you enter Amy's cookies as an item name?
What happens if you enter the order date in the format d/m/yyyy? What happens if you enter the specific value 20/10/2027?
What kinds of database errors could the new order code encounter? (Ignore formatting errors such as the user entering “ten” for the customer ID or entering an invalid order date like 20/40/2100.)

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for 19 PostgreSQL in C#

Create new playlist

Sign In

Sign Up