· 5 years ago · Mar 14, 2020, 06:26 PM
1using System;
2using System.Text;
3using System.Data;
4using System.Data.SqlClient;
5
6namespace SqlServerSample
7{
8
9 class Program
10 {
11
12 static void Main(string[] args)
13 {
14 try
15 {
16 Console.WriteLine("Connect to SQL Server and demo Create, Read, Update and Delete operations.");
17
18 // Build connection string
19 SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
20 builder.DataSource = "localhost"; // update me
21 builder.UserID = "sa"; // update me
22 builder.Password = "kawser"; // update me
23 builder.InitialCatalog = "master";
24
25 // Connect to SQL
26 Console.Write("Connecting to SQL Server ... ");
27 using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
28 {
29 connection.Open();
30 Console.WriteLine("Done.");
31
32 // Create a sample database
33 Console.Write("Dropping and creating database 'SampleDB' ... ");
34 String sql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]";
35 using (SqlCommand command = new SqlCommand(sql, connection))
36 {
37 command.ExecuteNonQuery();
38 Console.WriteLine("Done.");
39 }
40
41 // Create a Table and insert some sample data
42 Console.Write("Creating sample table with data, press any key to continue...");
43 Console.ReadKey(true);
44 StringBuilder sb = new StringBuilder();
45 sb.Append("USE SampleDB; ");
46 sb.Append("CREATE TABLE Employees ( ");
47 sb.Append(" Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ");
48 sb.Append(" Name NVARCHAR(50), ");
49 sb.Append(" Location NVARCHAR(50) ");
50 sb.Append("); ");
51 sb.Append("INSERT INTO Employees (Name, Location) VALUES ");
52 sb.Append("(N'Jared', N'Australia'), ");
53 sb.Append("(N'Nikita', N'India'), ");
54 sb.Append("(N'Tom', N'Germany'); ");
55 sql = sb.ToString();
56 using (SqlCommand command = new SqlCommand(sql, connection))
57 {
58 command.ExecuteNonQuery();
59 Console.WriteLine("Done.");
60 }
61
62 // INSERT demo
63 Console.Write("Inserting a new row into table, press any key to continue...");
64 Console.ReadKey(true);
65 sb.Clear();
66 sb.Append("INSERT Employees (Name, Location) ");
67 sb.Append("VALUES (@name, @location);");
68 sql = sb.ToString();
69 using (SqlCommand command = new SqlCommand(sql, connection))
70 {
71 command.Parameters.AddWithValue("@name", "Jake");
72 command.Parameters.AddWithValue("@location", "United States");
73 int rowsAffected = command.ExecuteNonQuery();
74 Console.WriteLine(rowsAffected + " row(s) inserted");
75 }
76
77 // UPDATE demo
78 String userToUpdate = "Nikita";
79 Console.Write("Updating 'Location' for user '" + userToUpdate + "', press any key to continue...");
80 Console.ReadKey(true);
81 sb.Clear();
82 sb.Append("UPDATE Employees SET Location = N'United States' WHERE Name = @name");
83 sql = sb.ToString();
84 using (SqlCommand command = new SqlCommand(sql, connection))
85 {
86 command.Parameters.AddWithValue("@name", userToUpdate);
87 int rowsAffected = command.ExecuteNonQuery();
88 Console.WriteLine(rowsAffected + " row(s) updated");
89 }
90
91 // DELETE demo
92 String userToDelete = "Jared";
93 Console.Write("Deleting user '" + userToDelete + "', press any key to continue...");
94 Console.ReadKey(true);
95 sb.Clear();
96 sb.Append("DELETE FROM Employees WHERE Name = @name;");
97 sql = sb.ToString();
98 using (SqlCommand command = new SqlCommand(sql, connection))
99 {
100 command.Parameters.AddWithValue("@name", userToDelete);
101 int rowsAffected = command.ExecuteNonQuery();
102 Console.WriteLine(rowsAffected + " row(s) deleted");
103 }
104
105 // READ demo
106 Console.WriteLine("Reading data from table, press any key to continue...");
107 Console.ReadKey(true);
108 sql = "SELECT Id, Name, Location FROM Employees;";
109 using (SqlCommand command = new SqlCommand(sql, connection))
110 {
111
112 using (SqlDataReader reader = command.ExecuteReader())
113 {
114 while (reader.Read())
115 {
116 Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
117 }
118 }
119 }
120 }
121 }
122 catch (SqlException e)
123 {
124 Console.WriteLine(e.ToString());
125 }
126
127 Console.WriteLine("All done. Press any key to finish...");
128 Console.ReadKey(true);
129 }
130 }
131}