How to write c# code for calling store procedure ?

Question

2.00/5 (2 votes)

See more:

Please tell me that how I can write code in C# for calling this store procedure?

and please check my store procedure also.... it is correct or not?

actually i want that when i run than i write id and click on button than it show me first name+last name in one text box and age in another textbox.

SQL

alter PROCEDURE sproc	
	(
	@Id int,
	@First_Name varchar(50)=null,
	@Last_Name varchar(50)=null,
	@Age int=null	
	)
AS
	SELECT First_Name + Last_Name,Age from store where Id=@Id;
GO
declare @First_Name as varchar(50)
declare @Last_Name as varchar(50)
declare @Age as int
declare @Id as int
exec sproc @First_Name=null,@Last_Name=null,@Age=null,@Id=''
select @First_Name + @Last_Name,@Age

I would be very much thankful for your help.

EDIT

Code Added...

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace demo_stored_procedure
{
    public partial class demooooo : System.Web.UI.Page
    {
        DataSet ds = new DataSet();
        SqlConnection con;
        //Here we declare the parameter which we have to use in our application
        SqlCommand cmd = new SqlCommand();
        //SqlParameter sp1 = new SqlParameter();
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            con = new SqlConnection("Data Source=NSEZ-DD4-028;Initial Catalog=Hima;Integrated Security= SSPI");
            cmd = new SqlCommand("sproc", con);
            cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = TextBox1.Text;
            cmd.Parameters.Add("@First_Name", SqlDbType.VarChar).Value = TextBox2.Text;
         //   cmd.Parameters.Add("@Last_Name", SqlDbType.VarChar).Value = TextBox2.Text;
            cmd.Parameters.Add("@Age", SqlDbType.VarChar).Value = TextBox3.Text;
            SqlParameter parameter = new SqlParameter("");
            parameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parameter);
            con.Open();
            cmd.Connection = con;
            string outResult = parameter.Value.ToString();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

Posted 25-Nov-10 18:32pm

goelhima

Updated 25-Nov-10 22:23pm

Dalek Dave

v3

Add a Solution

3 solutions

Solution 1

You need to read these:
MSDN: ADO.NET[^]
Accessing Data with ADO.NET[^]

Posted 25-Nov-10 19:05pm

Sandeep Mewara

Solution 4

thnq u soo much :-D

its working now :) :)

Posted 28-Nov-10 19:17pm

goelhima

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)

Indresh1210 · Accepted Answer · 2010-11-25T22:56:00

Hi
here is Stored procedure

SQL

create procedure test
@id int

As
begin 
SET NOCOUNT ON;
    SELECT (FirstName+' '+LastName) as name,age from Store where id=@id
END

And write this code on the button click

C#

protected void Button1_Click(object sender, EventArgs e)
{
Sqlconnection con=new SqlConnection("Your con string");
Sqlcommand cmd=new SqlCommand("test",con);
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter[] param = new SqlParameter[1];
param[0]=new SqlParameter("@id", SqlDbType.Int);
param[0].value=TextBox1.text;
cmd.parameters.addRange(param);
SqlDataReader dr=cmd.ExecuteReader();
con.open();
while(dr.read())
{
   TextBox2.Text=Convert.Tostring(dr["name"]);
   TextBox3.Text=Convert.Tostring(dr["age"]);

}
con.close();
}