stored procedure in sqlexpress

[jed]

I have created this example in sqlexpress
ALTER PROCEDURE [dbo].[gettaxbracket]

@annualtax FLOAT

AS
BEGIN

SELECT begin1,end1,ded uctedamount,pec entageextra
FROM tax
WHERE @annualtax BETWEEN begin1 AND end1
END
can u please tell me how to gain access to the @annualtax parameter in
C#. An then i want to assign a textbox value to it.thanks

[Champika Nirosh]

I think you are asking about talking in the value of the annualtax via a
input box and pass that parameter to the SQL SP via your code and get teh
result back to your code right?

The answer to your question depend on what technique you used to communicate
with your database. Whether you use a datareader or dataset etc.. but in
generatl assuming that you are using SQL Helper / Data Reader approach. It
does provide a method to Execute a SqlCommand against the database providing
as a parameters.

If you google a bit you may easily find a sample code.. I cannot give any
right now since yoru question is too broad

Nirosh.

<je*@auto-soft.co.zawrote in message
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

>I have created this example in sqlexpress
ALTER PROCEDURE [dbo].[gettaxbracket]

@annualtax FLOAT

AS
BEGIN

SELECT begin1,end1,ded uctedamount,pec entageextra
FROM tax
WHERE @annualtax BETWEEN begin1 AND end1
END
can u please tell me how to gain access to the @annualtax parameter in
C#. An then i want to assign a textbox value to it.thanks

[auratius]

I copied this from http://www.codeproject.com/useritems/simplecodeasp.asp

Stored Procedures are a set of sql commands which are compiled and are
stored inside the database. Every time you execute a sql command, the
command is parsed, optimization is done and then the command is
executed. Parsing and optimization the command each time you run the
query is very expensive. To solve this we have a set of commands
collectively called as stored procedure, which are already parsed and
optimized and are executed when ever we call them. This article
describes about how to call the stored procedures through Ado.net and
how to handle the output parameters of the called stored procedures.

Initially create a object of SqlConnection class which is available in
System.Data.Sql Client namespace. You has to provide the connection
string as a parameter which includes the Data Source name, the
database name and the authentication credentials. Open the connection
using the Open() method.

SqlConnection con = new SqlConnection(" Data Source= ;
initial catalog= Northwind ; User Id= ; Password=
'");

con.open();
Create the following stored procedure on the Region table in the
Northwind database which accepts two parameters and does not have any
output parameters.

CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,
@RegionDescript ion NCHAR(50)) AS
SET NOCOUNT OFF
UPDATE Region
SET RegionDescripti on = @RegionDescript ion
Create a SqlCommand object with the parameters as the name of the
stored procedure that is to be executed and the connection object con
to which the command is to be sent for execution.

SqlCommand command = new SqlCommand("Reg ionUpdate",con) ;
Change the command objects CommandType property to stored
procedure.

command.Command Type = CommandType.Sto redProcedure;
Add the parameters to the command object using the Parameters
collection and the SqlParameter class.

command.Paramet ers.Add(new
SqlParameter("@ RegionID",SqlDb Type.Int,0,"Reg ionID"));

command.Paramet ers.Add(new
SqlParameter("@ RegionDescripti on",SqlDbType.N Char,
50,"RegionDescr iption"));

Specify the values of the parameters using the Value property of the
parameters

command.Paramet ers[0].Value=4;

command.Paramet ers[1].Value="SouthEa st";

Excecute the stored procedure using the ExecuteNonQuery method which
returns the number of rows effected by the stored procedure.

int i=command.Execu teNonQuery();
Now let us see how to execute stored procedures which has output
parameters and how to access the results using the output parameters.

Create the following stored procedure which has one output parameter.

ALTER PROCEDURE RegionFind(@Reg ionDescription NCHAR(50)
OUTPUT,
@RegionID INTEGER )AS

SELECT @RegionDescript ion =RegionDescript ion from Region
where RegionID=@Regio nID

The above stored procedure accepts regionID as input parameter and
finds the RegionDescripti on for the RegionID input and results it as
the output parameter.

SqlCommand command1 = new SqlCommand("Reg ionFind",con);
command1.Comman dType = CommandType.Sto redProcedure;
Add the paremeters to the command1

command1.Parame ters.Add(new SqlParameter
("@RegionDescri ption",SqlDbTyp e.NChar ,
50,ParameterDir ection.Output,f alse,
0,50,"RegionDes cription",DataR owVersion.Defau lt,null));
command1.Parame ters.Add(new SqlParameter("@ RegionID" ,
SqlDbType.Int,
0 ,
"RegionID" ));
Observe that the parameter RegionDescripti on is added with the
ParameterDirect ion as Ouput.

specify the value for the input parameter RegionID.

command1.Parame ters["@RegionID"].Value = 4;
Assign the UpdatedRowSourc e property of the SqlCommand object to
UpdateRowSource .OutputParamete rs to indicate that data will be
returned from this stored procedure via output parameters.

command1.Update dRowSource =
UpdateRowSource .OutputParamete rs;
Call the stored procedure and access the RegionDescripti on for the
RegionID 4 using the value property of the parameter.

command1.Execut eNonQuery();
string newRegionDescri ption =(string)
command1.Parame ters["@RegionDescrip tion"].Value;

Close the sql connection.

con.Close();
In the same way you can call the stored procedure that returns a set
of rows by defining the parameters as appropriate and executing the
command using ExecuteReader() that is used to traverse the records
returned by the command.

Regards

http://www.auratius.co.za

Auratius