ExecuteNonQuery2

hi.
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) )
AS
BEGIN
    IF ( (SELECT Count(UserId) from Users WHERE UserName = @UserName )= 0)
        BEGIN
            INSERT INTO Users ( UserName , UserFamily , UserPassword ,UserLoginName,
                                UserMobile,UserEmail,UserCredit, UserRole)
                               VALUES
                              ( @UserName , @UserFamily  , @UserPassword,@UserLoginName,
                                @UserMobile,@UserEmail,@UserCredit,'User')
                                
            RETURN 1;
        END
    ELSE RETURN -2;
END
--------------------------------------------
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;

            try
            {
                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)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    OAParameter returnValue = new OAParameter("returnVal", SqlDbType.Int);
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    command.Parameters.Add(returnValue);

                    command.ExecuteNonQuery();
                    command.Parameters.Clear();

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

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 .

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:

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