how to call t-sql stored procedure in c#

Question

0.00/5 (No votes)

See more:

Hello everyone,

i am new to t-sql, i am trying to write on t-sql statement for multiple actions:

SQL

ALTER PROCEDURE [dbo].[SelectRecord] 
	@lastname Varchar = null,
	@firstname Varchar = null,
	@class Varchar = null,
	@SelectOption Varchar
AS
BEGIN
	SET NOCOUNT ON;
	if(@SelectOption='Lastname')
	BEGIN
      SELECT b.id AS ID, b.firstname As FIRSTNAME,b.lastname AS LASTNAME,b.middlename AS MIDDLENAME,w.class_name AS CLASS,p.passport_img AS PASSPORT  FROM base_user_details b Left JOIN passport p ON b.id = p.id LEFT JOIN ward_class w on b.id=w.id WHERE b.lastname =@lastname
    END
	ELSE IF(@SelectOption='Firstname')
	BEGIN
	SELECT b.id AS ID, b.firstname As FIRSTNAME,b.lastname AS LASTNAME,b.middlename AS MIDDLENAME,w.class_name AS CLASS,p.passport_img AS PASSPORT  FROM base_user_details b Left JOIN passport p ON b.id = p.id LEFT JOIN ward_class w on b.id=w.id WHERE b.firstname =@firstname
	END
	ELSE IF(@SelectOption='class')
	BEGIN
	SELECT b.id AS ID, b.firstname As FIRSTNAME,b.lastname AS LASTNAME,b.middlename AS MIDDLENAME,w.class_name AS CLASS,p.passport_img AS PASSPORT  FROM base_user_details b Left JOIN passport p ON b.id = p.id LEFT JOIN ward_class w on b.id=w.id WHERE w.class_name =@class
	END

END

I am using C#, i want to be able to call the stored procedure when i want to select base on firstname, lastname and class. Please is there anyway i can achieve this? Thanks in advance

Posted 27-Apr-14 21:58pm

Uwakpeter

Add a Solution

Comments

DamithSL 28-Apr-14 4:13am

why you can't use this stored procedure?

Uwakpeter 28-Apr-14 5:20am

i want to be able to call the stored procedure and supplied parameters base on select selectOption and with either Lastname, Firstname or class

example:
cmd.Parameters.AddWithValue("@firstname", "John");
cmd.Parameters.AddWithValue("@SelectOption", "Firstname");

or

cmd.Parameters.AddWithValue("@lastname", "Peter");
cmd.Parameters.AddWithValue("@SelectOption", "lastname");

or

cmd.Parameters.AddWithValue("@class", "JSS 3");
cmd.Parameters.AddWithValue("@SelectOption", "class");

i have tried this, it doesn't give the desired result, Please anyway out?

3 solutions

Solution 1

Try:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SelectRecord", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@lastname", "Smith");
        cmd.Parameters.AddWithValue("@firstname", "John");
        cmd.Parameters.AddWithValue("@SelectOption", "Firstname");
        using (SqlDataReader read = cmd.ExecuteReader())
            {
            while (read.Read())
                {
                ...
                }
            }
        }
    }

Posted 27-Apr-14 22:12pm

OriginalGriff

v2

Comments

Uwakpeter 28-Apr-14 5:06am

i want to call it on three different occasions, please it possible i remove cmd.Parameters.AddWithValue("@lastname", "Smith"); when calling the procedure to select base on firstname?

Manas Bhardwaj 28-Apr-14 5:15am

You need to change construction of you stored proc to make it more generic. See my answer.

OriginalGriff 28-Apr-14 5:20am

Yes - it defaults to null so it should be fine to leave it out.

Solution 2

OriginalGriff's answer is correct.

However, you should also simplify your query like this:

SELECT
    b.id AS ID,
    b.firstname As FIRSTNAME,
    b.lastname AS LASTNAME,
    b.middlename AS MIDDLENAME,
    w.class_name AS CLASS,
    p.passport_img AS PASSPORT
FROM
    base_user_details b Left JOIN passport p ON
        b.id = p.id
    LEFT JOIN ward_class w ON
        b.id=w.id
WHERE
    (@SelectOption == 'class' AND b.class_name = @filter)
    OR (@SelectOption == 'Firstname' AND b.firstname = @filter)
    OR (@SelectOption == 'Lastname' AND b.lastname = @filter)

Posted 27-Apr-14 22:21pm

Manas Bhardwaj

Updated 27-Apr-14 23:15pm

v3

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)

**Nirav Prabtani** · Accepted Answer · 2014-04-27T23:17:00

Solution 3

write SQL query like this with variable length,, ... :)

SQL

--exec [dbo].[SelectRecord] 'Firstname'

Alter PROCEDURE [dbo].[SelectRecord] 
	@SelectOption Varchar(50)='',
	@lastname Varchar(50) = null,
	@firstname Varchar(50) = null,
	@class Varchar(50) = null

AS
BEGIN
	
	if(@SelectOption='Lastname')
		BEGIN
			 SELECT 'LastName'
		END
	ELSE IF(@SelectOption='Firstname')
		BEGIN
			SELECT 'FirstName'
		END
	ELSE IF(@SelectOption='class')
		BEGIN
			select 'Class'
		END
 
END

now pass your @SelectOption variable correctly from code behind

Posted 27-Apr-14 23:17pm

Nirav Prabtani

Updated 28-Apr-14 0:05am

v2

Comments

Uwakpeter 28-Apr-14 5:25am

noted, Thanks for the correction, please how can i call this in c#?
i want to be able to call the stored procedure and supplied parameters base on select selectOption criteria, i.e. either Lastname, Firstname or class

example:
cmd.Parameters.AddWithValue("@firstname", "John");
cmd.Parameters.AddWithValue("@SelectOption", "Firstname");

or

cmd.Parameters.AddWithValue("@lastname", "Peter");
cmd.Parameters.AddWithValue("@SelectOption", "lastname");

or

cmd.Parameters.AddWithValue("@class", "JSS 3");
cmd.Parameters.AddWithValue("@SelectOption", "class");

i have tried this, it doesn't give the desired result, Please anyway out?

Nirav Prabtani 28-Apr-14 5:26am

have you code cmd.CommandType=CommandType.StoredProcedure;

??????????

Nirav Prabtani 28-Apr-14 5:32am

try
{

SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Your Connection String Name"].ConnectionString);
SqlCommand cmd = new SqlCommand("SelectRecord", cn);

cn.Open();
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@firstname", "John");
cmd.Parameters.AddWithValue("@SelectOption", "Firstname");

// or

cmd.Parameters.AddWithValue("@lastname", "Peter");
cmd.Parameters.AddWithValue("@SelectOption", "lastname");

// or

cmd.Parameters.AddWithValue("@class", "JSS 3");
cmd.Parameters.AddWithValue("@SelectOption", "class");

.

.

.
}
catch
{

}

Uwakpeter 28-Apr-14 5:51am

yes,i have tried that, it asking i must supply parameter for lastname and class while trying to query it against firstname: exec [dbo].[SelectRecord]'Asare','Lastname'

error msg:Procedure or function 'SelectRecord' expects parameter '@SelectOption', which was not supplied. is there anything i have done wrong?

Nirav Prabtani 28-Apr-14 5:54am

you have not passed @SelectOption thats why error occurs

Nirav Prabtani 28-Apr-14 6:06am

see updated solution and try to execute in that way

exec [dbo].[SelectRecord] 'Firstname'

Uwakpeter 28-Apr-14 6:24am

lastname is the parameter value for @selectOption while Asare is the parameter value for lastname

Uwakpeter 28-Apr-14 7:00am

selectOption is not a field in the table, should i declare it as a variable?

Nirav Prabtani 28-Apr-14 7:23am

you have already declared it in SP.. :)

Uwakpeter 28-Apr-14 7:25am

Please can you figure out why it is not working?

Nirav Prabtani 28-Apr-14 7:54am

have you tried to execute updated solution ???

Uwakpeter 28-Apr-14 8:10am

please which one is the update solution?

Nirav Prabtani 29-Apr-14 2:15am

Solution 3

Uwakpeter 29-Apr-14 3:16am

Whoa! it is working, thanks so much Nirav, i really appreciate.

Nirav Prabtani 29-Apr-14 5:11am

Welcomw peter.. :)