Introduction:
Here I will explain how to insert data into database in asp.net using stored procedure in c#, vb.net or insert / update / delete data into database using stored procedure in asp.net or get data from database and show it in gridview using stored procedure in c#, vb.net with example.
Description:
In previous articles I explained ajax gridview crud operations in asp.net with updatepanel, get current page name in asp.net, create error log files in asp.net with example, transfer rows from gridview data to datatable in asp.net, gridview examples in asp.net, display images from database using handler in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to insert and get data from database in asp.net using stored procedure in c#, vb.net with example.
In previous articles I explained ajax gridview crud operations in asp.net with updatepanel, get current page name in asp.net, create error log files in asp.net with example, transfer rows from gridview data to datatable in asp.net, gridview examples in asp.net, display images from database using handler in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to insert and get data from database in asp.net using stored procedure in c#, vb.net with example.
Before
implement this example first design one table productinfo in your database as
shown below
Column Name
|
Data Type
|
Allow Nulls
|
productid
|
Int(IDENTITY=TRUE)
|
Yes
|
productname
|
varchar(50)
|
Yes
|
price
|
varchar(50)
|
Yes
|
Now
create one new stored procedure “Crudoperations” in your sql
server database to perform insert, select, update and delete operations with
single procedure for that follow below script
CREATE PROCEDURE
CrudOperations
@productid int
= 0,
@productname varchar(50)=null,
@price int=0,
@status varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New Records
IF @status='INSERT'
BEGIN
INSERT INTO
productinfo1(productname,price) VALUES(@productname,@price)
END
--- Select Records in Table
IF @status='SELECT'
BEGIN
SELECT productid,productname,price FROM
productinfo1
END
--- Update Records in Table
IF @status='UPDATE'
BEGIN
UPDATE productinfo1 SET
productname=@productname,price=@price WHERE productid=@productid
END
--- Delete Records from
Table
IF @status='DELETE'
BEGIN
DELETE FROM
productinfo1 where productid=@productid
END
SET NOCOUNT OFF
END
|
In
case if you have any doubts to create procedure check below article
Once
we finish stored procedure creation in database now open your aspx page and
write the code like as shown below
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title>GridView Crud (Select
Insert Edit Update Delete) Operations using Single Stored Procedure in
ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida
Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif;
color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color:
#df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1"
runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server"
ID="gvDetails"
ShowFooter="true"
AllowPaging="true"
PageSize="10"
AutoGenerateColumns="false"
DataKeyNames="productid,productname"
OnPageIndexChanging="gvDetails_PageIndexChanging"
OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting"
OnRowCommand ="gvDetails_RowCommand"
>
<HeaderStyle CssClass="headerstyle"
/>
<Columns>
<asp:BoundField DataField="productid"
HeaderText="Product
Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Product
Name">
<ItemTemplate>
<asp:Label ID="lblProductname"
runat="server"
Text='<%# Eval("productname")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductname"
runat="server"
Text='<%# Eval("productname")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname"
runat="server"
/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText =
"Price">
<ItemTemplate>
<asp:Label ID="lblPrice"
runat="server"
Text='<%# Eval("price")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductprice"
runat="server"
Text='<%# Eval("price")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtprice"
runat="server"
/>
<asp:Button ID="btnAdd"
CommandName="AddNew"
runat="server"
Text="Add"
/>
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult"
runat="server"></asp:Label>
</div>
</form>
</body>
</html>
|
After completion of aspx page add following namespaces in
codebehind
C#
Code
using System;
using
System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
|
After completion of adding namespaces you need to write the
code like as shown below
protected void Page_Load(object
sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
DataSet ds = new DataSet();
using (SqlConnection con = new
SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status","SELECT");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else {
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount =
gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No
Records Found";
}
}
}
protected void gvDetails_RowCommand(object
sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname =
(TextBox)gvDetails.FooterRow.FindControl("txtpname");
TextBox txtprice =
(TextBox)gvDetails.FooterRow.FindControl("txtprice");
crudoperations("INSERT",
txtname.Text, txtprice.Text, 0);
}
}
protected void gvDetails_RowEditing(object
sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs
e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetails_RowUpdating(object
sender, GridViewUpdateEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
TextBox txtname =
(TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductname");
TextBox txtprice =
(TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductprice");
crudoperations("UPDATE",txtname.Text,txtprice.Text,productid);
}
protected void gvDetails_RowDeleting(object
sender, GridViewDeleteEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
string productname =
gvDetails.DataKeys[e.RowIndex].Values["productname"].ToString();
crudoperations("DELETE",productname,"",productid);
}
protected void crudoperations(string
status, string productname, string price, int
productid)
{
using (SqlConnection con = new
SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
if(status=="INSERT")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
}
else if(status=="UPDATE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@productid",productid);
}
else if(status=="DELETE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productid",productid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = productname+"
details "+status.ToLower()+"d
successfully";
gvDetails.EditIndex = -1;
BindGridview();
}
}
|
VB.NET
Code
Imports
System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As EventArgs) Handles
Me.Load
If Not
IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
Dim ds As New DataSet()
Using con As New SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("crudoperations",
con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@status",
"SELECT")
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
gvDetails.DataSource = ds
gvDetails.DataBind()
Else
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
gvDetails.DataSource = ds
gvDetails.DataBind()
Dim columncount As Integer =
gvDetails.Rows(0).Cells.Count
gvDetails.Rows(0).Cells.Clear()
gvDetails.Rows(0).Cells.Add(New TableCell())
gvDetails.Rows(0).Cells(0).ColumnSpan = columncount
gvDetails.Rows(0).Cells(0).Text = "No
Records Found"
End If
End Using
End Sub
Protected Sub gvDetails_RowCommand(ByVal
sender As Object,
ByVal e As GridViewCommandEventArgs)
If e.CommandName.Equals("AddNew")
Then
Dim txtname As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtpname"), TextBox)
Dim txtprice As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtprice"), TextBox)
crudoperations("INSERT",
txtname.Text, txtprice.Text, 0)
End If
End Sub
Protected Sub gvDetails_RowEditing(ByVal
sender As Object,
ByVal e As GridViewEditEventArgs)
gvDetails.EditIndex = e.NewEditIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
gvDetails.EditIndex = -1
BindGridview()
End Sub
Protected Sub gvDetails_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvDetails.PageIndex = e.NewPageIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowUpdating(ByVal
sender As Object,
ByVal e As GridViewUpdateEventArgs)
Dim productid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("productid").ToString())
Dim txtname As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtProductname"), TextBox)
Dim txtprice As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtProductprice"), TextBox)
crudoperations("UPDATE",
txtname.Text, txtprice.Text, productid)
End Sub
Protected Sub gvDetails_RowDeleting(ByVal
sender As Object,
ByVal e As GridViewDeleteEventArgs)
Dim productid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("productid").ToString())
Dim productname As String =
gvDetails.DataKeys(e.RowIndex).Values("productname").ToString()
crudoperations("DELETE",
productname, "", productid)
End Sub
Protected Sub crudoperations(ByVal
status As String,
ByVal productname As
String, ByVal
price As String,
ByVal productid As
Integer)
Using con As New SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("crudoperations",
con)
cmd.CommandType = CommandType.StoredProcedure
If status = "INSERT"
Then
cmd.Parameters.AddWithValue("@status",
status)
cmd.Parameters.AddWithValue("@productname",
productname)
cmd.Parameters.AddWithValue("@price",
price)
ElseIf status = "UPDATE" Then
cmd.Parameters.AddWithValue("@status",
status)
cmd.Parameters.AddWithValue("@productname",
productname)
cmd.Parameters.AddWithValue("@price",
price)
cmd.Parameters.AddWithValue("@productid",
productid)
ElseIf status = "DELETE" Then
cmd.Parameters.AddWithValue("@status",
status)
cmd.Parameters.AddWithValue("@productid",
productid)
End If
cmd.ExecuteNonQuery()
lblresult.ForeColor = Color.Green
lblresult.Text = (productname & Convert.ToString(" details ")) + status.ToLower() + "d successfully"
gvDetails.EditIndex = -1
BindGridview()
End Using
End Sub
End Class
|
Demo
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
2 comments :
Hi Suresh,
Thanks for the post. But , I am not able to insert any values for the productid.
thanx buh i would like to know why you created a table named productinfo and u are using productinfo1..
Note: Only a member of this blog may post a comment.