In this article i wll explain how to insert data using stored procedure in c#.
I am creating a table in sqlsever.
USE [dotnetdb]
GO
/******
Object: Table [dbo].[Employee] Script Date: 12/25/2013 14:25:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmpId] [nvarchar](50) NOT NULL,
[EmpName] [nvarchar](50) NOT NULL,
[EmpAddress] [nvarchar](50) NOT NULL,
[EmpDesignation] [nvarchar](50) NULL
) ON [PRIMARY]
GO
For this table I am creating a Stored procedure as shown.
USE [dotnetdb]
GO
/******
Object: StoredProcedure
[dbo].[SP_InsertEmpinfo] Script Date:
12/25/2013 14:27:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[SP_InsertEmpinfo]
(
@EmpId nvarchar(50),
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)
)
As Begin
Insert into Employee (EmpId,EmpName,EmpAddress,EmpDesignation) values (@EmpId,@EmpName,@EmpAddress,@EmpDesignation)
end
GO
Now I am designing UI.
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeBehind="WebForm1.aspx.cs"
Inherits="AspExample.WebForm1"
%>
<!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>
<div>
<label>EmpId</label>
<asp:TextBox ID ="txtempid"
runat ="server"></asp:TextBox>
</div>
<div>
<label>EmpName</label>
<asp:TextBox ID ="txtempname"
runat ="server"></asp:TextBox>
</div>
<div>
<label>EmpAddress</label>
<asp:TextBox ID ="txtempaddress"
runat ="server"></asp:TextBox>
</div>
<div>
<label>EmpDesignation</label>
<asp:DropDownList ID ="ddlempdes"
runat = "server">
<asp:ListItem>Select</asp:ListItem>
<asp:ListItem>SE</asp:ListItem>
<asp:ListItem>TL</asp:ListItem>
<asp:ListItem>PL</asp:ListItem>
</asp:DropDownList>
</div>
<div>
<asp:Button ID ="btnsubmit"
Text ="Submit"
runat ="server"
onclick="btnsubmit_Click"
/>
</div>
</div>
</form>
</body>
</html>
In code behind I am implementing the
logic as shown below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace AspExample
{
public partial class WebForm1 :
System.Web.UI.Page
{
SqlConnection con = new
SqlConnection("Data
Source=Chinnu;Database=dotnetdb;User Id=sa;Password=password123;");
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnsubmit_Click(object sender, EventArgs e)
{
SqlCommand cmd = new
SqlCommand("SP_InsertEmpinfo",
con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmpId",
SqlDbType.NVarChar).Value = txtempid.Text;
cmd.Parameters.Add("@EmpName",
SqlDbType.NVarChar).Value = txtempname.Text;
cmd.Parameters.Add("@EmpAddress",
SqlDbType.NVarChar).Value =
txtempaddress.Text;
cmd.Parameters.Add("@EmpDesignation",
SqlDbType.NVarChar).Value =
ddlempdes.SelectedValue;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
In web.config give the database connection.
<connectionStrings>
<add name="Connstring" connectionString="Data
Source=DotNetSharePoint;Database=Chinnu;User Id=sa;Password=Password123;" providerName="System.Data.SqlClient" />
</connectionStrings>
