Connect to Oracle with Dapper using C#
Download and activate dotConnect for Oracle
30-day free trial version
Download and install dotConnect for Oracle directly on your workstation, or install the Devart.Data.Oracle NuGet package.
No license key is required, and you can start exploring the product immediately.
Full version
After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the activation key.
To activate the connection in your application, include the license key in your connection string.
Select Oracle database objects
Using the built-in Data Explorer, first, configure the connection to Oracle and then select the objects you want to use in your project.
1. In Visual Studio, click Tools on the menu bar and select Connect to Database from the drop-down list.
2. In the Add Connection window, choose the Oracle data source, fill in the connection details for your Oracle database, and click OK to proceed.
3. In Server Explorer, select the tables and fields you want to use. In our tutorial, we will use the EMPLOYEE table.
Connect to Oracle and retrieve data
You can now create the application that connects to the Oracle database and displays data from the EMPLOYEE table using Dapper. In your Program.cs file, add the connection string to the Oracle database. It should be structured like this:
class Program {
static void Main(string[] args) {
// Define your Oracle connection string
string connectionString = "DataSource=127.0.0.1;Port=1521;SID=orcl;UserId=TestUser;Password=TestPassword;LicenseKey=**********;";
using(var connection = new OracleConnection(connectionString)) {
// Open the database connection
connection.Open();
// Define the SQL query to retrieve data from the EMPLOYEE table
string sql = "SELECT * FROM EMPLOYEE";
// Execute the query and map the results to a list of dynamic objects
var employees = connection.Query(sql).ToList();
// Display the retrieved data
foreach(var employee in employees) {
Console.WriteLine(employee);
}
}
}
}
Run the application
To build and run your application, press F5 or select Start from the menu in Visual Studio.
Mapping to strongly-typed objects
Mapping query results to strongly-typed objects improves code readability and maintainability. You can follow the example code below to map query results to strongly-typed objects using Dapper.
public class Employee {
public int Id {
get;
set;
}
public string Name {
get;
set;
}
public int DepartmentId {
get;
set;
}
}
using(var connection = new OracleConnection(connectionString)) {
connection.Open();
string sql = "SELECT * FROM EMPLOYEE";
var employees = connection.Query < Employee > (sql).ToList();
foreach(var employee in employees) {
Console.WriteLine($"{employee.Id}: {employee.Name} (Department ID: {employee.DepartmentId})");
}
}
As a result of mapping, the query results are transformed into strongly-typed objects, allowing you to work with them more effectively in your C# application.
Insert new records using Dapper
Let's examine how to insert 10 new rows into the EMPLOYEE table using Dapper in C#. This example assumes that your EMPLOYEE table has two columns: NAME and POSITION.
// Sample data to insert with new names and positions
var employees = new [] {
new {
NAME = "Karen White", POSITION = "Project Manager"
},
new {
NAME = "Liam Green", POSITION = "Software Engineer"
},
new {
NAME = "Mia Black", POSITION = "UX Designer"
},
new {
NAME = "Noah Blue", POSITION = "Data Scientist"
},
new {
NAME = "Olivia Red", POSITION = "QA Engineer"
},
new {
NAME = "Peter Yellow", POSITION = "Technical Support"
},
new {
NAME = "Quinn Pink", POSITION = "Recruiter"
},
new {
NAME = "Rachel Purple", POSITION = "Marketing Specialist"
},
new {
NAME = "Samuel Orange", POSITION = "Sales Representative"
},
new {
NAME = "Tina Grey", POSITION = "Business Analyst"
}
};
using(var connection = new OracleConnection(connectionString)) {
connection.Open();
// Define the SQL insert statement without the ID column
string insertSql = "INSERT INTO EMPLOYEE (NAME, POSITION) VALUES (:NAME, :POSITION)";
// Execute the insert operation for each employee
foreach(var employee in employees) {
connection.Execute(insertSql, employee);
}
Console.WriteLine("Data inserted successfully.");
// Define the SQL select statement to retrieve all rows
string selectSql = "SELECT * FROM EMPLOYEE";
// Execute the select query and map the results to a list of dynamic objects
var allEmployees = connection.Query(selectSql).ToList();
// Display the retrieved data
Console.WriteLine("\nAll rows in the EMPLOYEE table:");
foreach(var employee in allEmployees) {
Console.WriteLine($"ID: {employee.ID}, Name: {employee.NAME}, Position: {employee.POSITION}");
}
}
After running the program, you should see the following output in the console:
Delete Oracle data using Dapper
To delete rows from the EMPLOYEE table where the ID is between 71 and 80, you can use the following example with Dapper:
1. In the ActorController class, add the following method:
using(var connection = new OracleConnection(connectionString)) {
connection.Open();
// Define the SQL delete statement
string deleteSql = "DELETE FROM EMPLOYEE WHERE ID BETWEEN :StartId AND :EndId";
// Parameters for the delete statement
var parameters = new {
StartId = 71, EndId = 80
};
// Execute the delete operation
int rowsAffected = connection.Execute(deleteSql, parameters);
Console.WriteLine($"Rows deleted: {rowsAffected}");
// Define the SQL select statement to retrieve all rows
string selectSql = "SELECT * FROM EMPLOYEE";
// Execute the select query and map the results to a list of dynamic objects
var allEmployees = connection.Query(selectSql).ToList();
// Display the retrieved data
Console.WriteLine("\nAll rows in the EMPLOYEE table after deletion:");
foreach(var employee in allEmployees) {
Console.WriteLine($"ID: {employee.ID}, Name: {employee.NAME}, Position: {employee.POSITION}");
}
}
The retrieved data is then displayed in the console, showing the id, name, and position of each remaining employee.
