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());
}
}
}
}
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) deleteddebian@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)
|
| Sponsored Link |