How to Insert Data into Tables Using OracleCommand

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

Download Now dotConnect for Oracle

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 designer OracleConnection component from toolbox), you have to embed licensing information manually. This is described in dotConnect for Oracle documentation.

General information

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 OracleCommand. 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

deptno

dname

loc

10 Accounting New York
20 Sales Dallas
30 Sales2 Chicago

Table emp

empno

ename

job

mgr

hiredate

sal

comm

deptno

7369 Smith Clerk 7566 1980-12-17 800 Null 20
7499 Allen Salesman 7698 1981-02-20 1600 300 30
7521 Ward Salesman 7698 1981-02-22 1250 500 30
7566 Jones Manager 7839 1981-04-02 2975 Null 20
7654 Martin Salesman 7698 1981-09-28 1250 1400 30
7698 Blake Manager 7839 1981-05-01 2850 Null 30
7839 King President Null 1981-11-17 5000 Null 10

Inserting data in run time

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

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

The following code fragment executes the query:

[C#]
OracleConnection conn = new OracleConnection("User Id=scott;Password=tiger;Server=OraServer;");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "INSERT INTO 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();
}
[Visual Basic]
Dim conn As OracleConnection = New OracleConnection("User Id=scott;Password=tiger;Server=OraServer;")
Dim cmd As OracleCommand = New OracleCommand()
cmd.CommandText = "INSERT INTO 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 OracleCommand object, assigns the query text and connection to the OracleCommand instance. Connection is opened then. The ExecuteNonQuery() method of OracleCommand 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.

Design time setup

The same operations in design time include following steps:

  1. Place OracleConnection component on a designer.
  2. Set up its properties and open connection by changing the State property to Open
    - or -
    right-click on the component, choose Connect item and use the dialog to connect to server.
  3. Place OracleCommand component on the designer.
  4. In its Connection property select name of the OracleConnection instance on the designer.
  5. In the CommandText property type in the following query:
    INSERT INTO dept VALUES (20,'Sales','Dallas')
  6. Right-click on the OracleCommand and choose Execute from shortcut menu.
  7. Repeat steps 5 and 6 with the following CommandText:
    INSERT INTO dept VALUES (30,'Sales2','Chicago')

Note that the last two steps might be easier to do in the OracleCommand editor. To invoke it choose CommandText item form OracleCommand shortcut menu or click on the ellipsis in this property in Properties window.

Using dbForge Fusion for Oracle

Sometimes it is very handy to modify data in design time. This section describes one of the ways to edit data in a grid. These operations are convenient to do in dbForge Fusion for Oracle.

  1. In Database Explorer connect to server and browse to the table Emp (for detailed information on how to do it refer to the tutorial Creating Database Objects).
  2. Right-click on it and choose Retrieve Data from shortcut menu.
    The editor appears where you can see what data the table contains if any.
  3. Click the Append button (+) or press <insert> key.
  4. Click on the very first cell. Type in value: 7369.
  5. Click on the next cell or hit <Tab>, type in: Smith.
  6. In the following cell, type: Clerk.
  7. Go on filling the row until you reach the last column. Press <Enter> key to apply inserting the row.
  8. Click on the first cell of the second row. Now you have to fill it starting with value 7499. Repeat steps 3-7 to insert all required rows into the table.

Conclusion

This tutorial describes how you can use OracleCommand component to insert data into tables. 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, OracleLoader is the fastest way to insert data, OracleScript is designed for executing series of statements. For more information on these components refer to dotConnect for Oracle documentation.


Back to list

dotConnect for Oracle

Get enhanced ORM-enabled data provider for Oracle and develop .NET applications working with Oracle data quickly and easily!