How to get a value return form the db ?

Apr 11, 2008 at 5:05 PM
Hi,
This is the same post which i have posted in the WSSF Community.
since this is the community deals with the Reposiroty facory i am posting this here...please give me a solution.

Actually i wanna take the return value while inserting the UserDetails into the DB.
How can i get the return value.
My sp is shown below :

-----------------------------------------------------------------------------------------------------------------------------
Create PROCEDURE dbo.InsertUsers
@changedDate Datetime,
@createDate Datetime,
@domainName varchar(50),
@fullName varchar(50),
@lastUpdatedBy Datetime,
@phoneNumber bigint,
@userID int OUT,
@userName varchar(50)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
INSERT INTO dbo.Users (ChangedDate, CreateDate, DomainName, FullName, LastUpdatedBy, PhoneNumber, UserName)
VALUES (@changedDate, @createDate, @domainName, @fullName, @lastUpdatedBy, @phoneNumber, @userName)
SET @userID = SCOPE_IDENTITY()
END TRY

BEGIN CATCH
EXEC RethrowError;
END CATCH

SET NOCOUNT OFF
END

through this sp, i am getting the Value in @userID

But i am facing some problem in getting the values back to the action class.

-----------------------------------------------------------------------------------------------------------------------------
here blow i am providing a piece of code, from the UserInsertFactory :


public DbCommand ConstructInsertCommand(Database db, Users users)
{
DbCommand command = db.GetStoredProcCommand("dbo.InsertUsers");

if (users.ChangedDate != null)
{
db.AddInParameter(command, "changedDate", DbType.String, users.ChangedDate);
}
if (users.CreateDate != null)
{
db.AddInParameter(command, "createDate", DbType.String, users.CreateDate);
}
if (users.DomainName != null)
{
db.AddInParameter(command, "domainName", DbType.String, users.DomainName);
}
if (users.FullName != null)
{
db.AddInParameter(command, "fullName", DbType.String, users.FullName);
}
if (users.LastUpdatedBy != null)
{
db.AddInParameter(command, "lastUpdatedBy", DbType.String, users.LastUpdatedBy);
}
db.AddInParameter(command, "phoneNumber", DbType.Int64, users.PhoneNumber);
db.AddOutParameter(command, "userID", DbType.Int32, 4);
if (users.UserName != null)
{
db.AddInParameter(command, "userName", DbType.String, users.UserName);
}
return command;
}
-----------------------------------------------------------------------------------------------------------------------------
this is the method from my Repository class :

public void Add(Users users)
{
UsersInsertFactory insertFactory = new UsersInsertFactory();
try
{
base.Add(insertFactory, users);
}
catch (SqlException ex)
{
HandleSqlException(ex, insertFactory);
}
}

-----------------------------------------------------------------------------------------------------------------------------


using this i know that i cant return a value, since the return typr is void.

But i f i have to change the return type, where all should i change ?
should i change the return type in the repository factory(i mean for all repository based classes there is a common repository under Generic Folder)..


/// <summary>
/// Insert the given object into the database.
/// </summary>
/// <param name="insertFactory">Factory used to create the command.</param>
/// <param name="domainObj">Domain object to insert</param>
public void Add( IInsertFactory< TDomainObject > insertFactory,
TDomainObject domainObj )
{
using(DbCommand command = insertFactory.ConstructInsertCommand(db, domainObj))
{
db.ExecuteNonQuery(command);
insertFactory.SetNewID(db, command, domainObj);
}
}


well, i am bit confused about seeing all these codes, this is the first time i am goign into the respositoy classes which are auto generated...


If thers is any other way, please tell me....
really i am stuck up with this! this is a game of life and death...
pls...

nimi
Apr 12, 2008 at 5:02 PM
Hi Nimi, try generating a repository factory add crud sp, and see how it's handled, I really don't understand your problem, if you give me more info...
JPS
Apr 13, 2008 at 7:04 AM
well,
in brief.... i wanna take the return value after inserting userdetails, return value in the sense userID.
and that value i have to use in the BL.


nimi
Apr 14, 2008 at 5:48 AM
this issue is solved..........
May 15, 2009 at 4:05 AM

I have the same problem. Can you tell m how to sovle it ?

 

thanks.

Apr 21, 2010 at 7:20 PM

You need to add code to the UsersInsertFactory.SetNewID() method:

        public void SetNewID(Database db, DbCommand command, Users users)
        {
            object value = command.Parameters["userId"].Value;
            users.Id = (value == DBNull.Value) ? -1 : Convert.ToInt64(value);
            if (users.Id < 0)
            {
                throw new RepositoryException("Invalid new ID returned");
            }
        }