CODEWITHSUNDEEP
c#sql-serverstored-procedures
Charlie Wynn
Charlie Wynn

Reputation: 933

C# sql stored procedure set resultset's tablenames

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

Upvotes: 1

Views: 194

Answers (2)

granadaCoder
granadaCoder

Reputation: 27842

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.

Upvotes: 1

ken lacoste
ken lacoste

Reputation: 894

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. :)

Upvotes: 1

Related Questions