ADO.NET Results From Stored Procedure as String

Question

I am eliminating the use of a really slow web service by connecting to the database directly via ADO.NET. The SP I am executing returns the results as XML (For XML Auto) and I am looking to store the response as a string. Would ExecuteScaler work or is their a better method of doing this.

SqlCommand cmd = new SqlCommand("iweb_spx_controls_attributes", sqlConn);
cmd.Parameters.Add(new SqlParameter("@page_idASPX", pageID));
cmd.Parameters.Add(new SqlParameter("@user_id", userID));
cmd.CommandType = CommandType.StoredProcedure;

string reSults = (string)cmd.ExecuteScalar();
return reSults; 

Answer 1

This goes into some detail on your question and may be helpful.

ExecuteScalar vs ExecuteNonQuery when returning an identity value

You can also search execute vs in google and see the auto suggestions it gives you.

Answer 2

An alternative is to use the SqlCommand.ExecuteXmlReader() method, which gives you more control over how you can interpret your result set (and would probably perform better if you have a large Xml blob to send back).