C# – Connecting to SQL Server on Ubuntu20.04 -VS Code No.104

This blog provides instructions on how to connect to and run a select query in SQL Server on Ubuntu 20.04 using .NET Data Provider for SQL Server (SqlClient).

Contents

▼1. Connecting to SQL Server on Ubuntu by C#
▼3. Executing a select query in SQL Server by C#
▼4. Reference

▼1. Connecting to SQL Server on Ubuntu by C#

In the past, this blog demonstrated how to connect to SQL Server on Ubuntu and execute a query using Python and Java. However, in this case, C# will be used instead.

(As of 2022/12)

▼2. Prerequisites

2-1. Installing Ubuntu 20.04.2 LTS

https://releases.ubuntu.com/20.04/

2-2. Installing Visual Studio Code

https://code.visualstudio.com/docs/setup/linux

sudo snap install --classic code

2-3. Installing C# extension

To install the C# extension, press the Ctrl+P keys and type “ext install ms-dotnettools.csharp” in the search box, then press Enter. Once the installation is complete, you should be able to see the results as shown in the following screenshot. Ref:C# – Visual Studio Marketplace

2-4. Adding “Microsoft package signing key” into the list of trusted keys and adding the package repository as well

According to the following document, .NET has been installed on Ubuntu 22.04.
Install .NET on Ubuntu – .NET | Microsoft Docs

Before you install .NET, run the following commands to add the Microsoft package signing key to your list of trusted keys and add the package repository on Ubuntu 20.04.

wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
rm packages-microsoft-prod.deb

2-5. Installing .NET SDK on Ubuntu 20.04

sudo apt-get update &&  sudo apt-get install -y dotnet-sdk-6.0

(** Note) When I installed them on my environment, the following error happened. according to the error message, I did “apt –fix-broken install” to install .NET SDK 6.0.

xxx
W: Target CNF (main/cnf/Commands-amd64) is configured multiple times in /etc/apt/sources.list.d/microsoft-prod.list:1 and /etc/apt/sources.list.d/mssql-release.list:1
W: Target CNF (main/cnf/Commands-all) is configured multiple times in /etc/apt/sources.list.d/microsoft-prod.list:1 and /etc/apt/sources.list.d/mssql-release.list:1
Reading package lists... Done
Building dependency tree       
Reading state information... Done
You might want to run 'apt --fix-broken install' to correct these.
The following packages have unmet dependencies:
 dotnet-sdk-6.0 : Depends: dotnet-targeting-pack-6.0 (>= 6.0.12) but it is not going to be installed
                  Depends: netstandard-targeting-pack-2.1 (>= 2.1.0) but it is not going to be installed
                  Depends: aspnetcore-runtime-6.0 (>= 6.0.12) but it is not going to be installed
                  Depends: dotnet-apphost-pack-6.0 (>= 6.0.12) but it is not going to be installed
                  Depends: dotnet-runtime-6.0 (>= 6.0.12) but it is not going to be installed
                  Depends: aspnetcore-targeting-pack-6.0 (>= 6.0.12) but it is not going to be installed
 mysql-connector-odbc-setup : Depends: mysql-connector-odbc (= 8.0.31-1ubuntu20.04) but it is not installable
E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).

2-6. Confirming if the installation of .NET SDK worked successfully by dotnet command

dotnet
(results)
Usage: dotnet [options]
Usage: dotnet [path-to-application]

Options:
  -h|--help         Display help.
  --info            Display .NET information.
  --list-sdks       Display the installed SDKs.
  --list-runtimes   Display the installed runtimes.

path-to-application:
  The path to an application .dll file to execute.

▼3. Executing a select query in SQL Server by C#

3-1. Creating an application and then starting VSCode in the path where the application is generated

dotnet new console -o MytestApp
cd MytestApp
code .

3-2. Adding System.Data.SqlClient in MytestApp.csproj and saving this file by Ctrl+ S

The Namespace System.Data.SqlClient of .NET Data Provideris registered for SQL Server for the connection to SQL Serve. the latest version is Version 4.8.5 as of 2023/02.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
  </ItemGroup>


</Project>

3-3. Restoring the dependencies and tools of a project

dotnet restore

3-4. Creating Program.cs as C# code

The ‘sa’ user is used for SQL authentication to connect to the SQL Server. the password is set in the parameter “Password”. Database name is “sampledb1” in this code. The data is generated by this blog. Python – Create Sample data in SQL Server on Ubuntu v2 No.97

using System;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Build connection string
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "localhost";
                builder.UserID = "sa";
                builder.Password = "password";
                builder.InitialCatalog = "sampledb1";

                // Connect to SQL
                Console.Write("Connecting to SQL Server ... ");
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    connection.Open();
                    Console.WriteLine("Done.");
                    // execute select query
                    String sql = "SELECT id, cdatetime, note FROM sampletb";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetDateTime(1) ,reader.GetString(2));
                            }
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

3-5. Executing this code

dotnet run

Connecting to SQL Server ... Done.
3366109 12/9/2022 1:59:12 PM int
3366109 12/9/2022 1:59:12 PM mcranentiv
6732218 12/9/2022 1:59:12 PM orlsojlvuu
10098327 12/9/2022 1:59:12 PM uaclnlkehv
13464436 12/9/2022 1:59:12 PM xngcehsmtc
16830545 12/9/2022 1:59:12 PM llvzgooqaa
20196654 12/9/2022 1:59:12 PM atmordnpfz
23562763 12/9/2022 1:59:12 PM qpzlferdra
26928872 12/9/2022 1:59:12 PM lnbjlmclxx
30294981 12/9/2022 1:59:12 PM xfspqvetbf

▼4. Reference

That’s all. Have a nice day ahead !!!