Stored Procedures returning values instead of columns

Oct 19, 2007 at 6:52 AM
How to handle stored procedures that return specific value(s) than columns ?

Like for example, If I have a table SAMPLE having columns A,B,C,D,E and I have a stored procedure SPSAMPLE which is going to do many complex checkings looking through different tables and then return only A

Currently, this works like this,

1) Create Business Entity for the table SAMPLE, so I will get a class SAMPLE with properties A,B,C,D and E
2) If I create Repository Classes from Business Entities and choose the SPSAMPLE, I dont get mapping because mapping is done between Business Entities and Output Columns
3) However this would work if I change the stored procedure to return everything(column) by just having SELECT A from SAMPLE WHERE (conditions etc., ), because, that is going to return the whole column

But what happens is I have null values for B,C,D,E as I select only A and A would have the value. So, its going to be a waste of memory for simply creating the whole object SAMPLE for just returning A

Any idea how we could use repository to handle these kinda things ?

Thanks,

Regards,
Chakkaradeep
Developer
Oct 19, 2007 at 8:28 AM
In your scenario you shouldnt select all property from the table SAMPLE when you are creating your Business Entities, but only column A since you know u never gonna use the other fields.

regards
Benny
Oct 19, 2007 at 10:26 AM
Benny, I would agree with you if thats the only case. But I also have a stroed procedure which returns all columns (A,B,C,D,E). So, theoritically speaking, I do need the Business Entity class which would be used by the GetOne or GetMany stored procedures as well my above scenario.

Regards,
Chakkaradeep

Developer
Oct 20, 2007 at 5:57 PM
I believe that you are being blinded by your tables. I you have a procedure that returns only one column from one table, then you need a new Business Entity and forget about your SAMPLE Business Entity.

You need to abstract your business logic to relate to your needs not tables. U must see your stored procedure SPSAMPLE in the context of the place you use it.

Benny
Oct 23, 2007 at 2:29 AM


BennyXNO wrote:
I believe that you are being blinded by your tables. I you have a procedure that returns only one column from one table, then you need a new Business Entity and forget about your SAMPLE Business Entity.

You need to abstract your business logic to relate to your needs not tables. U must see your stored procedure SPSAMPLE in the context of the place you use it.

Benny


Ok, let me give an example

I have table called LoginTable - this has Login Name and Password and ID

Now, I have CRUD Stored procedures for this table as users might register,update,delete their accounts.

Now, users to use the system, they have to login, which is checking their login name with the password in the table. Now, for this, I create a stored procedure CheckUser which takes a string parameter and an integer as out parameter.

How to use this with Repository Factory ? I cannot create Business Entity again because I have already used them in CRUD, and its not wise enough to return the whole data(business entity) in place of integer.

If am making some mistake, please feel free to correct me :)
Oct 23, 2007 at 6:40 AM
Ok, I have used this way (ofcourse exceptions will come, just an example :D),

public bool CheckUser(String strName,String strPwd)
{
Database db = DatabaseFactory.CreateDatabase("DataAccess");

DbCommand dbCommand = db.GetStoredProcCommand("dbo.CheckUser");

db.AddInParameter(dbCommand, "LoginName", DbType.String, strName);
db.AddInParameter(dbCommand, "Password", DbType.String, strPwd);
db.AddOutParameter(dbCommand, "Result", DbType.Boolean,1);

db.ExecuteNonQuery(dbCommand);

bool result = Convert.ToBoolean(db.GetParameterValue(dbCommand,"Result"));

return result;
}

Is this the proper way ?
Oct 26, 2007 at 2:28 AM
Any suggestions ?

It would really help newbies like me :(

Thanks,

Regards,
Chakkaradeep