Tuesday, February 21, 2012

How to use stored Procedure with output parameters with ASP.NET,c#

 How to retrieve store procedure output parameter value?I have given a simple code example.  
 I have created a store procedure with output parameter. After that I get it from code behind page of  
 asp.net and stored it a variable.    
 private void GetInfo()  
   {  
     DALUtility objDALUtility = null;  
     SqlConnection con = null;  
     SqlCommand cmd = null;  
     SqlDataAdapter da = null;  
     try  
     {  
       objDALUtility = new DBUtility(ConfigurationManager.AppSettings["myconString"]);  
       con = objDALUtility.GetDBConnection();  
       con.Open();  
       cmd = new SqlCommand();  
       cmd.Connection = con;  
       cmd.CommandType = CommandType.StoredProcedure;  
       cmd.CommandTimeout = 500;  
       cmd.CommandText = "usp_MyInfo";  
       cmd.Parameters.AddWithValue("@Id", strId);  
       cmd.Parameters.AddWithValue("@empName", Convert.ToString(Session["Name"]));        
       cmd.Parameters.Add("@TotalCount", SqlDbType.Int).Direction = ParameterDirection.Output;  
       da = new SqlDataAdapter(cmd);  
       DataSet objDS = new DataSet("MyInfo");  
       da.Fill(objDS);  
       con.Close();  
       int Total = Convert.ToInt32(cmd.Parameters["@TotalCount"].Value);  
     }  
   }  
 Stored Procedure  
 CREATE Proc [dbo].[usp_MyInfo]  
  @Id int,            
  @Name varchar(50) = null,      
  @Total int = 0 OUTPUT  
 AS          
 SET @Total = (Select COUNT(Distinct tbl_Logs.ContentId) From tbl_Logs with(nolock)         
  Inner Join tbl_Logs_details with(nolock) on tbl_Logs_details.ContentId = tbl_Logs.ContentId  
  Where id = @id and tbl_Logs_details.ContentId = 2   
 Select @Total as Count   

0 comments:

Post a Comment