Are you a Power developer and looking to expand your horizons? Well, I have some exciting news for you! You can now run .NET applications on IBM Power. In fact, .NET 7 has supported Power for a while now. If you want to learn more about this, refer to, .NET 7 now includes support for Linux on Power. In this blog, I will show you how to install .NET on a Power virtual machine (VM) and to write a sample C# application that connects to Microsoft SQL Server to access or edit the database on the server.
Prerequisites
Following are the prerequisites for completing this activity:
For this blog, I installed the Microsoft SQL Server on an x86_64 machine with RHEL 8.6, however the steps here should also work without issues on RHEL 9.
For more information, refer to the following:
Install .NET on the Power VM
Install .NET 7 on the Power VM by entering the following command into the terminal:
sudo yum install dotnet-sdk-7.0 -y
Verify the installation by using the dotnet –info command. Upon successful installation the following output displays:
dotnet --info
.NET SDK:
Version: 7.0.103
Commit: 6359034b09
Runtime Environment:
OS Name: rhel
OS Version: 8
OS Platform: Linux
RID: rhel.8-ppc64le
Base Path: /usr/lib64/dotnet/sdk/7.0.103/
Host:
Version: 7.0.3
Architecture: ppc64le
Commit: 0a2bda10e8
.NET SDKs installed:
7.0.103 [/usr/lib64/dotnet/sdk]
.NET runtimes installed:
Microsoft.AspNetCore.App 7.0.3 [/usr/lib64/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 7.0.3 [/usr/lib64/dotnet/shared/Microsoft.NETCore.App]
Other architectures found:
None
Environment variables:
DOTNET_ROOT [/usr/lib64/dotnet]
global.json file:
Not found
Congratulations, you have installed .NET. Now let’s create and run your C# sample application on the Power VM.
Create a C# application to access the database server with SQL queries
-
Create a sample .NET console application using the following command:
dotnet new console -o SqlServerSample
Note: I create the sample application with name “SqlServerSample”. You should see the output displayed as follows:
# dotnet new console -o SqlServerSample
The template "Console App" was created successfully.
Processing post-creation actions...
Restoring /root/SqlServerSample/SqlServerSample.csproj:
Determining projects to restore...
Restored /root/SqlServerSample/SqlServerSample.csproj (in 433 ms).
Restore succeeded.
-
Change the directory to ‘SqlServerSample’ and add package ‘System.Data.SqlClient’ to your project as shown.
cd SqlServerSample/
dotnet add package System.Data.SqlClient
You should see the output displayed as follows.
# dotnet add package System.Data.SqlClient
Determining projects to restore...
Writing /tmp/tmphH7l8i.tmp
info : X.509 certificate chain validation will use the fallback certificate bundle at '/usr/lib64/dotnet/sdk/7.0.103/trustedroots/codesignctl.pem'.
info : X.509 certificate chain validation will use the fallback certificate bundle at '/usr/lib64/dotnet/sdk/7.0.103/trustedroots/timestampctl.pem'.
info : Adding PackageReference for package 'System.Data.SqlClient' into project '/root/SqlServerSample/SqlServerSample.csproj'.
info : GET https://api.nuget.org/v3/registration5-gz-semver2/system.data.sqlclient/index.json
info : OK https://api.nuget.org/v3/registration5-gz-semver2/system.data.sqlclient/index.json 178ms
info : Restoring packages for /root/SqlServerSample/SqlServerSample.csproj...
info : Package 'System.Data.SqlClient' is compatible with all the specified frameworks in project '/root/SqlServerSample/SqlServerSample.csproj'.
info : PackageReference for package 'System.Data.SqlClient' version '4.8.5' added to file '/root/SqlServerSample/SqlServerSample.csproj'.
info : Writing assets file to disk. Path: /root/SqlServerSample/obj/project.assets.json
log : Restored /root/SqlServerSample/SqlServerSample.csproj (in 765 ms).
-
Run the cat command to ensure that your project file “SqlServerSample.csproj” appears as follows.
cat SqlServerSample.csproj
<project sdk="Microsoft.NET.Sdk">
<propertygroup>
<outputtype>Exe</outputtype>
<targetframework>net7.0</targetframework>
<implicitusings>enable</implicitusings>
<nullable>enable</nullable>
</propertygroup>
<itemgroup>
<packagereference include="System.Data.SqlClient" version="4.8.5">
</packagereference></itemgroup>
</project>
-
Your project directory should contain a Program.cs file by default. Open the file and paste the following code into it, then save and close the file. This code connects to the Microsoft SQL Server and performs the following actions: Creates a table, inserts/updates/deletes data, and reads the data.
Note: Replace DataSource, UserID and Password with your own details in the following code.
using System;
using System.Text;
using System.Data.SqlClient;
namespace SqlServerSample
{
class Program
{
static void Main(string[] args)
{
try
{
Console.WriteLine("Connect to SQL Server and demo CRUD operations.");
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "your remote host IP"; // update me
builder.UserID = "sa"; // update me
builder.Password = "your_password"; // update me
builder.InitialCatalog = "master";
// Connect to SQL
Console.Write("Connecting to SQL Server ... ");
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("Done.");
// Create a sample database
Console.Write("Dropping and creating database 'SampleDB' ... ");
String sql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.WriteLine("Done.");
}
// Create a Table and insert some sample data
Console.Write("Creating sample table with data, press any key to continue...");
Console.ReadKey(true);
StringBuilder sb = new StringBuilder();
sb.Append("USE SampleDB; ");
sb.Append("CREATE TABLE Employees ( ");
sb.Append(" Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ");
sb.Append(" Name NVARCHAR(50), ");
sb.Append(" Location NVARCHAR(50) ");
sb.Append("); ");
sb.Append("INSERT INTO Employees (Name, Location) VALUES ");
sb.Append("(N'Jared', N'Australia'), ");
sb.Append("(N'Nikita', N'India'), ");
sb.Append("(N'Tom', N'Germany'); ");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.WriteLine("Done.");
}
// INSERT demo
Console.Write("Inserting a new row into table, press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("INSERT Employees (Name, Location) ");
sb.Append("VALUES (@name, @location);");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", "Jake");
command.Parameters.AddWithValue("@location", "United States");
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) inserted");
}
// UPDATE demo
String userToUpdate = "Nikita";
Console.Write("Updating 'Location' for user '" + userToUpdate + "', press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("UPDATE Employees SET Location = N'United States' WHERE Name = @name");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", userToUpdate);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) updated");
}
// DELETE demo
String userToDelete = "Jared";
Console.Write("Deleting user '" + userToDelete + "', press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("DELETE FROM Employees WHERE Name = @name;");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", userToDelete);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) deleted");
}
// READ demo
Console.WriteLine("Reading data from table, press any key to continue...");
Console.ReadKey(true);
sql = "SELECT Id, Name, Location FROM Employees;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("All done. Press any key to finish...");
Console.ReadKey(true);
}
}
}
-
Run the dotnet build command to build your sample application. You should see the output displayed as follows.
#dotnet build
MSBuild version 17.4.1+fedecea9d for .NET
Determining projects to restore...
All projects are up-to-date for restore.
SqlServerSample -> /root/SqlServerSample/bin/Debug/net7.0/SqlServerSample.dll
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:15.00
-
Run the dotnet run command to verify your sample application. You should see the output displayed as follows.
#dotnet run
Connect to SQL Server and demo Create, Read, Update and Delete operations.
Connecting to SQL Server ... Done.
Dropping and creating database 'SampleDB' ... Done.
Creating sample table with data, press any key to continue...Done.
Inserting a new row into table, press any key to continue...1 row(s) inserted
Updating 'Location' for user 'Nikita', press any key to continue...1 row(s) updated
Deleting user 'Jared', press any key to continue...1 row(s) deleted
Reading data from table, press any key to continue...
2 Nikita United States
3 Tom Germany
4 Jake United States
All done. Press any key to finish...
Your .NET sample application is now up and running, connecting to Microsoft SQL Server, and can access or edit the database using SQL queries. Next up, you'll create a C# application to access the database with Entity Framework Core
Create a C# application to access the database with Entity Framework Core
Entity Framework Core (EF Core) is a lightweight and cross-platform version of Entity Framework, a data access technology developed by Microsoft for .NET applications. EF Core simplifies data access by allowing developers to define their data models in C# or VB.NET code, and it automatically generates the necessary database schema. It is a versatile tool for interacting with databases in a .NET environment.
In this section, you'll create a sample .NET application that connects to Microsoft SQL Server using EF Core instead of writing raw SQL queries.
-
Create a sample .NET console application using the following command.
dotnet new console -o SqlServerEFSample
Note: I created the sample application with name, SqlServerEFSample.
-
Change the directory to ‘SqlServerEFSample’ and add packages ‘System.Data.SqlClient’ and ‘Microsoft.EntityFrameworkCore.SqlServer’ to your project as shown below.
cd SqlServerEFSample/
dotnet add package System.Data.SqlClient
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
-
Run the cat command to ensure that your project file “SqlServerEFSample.csproj” appears as follows.
cat SqlServerEFSample.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net7.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.3" />
<PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
</ItemGroup>
</Project>
-
Next, you will be creating two tables in the database: User and Task. You can achieve this by defining a C# class for each table and using EF Core to generate the necessary database schema.
-
Create a new file called "User.cs" and add the following code:
using System;
using System.Collections.Generic;
namespace SqlServerEFSample
{
public class User
{
public int UserId { get; set; }
public String FirstName { get; set; }
public String LastName { get; set; }
public virtual IList<Task> Tasks { get; set; }
public String GetFullName()
{
return this.FirstName + " " + this.LastName;
}
public override string ToString()
{
return "User [id=" + this.UserId + ", name=" + this.GetFullName() + "]";
}
}
}
-
Create a new file called "task.cs" and add the following code:
using System;
namespace SqlServerEFSample
{
public class Task
{
public int TaskId { get; set; }
public string Title { get; set; }
public DateTime DueDate { get; set; }
public bool IsComplete { get; set; }
public virtual User AssignedTo { get; set; }
public override string ToString()
{
return "Task [id=" + this.TaskId + ", title=" + this.Title + ", dueDate=" + this.DueDate.ToString() + ", IsComplete=" + this.IsComplete + "]";
}
}
}
-
To work with EF Core, you need to create a database context class. This class serves as the main point of interaction between the application and the database, allowing you to perform CRUD (create, read, update, delete) operations.
Create a new file called " EFSampleContext.cs" and add the following code:
using Microsoft.EntityFrameworkCore;
namespace SqlServerEFSample
{
public class EFSampleContext : DbContext
{
string _connectionString;
public EFSampleContext(string connectionString)
{
this._connectionString = connectionString;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(this._connectionString);
}
public DbSet<User> Users { get; set; }
public DbSet<Task> Tasks { get; set; }
}
}
-
Your project directory should contain a “Program.cs” file by default. Open the file and paste the following code into it, then save and close the file.
using System;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace SqlServerEFSample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("** C# CRUD sample with Entity Framework Core and SQL Server **\n");
try
{
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "your remote host"; // update me
builder.UserID = "sa"; // update me
builder.Password = "your_password"; // update me
builder.InitialCatalog = "EFSampleDB";
builder.Encrypt = false; //set encryption false for demo
using (EFSampleContext context = new EFSampleContext(builder.ConnectionString))
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
Console.WriteLine("Created database schema from C# classes.");
// Create demo: Create a User instance and save it to the database
User newUser = new User { FirstName = "Anna", LastName = "Shrestinian" };
context.Users.Add(newUser);
context.SaveChanges();
Console.WriteLine("\nCreated User: " + newUser.ToString());
// Create demo: Create a Task instance and save it to the database
Task newTask = new Task() { Title = "Ship Helsinki", IsComplete = false, DueDate = DateTime.Parse("04-01-2017"), AssignedTo = newUser };
context.Tasks.Add(newTask);
context.SaveChanges();
Console.WriteLine("\nCreated Task: " + newTask.ToString());
// Association demo: Assign task to user
newTask.AssignedTo = newUser;
context.SaveChanges();
Console.WriteLine("\nAssigned Task: '" + newTask.Title + "' to user '" + newUser.GetFullName() + "'");
// Read demo: find incomplete tasks assigned to user 'Anna'
Console.WriteLine("\nIncomplete tasks assigned to 'Anna':");
var query = from t in context.Tasks
where t.IsComplete == false &&
t.AssignedTo.FirstName.Equals("Anna")
select t;
foreach(var t in query)
{
Console.WriteLine(t.ToString());
}
// Update demo: change the 'dueDate' of a task
Task taskToUpdate = context.Tasks.First(); // get the first task
Console.WriteLine("\nUpdating task: " + taskToUpdate.ToString());
taskToUpdate.DueDate = DateTime.Parse("06-30-2016");
context.SaveChanges();
Console.WriteLine("dueDate changed: " + taskToUpdate.ToString());
// Delete demo: delete all tasks with a dueDate in 2016
Console.WriteLine("\nDeleting all tasks with a dueDate in 2016");
DateTime dueDate2016 = DateTime.Parse("12-31-2016");
query = from t in context.Tasks
where t.DueDate < dueDate2016
select t;
foreach(Task t in query)
{
Console.WriteLine("Deleting task: " + t.ToString());
context.Tasks.Remove(t);
}
context.SaveChanges();
// Show tasks after the 'Delete' operation - there should be 0 tasks
Console.WriteLine("\nTasks after delete:");
List<Task> tasksAfterDelete = (from t in context.Tasks select t).ToList<Task>();
if (tasksAfterDelete.Count == 0)
{
Console.WriteLine("[None]");
}
else
{
foreach (Task t in query)
{
Console.WriteLine(t.ToString());
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("All done. Press any key to finish...");
Console.ReadKey(true);
}
}
}
-
Run the dotnet build command to build your sample application. You should see the output displayed as follows.
# dotnet build
MSBuild version 17.4.1+fedecea9d for .NET
Determining projects to restore...
Restored /root/SqlServerEFSample/SqlServerEFSample.csproj (in 1.4 sec).
/root/SqlServerEFSample/User.cs(9,23): warning CS8618: Non-nullable property 'FirstName' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/User.cs(10,23): warning CS8618: Non-nullable property 'LastName' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/User.cs(11,36): warning CS8618: Non-nullable property 'Tasks' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/Task.cs(8,23): warning CS8618: Non-nullable property 'Title' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/Task.cs(11,29): warning CS8618: Non-nullable property 'AssignedTo' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
SqlServerEFSample -> /root/SqlServerEFSample/bin/Debug/net7.0/SqlServerEFSample.dll
Build succeeded.
/root/SqlServerEFSample/User.cs(9,23): warning CS8618: Non-nullable property 'FirstName' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/User.cs(10,23): warning CS8618: Non-nullable property 'LastName' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/User.cs(11,36): warning CS8618: Non-nullable property 'Tasks' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/Task.cs(8,23): warning CS8618: Non-nullable property 'Title' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
/root/SqlServerEFSample/Task.cs(11,29): warning CS8618: Non-nullable property 'AssignedTo' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/root/SqlServerEFSample/SqlServerEFSample.csproj]
5 Warning(s)
0 Error(s)
Time Elapsed 00:00:09.97
-
Run the dotnet run command to verify your sample application. You should see the output displayed as follows.
dotnet run
** C# CRUD sample with Entity Framework Core and SQL Server **
Created database schema from C# classes.
Created User: User [id=1, name=Anna Shrestinian]
Created Task: Task [id=1, title=Ship Helsinki, dueDate=4/1/2017 12:00:00 AM, IsComplete=False]
Assigned Task: 'Ship Helsinki' to user 'Anna Shrestinian'
Incomplete tasks assigned to 'Anna':
Task [id=1, title=Ship Helsinki, dueDate=4/1/2017 12:00:00 AM, IsComplete=False]
Updating task: Task [id=1, title=Ship Helsinki, dueDate=4/1/2017 12:00:00 AM, IsComplete=False]
dueDate changed: Task [id=1, title=Ship Helsinki, dueDate=6/30/2016 12:00:00 AM, IsComplete=False]
Deleting all tasks with a dueDate in 2016
Deleting task: Task [id=1, title=Ship Helsinki, dueDate=6/30/2016 12:00:00 AM, IsComplete=False]
Tasks after delete:
[None]
All done. Press any key to finish...
Your sample .NET application is now up and running, connecting to Microsoft SQL Server and can access or edit the database server using EF Core.
Conclusion
In this blog, I have demonstrated how to create a C# sample application to connect to a Microsoft SQL Database on IBM Power. We encourage you to experiment with different use cases and explore other capabilities of the platform to further enhance your development skills. Give it a try and please drop a comment below if you have any questions or feedback.
References