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.
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>
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;
}