Select a data from MS SQL Server 2005

Question

I have a table named t_Student in Microsoft SQL Server 2005 database. In that table there are three columns named student_regiNo, student_Name, student_Email.

I'm using following code segment to retrieve "student_Name". But instead of showing "student_Name" it shows "System.Data.SqlClient.SqlDataReader". Whats the problem?

    private void GetDatabaseConnection()
    {
        string connectionString = @"server=RZS-F839AD139AA\SQLEXPRESS; Integrated Security = SSPI; database = StudentCourseInformation";
        connection = new SqlConnection(connectionString);
        connection.Open();
    }


    public string GateStudentName(string selectedStudentRegiNo)
    {
        GetDatabaseConnection();

        string selectedStudentQuery = @"SELECT student_Name FROM t_Student WHERE (
                                      student_regiNo = 
                                      '" +selectedStudentRegiNo+ @"'  
                                       )";
        SqlCommand command = new SqlCommand(selectedStudentQuery, connection);
        SqlDataReader reader = command.ExecuteReader();

        string selectedStudentName = Convert.ToString(reader);
        return selectedStudentName;
    }

Answer 1

ExecuteReader returns a SqlDataReader. You need to use the SqlDataReader API to read the data from it. Don't forget that a query can return multiple rows, wit multiple columns in each row. For example:

while (reader.Read())
{
    string name = reader.GetString(0);
    Console.WriteLine("Read name: {0}", name);
}

Further note that you should use a parameterized query rather than including the ID directly into the SQL - otherwise you leave yourself open to SQL injection attacks. See the docs for SqlCommand.Parameters for more information.

Finally, you should use using statements for the SqlConnection, SqlCommand and SqlDataReader so that you dispose of them appropriately. Otherwise you're going to leak database connections.

Answer 2

if (reader.Read())
{
     string selectedStudentName = reader.GetString(0);
}

Answer 3

Use

return (string)command.ExecuteScalar();

as far as you have to return "the first column of the first row in the result set returned by the query" (from MSDN)

Also use parametrized query:

var command = new connection.CreateCommand()
command.CommandText = "SELECT student_Name FROM t_Student WHERE student_regiNo = @number";
command.Parameters.AddWithValue(@number, selectedStudentRegiNo);