Overview:
This article explain about how to insert data using sql store procedure, and passing value in sql parameter using csharp language in visual studio 2019.
step 1: Create SQL Database Table
OR
sql script for table creation
USE [Demo]
GO
/****** Object: Table [dbo].[tblDemoInsert] Script Date: 06-09-2020 08:10:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDemoInsert](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NULL,
[Address] [nvarchar](50) NULL,
CONSTRAINT [PK_tblDemoInsert] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
step 2: create form design
source code:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DemoInsert.aspx.cs" Inherits="DemoInsert.DemoInsert" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
<asp:Label ID="Label2" runat="server" Text="Address"></asp:Label>
<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox>
<asp:Button ID="btnsave" runat="server" OnClick="btnsave_Click" Text="Save" />
<asp:Button ID="btnClear" runat="server" OnClick="btnClear_Click" Text="Clear" />
<br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="Orange" AutoGenerateColumns="False" Width="426px">
<Columns>
<asp:BoundField DataField="Id" HeaderText="ID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Address" HeaderText="Address" />
</Columns>
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
Design:
step 3: Double click Save button
write the following insert code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DemoInsert
{
public partial class DemoInsert : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
BindGridview();
}
public void BindGridview()
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("prListInsertDemo", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void ClearFields()
{
txtname.Text = "";
txtaddress.Text = "";
}
protected void btnsave_Click(object sender, EventArgs e)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("prInsertDemo", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name",txtname.Text);
cmd.Parameters.AddWithValue("@address",txtaddress.Text);
int i= cmd.ExecuteNonQuery();
con.Close();
if(i>0)
{
Response.Write("<script language='javascript'>alert('Record Saved Successfully..!');</script>");
}
ClearFields();
BindGridview();
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
ClearFields();
}
}
}
step-4: Add database connection string in WebConfig.
<connectionStrings>
<add name="constring" connectionString="Data Source=LAPTOP-VOAQ0VQC;Initial Catalog=Demo;Integrated Security=True"/>
</connectionStrings>
 |
#Insert data in asp.net c# using sql procedure |
More Details:
No comments:
Post a Comment