Work with Oracle database in C#

Introduction
Connect to Oracle Database with C#
OracleCommand
Query
Insert
Update
Delete
Call procedures in C#
Call Functions in C#
ExecuteScalar

1. Introduction

Trong tài liệu này tôi sẽ hướng dẫn bạn thao tác với Oracle Database từ C#, mục tiêu bao gồm:

Query
Insert
Update
Delete
Call function, procedure in C#,...

Firstly you need to create a demo database, see the instructions at:

Sample Database

2. Connect to Oracle Database with C#

Create project CsOracleTutorial:

Project is created:

You need to declare the libraries, and need a utility class ( DBUtils.cs) which help to connect to the database. With Oracle database, you can see the instructions at:

Connect to Oracle Database in CSharp without Oracle Client

DBOracleUtils.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;

namespace Tutorial.SqlConn
{
    class DBOracleUtils
    {

        public static OracleConnection 
                       GetDBConnection(string host, int port, String sid, String user, String password)
        {

            Console.WriteLine("Getting Connection ...");

            // Connection string to connect directly to Oracle.
            string connString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = "
                 + host + ")(PORT = " + port + "))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = "
                 + sid + ")));Password=" + password + ";User ID=" + user;


            OracleConnection conn = new OracleConnection();

            conn.ConnectionString = connString;

            return conn;
        }
    
    }
}

DBUtils.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;

namespace Tutorial.SqlConn
{
    class DBUtils
    {

        public static OracleConnection GetDBConnection()
        {
            string host = "192.168.0.102";
            int port = 1521;
            string sid = "db12c";
            string user = "simplehr";
            string password = "12345";

            return DBOracleUtils.GetDBConnection(host, port, sid, user, password);
        }
    }

}

3. OracleCommand

In C# to manipulate Oracle Database, such query, insert, update, delete, you use a OracleSqlCommand object, OracleSqlCommand is a class extended from DbCommand. In case you need query, insert, update or delete in the SQL Server Database you need to use SqlCommand, or with MySQL is MySqlCommand. Unfortunately, it will be very difficult if you want to use a source code for the various Database.

Create a OracleCommand object to work with Oracle Database:

OracleConnection conn = DBUtils.GetDBConnection();

// Way 1:

// Create a Command from Connection.
OracleCommand cmd = conn.CreateCommand();

// Set Command Text
cmd.CommandText = sql;

// Way 2:

// Create new Command
OracleCommand cmd = new OracleCommand(sql);

// Set connection for Command.
cmd.Connection = conn;


// Way 3:

// Create new Command with parameters: Command text & connection

OracleCommand cmd = new OracleCommand(sql, conn);

4. Query

For example, data query using C#.

QueryDataExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn; 
using System.Data.Common;
using Oracle.DataAccess.Client;

namespace CsOracleTutorial
{
    class QueryDataExample
    {
        static void Main(string[] args)
        {
            // Get connection.
            OracleConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                QueryEmployee(conn);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                conn.Close(); 
                conn.Dispose();
            }       
            Console.Read();
        }

        private static void QueryEmployee(OracleConnection conn)
        { 
            string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee"; 

            // Create command.
            OracleCommand cmd = new OracleCommand();

            // Set connection for command.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Get index of Column Emp_ID in query statement.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // Index of Emp_ID = 1
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // Index of column Mng_Id.
                        // Chỉ số (index) của cột Mng_Id trong câu lệnh SQL.
                        int mngIdIndex = reader.GetOrdinal("Mng_Id");

                        long? mngId = null;
 
                        if (!reader.IsDBNull(mngIdIndex))
                        {
                            mngId = Convert.ToInt64(reader.GetValue(mngIdIndex)); 
                        }
                        Console.WriteLine("--------------------");
                        Console.WriteLine("empIdIndex:" + empIdIndex);
                        Console.WriteLine("EmpId:" + empId);
                        Console.WriteLine("EmpNo:" + empNo);
                        Console.WriteLine("EmpName:" + empName);
                        Console.WriteLine("MngId:" + mngId);
                    }
                }
            }

        }
    }

}

Running the example:

--------------------
empIdIndex:0
EmpId:7369
EmpNo:E7369
EmpName:SMITH
MngId:7902
--------------------
empIdIndex:0
EmpId:7499
EmpNo:E7499
EmpName:ALLEN
MngId:7698
--------------------
empIdIndex:0
EmpId:7521
EmpNo:E7521
EmpName:WARD
MngId:7698
--------------------
empIdIndex:0
EmpId:7566
EmpNo:E7566
EmpName:JONES
MngId:7839
.....

Node: The reason for the "using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.
// Use the keyword 'using' with IDispose objects.
// (The object of Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
     // Code ...
}

// Equivalent to the code:
DbDataReader reader = cmd.ExecuteReader();
try
{
     // Code ...
}
finally
{
   // Call the method to destroy the object
   // Free up resources.
   reader.Dispose();
}

5. Insert

For example, insert a record into Salary_Grade table.

InsertDataExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data.Common; 
using System.Data;
using Oracle.DataAccess.Client;

namespace CsOracleTutorial
{
    class InsertDataExample
    {
          static void Main(string[] args) 
          {
  
            OracleConnection connection = DBUtils.GetDBConnection();
            connection.Open();
            try
            {    
                // Insert statement.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

                OracleCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;  

                // Create Parameter.
                OracleParameter gradeParam = new OracleParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Add parameter @highSalary (Write shorter)
                OracleParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Add parameter @lowSalary (more shorter).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Execute Command (for Delete,Insert or Update).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                connection.Close(); 
                connection.Dispose();
                connection = null;
            }
           

            Console.Read();
  
         }
    }

}

Running the example:

Row Count affected = 1

6. Update

Update example:

UpdateExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Tutorial.SqlConn;
using System.Data;

namespace CsOracleTutorial
{
    class UpdateExample
    {
        static void Main(string[] args)
        {
          
            OracleConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";
 
                OracleCommand cmd = new OracleCommand();
 
                cmd.Connection = conn; 
                cmd.CommandText = sql;

         
                cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
                cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; 

                // Execute Command (for Delete, Insert,Update).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                conn.Close(); 
                conn.Dispose();
                conn = null;
            }


            Console.Read();

        }
    }

}

Running the example:

Row Count affected = 1

7. Delete

For example, use C# to delete data.

DeleteExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Tutorial.SqlConn;
using System.Data;

namespace CsOracleTutorial
{
    class DeleteExample
    {
        static void Main(string[] args)
        {
            // Get connection to database.
            OracleConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
               

                string sql = "Delete from Salary_Grade where Grade = @grade ";

                // Create Command object.
                OracleCommand cmd = new OracleCommand();

                // Set Connection for command.
                cmd.Connection = conn;

                // Command Text.
                cmd.CommandText = sql; 

                cmd.Parameters.Add("@grade", SqlDbType.Int).Value = 3;  

                // Execute Command (for Delete, insert, update).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                conn.Close(); 
                conn.Dispose();
                conn = null;
            }


            Console.Read();

        }
    }

}

8. Call procedures in C#

You need to create a simple procedure in Oracle and call it in C #:

Get_Employee_Info

-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date
 
Create Or Replace Procedure Get_Employee_Info(p_Emp_Id     Integer
                                           ,v_Emp_No     Out Varchar2
                                           ,v_First_Name Out Varchar2
                                           ,v_Last_Name  Out Varchar2
                                           ,v_Hire_Date  Out Date) Is
Begin
 v_Emp_No := 'E' || p_Emp_Id;
 --
 v_First_Name := 'Michael';
 v_Last_Name  := 'Smith';
 v_Hire_Date  := Sysdate;
End Get_Employee_Info;
/

CallProcedureExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CsOracleTutorial
{
    class CallProcedureExample
    {
        // Get_Employee_Info                
        // @p_Emp_Id       Integer ,
        // @v_Emp_No      Varchar(50)   OUTPUT
        // @v_First_Name  Varchar(50)   OUTPUT
        // @v_Last_Name  Varchar(50)    OUTPUT
        // @v_Hire_Date    Date         OUTPUT
        static void Main(string[] args)
        {
            OracleConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 

                // Create a Command object to call Get_Employee_Info procedure.
                OracleCommand cmd = new OracleCommand("Get_Employee_Info", conn);

                // Command Type is StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Add parameter @p_Emp_Id and set value = 100
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value =100;

                // Add parameter @v_Emp_No type of Varchar(20).
                cmd.Parameters.Add(new OracleParameter("@v_Emp_No", OracleDbType.Varchar2, 20));
                cmd.Parameters.Add(new OracleParameter("@v_First_Name", OracleDbType.Varchar2, 50));
                cmd.Parameters.Add(new OracleParameter("@v_Last_Name", OracleDbType.Varchar2, 50));
                cmd.Parameters.Add(new OracleParameter("@v_Hire_Date", OracleDbType.Date)); 

                // Register parameter @v_Emp_No is OUTPUT.
                cmd.Parameters["@v_Emp_No"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_First_Name"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_Last_Name"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_Hire_Date"].Direction = ParameterDirection.Output;
 
                // Execute procedure.
                cmd.ExecuteNonQuery();

                // Get output values.
                string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString();
                string firstName = cmd.Parameters["@v_First_Name"].Value.ToString();
                string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString();
                object hireDateObj =  cmd.Parameters["@v_Hire_Date"].Value;

                Console.WriteLine("hireDateObj type: "+ hireDateObj.GetType().ToString());
                OracleDate hireDate = (OracleDate)hireDateObj;


                Console.WriteLine("Emp No: " + empNo);
                Console.WriteLine("First Name: " + firstName);
                Console.WriteLine("Last Name: " + lastName);
                Console.WriteLine("Hire Date: " + hireDate);

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }


}

Running the example:

Getting Connection ...
hireDateObj type: Oracle.DataAccess.Types.OracleDate
Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 12-FEB-16

9. Call Functions in C#

You need to create a simple function in Oracle and call it in C#:

Get_Emp_No

CREATE or Replace Function Get_Emp_No (p_Emp_Id  Integer) Return Varchar2
As
Begin    

 return   'E' || p_Emp_Id;

END;
/

CallFunctionExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CsOracleTutorial
{
    class CallFunctionExample
    {

        // Function: Get_Emp_No                
        // Parameter: @p_Emp_Id       Integer 
        static void Main(string[] args)
        {
            OracleConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {             
                // Create a Command object to call Get_Emp_No function.
                OracleCommand cmd = new OracleCommand("Get_Emp_No", conn);

                // CommandType is StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // ** Note: With Oracle, The return parameter must be added first.
                // Create result Parameter (Varchar2(50))
                OracleParameter resultParam = new OracleParameter("@Result", OracleDbType.Varchar2, 50);    
            
                // ReturnValue
                resultParam.Direction = ParameterDirection.ReturnValue; 

                // Add to parameters
                cmd.Parameters.Add(resultParam);

                // Add parameter @p_Emp_Id and set value = 100.
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value = 100;
                
                // Call function.
                cmd.ExecuteNonQuery();

                string empNo = null;
                if (resultParam.Value != DBNull.Value)
                {
                    Console.WriteLine("resultParam.Value: "+ resultParam.Value.GetType().ToString());
                    OracleString ret = (OracleString) resultParam.Value;
                    empNo = ret.ToString();
                }                 
                Console.WriteLine("Emp No: " + empNo); 

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }

}

Running the example:

Getting Connection ...
resultParam.Value: Oracle.DataAccess.Types.OracleString
Emp No: E100

10. ExecuteScalar

OracleCommand.ExecuteScalar() is a method used to execute SQL statements, it returns the value of the first column of the first row in results of SQL statements.

-- The following statement returns only one value.

Select count(*) from Employee;

-- Or

Select Max(e.Salary) From Employee e;

Example:

ExecuteScalarExample.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using System.Data;
using Tutorial.SqlConn;


namespace CsOracleTutorial
{
    class ExecuteScalarExample
    {
        static void Main(string[] args)
        {
            OracleConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                OracleCommand cmd = new OracleCommand("Select count(*) From Employee", conn);
                 
                cmd.CommandType = CommandType.Text;

                // ExecuteScalar method returns the value of first column on first row.
                object countObj =  cmd.ExecuteScalar();

                int count = 0;
                if (countObj != null)
                {
                    count = Convert.ToInt32(countObj);
                }

                Console.WriteLine("Emp Count: " + count);

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }
}

Running the example:

Emp Count: 14

Work with Oracle database in C#

1. Introduction

2. Connect to Oracle Database with C#

3. OracleCommand

4. Query

5. Insert

6. Update

7. Delete

8. Call procedures in C#

9. Call Functions in C#

10. ExecuteScalar

C# Programming Tutorials

C# Programming Tutorials

Newest Articles