I have a stored procedure query that I want to return the details of columns within a specific table. When I run the sp in Sql server managements studio I get results returned. The problem is when I try to execute the query from C# code. There are no exceptions thrown and the relevant persmissions are granted to execute the procedure in the database but no results are returned to the code. I'm using the enterprise application block version 3.1.
This is my query
SELECT SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,
SysColumns.[Length] As Length
FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = 'U'
AND SysObjects.[Name] = 'MyTableName'
ORDER BY SysObjects.[Name]
C# Calling code
using (DbCommand dbCommand = db.GetStoredProcCommand("StoredProcedureName"))
{
DataSet data = new DataSet();
db.LoadDataSet(dbCommand, data, "MyTableName");
if (data.Tables.Count > 0 && data.Tables[0].Rows.Count > 0)
{
// Do stuff with the returned data
}
}