How To call Store procedure in ado.net

I think this[^] will help you.

More here[^]

You can try this

public static string ConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["TestConnectionString"].ToString();
public static SqlConnection Conn = new SqlConnection(ConnString);

public DataTable ExecuteNonQuery(string SpName, List<SqlParameter> para, bool isOutput)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandText = SpName;
da.SelectCommand.CommandTimeout = 1000;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Connection = Conn;
da.SelectCommand.Parameters.AddRange(para.ToArray());
Conn.Open();
da.Fill(dt);
}
catch (Exception ex)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
throw;
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
return dt;
}



This function will return datatable.....

Hiii,

you can use Stor procedure with multiple value like this way

SqlConnection = new SqlConnection(Connection);
SqlConnection.Open();
SqlCommand = new SqlCommand("UpdateUser", SqlConnection);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.AddWithValue("@Id", Id);
SqlCommand.Parameters.AddWithValue("@LoginId", LoginId);
SqlCommand.Parameters.AddWithValue("@FirstName", Firstname);
SqlCommand.Parameters.AddWithValue("@MiddleName", MiddleName);
SqlCommand.Parameters.AddWithValue("@LastName", LastName);
SqlCommand.Parameters.AddWithValue("@OrganisationName", OrganisationName);
SqlCommand.Parameters.AddWithValue("@InstitutionName", InstituitonName);
SqlCommand.Parameters.AddWithValue("@AddressLine1", AddressLine1);
SqlCommand.Parameters.AddWithValue("@AddressLine2", AddressLine2);
SqlCommand.Parameters.AddWithValue("@Email", Email);
SqlCommand.Parameters.AddWithValue("@Phone", Phone);
SqlCommand.Parameters.AddWithValue("@Fax", Fax);
SqlCommand.Parameters.AddWithValue("@State", State);
SqlCommand.Parameters.AddWithValue("@PostCode", PostCode);
SqlCommand.Parameters.AddWithValue("@City", City);
SqlCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
SqlCommand.Parameters.AddWithValue("@LoginStatus", 1);
SqlCommand.Parameters.AddWithValue("@IsActive", IsActive);
SqlCommand.Parameters.AddWithValue("@SecretQuestion", SecretQuestion);
SqlCommand.Parameters.AddWithValue("@SecretAnswer", SecretAnswer);
SqlCommand.Parameters.AddWithValue("@Gender", Gender);
SqlCommand.Parameters.AddWithValue("@Month", Month);
SqlCommand.Parameters.AddWithValue("@Day", day);
SqlCommand.Parameters.AddWithValue("@Mobilephone", Mobilephone);
SqlCommand.ExecuteNonQuery();
SqlConnection.ClearAllPools();
SqlConnection.Close();