Give name to table from stored procedure
-
I've built a fairly complex stored procedure that has a couple of selects in it that are only functional while I'm only interested in reading the resultset from the last select. Now when I fill my dataset, apart from retrieving useless data from the database, I have to acess my data doing something like ds.Tables[2].Rows I have thought of two possible solutions even though I don't know how to implement any of them. 1. Have the stored procedure return only the last recordset. If this is possible I'm missing some T-SQL command. Anybody on this? 2. Give a name to the table that I'm interested in reading so that I could access it disregarding it's index like ds.Tables["MyRecordset"].Rows This is much more elegant than accessing with numeric index bacause if I change the stored procedure I don't have to change my code. Anyway how does one give a name to a table from a stored procedure? Thanks Edd
-
I've built a fairly complex stored procedure that has a couple of selects in it that are only functional while I'm only interested in reading the resultset from the last select. Now when I fill my dataset, apart from retrieving useless data from the database, I have to acess my data doing something like ds.Tables[2].Rows I have thought of two possible solutions even though I don't know how to implement any of them. 1. Have the stored procedure return only the last recordset. If this is possible I'm missing some T-SQL command. Anybody on this? 2. Give a name to the table that I'm interested in reading so that I could access it disregarding it's index like ds.Tables["MyRecordset"].Rows This is much more elegant than accessing with numeric index bacause if I change the stored procedure I don't have to change my code. Anyway how does one give a name to a table from a stored procedure? Thanks Edd
If you are using a DataAdapter to fill you dataset you can always create a strong typed dataset which allows you to access the data as: myDs.MyTableName[row].MyField; or dsMyStrongType.MyStrongTypeRow dr = myDs.MyTableName[row]; dr.MyField = something Makes life a lot easier and less apt to have typos on field names (plus you get Intellesense). Just build your DataAdapter in the editor and the right click on it to generate a dataset. If your stored procedure returns multiple results it will build a table by name for each. You can also select the table mapping property to call the tables anything you like. Really cool stuff. Rocky <>< www.HintsAndTips.com
-
I've built a fairly complex stored procedure that has a couple of selects in it that are only functional while I'm only interested in reading the resultset from the last select. Now when I fill my dataset, apart from retrieving useless data from the database, I have to acess my data doing something like ds.Tables[2].Rows I have thought of two possible solutions even though I don't know how to implement any of them. 1. Have the stored procedure return only the last recordset. If this is possible I'm missing some T-SQL command. Anybody on this? 2. Give a name to the table that I'm interested in reading so that I could access it disregarding it's index like ds.Tables["MyRecordset"].Rows This is much more elegant than accessing with numeric index bacause if I change the stored procedure I don't have to change my code. Anyway how does one give a name to a table from a stored procedure? Thanks Edd
Add an output parameter to the stored procedure and send the Table using it. Send me the query. Free your mind...
-
I've built a fairly complex stored procedure that has a couple of selects in it that are only functional while I'm only interested in reading the resultset from the last select. Now when I fill my dataset, apart from retrieving useless data from the database, I have to acess my data doing something like ds.Tables[2].Rows I have thought of two possible solutions even though I don't know how to implement any of them. 1. Have the stored procedure return only the last recordset. If this is possible I'm missing some T-SQL command. Anybody on this? 2. Give a name to the table that I'm interested in reading so that I could access it disregarding it's index like ds.Tables["MyRecordset"].Rows This is much more elegant than accessing with numeric index bacause if I change the stored procedure I don't have to change my code. Anyway how does one give a name to a table from a stored procedure? Thanks Edd
When using
DataSets, try designing a strongly-typedDataSetby using theDataSetdesigner in VS.NET (there are other ways and tools, too, but I present the way in VS.NET). Right-click on your project or subfolder and select Add New Item. FindDataSet, give it a name, and click OK. You'll see a component designer screen. Right-click to add elements (tables) and each element has rows and their types (the columns). You can even add relationships and primary keys. Just play around with it a little. You can even use the Server Explorer if you have a database connection to drag-n-drop tables or stored procedures which returns tables to generate these automatically. When you fill the strongly-typedDataSet, yourDbDataAdapterderivative (likeSqlDataAdapter) has to be configured to map the tables. This is done throughDbDataAdapter.TableMappingsand can easily be configured by using the data adapter designer in VS.NET as well. Finally, you don't even need to use strongly-typed if you use theTableMappingsproperty mentioned above. When selecting multiple result sets, the sequence of table names is "Table", "Table1", "Table2", etc. If you map these to other names, you can useDataSet.Tables["_nameOfTable_"]. I recommend using strongly-typedDataSets, though. You can easily refer to columns by name and don't have to worry about casting since those named rows return the appropriate Type.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----