Created
July 28, 2018 12:18
-
-
Save mizuneko/acb80199b307405f692efe0421f32abf to your computer and use it in GitHub Desktop.
[DB接続] SQL Serverへの接続~CRUD操作
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Text; | |
using System.Data.SqlClient; | |
namespace DatabaseSample | |
{ | |
class Program | |
{ | |
static void Main(string[] args) | |
{ | |
try | |
{ | |
Console.WriteLine("Connect to SQL Server and Create, Read, Update and Delete operations."); | |
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder() | |
{ | |
DataSource = @"localhost", | |
//IntegratedSecurity = true, | |
UserID = "sa", | |
Password = "your_password", | |
InitialCatalog = "master" | |
}; | |
Console.Write("Connecting to SQL Server ... "); | |
using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) | |
{ | |
connection.Open(); | |
Console.WriteLine("Done."); | |
// Create a sample database | |
CreateDatabase(connection); | |
// Create a Table and insert some sample data. | |
CreateTable(connection); | |
// INSERT demo | |
Insert(connection); | |
// UPDATE demo | |
Update(connection); | |
// DELETE demo | |
Delete(connection); | |
// READ demo | |
Select(connection); | |
} | |
} | |
catch (SqlException e) | |
{ | |
Console.WriteLine(e.ToString()); | |
} | |
Console.WriteLine("All done. Press any key to finish..."); | |
Console.ReadKey(true); | |
} | |
private static void Select(SqlConnection connection) | |
{ | |
Console.WriteLine("Reading data from table, press any key to continue..."); | |
Console.ReadKey(true); | |
StringBuilder sb = new StringBuilder(); | |
sb.Append("SELECT Id, Name, Location FROM Employees "); | |
var sql = sb.ToString(); | |
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)); | |
} | |
} | |
} | |
} | |
private static void Delete(SqlConnection connection) | |
{ | |
string userToDelete = "Jared"; | |
Console.WriteLine($"Deleting user '{userToDelete}', press any key to continue..."); | |
Console.ReadKey(true); | |
StringBuilder sb = new StringBuilder(); | |
sb.Append("DELETE FROM Employees WHERE Name = @name "); | |
var 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"); | |
} | |
} | |
private static void Update(SqlConnection connection) | |
{ | |
string userToUpdate = "Nikita"; | |
Console.WriteLine($"Updating 'Location' for user '{userToUpdate}', press any key to continue..."); | |
Console.ReadKey(true); | |
StringBuilder sb = new StringBuilder(); | |
sb.Append("UPDATE Employees SET Location = N'United States' WHERE Name = @name "); | |
var 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"); | |
} | |
} | |
private static void Insert(SqlConnection connection) | |
{ | |
Console.WriteLine("Inserting a new row into table, press any key to continue..."); | |
Console.ReadKey(true); | |
StringBuilder sb = new StringBuilder(); | |
sb.Append("INSERT INTO Employees (Name, Location) "); | |
sb.Append("VALUES (@name, @location) "); | |
var 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"); | |
} | |
} | |
private static void CreateTable(SqlConnection connection) | |
{ | |
Console.WriteLine("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') "); | |
var sql = sb.ToString(); | |
using (SqlCommand command = new SqlCommand(sql, connection)) | |
{ | |
command.ExecuteNonQuery(); | |
Console.WriteLine("Done."); | |
} | |
} | |
private static void CreateDatabase(SqlConnection connection) | |
{ | |
Console.WriteLine("Dropping and creating database 'SampleDB' ... "); | |
StringBuilder sb = new StringBuilder(); | |
sb.Append("DROP DATABASE IF EXISTS [SampleDB]; "); | |
sb.Append("CREATE DATABASE [SampleDB]"); | |
var sql = sb.ToString(); | |
using (SqlCommand command = new SqlCommand(sql, connection)) | |
{ | |
command.ExecuteNonQuery(); | |
Console.WriteLine("Done."); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment