Asp.net : how to Call sql stored procedure on asp.net page

Call a sql server stored procedure when submit button is clicked In asp.net:

Sql Store procedure:

A stored procedure is a collection of SQL statements or sql queries, compiled into a single execution plan. We can write stored procedure and save it into the sql database. If we want to execute the same query over and over again, then it is better to create a stored procedure instead. A sample Stored Procedure Example is given below.

In this post we want to create a sample example in this we create a table first.

Now when we create my table in data base then we want to write the store procedure on data base. Some time the developer makes store procedure according to the table. Means every table have separate store procedure.

How to create a store procedure:

Here we create a store procedure in you want to learn more about sql server store procedure then click here.

CREATE PROCEDURE Proc_Mytest

@UserID int=NULL,

@UserName varchar(50)=null,

@Password varchar(50)=null,

@Address varchar(max)=null,

@QueryType varchar(50)=null

BEGIN

if(@QueryType='Insert')

begin

insert into Table_1(UserName,Password,Address)values(@UserName,@Password,@Address)

end

END

Here we make a store procedure for the table_1. And give the query of insert the record into the table.

Now we make an asp.net web application and save some record by user input. For this first we want to create the connection.

Write the connection string into web.config file.

<add name="Myconnection" connectionString="Data Source=<server name>;Initial Catalog=Mytest;Integrated Security=True;" providerName="System.Data.SqlClient"/>

</connectionStrings>

Now we create a web page in asp.net application with C#.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<table>

<tr>

<td>

User Name :

</td>

<td>

<asp:TextBox ID="TxtUserName" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Password :

</td>

<td>

<asp:TextBox ID="TxtPassword" runat="server" TextMode="Password"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Address :

</td>

<td>

<asp:TextBox ID="TxtAddress" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

</td>

<td>

<asp:Button ID="Submit" runat="server" Text="Submit" onclick="Submit_Click" />

<asp:Label ID="lblMsg" runat="server" ForeColor="Red" Text=""></asp:Label>

</td>

</tr>

</table>

<div>

</div>

</form>

</body>

</html>

In this page we take some text field and a button control, now we want to fill the text field and when click submit button the data will be store in to database by store procedure.

How to call sql store procedure on .asps.cs page:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString);

SqlCommand cmd;

DataSet ds;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

}

protected void Submit_Click(object sender, EventArgs e)

{

try

{

con.Open();

cmd = new SqlCommand("Proc_Mytest",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@UserName",TxtUserName.Text);

cmd.Parameters.AddWithValue("@Password", TxtPassword.Text);

cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);

cmd.Parameters.AddWithValue("@QueryType", "Insert");

cmd.ExecuteNonQuery();

lblMsg.Text = "data store in to data base";

// SqlDataAdapter adp = new SqlDataAdapter(cmd);

//adp.Fill(ds);

}

catch (Exception ex)

{

}

In this post we see a example of insert data in to database by the help of store procedure in asp.net and also learn how to call store procedure in C# code page. In the next post we try to fetch the data from data base by the help of store procedure.