Oracle CRUD Operations in .NET MAUI
Why choose dotConnect for Oracle?
dotConnect for Oracle is an ideal data provider for all Oracle-related operations. It offers features like on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.
Requirements
- Visual Studio 2022: The IDE of choice. Download and install it if you don't have it. We're going to use the community version, so you can get it as well.
- .NET MAUI: A .NET workload installable with the .NET CLI tool. It is available within the Visual Studio 2022 installer.
- dotConnect for Oracle: A feature-rich ADO.NET provider with Entity Framework, NHibernate, and LinqConnect support.
Download and activate dotConnect for Oracle
For the trial version, simply download and install dotConnect for Oracle on your machine. No license key is required and you can start exploring the product immediately.
Purchase and access the full Version
After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses, including dotConnect for Oracle purchase.
Click Details next to your dotConnect for Oracle license. Here, you'll find the license details and the Activation Key.
Download the full version
To access the list of available product versions, click Download Product Versions. Select the most recent version and download the installer.
Execute the downloaded file and follow the on-screen instructions to install dotConnect for Oracle on your machine. This process will install the software and activate all features associated with your license key.
Create a .NET MAUI app project
In Visual Studio, click Create New Project. In the Search Template field, search for .NET MAUI App and click it.
Name your project, select .NET 8 as the target framework, and leave all the other options as the default values. Finally,
click Create.
Install packages
We will install dotConnect for Oracle to our project via the NuGet Package Manager in Visual Studio. In the taskbar, navigate to Tools > NuGet Package Manager > Manage NuGet Packages for Solution.
The NuGet Package Manager page opens. Click Browse and search for Devart.Data.Oracle. Then, select your project and
click Install.
When you run the application, the scaffolded application will appear.
Create a MAUI test page
Next, create a new page where we'll do most of our work. Right-click the OracleMAUI solution and select Add New Item. Then select the .NET MAUI ContentPage(XAML) template, name this page OracleDemo.xaml, and click Add. Now, from the solution explorer, click the MainPage.xaml file and modify it to look as follows.
<ScrollView>
<VerticalStackLayout
Padding="30,0"
Spacing="25">
<Image
Source="dotnet_bot.png"
HeightRequest="185"
Aspect="AspectFit"
SemanticProperties.Description="dot net bot in a race car number eight" />
<Label
Text="Hello, World!"
Style="{StaticResource Headline}"
SemanticProperties.HeadingLevel="Level1" />
<Label
Text="Welcome to
.NET Multi-platform App UI"
Style="{StaticResource SubHeadline}"
SemanticProperties.HeadingLevel="Level2"
SemanticProperties.Description="Welcome to dot net Multi platform App U I" />
<Button
x:Name="CounterBtn"
Text="Click me"
SemanticProperties.Hint="Counts the number of times you click"
Clicked="OnCounterClicked"
HorizontalOptions="Fill" />
<Button Text="Go to Actor Page"
Clicked="OnNavigateButtonClicked"
VerticalOptions="Center"
HorizontalOptions="Center" />
</VerticalStackLayout>
</ScrollView>
We added a new button called Go to Actor Page to navigate to the created page. The final step here is to open MainPage.xaml.cs and add the OnNavigateButtonClicked method.
Right-click MainPage.xaml from the solution explorer and select View Code. Add this piece of code to the MainPage class.
private async void OnNavigateButtonClicked(object sender, EventArgs e)
{
await Navigation.PushAsync(new OracleDemo());
}
So, your MainPage.xaml.cs should look like this.
When you run the application and click Go to Actor page, you should see the page we just created.
Create a connection to an Oracle database
To add the DatabaseConnectionClass class, paste the following code.
public static class DatabaseConnectionClass
{
public static string databasePath = "" +
"Direct=True;" +
"Host=127.0.0.1;" +
"ServiceName=XE;" +
"UserID=TestUser;" +
"Password=TestPassword";
public static void CreateActorTable()
{
OracleConnection conn = new OracleConnection($"{databasePath};LicenseKey={LicenseKey.yourLicensekey};");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = @"
CREATE TABLE actor (
actor_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
last_update TIMESTAMP DEFAULT SYSTIMESTAMP
)";
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
}
Thus, we added a method that creates the actor table. In the connection strings, we specified the location of the database and the dotConnect for Oracle license key defined in another class. Next, in OracleDemo.xaml.cs, add the following code to define the method that will be called when the page opens.
public partial class OracleDemo : ContentPage
{
public OracleDemo()
{
InitializeComponent();
DatabaseConnectionClass.CreateActorTable();
}
}
To call the method for creating the table, run the application and click Go to Actor page. Since we created the table, you can comment out the CreateActorTable method.
INSERT Oracle data
Let's add some data from the application to the table. For this, create a class based on the created table. Right-click your solution and paste this code to include the Actor.cs class.
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime LastUpdate { get; set; }
}
Next, in DatabaseConnectionClass, add a new method.
public static void AddActor(Actor actor)
{
OracleConnection conn = new OracleConnection($"{databasePath};LicenseKey={LicenseKey.yourLicensekey};");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "INSERT INTO actor (first_name, last_name, last_update) VALUES (:first_name, :last_name, :last_update)";
cmd.Parameters.Add(new OracleParameter("first_name", actor.FirstName));
cmd.Parameters.Add(new OracleParameter("last_name", actor.LastName));
cmd.Parameters.Add(new OracleParameter("last_update", actor.LastUpdate));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
To configure the application's view, modify OracleDemo.xaml so it looks like this.
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="OracleMAUI.OracleDemo"
Title="OracleDemo">
<StackLayout>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
Then, in the OracleDemo.xaml.cs code-behind file, add these two methods within the OracleDemo class.
private void OnShowInsertSectionClicked(object sender, EventArgs e)
{
InsertFirstNameEntry.Text = string.Empty;
InsertSecondNameEntry.Text = string.Empty;
// Show the insert section and hide the Insert actor button
InsertSection.IsVisible = true;
InsertActor.IsVisible = false;
}
private void OnInsertButtonClicked(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(InsertFirstNameEntry.Text) ||
string.IsNullOrWhiteSpace(InsertSecondNameEntry.Text))
{
// Show error message
InsertErrorLabel.Text = "Both fields are required.";
InsertErrorLabel.IsVisible = true;
return;
}
// Create a new actor object with the inserted values
var newActor = new Actor
{
FirstName = InsertFirstNameEntry.Text,
LastName = InsertSecondNameEntry.Text,
LastUpdate = DateTime.Now
};
// Add the new actor to the database
DatabaseConnectionClass.AddActor(newActor);
// Refresh the CollectionView
BindingContext = null;
BindingContext = this;
// Hide the insert section
InsertSection.IsVisible = false;
}
Now, run the code and click Go to Actor page.
Click Insert New Actor, enter the actor's first and last names, and click Insert.
Read Oracle data
As we inserted the record into the table, we need a way to view what was added. In DataBaseConnectionClass, add this new method to get a list of all actors.
public static ListGetActors() { List actors = new List (); OracleConnection conn = new OracleConnection($"{databasePath};LicenseKey={LicenseKey.yourLicensekey};"); OracleCommand cmd = new(); cmd.CommandText = "SELECT * FROM actor;"; cmd.Connection = conn; conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Actor actor = new Actor { ActorId = Convert.ToInt32(reader["actor_id"]), FirstName = reader["first_name"].ToString(), LastName = reader["last_name"].ToString(), LastUpdate = Convert.ToDateTime(reader["last_update"]) }; actors.Add(actor); } return actors; }
Edit OracleDemo.xaml to make it look like this.
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="OracleMAUI.OracleDemo"
Title="OracleDemo">
<StackLayout>
<CollectionView ItemsSource="{Binding Actors}">
<CollectionView.ItemTemplate>
<DataTemplate>
<Grid Padding="2" RowDefinitions="Auto">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="50" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
<Label Text="{Binding ActorId}"
Grid.Column="0"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding FirstName}"
Grid.Column="1"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastName}"
Grid.Column="2"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastUpdate}"
Grid.Column="3"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
</Grid>
</DataTemplate>
</CollectionView.ItemTemplate>
</CollectionView>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
Then, in the OracleDemo.xaml.cs file, add a new property for a list of actors. Initialize it in the class constructor and set the binding to this property.
public partial class OracleDemo : ContentPage
{
public List Actors { get; set; }
public OracleDemo()
{
InitializeComponent();
//DatabaseConnectionClass.CreateActorTable();
Actors = DatabaseConnectionClass.GetActors();
// Set the BindingContext
BindingContext = this;
}
.....
}
To check the data, run the application and navigate to OracleDemo.
UPDATE Oracle data
Let's look at how to change Seth's last name. This example will likely be useful for your application. In DataBaseConnectionClass, create a new method called UpdateActor.
public static void UpdateActor(Actor actor)
{
OracleConnection conn = new OracleConnection($"{databasePath};LicenseKey={LicenseKey.yourLicensekey};");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "UPDATE actor SET first_name = :first_name, last_name = :last_name, last_update = :last_update WHERE actor_id = :actor_id";
cmd.Parameters.Add(new OracleParameter("first_name", actor.FirstName));
cmd.Parameters.Add(new OracleParameter("last_name", actor.LastName));
cmd.Parameters.Add(new OracleParameter("last_update", actor.LastUpdate));
cmd.Parameters.Add(new OracleParameter("actor_id", actor.ActorId));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
Next, in the OracleDemo.xaml file, replace the existing code with the following to add the Update button and a section for inserting the updated values.
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://schemas.microsoft.com/dotnet/2021/maui"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="OracleMAUI.OracleDemo"
Title="OracleDemo">
<StackLayout>
<CollectionView ItemsSource="{Binding Actors}">
<CollectionView.ItemTemplate>
<DataTemplate>
<Grid Padding="2" RowDefinitions="Auto">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="50" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="100" />
<ColumnDefinition Width="Auto" />
</Grid.ColumnDefinitions>
<Label Text="{Binding ActorId}"
Grid.Column="0"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding FirstName}"
Grid.Column="1"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastName}"
Grid.Column="2"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Label Text="{Binding LastUpdate}"
Grid.Column="3"
VerticalOptions="Center"
HorizontalOptions="Center"
Padding="3"
Margin="0.5" />
<Button Text="Update"
Grid.Column="4"
VerticalOptions="Center"
HorizontalOptions="Center"
Clicked="OnUpdateButtonClicked"
CommandParameter="{Binding ActorId}" />
</Grid>
</DataTemplate>
</CollectionView.ItemTemplate>
</CollectionView>
<StackLayout x:Name="UpdateSection" IsVisible="False" Padding="10">
<Label Text="Update First Name:" />
<Entry x:Name="UpdateFirstNameEntry" Placeholder="Enter new first name" />
<Label Text="Update Second Name:" />
<Entry x:Name="UpdateSecondNameEntry" Placeholder="Enter new Last name" />
<Label x:Name="UpdateErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Go" Clicked="OnGoButtonClicked" />
</StackLayout>
<StackLayout x:Name="InsertSection" IsVisible="False" Padding="10">
<Label Text="First Name:" />
<Entry x:Name="InsertFirstNameEntry" Placeholder="Enter first name" />
<Label Text="Second Name:" />
<Entry x:Name="InsertSecondNameEntry" Placeholder="Enter second name" />
<Label x:Name="InsertErrorLabel" TextColor="Red" IsVisible="False" />
<Button Text="Insert" Clicked="OnInsertButtonClicked" WidthRequest="80" />
</StackLayout>
<Button x:Name="InsertActor" Text="Insert New Actor" Clicked="OnShowInsertSectionClicked" WidthRequest="150" />
</StackLayout>
</ContentPage>
In OracleDemo.xaml.cs, add a new property.
private Actor? _selectedActor;
We created in the XAML file two methods: OnUpdateButtonClicked and OnGoButtonClicked.
private void OnUpdateButtonClicked(object sender, EventArgs e)
{
var button = sender as Button;
var actorId = (int)button.CommandParameter;
// Find the actor in the list by ID
_selectedActor = Actors.FirstOrDefault(p => p.ActorId == actorId);
if (_selectedActor != null)
{
// Populate Entry fields with existing values
UpdateFirstNameEntry.Text = _selectedActor.FirstName;
UpdateSecondNameEntry.Text = _selectedActor.LastName;
// Show the update section
UpdateSection.IsVisible = true;
}
}
private void OnGoButtonClicked(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(UpdateFirstNameEntry.Text) ||
string.IsNullOrWhiteSpace(UpdateSecondNameEntry.Text))
{
// Show error message
UpdateErrorLabel.Text = "Both fields are required.";
UpdateErrorLabel.IsVisible = true;
return;
}
if (_selectedActor != null)
{
// Create a new actor object with the updated values
var updatedActor = new Actor
{
ActorId = _selectedActor.ActorId,
FirstName = UpdateFirstNameEntry.Text,
LastName = UpdateSecondNameEntry.Text,
LastUpdate = DateTime.Now
};
// Update the actor in the database
DatabaseConnectionClass.UpdateActor(updatedActor);
// Hide the update section
UpdateSection.IsVisible = false;
}
}
When you run the code, you'll see a new Update button.
Clicking the button opens an insert modal where you can input new values.
To update a record, click Go.
DELETE Oracle data
Let's try deleting an actor from the database. For reference, we added a new actor, Johnny Blaze, to be removed.
In DataBaseConnectionClass, add the DeleteActor method.
public static void DeleteActor(int actorId)
{
OracleConnection conn = new OracleConnection($"{databasePath};LicenseKey={LicenseKey.yourLicensekey};");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "DELETE FROM actor WHERE actor_id = :actor_id";
cmd.Parameters.Add(new OracleParameter("actor_id", actorId));
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"An error occured; {ex.Message}");
}
}
Then, in OracleDemo.xaml, add a new button under <Button Text="Update".
<Button Text="Delete"
Grid.Column="5"
VerticalOptions="Center"
HorizontalOptions="Center"
Clicked="OnDeleteButtonClicked"
CommandParameter="{Binding ActorId}" />
After that, add a new column definition in <Grid.ColumnDefinitions>.
<ColumnDefinition Width="Auto" />
Now, in OracleDemo.xaml.cs, add the method for OnDeleteButtonClicked that we created in the XAML file above.
private void OnDeleteButtonClicked(object sender, EventArgs e)
{
var button = sender as Button;
var actorId = (int)button.CommandParameter;
// Find the actor in the database by ID
DatabaseConnectionClass.DeleteActor(actorId);
this.Actors = DatabaseConnectionClass.GetActors();
BindingContext = null;
BindingContext = this;
}
When you run the application, the Delete button will appear.
To delete Johnny Blaze's record, click the button.
Video tutorial: How to connect .NET MAUI application to Oracle database
Conclusion
In this guide, we've explored how to perform essential CRUD operations using Oracle in a .NET MAUI application. With these steps, you can gain a foundational understanding of how to expand your app's functionality and interact with complex data. Continue experimenting with these operations to build, optimize, and customize your applications to meet new data needs.