Monday, May 26, 2008

How to call store procedure using .NET

This is how I call store procedure using .NET having passing input value and return by output value

public static int GetSearchCount(string country)
{
int count = 0;
using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["dbnamestr"].ConnectionString))
{
conn.Open();
SqlCommand objCmd = new
SqlCommand("sp_SearchCount", conn);
objCmd.CommandType =
CommandType.StoredProcedure;
SqlParameter paramTotal = new
SqlParameter("@Total", SqlDbType.Int);
paramTotal.Direction =
ParameterDirection.ReturnValue;
objCmd.Parameters.Add(paramTotal);
objCmd.Parameters.AddWithValue("@Country", country);
objCmd.ExecuteScalar();
count = (int)paramTotal.Value;
}
return
count;
}