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