none
how to get @Return_Value of a stored procedure from Auto-Generated DataSet RRS feed

  • Question

  • In an auto-generated data set there is a public class ( QueriesTableAdpater ) which maps stored procedures of data source to my application. This has a protected property ( CommandsCollection ) which gets parameters of stored procedures. I want to use this property to retrieve “@Return_Value” parameter of a procedure. Something like this:

    int myVar=CommandCollection[4].Parameters[0].Value;

    now I don’t know where should I put my code or is this a correct way to get the return value of a procedure.

    Because it is protected property I can’t use it any where I want.





    • Edited by mortaza.mkr Saturday, June 9, 2012 12:36 PM
    Thursday, June 7, 2012 1:32 PM

Answers

All replies

  • Be aware that an update from a dataset can process needs three sql commands.

    An insert command, an update command, an delete command which are done in a not by you controlled way.

    I'm searching to the logical way you want to get from all those returned?

    AFAIK There are no  properties returnvalues from done commands.


    Success
    Cor

    Friday, June 8, 2012 7:10 AM
  • I think it will be a simple process.I only need to retrive an OUTPUT parameter of a stored procedure. I think I have to accomplish it this way. Is it correct way? if not how should I do this? Thanks.
    Friday, June 8, 2012 9:52 AM
  • this is my stored procedure : ( the idenntity column calculated automatically by serever )

    ALTER PROCEDURE dbo.InsertPardakht
    (@PardakhtType bit,@StudentNumber nchar(10),@MablaghePardakht decimal(18,0),@SaleTahsili nchar(5))
    AS
    	INSERT INTO Pardakht
    	                         (PardakhtType, StudentNumber, MablaghePardakht, SaleTahsili)
    	VALUES        (@PardakhtType,@StudentNumber,@MablaghePardakht,@SaleTahsili)
    	 SET NOCOUNT OFF
    	RETURN scope_identity()

    and I want to capture the scope_identity() value in my application which is returned by the procedure.
    now I'm  using data set created using wizard and i think i do't have access to the command object because it is defined protected ( am I right )
    this the bit of code of my QueriesTableAdapter class

      public partial class QueriesTableAdapter : global::System.ComponentModel.Component {
            
            private global::System.Data.IDbCommand[] _commandCollection;
            
            [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
            [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
            protected global::System.Data.IDbCommand[] CommandCollection {
                get {
                    if ((this._commandCollection == null)) {
                        this.InitCommandCollection();
                    }
                    return this._commandCollection;
                }
            }
    
    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
            [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
            private void InitCommandCollection() {
                this._commandCollection = new global::System.Data.IDbCommand[11];
    
    
     this._commandCollection[4] = new global::System.Data.SqlClient.SqlCommand();
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Connection = new global::System.Data.SqlClient.SqlConnection(global::DaftarMoien.Properties.Settings.Default.DaftarMoeinSQLDBConnectionString);
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).CommandText = "dbo.InsertShahriye";
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).CommandType = global::System.Data.CommandType.StoredProcedure;
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@StudentNumber", global::System.Data.SqlDbType.NChar, 10, global::System.Data.ParameterDirection.Input, 0, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SaleTahsili", global::System.Data.SqlDbType.NChar, 5, global::System.Data.ParameterDirection.Input, 0, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@MablagheShahriye", global::System.Data.SqlDbType.Money, 8, global::System.Data.ParameterDirection.Input, 19, 4, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@MizaneTakhfif", global::System.Data.SqlDbType.Money, 8, global::System.Data.ParameterDirection.Input, 19, 4, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                ((global::System.Data.SqlClient.SqlCommand)(this._commandCollection[4])).Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@MajmoePardakhti", global::System.Data.SqlDbType.Money, 8, global::System.Data.ParameterDirection.Input, 19, 4, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                
    
    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
            [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
            [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
            public virtual int InsertPardakht(global::System.Nullable<bool> PardakhtType, string StudentNumber, global::System.Nullable<decimal> MablaghePardakht, string SaleTahsili) {
                global::System.Data.SqlClient.SqlCommand command = ((global::System.Data.SqlClient.SqlCommand)(this.CommandCollection[3]));
                if ((PardakhtType.HasValue == true)) {
                    command.Parameters[1].Value = ((bool)(PardakhtType.Value));
                }
                else {
                    command.Parameters[1].Value = global::System.DBNull.Value;
                }
                if ((StudentNumber == null)) {
                    command.Parameters[2].Value = global::System.DBNull.Value;
                }
                else {
                    command.Parameters[2].Value = ((string)(StudentNumber));
                }
                if ((MablaghePardakht.HasValue == true)) {
                    command.Parameters[3].Value = ((decimal)(MablaghePardakht.Value));
                }
                else {
                    command.Parameters[3].Value = global::System.DBNull.Value;
                }
                if ((SaleTahsili == null)) {
                    command.Parameters[4].Value = global::System.DBNull.Value;
                }
                else {
                    command.Parameters[4].Value = ((string)(SaleTahsili));
                }
                global::System.Data.ConnectionState previousConnectionState = command.Connection.State;
                if (((command.Connection.State & global::System.Data.ConnectionState.Open) 
                            != global::System.Data.ConnectionState.Open)) {
                    command.Connection.Open();
                }
                int returnValue;
                try {
                    returnValue = command.ExecuteNonQuery();
                }
                finally {
                    if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
                        command.Connection.Close();
                    }
                }
                return returnValue;
            }
            
    


    • Edited by mortaza.mkr Saturday, June 9, 2012 12:39 PM
    Saturday, June 9, 2012 12:34 PM
  • Instead of returning SCOPE_IDENTITY() from the Stored Procedure, set the PrimaryKey parameter to be OUTPUT, and set that parameter to the SCOPE_IDENTITY(), then get the value of the parameter in your code (in your code, that parameter also needs to have the .Direction set to ParameterDirection.InputOutput).

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Saturday, June 9, 2012 3:16 PM
    Saturday, June 9, 2012 3:06 PM
  • my stored procedure works correct and it returns exactly what i need the problem is i don't know how to get that value in my application.for example i don't know where should i place code ( ParameterDirection.InputOutput ).
    Sunday, June 10, 2012 7:09 AM
    • Proposed as answer by TinMgAye Monday, June 11, 2012 1:45 AM
    • Marked as answer by mortaza.mkr Monday, June 11, 2012 6:18 PM
    Sunday, June 10, 2012 10:17 AM
  • @tinmgaye -- I wasn't aware of the ParameterDirection.ReturnValue! I guess because I've never utilized my Stored Procs in that manner (I also don't use TableAdapters, so I'm free to implement my data access any way I want to. LOL)

    @mortaza -- the link provided by @tinmgaye should do the trick for you. It involves creating a partial class of your TableAdapter, which is a good idea.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, June 10, 2012 4:10 PM
  • Morza,

    But if you use one stored procedure in a TableAdapter update, then it goes wrong. You cannot do that, you need 3 stored procedures for that. That you call it simple makes if very complex.


    Success
    Cor



    Monday, June 11, 2012 6:21 AM