how to call store procedure in asp.net using c#

Question

1.00/5 (1 vote)

See more:

USE [security]
GO
/****** Object: StoredProcedure [dbo].[exam] Script Date: 10/15/2013 23:15:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[exam]
@type varchar(50)=null,
@user_name varchar(50)=null,
@email_id varchar(50)=null,
@password varchar(50)=null,
@exp varchar(50)=null
as
begin
if(@type='insert')
begin

insert into user_info values(@user_name,@email_id,@password,@exp)
end
if(@type='select')
begin

if exists(select @email_id,@password from user_info where email_id=@email_id and password=@password)
select @email_id,@password from user_info where email_id=@email_id and password=@password
end
end

Posted 15-Oct-13 7:48am

sanjiv yadav

Add a Solution

Comments

sanjiv yadav 15-Oct-13 13:49pm

how to call these store procedure

3 solutions

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)

OriginalGriff · Answer 1 · 2013-10-15T08:00:00

This creates, uses and removes a stored procedure:

C#

string r;
string s;
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand create = new SqlCommand("CREATE PROC [dbo].Sample @Name varchar(100) OUTPUT AS BEGIN SELECT @Name=Username FROM myTable WHERE iD=1 END", con))
        {
        create.ExecuteNonQuery();
        }
    using (SqlCommand com = new SqlCommand("Sample", con))
        {
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter name = new SqlParameter("@Name", SqlDbType.VarChar, 100);
        name.Direction = ParameterDirection.Output;
        com.Parameters.Add(name);
        com.ExecuteNonQuery();
        r = (string) name.Value;
        s = (string) com.Parameters["@Name"].Value;
        }
    using (SqlCommand drop = new SqlCommand("DROP PROCEDURE [dbo].Sample", con))
        {
        drop.ExecuteNonQuery();
        }
    con.Close();
    }
Console.WriteLine("Name : {0}\nParam: {1}", r, s);

mitchiee1226 · Answer 2 · 2013-10-15T19:36:00

this is for class.

C#

using Microsoft.ApplicationBlocks.Data;
using Microsoft.ApplicationBlocks.ExceptionManagement;

public string connection()
{
    return ConfigurationSettings.AppSettings["connection"];
}

public SqlDataReader GetData(string type, string username, string email, string password, string exp)
{
    SqlDataReader sqlReader = SqlHelper.ExecuteReader(connection(), CommandType.StoredProcedure, "exam",
        new SqlParameter("@type", type),
        new SqlParameter("@username, username"),
        new SqlParameter("@email", email),
        new SqlParameter("@password", password),
        new SqlParameter("@exp", exp));
    return sqlReader;
}

this is the most simplest way to do it. Hope this helped.

ranukamadushan · Answer 3 · 2013-10-15T19:49:00

Create the store procedure in SQL and call this in your code.

public void methodName(string parameter1,string parameter2)
{
SqlConnection con = new SqlConnection("Data Source=server_name;Initial Catalog=Database_name;User ID=username;Password=password");
con.Open();
SqlConnection.ClearAllPools();

SqlCommand cmd = new SqlCommand("Stored Procedure Name", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 50));
cmd.Parameters[0].Value = parameter1;

cmd.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 50));
cmd.Parameters[1].Value = parameter2;

SqlDataReader rdr = cmd.ExecuteReader();

return rdr.Read();

con.Close();
}

how to call store procedure in asp.net using c#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0