C# db sample code throws "Connection property has not been initialized"

Question

I've installed sql server db on my machine, and visual studio "server explorer" can connect to it, and the connection string is provided in "property", I then use this string to write my C# code as below:

SqlConnection conn = new SqlConnection(
    "Data Source=MININT-EP12N1V;Initial Catalog=EmployeeDB;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("select FirstName, LastName from Employees");
//above sql can be executed in sql server management studio successfully.
SqlDataReader reader = cmd.ExecuteReader();//throws exception
while (reader.Read())
{
    Console.Write("{1}, {0}", reader.GetString(0), reader.GetString(1));
}
reader.Close();
cmd.Dispose();
conn.Close();

It throws out exception:

Unhandled Exception: System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

So how to fix it?

Answer 1

You are not passing the SqlConnection to the SqlCommand which is required as command does not know that on which server it needs to execute the query unless you provide the connection details.

Try :

SqlCommand cmd = new SqlCommand("select FirstName, LastName from Employees",conn);

You can always see the examples provided in the docs.

You can see in the docs example the approach to do. you should wrap connection in using so that it gets disposed off as soon as the code block exits :

using(SqlConnection conn = new SqlConnection(
    "Data Source=MININT-EP12N1V;Initial Catalog=EmployeeDB;Integrated Security=True"))
{

    SqlCommand cmd = new SqlCommand("select FirstName, LastName from Employees");
    SqlDataReader reader = cmd.ExecuteReader();
    conn.Open();
    while (reader.Read())
    {
        Console.Write("{1}, {0}", reader.GetString(0), reader.GetString(1));
    }
    reader.Close();
    cmd.Dispose();
}