Developing .NET Applications for Oracle Database (On-Premises)

These tutorials show you how to connect .NET applications to Oracle Database (On-Premises) using Oracle Data Provider for .NET Core via the command line, with Visual Studio Code, and with Visual Studio. Also learn how to connect .NET Framework apps to Oracle Database (On-Premises) using Visual Studio and Oracle Data Provider for .NET. Visit Developing .NET Applications for Oracle Autonomous Database for a tutorial using Oracle Autonomous Database.


.NET Command Line
.NET with Visual Studio Code
.NET with Visual Studio
.NET Framework with Visual Studio

    .NET Command Line

    Open all Close all

    This tutorial shows you how use the command line to connect .NET applications to Oracle Databases using Oracle Data Provider for .NET (ODP.NET) Core. Follow the steps below:

  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, click the links below to install Oracle Database Express Edition (XE). (If you would like to use the Always Free Oracle Autonomous Database instead, see the Developing .NET Applications for Oracle Autonomous Database tutorial).

    More Resources:

  • 2. Install the .NET SDK

  • 3. Create the Database User (Optional)

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

    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): 
      dotnet new console
    • Add ODP.NET Core to the project: 
      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: 
      


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/FREEPDB1" for Oracle Database 23ai Free
        public static string db = "<localhost/FREEPDB1>";

        //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: 
      dotnet run
    • You should see a message that you connected to the database and a new user was created.

  • 4. Build and Run a .NET Application

      We are ready to setup a simple database schema, add data, and then use a .NET 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): 
      dotnet new console
    • Add ODP.NET Core to the project: 
      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: 
      


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/FREEPDB1" for Oracle Database 23ai Free
        public static string db = "<localhost/FREEPDB1>";

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

    .NET with Visual Studio Code

    Open all Close all

    This tutorial shows you how to connect .NET applications to Oracle Databases using Oracle Data Provider for .NET (ODP.NET) Core, Visual Studio Code, and the Oracle Developer Tools for VS Code extension. Follow the steps below:

  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, click the links below to install Oracle Database Express Edition (XE). (If you would like to use the Always Free Oracle Autonomous Database instead, see the Developing .NET Applications for Oracle Autonomous Database tutorial).

    More Resources:

  • 2. Install the .NET SDK

  • 3. Install Oracle Developer Tools For VS Code

    • Launch Visual Studio Code. (You can install Visual Studio Code if you do not already have it.)
    • Open the Extensions Marketplace by clicking the icon in the Activity Bar on the left side of Visual Studio Code or use the View: Show Extensions command (Ctrl+Shift+X).
    • Enter oracle into the Marketplace search text box and press return.
    • Locate the Oracle Developer Tools for VS Code extension and click it to open the extension's page.
    • Click the Install button to install the extension. During the installation you may be prompted to install a specific version of .NET runtime depending on what is already installed on your machine.
    • Close Visual Studio Code and reopen it.

  • 4. Create the Database User (Optional)

    We recommend using a new database user with the app we will create in this walkthrough. We will store a "To Do" table in the new user's schema. If you are not familiar with creating a user and assigning privileges, follow the steps below to use Visual Studio Code to log in as a database administrator and execute the SQL needed to create a new database user.

    Alternatively, you may skip to Step 5 and use an existing user schema if you do not wish to create a new user.

    • Click on the Database icon in the Activity Bar on the left side of Visual Studio Code to open Oracle Database Explorer.
    • Click the plus sign (+) to open the connection dialog.
    • Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select SYSDBA (Administrator)
      • User name: Enter SYS
      • Password: Enter the password for SYS.
      • Save password: Check this box if desired
    • Click the Create Connection button.
    • In Oracle Explorer, click on the connection node to expand it (labeled SYS.DBName).
    • Right click on the connection node and from the menu choose Open New SQL File.
    • Copy and paste the following SQL statements into the SQL file (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      CREATE USER DEMODOTNET IDENTIFIED BY PASSWORD

   DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE,
   CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER,
     CREATE TYPE, CREATE MATERIALIZED VIEW TO DEMODOTNET;
    • This SQL will create a user named "DEMODOTNET". You can modify this user name if you choose. Change "PASSWORD" to the password of your choice. If your database does not use a USERS tablespace, you can include the SQL statement SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; to see the list of available tablespaces. When ready, right click in the editor and choose Execute All.
    • View the results to make sure there were no errors.

  • 5. Connect to Oracle Database

    • Click on the Database icon in the Activity Bar on the left side of Visual Studio Code to open Oracle Database Explorer.
    • Click the plus sign (+) to open the connection dialog
    • Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select Default (Non-Administrator)
      • User name: Enter the username (eg DEMODOTNET)
      • Password: Enter the password
      • Save password: Check this box if desired
    • Click the Create Connection button.

  • 6. Execute SQL Statements

    • In Oracle Explorer, click on the connection node to expand it (labeled DEMODOTNET.DBName)
    • Right click on the connection node and from the menu choose Open New SQL File.
    • Copy and paste the following SQL statements into the SQL file (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      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));

INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 1', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 2', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 3', 1);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 4', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 5', 1);
COMMIT;

SELECT DESCRIPTION, DONE FROM TODOITEM;
    • Right click in the editor and choose Execute All.
    • View the results and check for errors.

  • 7. Build and Run a .NET Application

    • From the Visual Studio Code menu choose View->Terminal to open the Terminal window. At the command prompt in the terminal, create a new directory for your application and change into that directory. Follow the steps below and continue entering commands into the terminal.
    • Use the dotnet executable to create a new project: 
      dotnet new console
    • Add ODP.NET Core to the project: 
      dotnet add package Oracle.ManagedDataAccess.Core
    • From the Visual Studio Code menu choose File->Open Folder and select the directory you created above.
    • The Visual Studio Code Explorer pane will open. Double click Program.cs to open it in the editor.
    • Replace the contents of the Program.cs file with the following code: 
      



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/FREEPDB1" for Oracle Database 23ai Free
        public static string db = "<localhost/FREEPDB1>";

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

                        //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.");
                        }

                      
                        reader.Dispose();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }
    }
}
    • Modify Program.cs to include your DB connection information:
      • User Id: Enter DOTNETDEMO (If you wish to use another user, you can enter it instead.)
      • Password: Enter the user's password.
      • Data Source: Enter the Easy Connect connect string (eg "yourhost:yourport/yourdbservice"), the net service name, or connect descriptor to connect to the pluggable database. If Oracle Database XE is on your local machine, use the Easy Connect connect string "localhost/XEPDB1" as the Data Source.
    • From the Visual Studio Code menu choose File-Save to save the changes to Program.cs.
    • From the terminal, run the app: 
      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 terminal. Congratulations! You have successfully queried the Oracle Database.

    .NET with Visual Studio

    Open all Close all

    This tutorial shows you how to connect .NET applications to Oracle Database (on-premises) using Oracle Data Provider for .NET (ODP.NET) Core, Visual Studio 2019, and the Oracle Developer Tools for Visual Studio extension. Follow the steps below.

    (Note: If you have already completed the tutorial for .NET Framework with Visual Studio, you can skip to the last step of this tutorial. The other steps are identical.)

  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, click the links below to install Oracle Database Express Edition (XE). (If you would like to use the Always Free Oracle Autonomous Database instead, see the Developing .NET Applications for Oracle Autonomous Database tutorial).

    More Resources:

  • 2. Install Oracle Developer Tools For Visual Studio

    • Install Visual Studio 2019 if you do not already have it.
    • Download Oracle Developer Tools for Visual Studio 19.3.2.
    • Open the downloaded zip file and extract the ODTforVS2019_193200.vsix file to a temporary location. Double click the vsix file to run the installer.
    • Once the installation is complete, launch Visual Studio 2019.
    • A dialog may appear stating that Oracle Developer Tools for Visual Studio needs to perform additional configuration. If it does, close Visual Studio then follow the prompts in the dialog. Once the dialog finishes, launch Visual Studio 2019 again.

  • 3. Create the Database User (Optional)

    We recommend using a new database user with the app we will create in this walkthrough. We will store a "To Do" table in the new user's schema. If you are not familiar with creating a user and assigning privileges, follow the steps below to use Visual Studio to log in as a database administrator and execute the SQL needed to create a new database user.

    Alternatively, you may skip to the next step (Step 4: Connect to Oracle Database) and use an existing user schema if you do not wish to create a new user.

    • Right click on the Data Connections node in Server Explorer and select Add Connection from the menu. (If Server Explorer is not visible in Visual Studio, select View->Server Explorer from the Visual Studio menu.
    • If the Data Source field is not already set to Oracle Database (ODP.NET, Managed Driver), click the Change button. Otherwise, skip the next bullet point.
    • In the Change Data Source dialog, in the Data Source: box, select Oracle Database and in the Data Provider drop down select ODP.NET, Managed Driver. (If these options are not available then Oracle Developer Tools for Visual Studio is not installed properly).
    • The Add Connection will appear. Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select SYSDBA (Administrator)
      • User name: Enter SYS
      • Password: Enter the password for SYS.
      • Save password: Check this box if desired
    • Click the OK button.
    • In Server Explorer, click on the connection node to expand it (labeled SYS.DBName). Then, right click on the node and from the menu select Query Window
    • Copy and paste the following SQL statements into the Query Window (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      CREATE USER DEMODOTNET IDENTIFIED BY PASSWORD

   DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE,
   CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER,
     CREATE TYPE, CREATE MATERIALIZED VIEW TO DEMODOTNET;
    • This SQL will create a user named "DEMODOTNET". You can modify this user name if you choose. Change "PASSWORD" to the password of your choice. If your database does not use a USERS tablespace, you can include the SQL statement SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; to see the list of available tablespaces. When ready, select both of SQL statements then right click in the editor and choose Execute Query.
    • View the results to make sure there were no errors and that both SQL statements were executed.

  • 4. Connect to Oracle Database

    • Right click on the Data Connections node in Server Explorer and select Add Connection from the menu. (If Server Explorer is not visible in Visual Studio, select View->Server Explorer from the Visual Studio menu.
    • In the Add Connection dialog if the Data Source field is not already set to Oracle Database (ODP.NET, Managed Driver), click the Change button. Otherwise, skip the next bullet point.
    • In the Change Data Source dialog, in the Data Source: box, select Oracle Database and in the Data Provider drop down select ODP.NET, Managed Driver. (If these options are not available then Oracle Developer Tools for Visual Studio is not installed properly).
    • The Add Connection will appear. Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select Non-Administrator
      • User name: Enter the user name (eg. DEMODOTNET)
      • Password: Enter the password for the user.
      • Save password: Check this box if desired
    • Click the OK button.

  • 5. Execute SQL Statements

    • In Server Explorer, click on the connection node to expand it (labeled DEMODOTNET.DBName). Then, right click on the node and from the menu select Query Window
    • Copy and paste the following SQL statements into Query Window (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      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));

INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 1', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 2', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 3', 1);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 4', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 5', 1);
COMMIT;

SELECT DESCRIPTION, DONE FROM TODOITEM;
    • Select all of the SQL statements then right click in the editor and choose Execute Query.
    • View the results and check for errors, making sure that all SQL statements were executed.

  • 6. Build and Run a .NET (Cross Platform) Application

      In this step you will build a .NET application that can run on Windows, Linux, and macOS.

    • From the Visual Studio menu choose File->New->Project. In the Create a new project dialog, enter console into the Search for templates field. Select the Console Application project type listed for C# and .NET. Then press the Next button.
    • In the Configure your new project dialog enter ODP.NET_Core_Get_Started for the Project Name and then press the Next button.
    • In the Additional Information dialog, enter a .NET version and then press the Create button.
    • After the new project opens, view Solution Explorer, right click on the Dependencies node and select Manage NuGet Packages (If you don't see Solution Explorer, go to the Visual Studio menu and select View->Solution Explorer)
    • In Nuget Package Manager, click Browse, then in the Search box, enter oracle.
    • Select the latest Oracle.ManagedDataAccess.Core version, and then click the Install button. When the Preview Changes dialog opens click OK.
    • Replace the contents of the Program.cs file with the following code: 
      



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/FREEPDB1" for Oracle Database 23ai Free
        public static string db = "<localhost/FREEPDB1>";

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

                        //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.");
                        }

                      
                        reader.Dispose();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }
    }
}
    • Modify Program.cs to include your DB connection information:
      • User Id: Enter DOTNETDEMO (If you wish to use another user, you can enter it instead.)
      • Password: Enter the user's password.
      • Data Source: Enter the Easy Connect connect string (eg "yourhost:yourport/yourdbservice"), the net service name, or connect descriptor to connect to the pluggable database. If Oracle Database XE is on your local machine, use the Easy Connect connect string "localhost/XEPDB1" as the Data Source.
      • From the Visual Studio menu choose Debug->Start Debugging (or press F5) to save, build and then run the application.
      • You should see a message that you connected to Oracle Database and customer information returned from the database. Congratulations! You have successfully queried the Oracle Database.

    .NET Framework with Visual Studio

    Open all Close all

    This tutorial shows you how to connect .NET Framework applications to Oracle Database (on-premises) using Oracle Data Provider for .NET (ODP.NET), Visual Studio 2019, and the Oracle Developer Tools for Visual Studio extension. Follow the steps below.

    (Note: If you have already completed the tutorial for .NET with Visual Studio, you can skip to the last step of this tutorial. The other steps are identical.)

  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, click the links below to install Oracle Database Express Edition (XE). (If you would like to use the Always Free Oracle Autonomous Database instead, see the Developing .NET Applications for Oracle Autonomous Database tutorial).

    More Resources:

  • 2. Install Oracle Developer Tools For Visual Studio

    • Install Visual Studio 2019 if you do not already have it.
    • Download Oracle Developer Tools for Visual Studio 19.3.2.
    • Open the downloaded zip file and extract the ODTforVS2019_193200.vsix file to a temporary location. Double click the vsix file to run the installer.
    • Once the installation is complete, launch Visual Studio 2019.
    • A dialog may appear stating that Oracle Developer Tools for Visual Studio needs to perform additional configuration. If it does, close Visual Studio then follow the prompts in the dialog. Once the dialog finishes, launch Visual Studio 2019 again.

  • 3. Create the Database User (Optional)

    We recommend using a new database user with the app we will create in this walkthrough. We will store a "To Do" table in the new user's schema. If you are not familiar with creating a user and assigning privileges, follow the steps below to use Visual Studio to log in as a database administrator and execute the SQL needed to create a new database user.

    Alternatively, you may skip to the next step (Step 4: Connect to Oracle Database) and use an existing user schema if you do not wish to create a new user.

    • Right click on the Data Connections node in Server Explorer and select Add Connection from the menu. (If Server Explorer is not visible in Visual Studio, select View->Server Explorer from the Visual Studio menu.
    • If the Data Source field is not already set to Oracle Database (ODP.NET, Managed Driver), click the Change button. Otherwise, skip the next bullet point.
    • In the Change Data Source dialog, in the Data Source: box, select Oracle Database and in the Data Provider drop down select ODP.NET, Managed Driver. (If these options are not available then Oracle Developer Tools for Visual Studio is not installed properly).
    • The Add Connection will appear. Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select SYSDBA (Administrator)
      • User name: Enter SYS
      • Password: Enter the password for SYS.
      • Save password: Check this box if desired
    • Click the OK button.
    • In Server Explorer, click on the connection node to expand it (labeled SYS.DBName). Then, right click on the node and from the menu select Query Window
    • Copy and paste the following SQL statements into the Query Window (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      CREATE USER DEMODOTNET IDENTIFIED BY PASSWORD

   DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE,
   CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER,
     CREATE TYPE, CREATE MATERIALIZED VIEW TO DEMODOTNET;
    • This SQL will create a user named "DEMODOTNET". You can modify this user name if you choose. Change "PASSWORD" to the password of your choice. If your database does not use a USERS tablespace, you can include the SQL statement SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; to see the list of available tablespaces. When ready, select both of SQL statements then right click in the editor and choose Execute Query.
    • View the results to make sure there were no errors and that both SQL statements were executed.

  • 4. Connect to Oracle Database

    • Right click on the Data Connections node in Server Explorer and select Add Connection from the menu. (If Server Explorer is not visible in Visual Studio, select View->Server Explorer from the Visual Studio menu.
    • In the Add Connection dialog if the Data Source field is not already set to Oracle Database (ODP.NET, Managed Driver), click the Change button. Otherwise, skip the next bullet point.
    • In the Change Data Source dialog, in the Data Source: box, select Oracle Database and in the Data Provider drop down select ODP.NET, Managed Driver. (If these options are not available then Oracle Developer Tools for Visual Studio is not installed properly).
    • The Add Connection will appear. Fill in the connection dialog:
      • Connection Type: Basic
      • Database host name: Enter the hostname where your database is located (eg. localhost)
      • Port number : XE Database uses port 1521 by default
      • Service name : Enter the service name (eg. XEPDB1 if using XE)
      • Role: select Non-Administrator
      • User name: Enter the user name (eg. DEMODOTNET)
      • Password: Enter the password for the user.
      • Save password: Check this box if desired
    • Click the OK button.

  • 5. Execute SQL Statements

    • In Server Explorer, click on the connection node to expand it (labeled DEMODOTNET.DBName). Then, right click on the node and from the menu select Query Window
    • Copy and paste the following SQL statements into Query Window (click the copy button in the lower right corner of the code box in the examples below to copy and paste the contents): 
      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));

INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 1', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 2', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 3', 1);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 4', 0);
INSERT INTO TODOITEM (DESCRIPTION, DONE) VALUES('Task 5', 1);
COMMIT;

SELECT DESCRIPTION, DONE FROM TODOITEM;
    • Select all of the SQL statements then right click in the editor and choose Execute Query.
    • View the results and check for errors, making sure that all SQL statements were executed.

  • 6. Build and Run a .NET Framework Application

      In this step you will build a .NET Framework application.

    • From the Visual Studio menu choose File->New->Project. In the Create a new project dialog, enter console into the Search for templates field. Select the Console Application project type listed for C# and .NET Framework. Then press the Next button.
    • In the Configure your new project dialog enter ODP.NET_Framework_Get_Started for the Project Name and then press the Next button.
    • In the Framework dropdown choose .NET Framework 4.8. (If you don't have that version of .NET available, select another version). Then press the Create button.
    • After the new project opens, view Solution Explorer, right click on the References node and select Manage NuGet Packages (If you don't see Solution Explorer, go to the Visual Studio menu and select View->Solution Explorer)
    • In Nuget Package Manager, click Browse, then in the Search box, enter oracle.
    • Select Oracle.ManagedDataAccess version 19.11.0 and then click the Install button.
    • Replace the contents of the Program.cs file with the following code: 
      



using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace ODP.NET_Framework_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/FREEPDB1" for Oracle Database 23ai Free
        public static string db = "<localhost/FREEPDB1>";

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

                        //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.");
                        }

                      
                        reader.Dispose();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }
    }
}
    • Modify Program.cs to include your DB connection information:
      • User Id: Enter DOTNETDEMO (If you wish to use another user, you can enter it instead.)
      • Password: Enter the user's password.
      • Data Source: Enter the Easy Connect connect string (eg "yourhost:yourport/yourdbservice"), the net service name, or connect descriptor to connect to the pluggable database. If Oracle Database XE is on your local machine, use the Easy Connect connect string "localhost/XEPDB1" as the Data Source.
      • From the Visual Studio menu choose Debug->Start Without Debugging (or press Control-F5) to save, build and then run the application.
      • You should see a message that you connected to Oracle Database and customer information returned from the database. Congratulations! You have successfully queried the Oracle Database.