This tutorial describes how to use PgSqlCommand component to insert data into tables by means of executing SQL queries.

In this tutorial:

• Requirements
• General Information
• Inserting Data in Run-time
• Inserting Data in Design-time
• Additional Information

Requirements

This walkthrough supposes that you know how to connect to server (tutorial Logging onto the server) and that necessary objects are already created on the server (tutorial Creating database objects).

Note that if you do not use design-time (specifically, if you do not place on a form designer PgSqlConnection component from toolbox), you have to embed licensing information manually. This is described in the Licensing topic.

Inserting data in run-time

Data on server can be modified (inserted, changed or deleted) using Data Manipulation Language (DML), which is a part of SQL. The DML statements can be executed on server by account that has necessary privileges.

There are two ways to manipulate a database. You can build DML statements manually and run them within some component like PgSqlCommand. Another way is to use design-time features that provide graphical user interface to manage database. We will discuss both ways.

The goal of this tutorial is to insert the following data into tables dept and emp:

Table dept

Table emp

Inserting Data in Run-time

To insert the first row into table dept you can use the following statement:

INSERT INTO public.dept(deptno,dname,loc) VALUES (10,'Accounting','New York')

The following code fragment executes the query:

PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=postgres;host=localhost;database=postgres;");
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandText = "INSERT INTO public.dept (deptno, dname, loc) VALUES (10,'Accounting','New York')";
cmd.Connection = conn;
conn.Open();
try {
  int aff = cmd.ExecuteNonQuery();
  MessageBox.Show(aff + " rows were affected.");
}
catch {
  MessageBox.Show("Error encountered during INSERT operation.");
}
finally {
  conn.Close();
}

Dim conn As PgSqlConnection = New PgSqlConnection("User Id=postgres;Password=postgres;host=localhost;database=postgres;")
Dim cmd As PgSqlCommand = New PgSqlCommand()
cmd.CommandText = "INSERT INTO public.dept (deptno, dname, loc) VALUES (10,'Accounting','New York')"
cmd.Connection = conn
conn.Open()
Try
  Dim aff As Integer = cmd.ExecuteNonQuery()
  MessageBox.Show(aff & " rows were affected.")
Catch
  MessageBox.Show("Error encountered during INSERT operation.")
Finally
  conn.Close()
End Try

The sample first creates a connection with hardcoded connection string. Then it creates PgSqlCommand object, assigns the query text and connection to the PgSqlCommand instance. Connection is opened then. The ExecuteNonQuery() method of PgSqlCommand runs SQL statement in the CommandText property and returns number of rows affected by the query. This method is not intended to run SELECT statements. We will discuss retrieving data in other tutorials.

If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally clauses to make sure the connections are closed properly.

Inserting Data in Design-time

Same operations in design time include following steps:

1. Place PgSqlConnection component on a form designer.

2. Setup its properties and open connection by changing the State property to Open
   - or -
   right-click on the component, choose Connect from the popup menu and use the dialog to connect to server.

   While setting the connection properties, set its Protocol property to Ver20 to use PgSqlCommand for executing more than one statement at once. Otherwise, you may need to execute each statement separately.

3. Place PgSqlCommand component on the designer.

4. Select name of the PgSqlConnection instance in its Connection property.

5. Select true in its UnpreparedExecute property (that allows you to execute several statements at once with one PgSqlCommand).

6. In the CommandText property type in the following query:

   INSERT INTO public.dept(deptno,dname,loc) VALUES (20,'Sales','Dallas');
   INSERT INTO public.dept(deptno,dname,loc) VALUES (30,'Sales2','Chicago');

7. Right-click on the PgSqlCommand and choose Execute from popup menu.

Note that the last two steps might be easier to do in the PgSqlCommand editor. To invoke it right-click the component and choose CommandText from the PgSqlCommand popup menu.

Additional Information

Actually there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, PgSqlLoader is the fastest way to insert data, PgSqlScript is designed for executing series of statements. For more information on these components refer to dotConnect for PostgreSQL reference.

See Also

Getting Started  | PgSqlCommand Class  | PgSqlScript Class  | PgSqlLoader Class