How To Connect To SQL Database Using ADO.NET From C# Program

First of all, we will create a database and table followed by insertion of data in table. We will be using the following queries.

create database EmployeeDatabase
use EmployeeDatabase

Create Table EmployeeDetails
(
Name varchar(50),
Email varchar(50),
Salary varchar(30),
Department varchar (50)
)
select * from EmployeeDetails

INSERT INTO EmployeeDetails(Name, Email, Salary, Department)
VALUES ('ram', '[email protected]', '35000', 'IT');

Secondly, we will create a new project and choose console application in C# and give a name to the project as SqlConnectionDemo, and write the following code in our program.cs 

using System;
using System.Data.SqlClient;

namespace SqlConnectionDemo
{
    /// <summary>
    ///ADO.NET is an object oriented libraries that allows us to interact with database and we weill learn how to connect with sqldatabase using ADO.NET
    /// </summary>
    class Program
    {
        public void SelectData()
        {
            // Create object(instance) of sql connection which will provide a connection to the data source(database) to read and write database
            SqlConnection cn = new SqlConnection(@"data source=DESKTOP-6DF0CN3\SQLEXPRESS; Database=EmployeeDatabase; integrated security = true");
            // Sql command allow data adpeters to read, add, update and delete the records in a data source.
            // Data adapters support 4 properties that give us access to these objects - select, insert, update adn delete for creating queries to database.
            // Sqlcommand perform a query on the EmployeeDetails
            SqlCommand cmd = new SqlCommand("select * from EmployeeDetails", cn);
            // Open the connection by calling open() method of sqlconnection object cn
            cn.Open();
            //holds a read only, forward only set of data from a database.
            //using a sql Data Reader can increase speed because only one row of data is in memory at a time.
            // result set is returned as SqlDataReader
            SqlDataReader rdr = cmd.ExecuteReader();
            // While Loop Read the first column from each row of result set which is employee name
            while (rdr.Read())
    {
                // Print employee details
         Console.WriteLine(rdr[0] + "  " + rdr[1] + "  " + rdr[2]+ "  " + rdr[3]);
    }
            // Close the reader by calling CLose() method
            rdr.Close();
            // Close the connection by calling close() method
            cn.Close();
        }
        static void Main(string[] args)
        {
            // Create the object of Program class P
            Program p = new Program();
            // invoke the SelectData () Method
            p.SelectData();
            Console.ReadLine();
        }
    }
}

Output

ram [email protected] 35000 IT