Работа с базой данных Oracle на C#

Введение
Соединить C# в Oracle Database
OracleCommand
Запрос данных
Insert данные
Update данных
Удалить данные
Вызов процедур C#
Вызвать функции в C#
ExecuteScalar

1. Введение

В данной статье я покажу вам как работать с базой данных Oracle из C#, цель включает:

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

В статье используется SIMPLEHR, Database Schema пример используется во многих примерах руководств o7planning.org, вы можете создать этот Schema на Oracle, MySQL илиSQL Server. Можете посмотреть инструкцию по ссылке:

Пример базы данных

2. Соединить C# в Oracle Database

Создать project CsOracleTutorial:

Project создан.

Вам нужно объявить библиотеки DLL, и нужен утилитарный класс (DBUtils.cs) помогающий соединить в базу данных. С базой данных Oracle, вы можете посмотреть инструкцию по ссылке:

Подключиться к базе данных Oracle в C# без 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 для прямого подключения к 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

В C# чтобы работать с базой данных Oracle, например query, insert, update, delete используйте объект OracleCommand, OracleCommand это расширенный класс DbCommand. В случае, если вам нужен query, insert,update или delete в базе данных MySQL вам нужно использовать MySqlCommand, или с SQL Server это SqlCommand. К сожалению вам будет трудно, если хотите использовать исходный код для разных баз данных.

Вы можете создать объект OracleCommand для работы с 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. Запрос данных

Например сделать запрос данных используя 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)
        {
            // Получить объект Connection для подключения к DB.
            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"; 

            // Создать объект Command.
            OracleCommand cmd = new OracleCommand();

            // Сочетать Command с Connection.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Индекс (index) столбца Emp_ID в команде SQL.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // Индекс столбца Emp_No = 1.
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);


                        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);
                    }
                }
            }

        }
    }

}

Запуск примера:

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

Примечание: Команда using используется, чтобы удостовериться, что если объект будет уничтожен (dispose) сразу после того как он вышел за рамки, не требуя написания явного кода.
// Использовать ключевое слово 'using' с объектами вида IDispose.
// (Это объект у Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
     // Code ...
}

// Соответствует написанию визуального способа:
DbDataReader reader = cmd.ExecuteReader();
try
{
     // Code ...
}
finally
{
   // Вызвать метод для разрушения объекта
   // Освободить ресурс.
   reader.Dispose();
}

5. Insert данные

Например insert (вставить) еще одну запись в таблицу Salary_Grade.

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.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

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

                // Создать объект Parameter.
                OracleParameter gradeParam = new OracleParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Добавить параметр @highSalary (Написать кратко).
                OracleParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Добавить параметр @lowSalary (Написать кратко).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Выполнить Command (Используется для 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
            { 
                connection.Close(); 
                connection.Dispose();
                connection = null;
            }
           

            Console.Read();
  
         }
    }

}

Запуск примера:

Row Count affected = 1

6. Update данных

Пример update (обновления) в C#.

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; 

                // Выполнить Command (Используется для 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();

        }
    }

}

Запуск примера:

Row Count affected = 1

7. Удалить данные

Пример использования C# для удаления данных в Database.

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)
        {
            // Получить подключение к базе данных.
            OracleConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
               

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

                // Создать объект Command
                OracleCommand cmd = new OracleCommand();

                // Сочетать с Connection
                cmd.Connection = conn;

                // Command Text.
                cmd.CommandText = sql; 

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

                // Выполнить Command (Используется для 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. Вызов процедур C#

Вам нужно создать простую процедуру в Oracle и вызвать ее в 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
            { 

                // Создать объект Command для вызова процедуры Get_Employee_Info.
                OracleCommand cmd = new OracleCommand("Get_Employee_Info", conn);

                // Видом Command является StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Добавить параметр @p_Emp_Id и настроить его значение = 100.
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value =100;

                // Добавить параметр @v_Emp_No вида 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)); 

                // Зарегистрировать параметр @v_Emp_No как 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;
 
                // Выполнить процедуру.
                cmd.ExecuteNonQuery();

                // Получить выходные значения.
                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();
        }
    }


}

Запуск примера:

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

9. Вызвать функции в C#

Вам нужна простая функция и вызвать ее в 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
            {             
                // Создать объект Command для вызова функции Get_Emp_No.
                OracleCommand cmd = new OracleCommand("Get_Emp_No", conn);

                // Видом Command является StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // ** Примечание: С Oracle, возвращенный параметр должен быть добавлен первым. 
                OracleParameter resultParam = new OracleParameter("@Result", OracleDbType.Varchar2, 50);    
            
                // ReturnValue
                resultParam.Direction = ParameterDirection.ReturnValue; 

                // Добавить в список параметров.
                cmd.Parameters.Add(resultParam);

                // Добавить параметр @p_Emp_Id и настроить его значение = 100.
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value = 100;
                
                // Вызвать функцию.
                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();
        }
    }

}

Запуск примера:

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

10. ExecuteScalar

OracleCommand.ExecuteScalar() это метод использующийся для выполнения команды SQL, возвращает значение первого столбца первой строки.

-- The following statement returns only one value.

Select count(*) from Employee;

-- Or

Select Max(e.Salary) From Employee e;

Пример:

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 возвращает значение первого столбца на первой строке.
                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();
        }
    }
}

Запуск примера:

Emp Count: 14

Работа с базой данных Oracle на C#

1. Введение

2. Соединить C# в Oracle Database

3. OracleCommand

4. Запрос данных

5. Insert данные

6. Update данных

7. Удалить данные

8. Вызов процедур C#

9. Вызвать функции в C#

10. ExecuteScalar

Pуководства C#

Pуководства C#

Новейшие статьи