Getting "ExecuteXmlReader: Connection property has not been initialized" using Enterprise Library 5.0

Question

I have the following code:

public string GetClients()
    {
        string outputxml = string.Empty;
        Database db = DatabaseFactory.CreateDatabase("MyDatabase");
        SqlCommand sqlcmd = db.GetSqlStringCommand("SELECT CLIENTID, CLIENTNAME FROM [CLIENTS] FOR XML AUTO, ELEMENTS") as SqlCommand;
        using (XmlReader reader = sqlcmd.ExecuteXmlReader()) //Exception thrown in this line
        {
            while (reader.Read())
            {
                outputxml = reader.ReadOuterXml();
            }
            return outputxml;
        }
    }

This method is part of an exercise I'm doing as a part of WCF self-tutoring.

While invoking this method from a WCF client application, I get the error mentioned in the subject of this post at the spot marked above. The connection string to the database is specified in the config file for MyDatabase, and it is set as the Default database as well.

What am I doing incorrectly here? Thank you SOF community.

Answer 1

Have you tried to check the underlying connection state? You can also attempt to open it from there.

You have:

   SqlCommand sqlcmd = db.GetSqlStringCommand("SELE..") as SqlCommand

You can test the command with:

(sqlcmd.Connection.State == ConnectionState.Open)

You can also try opening the connection directly:

sqlcmd.Connection.Open(); 

Answer 2

Thanks @t3rse. I got what is wrong. Here's the same code rewritten:

 public string GetClients()    
{         
string outputxml = string.Empty;         
Database db = DatabaseFactory.CreateDatabase("MyDatabase");         
SqlCommand sqlcmd = db.GetSqlStringCommand("SELECT CLIENTID, CLIENTNAME FROM [CLIENTS] FOR XML AUTO, ELEMENTS") as SqlCommand;         
using (XmlReader reader = db.ExecuteXmlReader(sqlcmd)) 
{             
  while (reader.Read())             
  {                 
    outputxml = reader.ReadOuterXml();             
  }             
return outputxml;         
}
}