devstory

Utilisation d'une base de données Oracle en utilisant C#

  1. Introduction
  2. Se connecter à Oracle Database avec C#
  3. OracleCommand
  4. Requêter
  5. Insérer des données
  6. Mettre à jour des données
  7. Effacer des données
  8. Appeler des procédures dans C#
  9. Appeler des fonctions dans C#
  10. ExecuteScalar

1. Introduction

Dans ce document, je vous donnerai des instructions sur la base de données Oracle de C#, des objectifs sont comme suivant:
  • Query
  • Insert
  • Update
  • Delete
  • Call function, procedure in C#,...
Ce document utilise SIMPLEHR, un exemple de Database Schema est utilisé dans plusieurs guides sur o7planning.org, vous pouvez créer ce Schema sur Oracle, MySQL ou SQL Server. Vous pouvez voir des instructions à:

2. Se connecter à Oracle Database avec C#

Créez le projet CsOracleTutorial:
Le projet est créé.
Vous devez déclarer les bibliothèques DLL, et vous avez besoin d'une classe utilitaire (DBUtils.cs) qui aide à vous connecter à la base de données. Avec la base de données Oracle, vous pouvez voir des instructions à:
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 ...");

            // Connexion de la chaine (String) pour se connecter directement à 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

Dans C# pour manipuler la base de données Oracle, telles que query, insert, update, delete vous utilisez un objet OracleCommand, OracleCommand est une classe étendue de DbCommand. Dans le cas où vous avez besoin de query, insert,update ou delete dans la base de données MySQL vous avez besoin d'utiliser MySqlCommand, ou avec SQL Server ce sera SqlCommand. Malheureusement, ce sera très difficile si vous voulez utiliser un code source pour la base de données différente.
Vous pouvez créer un objet OracleCommand pour manipuler sur 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. Requêter

Par exemple, requêtez des données en utilisant 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)
        {
            // Obtenez l'objet Connection pour se connecter à 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"; 

            // Créez un objet Command.
            OracleCommand cmd = new OracleCommand();

            // Définissez Command pour Connection.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Obtenez l'index (index) de la colonne Emp_ID dans la commande SQL.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // L'index de la colonne Emp_No est 1.
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // Index de la colonne Mng_Id.
                        // L'index (index) de la colonne Mng_Id dans la commande 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);
                    }
                }
            }

        }
    }

}
L'exécution de l'exemple:
--------------------
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
.....
Remarque: La raison pour laquelle la commande using consiste à s'assurer que l'objet est disposé (dispose) dès sa sortie de portée et qu'il ne nécessite pas de code explicite pour s'assurer que cela se produit.
// Utilisez le mot de passe 'using' avec tous les objets IDispose.
// (L'objet de l'Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
     // Code ...
}

// Équivalent au code:
DbDataReader reader = cmd.ExecuteReader();
try
{
     // Code ...
}
finally
{
   // Appelez la méthode de détruire l'objet
   // Libérez de la ressource.
   reader.Dispose();
}

5. Insérer des données

Par exemple, après insert un enregistrement au tableau 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
            {    
                // La déclaration Insert.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

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

                // Créez un objet Parameter.
                OracleParameter gradeParam = new OracleParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Ajoutez le paramètre @highSalary (Écrivez plus court).
                OracleParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Ajoutez le paramètre @lowSalary (Plus court).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Exécutez Command (pour 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();
  
         }
    }

}
L'exécution de l'exemple:
Row Count affected = 1

6. Mettre à jour des données

L'exemple de update en 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; 

                // Exécutez Command (utilisé pour 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();

        }
    }

}
L'exécution de l'exemple:
Row Count affected = 1

7. Effacer des données

Par exemple, utilisez C# pour supprimer des données dans 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)
        {
            // Obtenez la connexion à la base de données
            OracleConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
               

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

                // Créez l'objet Command
                OracleCommand cmd = new OracleCommand();

                // Définissez Connection pour Command
                cmd.Connection = conn;

                // Command Text.
                cmd.CommandText = sql; 

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

                // Exécutez Command (utilisé pour 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. Appeler des procédures dans C#

Vous devez créer une procédure simple dans Oracle et l'appeler dans 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
            { 

                // Créez un objet Command en appelant la procédure Get_Employee_Info.
                OracleCommand cmd = new OracleCommand("Get_Employee_Info", conn);

                // Le type de Command est StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Ajoutez le paramètre @p_Emp_Id et définissez sa valeur = 100.
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value =100;

                // Ajoutez le paramètre @v_Emp_No dont le type est 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)); 

                // L'inscription du paramètre @v_Emp_No est 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;
 
                // Exécutez la procédure.
                cmd.ExecuteNonQuery();

                // Obtenez des valeurs délivrée.
                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();
        }
    }


}
L'exécution de l'exemple:
Getting Connection ...
hireDateObj type: Oracle.DataAccess.Types.OracleDate
Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 12-FEB-16

9. Appeler des fonctions dans C#

Vous devez créer une fonction simple dans Oracle et l'appeler dans 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
            {             
                // Créez un objet Command pour appeler la fonction Get_Emp_No.
                OracleCommand cmd = new OracleCommand("Get_Emp_No", conn);

                // Le type de Command est StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // ** Remarque: Avec Oracle, le paramètre de retour doit être ajouté d'abord.
                // Créez un paramètre dont le résultat de retour de la fonction est (Varchar2(50)).
                OracleParameter resultParam = new OracleParameter("@Result", OracleDbType.Varchar2, 50);    
            
                // ReturnValue
                resultParam.Direction = ParameterDirection.ReturnValue; 

                // Ajoutez aux paramètres.
                cmd.Parameters.Add(resultParam);

                // Ajoutez le paramètre @p_Emp_Id et définissez sa valeur = 100.
                cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value = 100;
                
                // Appelez la fonction.
                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();
        }
    }

}
L'exécution de l'exemple:
Getting Connection ...
resultParam.Value: Oracle.DataAccess.Types.OracleString
Emp No: E100

10. ExecuteScalar

OracleCommand.ExecuteScalar() est une méthode qui est utilisé pour exécuter des commandes SQL et renvoie la valeur de la première colonne de la première ligne dans des résultats de la commande SQL.
-- The following statement returns only one value.

Select count(*) from Employee;

-- Or

Select Max(e.Salary) From Employee e;
Exemple:
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;

                // La méthode ExecuteScalar renvoie la valeur de la première colonne de la première ligne.
                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();
        }
    }
}
L'exécution de l'exemple:
Emp Count: 14