Write code to retrieve data from SQL database in C# asp net

Here are the steps to connect SQL database server, access database and retrieve data from a table programmatically in C# asp net.

Steps:

• Create an object of SqlConnection class
• Prepare connection string for SqlConnection class object.
• Create object of SqlCommand class and prepare sql command with types, queries and sql connection object.
• Open sql connection
• Execute sql query with cmd.ExecuteReader();
• Retrieve the data usig SqlDataReader object
• Close the database connection.

Connect to SQL Server database example in C#

using System;
using System.Data.SqlClient;
using System.Data;

namespace AccessingDatabase
{
    class Program
    {
        static void Main(string[] args)
        {     

            //Create the object of SqlConnection class to connect with database sql server
           using( SqlConnection conn=new SqlConnection())
           {
               //prepare conectio string
               conn.ConnectionString = "server=ACER; database=BankingTransactions; Integrated Security=True";
               
               try
               {
                 
                   //Prepare SQL command that we want to query
                   SqlCommand cmd = new SqlCommand();
                   cmd.CommandType = CommandType.Text;
                   // cmd.CommandText = "SELECT * FROM MYTABLE";
                   cmd.CommandText = "SELECT id FROM UserRegistration";  
                   cmd.Connection = conn;

                   // open database connection.
                   conn.Open();
                   
                   Console.WriteLine("Connection Open ! ");

                   //Execute the query 
                 SqlDataReader sdr=  cmd.ExecuteReader();

                 ////Retrieve data from table and Display result
                   while(sdr.Read())
                   {
                       int id = (int)sdr["id"];
                       Console.WriteLine(id);
                   }
                   //Close the connection
                   conn.Close();
               }
               catch (Exception ex)
               {
                   Console.WriteLine("Can not open connection !");
                  
               }
               
           }
        }
    }
}