Working on SQL Queries in C#

Your sample makes no sense. You query two times for the same table. In the first query you get back all the column from the Student table and then you use only the ID and Name fields, in the second one you get back just the student name, but this was already available in the first query

Just one query could be enough (and apply the using statement to properly close and dispose the objects involved)

string conString = ".....";
using(SqlConnection con = new SqlConnection(conString))
using(SqlCommand cmd = new SqlCommand("Select ID, Name from Student", con))
{
   con.Open();
   using( SqlDataReader dr = cmd.ExecuteReader())
   while (dr.Read())
   {
        Console.WriteLine("Id is:"+dr[0]+" Name is:"+ dr[1]);
   }
}

If you want to execute two queries in the same time, you could append the two queries to the same command separating them with a semicolon

using(SqlCommand cmd = new SqlCommand("Select ID, Name from Student;" + 
                                      "Select CourseID, CourseName from Course", con))

In this example you get back two set of records, one for the Student table and one for Course table. When you call the ExecuteReader, the readear is positioned on the first result set (the Student list), but you enclose this loop in a do/while block that will control the switch to the second result set (the Course) when the first has been totally read

using(SqlDataReader reader = cmd.ExecuteReader())
{
    do
    {
        // First time reads the student, when finished, the NextResult call switch the reader
        // on the second set and then exits (because there are no more result sets)
        while(reader.Read())
        {

        }
    }while(reader.NextResult());

You already have name in your first query, so why use second query to fetch name.

Few general points :

• Acquire connection as late as possible, and, close them early.

• Re-usable connection instead of opening a new connection every time a connection request to the database is made by the application.

• When you are specifying the connection string, ensure that you specify the IP address of the database server to connect to, rather than the Database Server’s DNS name.

Credit goes to

 static void Main(string[] args)
        {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\MUHAMMAD\Documents\samEE.mdf;Integrated Security=True;Connect Timeout=30";
            SqlCommand cmd = new SqlCommand("Select Id,Name from Student", con);
            con.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Console.WriteLine("Id is:"+dr[0]+" Name is:"+ dr[1]);

            }
            con.Close();
            con.Dispose();  
            Console.ReadKey();
        }
           catch (Exception ex)
          {
           Response.Write("Error: " + ex.Message);
           }
        finally
          {
            if(con.State == ConnectionState.Open)
             con.Close();
            }

        }

Also check this whether the ADO.NET DataReader or the DataSet is the better tool.

Try this..

static void Main(string[] args)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\MUHAMMAD\Documents\samEE.mdf;Integrated Security=True;Connect Timeout=30";

    con.Open();

    SqlCommand cmd = new SqlCommand("Select * from Student", con);
    SqlDataReader dr;
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        Console.WriteLine("Id is:"+dr[0]+" Name is:"+ dr[1]);
    }
    dr.Close();

    //con.Close();
    //SqlConnection con2 = new SqlConnection();
    //con2.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\MUHAMMAD\Documents\samEE.mdf;Integrated Security=True;Connect Timeout=30";

    cmd = new SqlCommand("Select Name from Student", con);
    //con2.Open();
    SqlDataReader dr2;
    dr2 = cmd.ExecuteReader();
    while (dr2.Read())
    {
        Console.WriteLine("Name is :"+ dr2[0]);
    }    
    dr2.Close();

    con.Close();
    Console.ReadKey();
}

You can pass multiple queries to a single SqlCommand separated by semicolon. Then you can iterate through the data reader like you currently do and at the end request the next query result via dr.NextResult method.

Here's a basic code sample

SqlCommand cmd = new SqlCommand("SELECT Something1 FROM Table1;SELECT Something2 FROM Table2" , con)
con.Open();
SqlDataReader dr = cmd.ExecuteReader();

do {
  while (dr.Read())
  {
      // do stuff with results
  }
} while (dr.NextResult())

Outer loop iterates thru query results while inner loop goes thru individual rows of current result.