C# sql stored procedure set resultset's tablenames

Question

I'm running stored procedures by name in .net, and returning the data as JSON to my frontend

If my stored procedure is

SELECT name from employees

I get back a table with tablename = "Table1"

So my JSON is {Table1 : [ {row1stuff},{row2stuff}]}

I'd like to define the tablenames in my procedures.

Right now I'm doing

SELECT 'names'
SELECT name from employees

and in my c# I run through the first tables values and name the other tables

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt); //dt is a dataset
//start at 1 since the first table is the tablenames table
for (int i = 1; i < dt.Tables.Count; i++)
  dt.Tables[i].TableName = dt.Tables[0].Rows[0].ItemArray[i-1].ToString();
//get rid of the first table
dt.Tables.RemoveAt(0);
return JObject.FromObject(dt);

It's working now, and my json is {names:[{row1},{row2}...]}

It'd be great if I could do

SELECT name from employees as names

(but this won't set the table name in what the sp returns, just sets it for the sql)

Just to be clear, some of my stored procedures are returning multiple result sets so I do

SELECT 'Employees','Cars','Websites'
SELECT * FROM employees
SELECT * FROM cars
SELECT * from sites

so I get back json

{Employees:[{emp1},{emp2}...], Cars:[{car1}], Websites:[{site1}, {site2}]}

Which is perfect, but makes the SP weird how I have the select at the top.. just not super obvious when you're just looking at the sp

No information about the names of queried tables is passed back from a stored procedure, what would it return for a select from *n* joined tables? The `Table1` is an artifact of the .net `DataTable` class. An output parameter would be an alternative to a `SELECT`. — Alex K., Aug 11 '15 at 14:55
@AlexK. I wasn't wanting it to decide the tablenames from the tables I was querying, it'd be something I'd have to type (so joins wouldn't matter). The "Table1 is an artifact..." is what I was afraid of :( I was using an output param, but it was a bigger hassle in the SP than doing the SELECT. Thanks though! - sometimes it's nice to hear it can't be done so you stop worrying about it. — Charlie Wynn, Aug 11 '15 at 15:36

granadaCoder · Answer 1 · 2019-04-25T17:19:49.170

1

https://msdn.microsoft.com/en-us/library/Bb748727.aspx?f=255&MSPPError=-2147217396

LoadDataSet has a parameter for naming the tables.

(the below is pasted from the above URL in case the URL di3s in the future)

'Declaration
Public Overridable Sub LoadDataSet ( _
    storedProcedureName As String, _
    dataSetAs DataSet, _
    tableNames As String(), _
    ParamArray parameterValues As Object() _
)

tableNames
Type: array<System.String>

An array of table name mappings for the DataSet.

edited Apr 25 '19 at 17:19

answered Aug 11 '15 at 16:39

granadaCoder

26,328
10
113
146

I want to name the table in SQL, not in C# - sorry if that wasn't clear – Charlie Wynn Aug 11 '15 at 16:41
1

Then your "solution" is about as good as it gets. You have write select statement and your parse the it to populate the other datatable names. – granadaCoder Aug 11 '15 at 17:30

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

Some discussions has been referred here.. Set dbo.Table name as DataTable Name.. and yeah, you could only do this in C# through TableMapping or something as clever as you did. :)

edited May 23 '17 at 12:06

Community

1
1

answered Aug 11 '15 at 17:49

ken lacoste

894
8
22

C# sql stored procedure set resultset's tablenames

2 Answers2