So, the code will go something like below...You can use
DataSet
to return the data you want to the Presentation Layer.
public DataSet ViewUserProfile(string userName, string password)
{
SqlConnection con = new SqlConnection(conStr);
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "proc_GetUserDetails";
cmd.Parameters.Add(new SqlParameter("@UserName", UserName));
cmd.Parameters.Add(new SqlParameter("@Password", Password));
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
return ds;
}
Now, this will return data in a tabular format with all the colums you mentioned in the stored procedure.
So, in the front end you can show the values using the code below...
BAlLayer objUser = new BAlLayer();
DataSet ds = objUser.ViewUserProfile(userName, password);
txtCityName.Value = ds.Tables[0].Rows[0]["CityName"].ToString();