insert data using stored procedure in c#

Advertisement
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>






Advertisements
SHARE

Unknown

  • Image
  • Image
  • Image
  • Image
  • Image
    Blogger Comment
    Facebook Comment

0 comments:

Post a comment