Alma Alma - 13 days ago 6
C# Question

Return value in a method that call stored procedure

I am using the

Microsoft.Practices.EnterpriseLibrary
for accessing a SQL Server database in my C# code. I have a method that call the stored procedure and send a parameter to the stored procedure, and that stored procedure returns a row (username and password).

I am not sure how return the value in this method.

This is my code:

public Model.Login GetUsernameandPasswordByPartnerID(int partnerId)
{
Model.Login login;

string myConnection = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ToString();
SqlDatabase db = new SqlDatabase(myConnection);

using (DbCommand command = db.GetStoredProcCommand("AS_AuthenticateByPartner"))
{
db.AddInParameter(command, "partnerID", DbType.String, partnerId);

try
{
login = db.ExecuteScalar(command); //error on this line

// rtn = Message("Success", "Acquisition", "GetLogin", "Service", db.ExecuteScalar(command).ToString());
}
catch (Exception ex)
{
}

db = null;
return login;
}
}


and my Model.login

public class Login
{
public string username;
public string password;
}


I am not sure how should have this line:

login= db.ExecuteScalar(command);


now I am getting error as you cannot explicitly convert object to
Model.login
.

Answer

This error is because of object is return of following statement.

login=  db.ExecuteScalar(command);

You have to do something like this

login=  (Model.Login)db.ExecuteScalar(command);

But this is not solve your problem as ExecuteScalar does not return .net type Login. It is only useful if you return int ,string etc from SQL Server. To help on this I need to know what is return from your stored procedure.

If I assume that you are returning something like this

 select username,password from tblUser

Then you can not use ExecuteScalar as it is only use to return single value.

You have to do something like this.

 Model.Login model = null;
 var result  =  db.ExecuteReader();
 if (!reader.HasRows)
    model = null;
 else 
  {
        reader.Read();
        model = new Model.Login(){ UserName = reader.GetString(0) , Password=  reader.GetString(1) };
  } 
Comments