ADO.

NET SQL Server Connection

To connect with SQL Server, we must have it installed in our system. We are
using Microsoft SQL Server Management Tool to connect with the SQL
Server. We can use this tool to handle database. Now, follow the following
steps to connect with SQL Server.

1. Open Microsoft SQL Server Management Tool

It will prompt for database connection. Provide the server name and
authentication.

After successful connection, it displays the following window.

2. Creating Database

Now, create database by selecting database option then right click on

it. It pops up an option menu and provides couple of options.

Click on the New Database then it will ask for the database name.

Here, we have created a Student database.

Click on the Ok button then it will create a database that we can see in
the left window of the below screenshot.

3. Establish connection and create a table

After creating database, now, let's create a table by using the following
C# code. In this source code, we are using created student database
to connect.
 In visual studio 2017, we created a .NET console application project
that contains the following C# code.

// Program.cs

1. using System;  
2. using System.Data.SqlClient;  
3. namespace AdoNetConsoleApplication  
4. {  
5.     class Program  
6.     {  
7.         static void Main(string[] args)  
8.         {  
9.             new Program().CreateTable();  
10.         }  
11.         public void CreateTable()  
12.         {  
13.             SqlConnection con = null;  
14.             try  
15.             {  
16.                 // Creating Connection  
17.                 con = new SqlConnection("data source=.; database=
student; integrated security=SSPI");  
18.                 // writing sql query  
19.                 SqlCommand cm = new SqlCommand("create table s
tudent(id int not null,   
20.                 name varchar(100), email varchar(50), join_date dat
e)", con);  
21.                 // Opening Connection  
22.                 con.Open();  
23.                 // Executing the SQL query  
24.                 cm.ExecuteNonQuery();  
25.                 // Displaying a message  
26.                 Console.WriteLine("Table created Successfully");  
27.             }  
28.             catch (Exception e)  
29.             {  
30.                 Console.WriteLine("OOPs, something went wrong."+e
);  
31.             }  
32.             // Closing the connection  
33.             finally  
34.             {  
35.                 con.Close();  
36.             }  
37.         }  
38.     }  
39. }  

Execute this code using Ctrl+F5. After executing, it displays a

message to the console as below.

We can see the created table in Microsoft SQL Server Management

Studio also. It shows the created table as shown below.

See, we have a table here. Initially, this table is empty so we need to

insert data into it.

4. Insert Data into the Table

// Program.cs
1. using System;  
2. using System.Data.SqlClient;  
3. namespace AdoNetConsoleApplication  
4. {  
5.     class Program  
6.     {  
7.         static void Main(string[] args)  
8.         {  
9.             new Program().CreateTable();  
10.         }  
11.         public void CreateTable()  
12.         {  
13.             SqlConnection con = null;  
14.             try  
15.             {  
16.                 // Creating Connection  
17.                 con = new SqlConnection("data source=.; database=
student; integrated security=SSPI");  
18.                 // writing sql query  
19.                 SqlCommand cm = new SqlCommand("insert into stu
dent  
20.                 (id, name, email, join_date)values('101','Ronald Trum
p','[email protected]','1/12/2017')", con);  
21.                 // Opening Connection  
22.                 con.Open();  
23.                 // Executing the SQL query  
24.                 cm.ExecuteNonQuery();  
25.                 // Displaying a message  
26.                 Console.WriteLine("Record Inserted Successfully");  
27.             }  
28.             catch (Exception e)  
29.             {  
30.                 Console.WriteLine("OOPs, something went wrong."+e
);  
31.             }  
32.             // Closing the connection  
33.             finally  
34.             {  
35.                 con.Close();  
36.             }  
37.         }  
38.     }  
39. }  

Execute this code by using Ctrl+F5 and it will display the following

output.

2. Retrieve Record

Here, we will retrieve the inserted data. Look at the following C# code.

// Program.cs

1. using System;  
2. using System.Data.SqlClient;  
3. namespace AdoNetConsoleApplication  
4. {  
5.     class Program  
6.     {  
7.         static void Main(string[] args)  
8.         {  
9.             new Program().CreateTable();  
10.         }  
11.         public void CreateTable()  
12.         {  
13.             SqlConnection con = null;  
14.             try  
15.             {  
16.                 // Creating Connection  
17.                 con = new SqlConnection("data source=.; database=
student; integrated security=SSPI");  
18.                 // writing sql query  
19.                 SqlCommand cm = new SqlCommand("Select * from 
student", con);  
20.                 // Opening Connection  
21.                 con.Open();  
22.                 // Executing the SQL query  
23.                 SqlDataReader sdr = cm.ExecuteReader();  
24.                 // Iterating Data  
25.                 while (sdr.Read())  
26.                 {  
27.                     Console.WriteLine(sdr["id"] + " " + sdr["name"]+" 
"+sdr["email"]); // Displaying Record  
28.                 }  
29.             }  
30.             catch (Exception e)  
31.             {  
32.                 Console.WriteLine("OOPs, something went wrong.\n"
+e);  
33.             }  
34.             // Closing the connection  
 35.             finally  
36.             {  
37.                 con.Close();  
38.             }  
39.         }  
40.     }  
41. }  

Execute this code by Ctrl+F5 and it will produce the following result.

This displays two records, one we inserted manually.

Output:

2. Deleting Record

This time student table contains two records. The following C# code delete

one row from the table.

// Program.cs

1. using System;  
2. using System.Data.SqlClient;  
3. namespace AdoNetConsoleApplication  
4. {  
5.     class Program  
6.     {  
7.         static void Main(string[] args)  
8.         {  
9.             new Program().CreateTable();  
10.         }  
11.         public void CreateTable()  
12.         {  
13.             SqlConnection con = null;  
14.             try  
15.             {  
16.                 // Creating Connection  
17.                 con = new SqlConnection("data source=.; database=stude
nt; integrated security=SSPI");  
18.                 // writing sql query  
19.                 SqlCommand cm = new SqlCommand("delete from student 
where id = '101'", con);  
20.                 // Opening Connection  
21.                 con.Open();  
22.                 // Executing the SQL query  
23.                 cm.ExecuteNonQuery();  
24.                 Console.WriteLine("Record Deleted Successfully");  
25.             }  
26.             catch (Exception e)  
27.             {  
28.                 Console.WriteLine("OOPs, something went wrong.\n"+e);  
29.             }  
30.             // Closing the connection  
31.             finally  
32.             {  
33.                 con.Close();  
34.             }  
35.         }  
36.     }  
37. }  

Output:

It displays the following output.

We can verify it by retrieving data back by using SqlDataReader.