2 posts, 0 answers
  1. Babak
    Babak avatar
    12 posts
    Member since:
    Jan 2012

    Posted 08 Dec 2012 Link to this post

    sometimes I need to return multiple and different value from a Store Procedure. For example I want to insert a user to database , before doing this I have to check the Username . I do this :

    ALTER PROCEDURE [dbo].[UserInsert] ( @UserName nvarchar(255) ,@UserPassword nvarchar(255) )
        IF ( (SELECT Count(UserId) from Users WHERE UserName = @UserName )= 0)
                INSERT INTO Users ( UserName , UserFamily , UserPassword ,UserLoginName,
                                    UserMobile,UserEmail,UserCredit, UserRole)
                                  ( @UserName , @UserFamily  , @UserPassword,@UserLoginName,
                RETURN 1;
        ELSE RETURN -2;
    I change your ExecuteNonQuery code to can return specific value from my StoreProcedure . I copy method to EntitiesModel.cs .
    this is my code :

    private OAConnection GetConnection(out bool isNew)
                IExtendedObjectScope scope = ((IExtendedObjectScope)GetScope());
                OAConnection connection = scope.GetConnection(out isNew);

                return connection;

    public int ExecuteNonQuery2(string commandText, System.Data.CommandType commandType, params System.Data.Common.DbParameter[] parameters)
                bool isNew = true;
                OAConnection connection = null;

                    connection = GetConnection(out isNew);
                    if (connection.State != System.Data.ConnectionState.Open)
                        throw new InvalidOperationException("You need an open connection to perform the requested operation");

                    using (OACommand command = connection.CreateCommand())
                        command.CommandText = commandText;
                        command.CommandType = commandType;
                        if (parameters != null && parameters.Length != 0)
                        OAParameter returnValue = new OAParameter("returnVal", SqlDbType.Int);
                        returnValue.Direction = ParameterDirection.ReturnValue;


                        return Convert.ToInt32(returnValue.Value);
                    if (isNew && connection != null)

    but I have some Problem.
    each time I rebuild the Entities all my code cleared and I have to paste it again and ...
    I don't know if there are other way for doing this.
    if there is please help me .
    thanks a lot .
  2. Dimitar Tachev
    Dimitar Tachev avatar
    67 posts

    Posted 12 Dec 2012 Link to this post

    Hi Babak,

    In general the ExecuteNonQuery method is returning the number of the affected rows. In order to get the return values of your stored procedure you could add a return parameter.

    For your convenience I prepared the following code snipped demonstrating that approach:
    using (EntitiesModel context = new EntitiesModel())
        OAParameter UserName = new OAParameter();
        UserName.ParameterName = "@UserName";
        UserName.Value = "someUserName";

        OAParameter UserPassword = new OAParameter();
        UserPassword.ParameterName = "@UserPassword";
        UserPassword.Value = "somePassword";

        OAParameter returnValue = new OAParameter();
        returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
        returnValue.DbType = System.Data.DbType.Int32;
        int rowsAffected = context.ExecuteNonQuery( "[dbo].[UserInsert]",
                       System.Data.CommandType.StoredProcedure, UserName,                                UserPassword, returnValue);

        context.SaveChanges(); // commit the transaction
        // the number of affected rows (-1 if now rows affected)
         // the return value (in your case 1 or -2)

    If that is not applicable for you and you still need to use your custom ExecuteNonQuery method you should add it in a partial class. 

    In order to do that you have to create a separated file (e.g. EntitiesModel.partial.cs) in the same project as your domain model, create a partial class with the same name as the class that you want to extend in the same namespace (e.g. public partial class EntitiesModel) and add your custom method there.

    This is necessary because the domain classes are auto-generated and they will regenerate on each save of your rlinq file which is the reason why you are losing your code on each rebuild.

    You could find more information about how to extend the auto-generated classes by adding new methods or non-persistent properties in this documentation section

    I hope this helps.

    Kind regards,

    Dimitar Tachev
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
Back to Top