Parameters in Stored Procedure

Question

0.00/5 (No votes)

See more:

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 ?

SQL

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 :)

Posted 19-Nov-12 2:55am

Krunal Rohit

Updated 19-Nov-12 3:06am

v2

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 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 ?

[no name] 19-Nov-12 9:07am

Question is just got updated.. please look out.. :)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 19-Nov-12 9:20am

Are you facing problems to show the details of User in the front end ?

[no name] 19-Nov-12 9:23am

yes I am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 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 ?

[no name] 19-Nov-12 9:32am

ya I want to show it on Label..

Tim Corey 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 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.

[no name] 19-Nov-12 9:14am

City is there in the Table..

[no name] 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)

2 solutions

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[^]

Posted 19-Nov-12 3:21am

Tim Corey

Comments

[no name] 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 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?

[no name] 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 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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2012-11-19T03:58:00

So, the code will go something like below...You can use DataSet to return the data you want to the Presentation Layer.

C#

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...

C#

BAlLayer objUser = new BAlLayer();
DataSet ds = objUser.ViewUserProfile(userName, password);

txtCityName.Value = ds.Tables[0].Rows[0]["CityName"].ToString();