Inserting records in a database v9.0.3.1

Suggest edits

You can use the ExecuteNonQuery() method of EDBCommand to add records to a database stored on an EDB Postgres Advanced Server host with an INSERT command.

In the example that follows, the INSERT command is stored in the variable insertCommand. The values prefixed with a colon (:) are placeholders for EDBParameters that are instantiated, assigned values, and then added to the INSERT command's parameter collection in the statements that follow. The INSERT command is executed by the ExecuteNonQuery() method of the insertCommand object.

Note that ExecuteNonQuery() method returns the number of rows affected by the command. It is usually a good practice to check the number of affected rows matches your expectations (1 in this example).

The example adds an employee to the emp table:

.NET
.NET Framework

using EnterpriseDB.EDBClient;

namespace InsertingRecords;

internal static class Program
{
    static async Task Main(string[] args)
    {
        // NOT FOR PRODUCTION, consider moving the connection string in a configuration file
        var connectionString = "Server=127.0.0.1;Port=5444;User Id=enterprisedb;Password=edb;Database=edb";
        try
        {
            await using var dataSource = EDBDataSource.Create(connectionString);
            await using var conn = await dataSource.OpenConnectionAsync();

            var query = "INSERT INTO emp(empno,ename) VALUES(:EmpNo, :EName)";
            using var insertCommand = new EDBCommand(query, conn);
            var empNo = insertCommand.Parameters.Add(new EDBParameter("EmpNo", EDBTypes.EDBDbType.Integer));
            var empName = insertCommand.Parameters.Add(new EDBParameter("EName", EDBTypes.EDBDbType.Text));                        
            empNo.Value = 1234;
            empName.Value = "Lola";

            var numRows = await insertCommand.ExecuteNonQueryAsync();

            Console.WriteLine($"{numRows} record(s) inserted successfully");
            await conn.CloseAsync();
        }
        catch (Exception exp)
        {
            Console.WriteLine($"An error occured: {exp}");
        }
    }
}

using System;
using System.Threading.Tasks;
using EnterpriseDB.EDBClient;

namespace InsertingRecords
{
    internal static class Program
    {
        static async Task Main(string[] args)
        {
            // NOT FOR PRODUCTION, consider moving the connection string in a configuration file
            var connectionString = "Server=127.0.0.1;Port=5444;User Id=enterprisedb;Password=edb;Database=edb";
            try
            {
                using (var dataSource = EDBDataSource.Create(connectionString))
                using (var conn = await dataSource.OpenConnectionAsync())
                {

                    var query = "INSERT INTO emp(empno,ename) VALUES(:EmpNo, :EName)";
                    using (var insertCommand = new EDBCommand(query, conn))
                    {

                        var empNo = insertCommand.Parameters.Add(new EDBParameter("EmpNo", EDBTypes.EDBDbType.Integer));
                        var empName = insertCommand.Parameters.Add(new EDBParameter("EName", EDBTypes.EDBDbType.Text));
                        empNo.Value = 1234;
                        empName.Value = "Lola";

                        var numRows = await insertCommand.ExecuteNonQueryAsync();

                        Console.WriteLine($"{numRows} record(s) inserted successfully");
                    }
                    await conn.CloseAsync();
                }
            }
            catch (Exception exp)
            {
                Console.WriteLine($"An error occured: {exp}");
            }
        }
    }
}

This program should show the following output in the Console:

1 record(s) inserted successfully

Note

There are several ways to declare parameters and assign their values. Here are some examples :

// One-liners (parameter types are induced from their value)
insertCommand.Parameters.AddWithValue("EmpNo", 1234);
insertCommand.Parameters.AddWithValue("EName", "Lola");

// Using the parameter variable to set its value, instead of getting it via the command
var empParam = insertCommand.Parameters.Add(new EDBParameter("EmpNo", EDBTypes.EDBDbType.Integer));
empParam.Value = 1234;
var nameParam = insertCommand.Parameters.Add(new EDBParameter("EName", EDBTypes.EDBDbType.Text));
nameParam.Value = "Lola";

insertCommand.Parameters[0].Value = 1234; // works but not recommended: access by index is error-prone
insertCommand.Parameters["EmpNo"].Value = 1234; // works but any parameter name change will break here

← Prev

Parameterized queries

↑ Up

EDB .NET Connector

Deleting records in a database

Inserting records in a database v9.0.3.1

Note

← Prev

↑ Up

Next →