Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL StoredProcedures
Hello Code Project,
 
I'm using 3 tier architecture & passing only 2 parameter (i.e user_name and password) in SP and I want to use table field data (i.e City Name) for that user.. I want that City Name at Presentation Side, How do I achieve this ?
 
ALTER PROCEDURE [dbo].[LogInProcedure]
    @username nvarchar (50),
    @password nvarchar (50)
AS
    SET NOCOUNT ON;
SELECT  * FROM users
WHERE   user_username=@username AND user_password=@password
 
My question is, how can get the City Name from table to the Presentation Side ??
 
Thanks in advance Smile | :)
Posted 19-Nov-12 2:55am
Edited 19-Nov-12 3:06am
v2
Comments
Tadit Dash at 19-Nov-12 9:01am
   
You want to get the City Name of the User from the stored procedure by matching User Name ans Password, correct ?
If correct, then have you tried any query for that in the procedure ?
If yes, then post that and tell us what problem you are facing exactly ?
Krunal R at 19-Nov-12 9:07am
   
Question is just got updated.. please look out.. :)
Tadit Dash at 19-Nov-12 9:20am
   
Are you facing problems to show the details of User in the front end ?
Krunal R at 19-Nov-12 9:23am
   
yes I am
Tadit Dash at 19-Nov-12 9:27am
   
So are you showing that in a webpage ?
How you exactly want to show ? In a grid or in some text boxes or dropdowns ?
Please define your front end ?
Krunal R at 19-Nov-12 9:32am
   
ya I want to show it on Label..
Tim Corey at 19-Nov-12 9:09am
   
We really need you to expand upon your question. Where exactly are you stuck? It seems like what you are asking for is to have the stored procedure return the city name for the user that is passed in. This is a straight-forward query. We don't have your database schema so we couldn't give you the TSQL to do so. If this isn't what you were intending to communicate, please clarify this point.
Tim Corey at 19-Nov-12 9:10am
   
In response to your edit, it still isn't clear. Is city not in the users table? If it isn't, you will need to do a join on the table where it is located. If it is, then you already have city at the presentation layer.
Krunal R at 19-Nov-12 9:14am
   
City is there in the Table..
Krunal R at 19-Nov-12 9:13am
   
Ok just making simple for you guys, just assume that my table has following fields like, username, password, city, is Login etc. Here my query returning all the data if condition will true ok, now how do I get the particular field data (like City Name) to the UI side ? (remember I'm using 3 tier architecture)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Based upon the comments above and your clarifications, I'm going to post this as the answer. I cannot give you a "here is the code to do it" answer because we do not have nearly enough information to do so. However, I can answer you in general terms.
 
The term "3-tier architecture" is generic. There are multiple ways to implement a 3-tier architechture. For example, are you using MVC, MVP, MVVM, etc. or have you done something different? In general, the middle layer asks the data access layer for information. The middle layer then transforms it and gives it to the presentation layer. In that scenario, the way you would get the city information to the presentation layer would be to have the middle layer pass it along.
 
I would recommend reading up on different ways to accomplish this task. There are some great articles out there, including this one, that will help:
 
Three Layer Architecture in C# .NET[^]
  Permalink  
Comments
Krunal R at 19-Nov-12 9:24am
   
OMG.!! I know all this things.. Remove it as solution, please.. Because it is not the solution for I have asked for
Tim Corey at 19-Nov-12 9:25am
   
Then what exactly are you asking for? You know how to get the data in SQL, you know how to call the stored proc, and according to this post, you know how to get the data to the presentation layer. What are you missing?
Krunal R at 19-Nov-12 9:30am
   
Mate, I'm going to tell you what I have done ok..
I have written stored procedure, that will be executed in Data Logic, then I'm passing parameters (textboxe's valeus-username and password) for SP from Presentation Logic to Business Logic..
 
SP >> Data Logic (Execute SP) >> Business Logic (Get Set for parameters) >> Presentation Logic (passing values for parameters)... Now I want City Name at the Presentation Side anyhow...
n.podbielski at 19-Nov-12 9:48am
   
Don't you know how do what? Google 'executing stored procedure from c#' and stop wasting people, and yours in that matter, time.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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();
Comments
Tadit Dash at 20-Nov-12 0:51am
   
Thanks for accepting the answer @Krunal R.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 235
1 Sergey Alexandrovich Kryukov 120
2 Richard MacCutchan 100
3 ChauhanAjay 95
4 BotCar 95
0 Sergey Alexandrovich Kryukov 9,056
1 OriginalGriff 8,041
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100