ADO.NET using SQL Server

ADO.NET using SQL Server

In this article, I am going to discuss ADO.NET using SQL Server. Please read our previous article where we discussed the Architecture of ADO.NET. At the end of this article, 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 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.

Open Microsoft SQL Server Management Tool

Once you click on the Connect, it will connect to the SQL Server Database and after successful connection, it will display the following window.

ADO.NET using SQL Server

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 option menu and here, you need to click on the New Database option as shown in the below image.

Creating Database in SQL Server

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 its upto you, you can provide any meaningful name as per your choice. 

Connecting to SQL Server using ADO.NET

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.

How to connect to SQL Server using ADO.NET

That’s it. Our database part is over. Now let us move to ado.net part.

Establish connection and create a table

Once the Student Database is ready, now, let’s move and create a table (Student table) by using the ADO.NET Provider and C# code. In the below example, we are using created student database to connect.

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 then modify the Program.cs class file as shown below. In this article, I am not going to explain you the code. Here in this article, I am just going to show you how to communicate with SQL Server database. From our next article onwards, I will explain each and everything in detail.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().CreateTable();
            Console.ReadKey();
        }
        public void CreateTable()
        {
            SqlConnection con = null;
            try
            {
                // Creating Connection  
                con = new SqlConnection("data source=.; database=student; integrated security=SSPI");

                // writing sql query  
                SqlCommand cm = new SqlCommand("create table student(id int not null, name varchar(100), email varchar(50), join_date date)", con);  

                // Opening Connection  
                con.Open();

                // Executing the SQL query  
                cm.ExecuteNonQuery();

                // Displaying a message  
                Console.WriteLine("Table created Successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong." + e);
            }
            // Closing the connection  
            finally
            {
                con.Close();
            }
        }
    }
}

Now, execute the program and you should see the following message on the console.

Establish connection and create a table using ado.net

We can see the created table in Microsoft SQL Server Management Studio also. It shows the created table as shown below.

Establish connection to SQL Server and create a table using ado.net

See, we have the Student table within the Student database. As of now the Student table is empty. Lets insert one record into the Student table using ADO.NET and C#.

Inserting Record using ADO.NET:

Please modify the Program.cs class file as shown below. Here, we will insert a record into the student table.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().InsertRecord();
            Console.ReadKey();
        }
        public void InsertRecord()
        {
            SqlConnection con = null;
            try
            {
                // Creating Connection  
                con = new SqlConnection("data source=.; database=student; integrated security=SSPI");

                // writing sql query  
                SqlCommand cm = new SqlCommand("insert into student (id, name, email, join_date) values ('101', 'Ronald Trump', 'ronald@example.com', '1/12/2017')", con); 
                
                // Opening Connection  
                con.Open();

                // Executing the SQL query  
                cm.ExecuteNonQuery();

                // Displaying a message  
                Console.WriteLine("Record Inserted Successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong." + e);
            }
            // Closing the connection  
            finally
            {
                con.Close();
            }
        }
    }
}

Once you run the application, you will get the following output.

Inserting Record using ADO.NET

Retrieve Record using 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.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().DisplayData();
            Console.ReadKey();
        }
        public void DisplayData()
        {
            SqlConnection con = null;
            try
            {
                // Creating Connection  
                con = new SqlConnection("data source=.; database=student; integrated security=SSPI");

                // writing sql query  
                SqlCommand cm = new SqlCommand("Select * from student", con);

                // Opening Connection  
                con.Open();

                // Executing the SQL query  
                SqlDataReader sdr = cm.ExecuteReader();

                // Iterating Data  
                while (sdr.Read())
                {
                    // Displaying Record  
                    Console.WriteLine(sdr["id"] + " " + sdr["name"] + " " + sdr["email"]); 
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong." + e);
            }
            // Closing the connection  
            finally
            {
                con.Close();
            }
        }
    }
}

You will get the following output when you run the above program.

Retrieve Record using ADO.NET

Deleting Record from SQL Server database using 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.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().DeleteData();
            Console.ReadKey();
        }
        public void DeleteData()
        {
            SqlConnection con = null;
            try
            {
                // Creating Connection  
                con = new SqlConnection("data source=.; database=student; integrated security=SSPI");

                // writing sql query  
                SqlCommand cm = new SqlCommand("delete from student where id = '101'", con);

                // Opening Connection  
                con.Open();

                // Executing the SQL query  
                cm.ExecuteNonQuery();
                Console.WriteLine("Record Deleted Successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong." + e);
            }
            // Closing the connection  
            finally
            {
                con.Close();
            }
        }
    }
}

It will display the following output once you execute the program.

Deleting Record from SQL Server database using ADO.NET

Now, if you verify the student table, then you will see that the record is deleted. In this article, I didn’t explain a single line of code intentionally. I will explain each and everything in details from our next article.

In the next article, I am going to discuss ADO.NET SqlConnection Class in detail. Here, in this I try to explain ADO.NET using SQL Server i.e. how to connect to SQL server using ADO.NET. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *