Processing the results of FOR XML PATH stored procedure c#.NET 3.5

Question

I want to call a stored procedure in SQL Server 2005 that returns an XML string that can then be passed into another method; I think the below code is right up until the point where I execute the query, but then I get a bit lost...

private string GetChartData(string  OC_Ttl1, string OC_OL31, string OC_OL32)
    {
        string chartData;
        //Prepare Connection Variables
        SqlConnection conn_Org  = new SqlConnection();
        SqlCommand cmd_Org = new SqlCommand();


        //Open Connection
        conn_Org.ConnectionString = Set_OrgChartConn();
        conn_Org.Open();

        //Execute Procedure
        cmd_Org.Connection = conn_Org;
        cmd_Org.CommandText = "dbo.usp_CreateOrgDataSet '" + OC_Ttl1 +"','" + OC_OL31 + "'.'" + OC_OL32 +"'";
        cmd_Org.CommandType = CommandType.StoredProcedure;

        chartData = cmd_Org.ExecuteScalar();

        conn_Org.Close();

        return chartData;
    }

Any ideas?

Answer 1

Can you not just call cmd_Org.ExecuteScalar()?

Answer 2

cmd_Org.CommandText = "dbo.usp_CreateOrgDataSet '" + OC_Ttl1 +"','" + OC_OL31 + "'.'" + OC_OL32 +"'";
        cmd_Org.CommandType = CommandType.StoredProcedure;

The above line may cause error. If you set

cmd_Org.CommandType = CommandType.StoredProcedure; and "dbo.usp_CreateOrgDataSet '" + OC_Ttl1 +"','" + OC_OL31 + "'.'" + OC_OL32 +"'"

as CommandText, then the CommandText will be treated as storeproc name and the error 'Could not find stored procedure [dbo.usp_CreateOrgDataSet ....] will occur.

You can set only "dbo.usp_CreateOrgDataSet" as commandtext and pass the parameters by e.g.

mycommand.Parameters.AddWithValue('@param1', OC_Ttl1);
 mycommand.Parameters.AddWithValue('@param2',  OC_OL31);
 mycommand.Parameters.AddWithValue('@param3',OC_OL32 );
 cmd_Org.CommandType = CommandType.StoredProcedure;
 chartData = cmd_Org.ExecuteScalar();