How to use stored procedure in simple easy way with Web.Config file?

In this article I have given a simple stored procedure. After running the first page in an internet browser, only enter the user id and the data is retrieved from the table, showing that it is there in Cookies. You cannot imagine how faster it is.

I am using a stored procedure in SQL 2005 and connected this in font endASP.Net. There are three fields named- ID, Name, and Salary. We are using stored procedure for pre-compilation, which is faster for execution. In many interviews, I asked why do we use a stored procedure? When I really got the time, I understood these concepts. You will see how they can benefit you.

1. Create Table in SQL2005 and save this table named as tblemp.
   
   
    
2. Add values in tblemp by right-clicking on tblemp in the Object Explorer. Then select Open table and add values one by one.
   
   
    
3. Create a stored procedure in SQL 2005.
   
   
   

Here my data base is msdb. Write this stored procedure query and select the query, then execute it. If no error, after execution, then you will see dbo.usp_selectrow in the object explorer.

In ASP.Net 3.5, I added these.

In Web.config file,
 
<connectionStrings>
<add name="config" connectionString="Data Source=leonora\sqlexpress;Initial Catalog=msdb;Integrated Security=True"   providerName="System.Data.SqlClient"/>
</connectionStrings>

I used Windows Authentication mode in SQL 2005, so in the web Config file I used: Integrated Security=True. If you want SQL Server Authentication mode in SQL 2005, then Give uid=sa, Pwd=test and cut Integrated Security=True.

In Default.aspx Page, design this.



In Default.aspx.cs, copy and Past the following code-

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string web = ConfigurationManager.ConnectionStrings["config"].ConnectionString;
        SqlConnection con = new SqlConnection(web);
        //string q = "insert into tblsal";
        SqlCommand com = new SqlCommand("usp_selectrow",con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@id",TextBox1.Text);
        com.Parameters.AddWithValue("@name", TextBox2.Text);
        com.Parameters.AddWithValue("@sal", TextBox3.Text);
        con.Open();
        SqlDataReader dr;
        dr = com.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                TextBox2.Text = dr["name"].ToString();
                TextBox3.Text = dr["sal"].ToString();
            }
        }
        else
        //com.ExecuteNonQuery();
         {
        Response.Write("<script>alert('No User Name')</script>");
    }
        con.Close();
      
    }
}