I am trying to pull records from a db with an optional order by. For that I have created a stored proc (left out a few columns for readability):
CREATE PROCEDURE [dbo].[sp_VendorOverview] (@sortCol nvarchar(50)=NULL)
AS
BEGIN
SET NOCOUNT ON;
select v.Vid, s.Salutation, v.LastName, CONVERT(varchar(100), CAST(v.VAT AS decimal(38,0))) AS VAT
from vendors v
inner join Salutations s
on v.salutation=s.anrede
order by
CASE WHEN @sortCol='LastName' THEN v.LastName
WHEN @sortCol='FirstName' THEN v.FirstName
ELSE NULL
END,
CASE WHEN @sortCol ='VendorNumber' THEN v.VendorNumber
ELSE v.Vid
END
for xml path('VendorBasic'), root('Vendors')
END
When running this sp in SSMS, all is fine, results are as expected. Not so, however, when trying to read this from C# application like this:
var vendoren = new List<VendorBasic>();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["Vendor"].ConnectionString))
{
var xml = string.Empty;
con.Open();
using (var cmd = new SqlCommand("dbo.sp_VendorOverview", con))
{
if (!string.IsNullOrEmpty(orderby))
cmd.Parameters.AddWithValue("@sortCol", orderby);
using (XmlReader idr = cmd.ExecuteXmlReader())
{
if (idr.Read())
{
xml = idr.ReadOuterXml();
}
idr.Close();
}
con.Close();
}
if (xml != string.Empty)
{
XmlRootAttribute xRoot = new XmlRootAttribute
{
ElementName = "Vendors",
IsNullable = true
};
var engine = new XmlSerializer(typeof(List<VendorBasic>), xRoot);
vendoren = (List<VendorBasic>)engine.Deserialize(new StringReader(xml));
}
}
Deserialization works fine, XmlRoot attribute is set for the VendorBasic class. I DO get results. They are just never ordered by anything other than Vid. I have set a break point to check whether the parameter is correctly applied in case I want to order by any other column. It is:
Am I missing something? Am I doing something wrong?
You need to tell SqlCommand
that you are executing stored procedure and not arbitrary command, by doing
cmd.CommandType = CommandType.StoredProcedure;
Without that it will basically ignore all your parameters and execute procedure with default parameters (null
in this case). You might find more info about this in this question: When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?.