ADO.

NET SQL Server Connection

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.

// [Link]

1. using System;
2. using [Link];
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 student(id int not null,
20. name varchar(100), email varchar(50), join_date date)", con);
21. // Opening Connection
22. [Link]();
23. // Executing the SQL query
24. [Link]();
25. // Displaying a message
26. [Link]("Table created Successfully");
27. }
28. catch (Exception e)
29. {
30. [Link]("OOPs, something went wrong."+e);
31. }
32. // Closing the connection
33. finally
34. {
35. [Link]();
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

// [Link]

1. using System;
2. using [Link];
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 student
20. (id, name, email, join_date)values('101','Ronald Trump','ronald@[Link]','1/12/2017')",
con);
21. // Opening Connection
22. [Link]();
23. // Executing the SQL query
24. [Link]();
25. // Displaying a message
26. [Link]("Record Inserted Successfully");
27. }
28. catch (Exception e)
29. {
30. [Link]("OOPs, something went wrong."+e);
31. }
32. // Closing the connection
33. finally
34. {
35. [Link]();
36. }
37. }
38. }
39. }
Execute this code by using Ctrl+F5 and it will display the following output.

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

// [Link]

1. using System;
2. using [Link];
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. [Link]();
22. // Executing the SQL query
23. SqlDataReader sdr = [Link]();
24. // Iterating Data
25. while ([Link]())
26. {
27. [Link](sdr["id"] + " " + sdr["name"]+" "+sdr["email"]); // Displaying Record
28. }
29. }
30. catch (Exception e)
31. {
32. [Link]("OOPs, something went wrong.\n"+e);
33. }
34. // Closing the connection
35. finally
36. {
37. [Link]();
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:
 6. Deleting Record

This time student table contains two records. The following C# code delete one row from the table.

// [Link]

1. using System;
2. using [Link];
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=SSP
I");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("delete from student where id = '101'", con);
20. // Opening Connection
21. [Link]();
22. // Executing the SQL query
23. [Link]();
24. [Link]("Record Deleted Successfully");
25. }
26. catch (Exception e)
27. {
28. [Link]("OOPs, something went wrong.\n"+e);
29. }
30. // Closing the connection
31. finally
32. {
33. [Link]();
34. }
35. }
36. }
37. }
Output:

PauseNext
Mute

Current Time 0:05

Duration 18:10
Loaded: 3.67%

Fullscreen
It displays the following output.

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