[9] SQL Server – C#
1 lutego 2022[1] W tym przykładzie połączymy SQL Server wraz z C#. Zakładam, że Microsoft >NET Core jest już zainstalowany.
[cent@vlsr01 ~]$ dotnet --version 5.0.210 [cent@vlsr01 ~]$ dotnet new console -o MssqlTest .NET 5.0 — Zapraszamy! --------------------- Wersja zestawu SDK: 5.0.210 ---------------- Zainstalowano certyfikat deweloperski HTTPS programu ASP.NET Core. Aby zaufać certyfikatowi, uruchom polecenie „dotnet dev-certs https --trust” (tylko systemy Windows i MacOS). Dowiedz się więcej o protokole HTTPS: https://aka.ms/dotnet-https ---------------- Napisz swoją pierwszą aplikację: https://aka.ms/dotnet-hello-world Dowiedz się, co nowego: https://aka.ms/dotnet-whats-new Zapoznaj się z dokumentacją: https://aka.ms/dotnet-docs Raportuj problemy i znajdź źródło w serwisie GitHub: https://github.com/dotnet/core Użyj polecenia „dotnet --help”, aby wyświetlić dostępne polecenia, lub odwiedź stronę https://aka.ms/dotnet-cli -------------------------------------------------------------------------------------- Getting ready... The template "Console Application" was created successfully. Processing post-creation actions... Running 'dotnet restore' on MssqlTest/MssqlTest.csproj... Determining projects to restore... Restored /home/cent/MssqlTest/MssqlTest.csproj (in 75 ms). Restore succeeded. [cent@vlsr01 ~]$ cd MssqlTest/ [cent@vlsr01 MssqlTest]$ mcedit MssqlTest.csproj <Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <OutputType>Exe</OutputType> <TargetFramework>net5.0</TargetFramework> </PropertyGroup> #dodaj <ItemGroup> <PackageReference Include="System.Data.SqlClient" Version="4.4.0" /> </ItemGroup> </Project>
[2] Stwórz użytkownika i bazę danych do testów.
[cent@vlsr01 ~]$ sqlcmd -S localhost -U SA Password: 1> create login cent with PASSWORD= N'TajneHasło'; 2> go 1> create database SampleDB; 2> go 1> use SampleDB; 2> go Changed database context to 'SampleDB'. 1> create user cent for login cent; 2> go 1> exec sp_addrolemember 'db_owner', 'cent'; 2> go 1> create table SampleTable ( 2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50) ); 3> insert into SampleTable ( 4> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux' ); 5> go (3 rows affected)
[3] No i cel naszych działań – przykładowe użycie C# wraz z SQL Server.
[cent@vlsr01 ~]$ cd MssqlTest/ [cent@vlsr01 MssqlTest]$ mcedit Program.cs #stwórz nowy 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 = "cent"; builder.Password = "TajneHasło"; builder.InitialCatalog = "SampleDB"; Console.Write("Connecting to SQL Server... "); using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); Console.WriteLine("Done."); StringBuilder sb = new StringBuilder(); 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) ); } } } 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", "Ubuntu"); command.Parameters.AddWithValue("@last_name", "Linux"); int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine(rowsAffected + " row(s) inserted"); } String userToUpdate = "Redhat"; Console.Write("\r\nUpdating 'Last_Name' for user " + userToUpdate + "\r\n"); sb.Clear(); sb.Append("update SampleTable set Last_Name = N'Ootpa' 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) ); } } } String userToDelete = "Ubuntu"; 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()); } } } } [cent@vlsr01 MssqlTest]$ dotnet restore Determining projects to restore... Restored /home/cent/MssqlTest/MssqlTest.csproj (in 9,74 sec). [cent@vlsr01 MssqlTest]$ dotnet run Connecting to SQL Server... Done. Reading data from SampleTable... 1 CentOS Linux 2 RedHat Linux 3 Fedora Linux Inserting into SampleTable... 1 row(s) inserted Updating 'Last_Name' for user Redhat 1 row(s) updated 1 CentOS Linux 2 RedHat Ootpa 3 Fedora Linux 4 Ubuntu Linux Deleting user 'Ubuntu' 1 row(s) deleted