In this Video, I am going to discuss ADO.NET using SQL Server Database with Examples. Please read our previous Video where we discussed the Architecture of ADO.NET. At the end of this Video, you will understand how to connect with SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using SQL Server Management Studio (SSMS) Tool to interact with SQL Server.
Open SQL Server Management Studio Tool
Once you open SSMS (SQL Server Management Studio), It will prompt you the connect to the server window. Here, you need to provide the server name and authentication details, select Database Engine as the server type and finally click on the Connect button as shown in the below image.
Once you click on the Connect button, it will connect to the SQL Server Database and after a successful connection, it will display the following window.
Creating Database in SQL Server
In order to create a database using GUI, you need to select the database option from object explorer and then right-click on it. It pops up an options menu and here, you need to click on the New Database option as shown in the below image.
Once you click on the New Database option, then it will open the following New Database window. Here, you just need to provide the database name and click on the OK button. Here, I created a database with the name Student. But it is up to you, you can provide any meaningful name as per your choice.
Once you click on the OK button, then it will create a Student database and you can see the Student database in the object explorer as shown in the below image.
Thats it. Our database part is over. Now let us move to the ADO.NET part.
Establish a connection to SQL Server database and create a table using ADO.NET
Once the Student Database is ready, now, lets move and create a table (Student table) by using the ADO.NET Provider and C# code. Open visual studio 2017 (you can use any version of visual studio), then create a new .NET console application project. Once you create the project, then modify the Program.cs class file as shown below. In this Video, I am not going to explain the code. Here in this Video, I am just going to show you how to communicate with SQL Server database. From our next Video onwards, I will explain each and everything in detail.
Now, execute the program and you should see the following message on the console.
We can see the created table in Microsoft SQL Server Management Studio also. It shows the created table as shown below.
See, we have the Student table within the Student database. As of now, the Student table is empty. Let us insert one record into the Student table using ADO.NET and C#.
Inserting Record using C# and ADO.NET:
Please modify the Program.cs class file as shown below. Here, we will insert a record into the student table.
Once you run the application, you will get the following output.
Retrieve Record using C# and ADO.NET
Here, we will retrieve the inserted data from the Student table of the student database. Please modify the Program.cs class file as shown below.
You will get the following output when you run the above program.
Deleting Record from SQL Server database using C# and ADO.NET
As of now, the student table contains one record. Let us delete that record using ADO.NET and C#. Please modify the Program.cs class file code as shown below which will delete the record from the Student table.
It will display the following output once you execute the program.
Now, if you verify the student table, then you will see that the record is deleted. In this Video, I didnt explain a single line of code intentionally. I will explain each and everything in detail from our next Video.