Programming Languages on Power

 View Only

Connect .NET C# application to Microsoft SQL database on IBM Power

By Sapana Khemkar posted Tue March 28, 2023 09:46 AM

  

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:

  • A Virtual Machine(VM) capable of running Microsoft SQL Server.

  • A Power VM with Red Hat Enterprise Linux (RHEL) 8.6.

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

  1. 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.
    
  2. 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).
    
  3. 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>
  4. 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);
            }
        }
    }
  5. 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
    
  6. 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.

  1. Create a sample .NET console application using the following command.

    dotnet new console -o SqlServerEFSample

    Note: I created the sample application with name, SqlServerEFSample.

  2. 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
    
  3. 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>
    
  4. 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.

    1. 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() + "]";
              }
          }
      }
      
    2. 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 + "]";
              }
          }
      }
  5. 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; }
        }
    }
    
  6. 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);
            }
        }
    }
    
  7. 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
    
  8. 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

Permalink