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

In this article I have given simple stored Procedure. After running the first page in Internet Browser, only enter user Id and the data is retrieved from table, showing that it is there in Cookies. You cannot imagine the how faster it is.

I am using 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. Many interview, I asked why we use stored Procedure? We I got really time, I understood these concept. You see, how it is benefit for you.

1. Create Table in SQL2005 and save this table named as tblemp.
2. Add values in tblemp by right click on tblemp on Object Explorer. And select Open table, add values one by one
3. Create stored Procedure in SQL2005

Here my data base is msdb. Write these stored Procedure query and select thes query, then execute it. If no error, after execution, then u will see dbo.usp_selectrow in object Explorer.

In asp.net3.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 SQL2005, So 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.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();

Read more: C# Corner

Posted via email from Jasper-net