Click here to Skip to main content
11,802,636 members (67,994 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: C# SQL stored-procedure
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)
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
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
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[^]
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
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.CommandText = "proc_GetUserDetails";
            cmd.Parameters.Add(new SqlParameter("@UserName", UserName));
            cmd.Parameters.Add(new SqlParameter("@Password", Password));
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            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();
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 KrunalRohit 366
1 OriginalGriff 365
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CPallini 230
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,695
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2015
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