How to call stored procedure through ASP.net application?


Commonly performed code can be written in the stored procedure. Different business rules can be enforced by using stored procedure. This code is simple to use and understand. It will improve performance of your .Net application.

How to call stored procedure through ASP.net application?
Description - If you have written stored procedure and you want to know how to call it then this code will help you.


public bool CallStoredProcedure()
{
try
{
CONN = GetConnection();
CONN.Open();
CMD = new SqlCommand("yourStoredProcedureName", CONN);
CMD.CommandType = CommandType.StoredProcedure;
CMD.ExecuteNonQuery();
CONN.Close();
return true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CMD.Dispose();
CONN.Close();
CONN.Dispose();
}

}


public SqlConnection GetConnection()
{
CONN = new SqlConnection(ConfigurationManager.ConnectionStrings["Keyname"].ToString());
return CONN;
}

You have to put following code in Web.config

<connectionStrings>
<add name="Keyname" connectionString="Data Source=yourPCname\yourSQLServerInstancename;Initial Catalog=yourDatabaseName;User ID =youruseridcredentials;Password=yourPassword" providerName="System.Data.SqlClient"/>
</connectionStrings>


Comments

Author: Vinod Kumar Sahu10 Feb 2013 Member Level: Silver   Points : 6

store procedure defination
-----------------------------------------

create procedure sp_Test
(
@Id int, @Name varchar(50)
)
Insert into table1(Id, FullName) values(@Id,@Name)



C# code for call store procedure and insert data in database
------------------------------------------------------------------------------
try
{
sqlConnection = new SqlConnection(dbConnectionString);
SqlCommand command = new SqlCommand("sp_Test", sqlConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Id", SqlDbType.VarChar).Value = txtId.Text;
command.Parameters.Add("@Name", SqlDbType.DateTime).Value = txtName.Text;
sqlConnection.Open();
return command.ExecuteNonQuery();
sqlConnection.Close();
}
catch (SqlException ex)
{
Console.WriteLine("SQL Error" + ex.Message.ToString());
return 0;
}



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: