How to take the return value while inserting Details into DB ?

Topics: General Discussion Forum, July and December Releases Forum
Apr 10, 2008 at 4:18 PM
Hi,

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.


This is a piece of the 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);
}
}

can anybody please help me up with this !!!
Really i am stuck up with this.....
Only after getting a reply i can go further.....

Pls... hopefully,


nimi
Developer
Apr 10, 2008 at 10:53 PM
The issue here is that your are using the "Add or Insert" pattern and then you want a result back, where in this scenario you may not have return values. So I think that you need a slightly modifued version where the Add method returns an int or a response class with that UserID value.
The other approach may be using an "update" operation and then in the SP add the insert command. Again, that will require s imple change but you still need some twicks to get your required solution.
In my opinion I would go with the first approach, that is, updating the Add operation with a response that you will set in the caller of the 'ConstructInsertCommand' method after returning the Command with the output parameter.
Apr 11, 2008 at 5:19 AM
Thanks a lot.......
one more thign i would like to know is that...
i have 3 tables, among these three tables i wanna insert value to two tables in an operation.
Where i am facing some difficulty in understanding the technique,that is :
my tables are :

UserGoup
Users
UserGroupUsage.

This operation must perform when the Details of user is inserting into Db. Here the DC and BE for the three tables will be different.
my realtion here is 1 to many. that is one user can be related with more than one userGroups.
i can take the userGroups into the string[] from the client side.

Some of the fields that are avialable in one table may not be available in othe tables,
So here i am not understanding how can i do the transalation and all.
in this case, whether i have to add one more member called UserGroup into the Users DC and BE;


am i confusing ?....

Hope for the best,
Nimi
Apr 11, 2008 at 6:21 AM

charlyfriend wrote:
The issue here is that your are using the "Add or Insert" pattern and then you want a result back, where in this scenario you may not have return values. So I think that you need a slightly modifued version where the Add method returns an int or a response class with that UserID value.
The other approach may be using an "update" operation and then in the SP add the insert command. Again, that will require s imple change but you still need some twicks to get your required solution.
In my opinion I would go with the first approach, that is, updating the Add operation with a response that you will set in the caller of the 'ConstructInsertCommand' method after returning the Command with the output parameter.



well, while doing again i got confused and one after the other i got many bugs, i changed some return type and all...
here along with this i can provide you some of my codes, so that you can really guess out what i have to change,,

This is the Class in my BL

internal void Insertusers(Users users)
{
UsersRepository Repository = new UsersRepository();
UserGroupUsageRepository UGURepository = new UserGroupUsageRepository();
UserGroupRepository UGRepository=new UserGroupRepository();
UserGroupUsage userGroupUsage = new UserGroupUsage();
if (Repository.GetUserByUserName(users.UserName) != null)
{
users.LastUpdatedBy = DateTime.Now;
users.CreateDate = DateTime.Today.Date;
users.ChangedDate = DateTime.Today.Date;
userGroupUsage.ChangeDate = DateTime.Today.Date;
userGroupUsage.CreateDate = DateTime.Today.Date;
userGroupUsage.LastUpdatedBy = DateTime.Now;

//Int32 Uid=Repository.Add(users); -----------------> this is where i have to take the userID
//userGroupUsage.UserID = Uid;
int i = 0;
foreach (UserGroup ug in users.userGroup)
{
users.userGroupi = UGRepository.GetUserGroupByGroupName(ug.UserGroups);
userGroupUsage.UserGroupID = users.userGroupi.UserGroupID;
UGURepository.Add(userGroupUsage);
i++;
}
}
else
{
throw new Exception("EX008");
}

}


Definitinon for this : Int32 Uid=Repository.Add(users);
---------------------from the UserRepository class--------------------------------------------
public void Add(Users users)
{
UsersInsertFactory insertFactory = new UsersInsertFactory();
try
{
base.Add(insertFactory, users); -------------------------------------------------> definition provided below

}
catch (SqlException ex)
{
HandleSqlException(ex, insertFactory);
}

}


definition : base.Add(insertFactory, users);
---------------------------------from the Common Repository class------------------------
public void Add( IInsertFactory< TDomainObject > insertFactory,
TDomainObject domainObj )
{
using(DbCommand command = insertFactory.ConstructInsertCommand(db, domainObj))
{
db.ExecuteNonQuery(command);
insertFactory.SetNewID(db, command, domainObj);
}
}

Note: i changed the return type here, but that affects the other operations which uses the Add...

u know, i am very much confused with this. please help me, with a solution.


nimi
Apr 11, 2008 at 10:11 AM
hi... nobody is here to give me a solution.....
pls.. respond to this !!
Developer
Apr 11, 2008 at 6:58 PM
The caller of "base.Add(insertFactory, users);" will get the insert factory object back with the new ID so you should be abe le to get this data from that object and set the return object of the parent classes (callers of base.Add).
Apr 13, 2008 at 9:17 AM
Edited Apr 14, 2008 at 5:44 AM
thanks, this issue is solved