How to insert value in database by call SQL stored procedures in ASP.NET page
SQL Server Stored Procedures for insert values
in database using ASP.Net Example:
Call SQL stored procedures in ASP.NET page:
In this post we take an 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 this post we want to create a sample
example for this we first create a table.
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 for insert in to data base table:
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
As
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:
<connectionStrings>
<add name="Myconnection" connectionString="Data Source=<server
name>;Initial Catalog=Mytest;Integrated Security=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
create a web page in asp.net 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<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>
</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.
Store procedure on .asps.cs page for insert data:
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)
{
}
}
}
The next post we try to fetch the data from data base by the
help of store procedure.
Comments
Post a Comment