• Download and install .NET Core SDK 3.1 or higher if you do not already have it installed on your machine.

We recommend using a new database user for this app. We will store the To Do table in the new user's schema. If you are not familiar with creating a user and assigning privileges, run the Create User ODP.NET Core app below. This app creates a new database user on behalf of a database administrator from the command line. This tool is intended for on-premises databases or cloud databases with TCP connections. It requires .NET Core 3.1 or higher.

Alternatively, you may run the sample code in Step 4 from an existing user schema if you do not wish to create a new user.

• From a command line, create a new directory for the Create User app and change into that directory.
• Use the dotnet executable to create a new project (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents):
  Copy

  dotnet new console
    

• Add ODP.NET Core to the project:
  Copy

  dotnet add package Oracle.ManagedDataAccess.Core
    

• Open Program.cs in a text editor.
• Replace the contents of the Program.cs file with the following code:
  Copy

  
  
  using System;
  using Oracle.ManagedDataAccess.Client;
  
  namespace Create_User
  {
      class Program
      {
          //Set the net service name, Easy Connect, or connect descriptor of the pluggable DB, 
          // such as "localhost/XEPDB1" for 18c XE or higher
          public static string db = "<localhost/XEPDB1>";
  
          //Provide the DBA's user id, password, and role to create the demo user
          //If admin has no role, then set to empty string
          public static string sysUser = "<SYS>";
          public static string sysPwd = "<Admin PASSWORD>";
          public static string role = "<SYSDBA>";
  
          //Set the demo user id, such as DEMODOTNET and password
          public static string user = "<DEMODOTNET>";
          public static string pwd = "<PASSWORD>";
  
          static void Main()
          {
              //Create connection string. Check whether DBA Privilege is required.
              string conStringDBA;
              if (role == "")
                  conStringDBA = "User Id=" + sysUser + ";Password=" + sysPwd + ";Data Source=" + db + ";";
              else
                  conStringDBA = "User Id=" + sysUser + ";Password=" + sysPwd + ";DBA Privilege=" + role + ";Data Source=" + db + ";";
  
              using (OracleConnection con = new OracleConnection(conStringDBA))
              {
                  using (OracleCommand cmd = con.CreateCommand())
                  {
                      try
                      {
                          con.Open();
                          Console.WriteLine("Successfully connected to Oracle Database");
                          Console.WriteLine();
  
                          //Modify the anonymous PL/SQL GRANT command if you wish to modify the privileges granted
                          cmd.CommandText = "BEGIN " +
                              "EXECUTE IMMEDIATE ('CREATE USER " + user + " IDENTIFIED BY " + pwd + 
                                " DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS'); " +
                              "EXECUTE IMMEDIATE ('GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, " + 
                                 "CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE MATERIALIZED VIEW TO " + user + "'); " +
                              "END;";
                          cmd.ExecuteNonQuery();
  
                          Console.WriteLine(user + " user created");
                          Console.WriteLine();
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine(ex.Message);
                      }
                  }
              }
          }
      }
  }
  
  

• Modify Program.cs to include your DB connection information:
  • Data Source: Enter the net service name, Easy Connect, or connect descriptor to connect to the pluggable database. If Oracle Database XE is on your local machine, use the Easy Connect "localhost/XEPDB1 as the Data Source.
  • Administrator User Id: Enter the administrator username with privileges to create an user and assign privileges, such as SYSDBA.
  • Administrator Password: Enter the administrator's password.
  • Administrator Role: Enter the administrator's role, such as SYSDBA. Leave blank if there is no role to assign.
  • User Id: Enter the username that will store the To Do table, such as DEMODOTNET.
  • Password: Enter the user's password.
  Note: The new user will be given access to the USERS tablespace. Modify the CREATE USER command if you wish to use a different tablespace.
• Save changes to Program.cs.
• Run the app:
  Copy

  dotnet run
    

• You should see a message that you connected to the database and a new user was created.

We are ready to setup a simple database schema, add data, and then use a .NET Core app to select results from a table. We will create a basic To Do list app. The To Do items will be stored in a database table that will track a task list and task completion status.

• From a command line, create a new directory for your application and change into that directory.
• Use the dotnet executable to create a new project (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents):
  Copy

  dotnet new console
    

• Add ODP.NET Core to the project:
  Copy

  dotnet add package Oracle.ManagedDataAccess.Core
    

• Open Program.cs in a text editor.
• Replace the contents of the Program.cs file with the following code:
  Copy

  
  
  using System;
  using System.Data;
  using Oracle.ManagedDataAccess.Client;
  
  namespace ODP.NET_Core_Get_Started
  {
      class GettingStarted
      {
          //Prerequisite: This app assumes the user has already been created with the
          // necessary privileges
          //Set the demo user id, such as DEMODOTNET and password
          public static string user = "<DEMODOTNET>";
          public static string pwd = "<PASSWORD>";
  
          //Set the net service name, Easy Connect, or connect descriptor of the pluggable DB, 
          // such as "localhost/XEPDB1" for 18c XE or higher
          public static string db = "<localhost/XEPDB1>";
  
          static void Main()
          {
              string conStringUser = "User Id=" + user + ";Password=" + pwd + ";Data Source=" + db + ";";
  
              using (OracleConnection con = new OracleConnection(conStringUser))
              {
                  using (OracleCommand cmd = con.CreateCommand())
                  {
                      try
                      {
                          con.Open();
                          Console.WriteLine("Successfully connected to Oracle Database as " + user);
                          Console.WriteLine();
  
                          //Create the sample table. Checks if table already exists
                          cmd.CommandText = "DECLARE tableExists INTEGER; " +
                              "BEGIN " +
                              "SELECT count(*) into tableExists FROM user_tables where table_name = 'TODOITEM';" +
                              "IF (tableExists = 0) THEN " +
                              "EXECUTE IMMEDIATE ('CREATE TABLE todoitem (id NUMBER GENERATED ALWAYS AS IDENTITY, " + 
                                "description VARCHAR2(4000), creation_ts TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, " + 
                                "done NUMBER(1, 0), PRIMARY KEY(id))'); " +
                              "END IF; END;";
                          cmd.ExecuteNonQuery();
  
                          //Insert sample data
                          String[] description = { "Task 1", "Task 2", "Task 3", "Task 4", "Task 5" };
                          int[] done = new int[5] { 0, 0, 1, 0, 1 };
  
                          cmd.CommandText = "INSERT INTO todoitem (description, done) VALUES(:descrip, :done)";
                          cmd.ArrayBindCount = 5;
                          cmd.BindByName = true;
  
                          OracleParameter descripParam = new OracleParameter("descrip", OracleDbType.Varchar2);
                          descripParam.Direction = ParameterDirection.Input;
                          descripParam.Value = description;
                          cmd.Parameters.Add(descripParam);
  
                          OracleParameter doneParam = new OracleParameter("done", OracleDbType.Int16);
                          doneParam.Direction = ParameterDirection.Input;
                          doneParam.Value = done;
                          cmd.Parameters.Add(doneParam);
  
                          cmd.ExecuteNonQuery();
                          Console.WriteLine("{0} Rows Inserted", cmd.ArrayBindCount);
                          Console.WriteLine();
  
                          //Retrieve sample data
                          cmd.CommandText = "SELECT description, done FROM todoitem";
                          OracleDataReader reader = cmd.ExecuteReader();
                          while (reader.Read())
                          {
                              if (reader.GetBoolean(1))
                                  Console.WriteLine(reader.GetString(0) + " is done.");
                              else
                                  Console.WriteLine(reader.GetString(0) + " is NOT done.");
                          }
  
                          descripParam.Dispose();
                          doneParam.Dispose();
                          reader.Dispose();
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine(ex.Message);
                      }
                  }
              }
          }
      }
  }
    
  

• Modify Program.cs to include your DB connection information:
  • User Id: Enter the username that will store the To Do table, such as DEMODOTNET.
  • Password: Enter the user's password.
  • Data Source: Enter the net service name, Easy Connect, or connect descriptor to connect to the pluggable database. If Oracle Database XE is on your local machine, use the Easy Connect "localhost/XEPDB1 as the Data Source.
• Save changes to Program.cs.
• Run the app:
  Copy

  dotnet run
    

• You should see a message that you connected to the database, five rows were inserted, and the task list with each task's completion status returned to the console. Congratulations! You have successfully queried the Oracle Database.