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.
Download and activate dotConnect for Oracle
30-day free trial version
Download and install dotConnect for Oracle directly on your machine, or install the Devart.Data.Oracle NuGet package.
To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.
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 a connection in your application, add the License Key to your connection string.
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.