Debian 12 bookworm
Sponsored Link

SQL Server 2022 : C# から利用する2023/12/15

 
C# からの SQL Server 利用例です。
[1] Microsoft .NET インストール済みを前提として例示します。
debian@dlp:~$
dotnet --version

7.0.404
debian@dlp:~$
dotnet new console -o MssqlTest

Welcome to .NET 7.0!
---------------------
SDK Version: 7.0.404

Telemetry
---------
The .NET tools collect usage data in order to help us improve your experience. It is collected by Microsoft and shared with the community. You can opt-out of telemetry by setting the DOTNET_CLI_TELEMETRY_OPTOUT environment variable to '1' or 'true' using your favorite shell.

Read more about .NET CLI Tools telemetry: https://aka.ms/dotnet-cli-telemetry

----------------
Installed an ASP.NET Core HTTPS development certificate.
To trust the certificate run 'dotnet dev-certs https --trust' (Windows and macOS only).
Learn about HTTPS: https://aka.ms/dotnet-https
----------------
Write your first app: https://aka.ms/dotnet-hello-world
Find out what's new: https://aka.ms/dotnet-whats-new
Explore documentation: https://aka.ms/dotnet-docs
Report issues and find source on GitHub: https://github.com/dotnet/core
Use 'dotnet --help' to see available commands or visit: https://aka.ms/dotnet-cli
--------------------------------------------------------------------------------------
The template "Console App" was created successfully.

Processing post-creation actions...
Restoring /home/debian/MssqlTest/MssqlTest.csproj:
  Determining projects to restore...
  Restored /home/debian/MssqlTest/MssqlTest.csproj (in 69 ms).
Restore succeeded.

debian@dlp:~$
cd MssqlTest

debian@dlp:~/MssqlTest$
vi MssqlTest.csproj
<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.4.0" />
  </ItemGroup>
</Project>
[2] 基本的な利用例です。データベースや接続ユーザーは事前に適当に作成したものを使用します。
# 事前準備のテスト用データベース

debian@dlp:~$
sqlcmd -S localhost -U debian -d SampleDB3 -Q 'select * from dbo.SampleTable;'

Password:
ID          First_Name          Last_Name
----------- ------------------- ---------------------
          1 Ubuntu              Linux
          2 Debian              Linux
          3 RedHat              RHEL 9 Plow

(3 rows affected)

debian@dlp:~$
cd MssqlTest

debian@dlp:~/MssqlTest$
vi Program.cs
using System;
using System.Text;
using System.Data.SqlClient;

namespace SqlServerSample
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "127.0.0.1";
                builder.UserID = "debian";
                builder.Password = "P@ssw0rd01";
                builder.InitialCatalog = "SampleDB3";

                Console.Write("Connecting to SQL Server... ");
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    connection.Open();
                    Console.WriteLine("Done.");
                    StringBuilder sb = new StringBuilder();

                    // SampleTable を Select
                    Console.WriteLine("Reading data from SampleTable...");
                    String sql = "select * from SampleTable;";
                    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)
                                    );
                            }
                        }
                    }
                    
                    // SampleTable に Insert
                    Console.Write("\r\nInserting into SampleTable...\r\n");
                    sb.Clear();
                    sb.Append("insert SampleTable (First_Name, Last_Name) ");
                    sb.Append("values (@first_name, @last_name);");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", "Windows");
                        command.Parameters.AddWithValue("@last_name", "Microsoft");
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) inserted");
                    }

                    // 特定の行を Update
                    String userToUpdate = "Ubuntu";
                    Console.Write("\r\nUpdating 'Last_Name' for user " + userToUpdate + "\r\n");
                    sb.Clear();
                    sb.Append("update SampleTable set Last_Name = N'Jammy' where First_Name = @first_name");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", userToUpdate);
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) updated\r\n");
                    }
                    
                    sql = "select * from SampleTable;";
                    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)
                                    );
                            }
                        }
                    }
                    
                    // 特定の行を Delete
                    String userToDelete = "Windows";
                    Console.Write("\r\nDeleting user '" + userToDelete + "'\r\n");
                    sb.Clear();
                    sb.Append("delete from SampleTable where First_Name = @first_name;");
                    sql = sb.ToString();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@first_name", userToDelete);
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine(rowsAffected + " row(s) deleted");
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

debian@dlp:~/MssqlTest$
dotnet restore

  Determining projects to restore...
  Restored /home/debian/MssqlTest/MssqlTest.csproj (in 3.87 sec).
debian@dlp:~/MssqlTest$
dotnet run

Connecting to SQL Server... Done.
Reading data from SampleTable...
1 Ubuntu Linux
2 Debian Linux
3 RedHat RHEL 9 Plow

Inserting into SampleTable...
1 row(s) inserted

Updating 'Last_Name' for user Ubuntu
1 row(s) updated

1 Ubuntu Jammy
2 Debian Linux
3 RedHat RHEL 9 Plow
6 Windows Microsoft

Deleting user 'Windows'
1 row(s) deleted

debian@dlp:~/MssqlTest$
sqlcmd -S localhost -U debian -d SampleDB3 -Q 'select * from dbo.SampleTable;'

Password:
ID          First_Name           Last_Name
----------- -------------------- -----------------------
          1 Ubuntu               Jammy
          2 Debian               Linux
          3 RedHat               RHEL 9 Plow

(3 rows affected)
関連コンテンツ