Sunday, January 17, 2010

Calling SQL STORED PROCEDURE IN C#:

SQL COMMANDS USED TO CREATE getFees STORED PROCEDURE:

create procedure getFees @vcno int,@Vfees int output
as
select @Vfees=fees from course where cno=@vcno

SQL COMMAND USED TO CREATE TABLE COURSE:

Create table courses(cno int primary key identity(1,1),cname varchar(20) not null,duration int Check (duration >0),fees int check(fees>0))

SQL COMMAND USED TO CHECK OUTPUT OF STORED PROCEDURE IN QUERY ANALYZER:

declare @vfees int
exec getFees 2,@vfees output
select fees= @vfees

*******************************************************************************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace using_sql_stored_Procedure_in_Csharp
{
class Program
{

static void Main(string[] args)
{
SqlConnection con= new SqlConnection("Integrated Security=SSPI;Initial Catalog=sangram;Data Source=(local)");
con.Open();
SqlCommand cmd=con.CreateCommand();

cmd.CommandType=CommandType.StoredProcedure ;
cmd.CommandText="getFees";

SqlParameter parm1= new SqlParameter("@vcno",SqlDbType.Int );
SqlParameter parm2= new SqlParameter("@vfees",SqlDbType.Money );

cmd.Parameters.Add(parm1);
cmd.Parameters[0].Direction=ParameterDirection.Input ;

cmd.Parameters.Add(parm2);
cmd.Parameters[1].Direction=ParameterDirection.Output;

cmd.Parameters[0].Value =3;
cmd.ExecuteNonQuery();
Console.WriteLine("Cno=3"+" Fees="+cmd.Parameters[1].Value .ToString());



con.Close();
Console.ReadKey();
}
}
}

No comments:

Post a Comment