Set DataTable name in returned data set

15 pts.
iSeries SQL
SQL stored procedures
I have a SQL stored procedure with simple select statements that returns multiple datatables within the dataset.  The stored procedure is being called from C#/.NET code. 

The stored procedure is returning the data but the datatable names end up Table, Table1, Table2,...  when the dataset is received on the .NET side.  Is there a way to define the datatable name within the stored procedure that will be used in the dataset and seen on the .NET side? 

Software/Hardware used:
iSeries, SQL, C#, .NET

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can alias the table names. Do you have an example I can see? thank you.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • LordPerrin
    This is just a couple of the result sets we are returning. What it does is pulls data from mutiple tables and returns them for use in dropdowns on the various pages so it is only done once. C1 comes across as Table C2 comes across as Table1 What I need is to define them like this C1 as AccountType C2 as AdjustmentCodes I know they can be renamed once they are in C# code but then I will need to coordinate changes if we add or remove result sets later on. Thanks for the help. Create procedure DropDown ( In Library char(10)) Language SQL Reads SQL Data Result Sets 31 Specific DropDown NOT DETERMINISTIC Begin Declare SqlStatement VarChar(250); Declare C1 Cursor with return for S1; Declare C2 Cursor with return for S2; -- Account Type Set SqlStatement = 'Select AYACTT As AccountTypeCode, AYDSAT As AccountTypeDesc' || ' From ' || Trim(Library) || '/ACTTYP' ||' Where AYACTT not in ('' '',''*'')' || ' Order by AyActT for read only'; Prepare S1 from SqlStatement; -- Adjustment Codes Set SqlStatement = 'Select AJADJ@ As AdjustmentCode, AJDSAJ As AdjustmentDesc ' ||' From ' || Trim(Library) || '/ADJCOD ' ||' Order By AJADJ@ for read only'; Prepare S2 from SqlStatement; Open C1; Open C2; return end
    15 pointsBadges:
  • magnumx
    First, I must explain how TableMapping works. If we don't specify TableMappings with SqlDataAdapter (SqlDataAdapter that will fill our DataSet) then by default first table will be named "Table", second will be named "Table1", third will be named "Table2" etc.

    So, when we want to name DataTable in DataSet, we use it like this:


    System.Data.DataSet myDataSet = new System.Data.DataSet();

    using (System.Data.SqlClient.SqlDataAdapter dbAdapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand))
        dbAdapter.TableMappings.Add("Table", "MyFirstTitleForTableOne");
        dbAdapter.TableMappings.Add("Table1", "MyFirstTitleForTableTwo");
        dbAdapter.TableMappings.Add("Table2", "MyFirstTitleForTableThree");
        dbAdapter.TableMappings.Add("Table3", "MyFirstTitleForTableFour");


    And, now we can access DataTable by our title:

    System.Data.DataTable firstTable = myDataSet.Tables["MyFirstTitleForTableOne"];
    System.Data.DataTable secondTable = myDataSet.Tables["MyFirstTitleForTableTwo"];

    Sorry, but I didn't write code that will check for null (myDataSet) or set it in try/catch block because I think that is not irrelevant to this question.
    25 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: