In this blog I tried to describe the insert operation into database using Store Procedure in some simple steps.
Create Database, Table and Store Procedure in SQL Server
Firstly, Start your “SQL Server” and connect it. After that follow the following steps:
Step 1: Create a Database as I created a database name ‘crud’. Command is as follows:
create database crud;Step 2: Create a table as I created a table name ‘tb_ank‘. Command to create a table is as follows:
create table tb_ank(
Id int identity(1,1) primary key,
Name varchar(max),
Email varchar(max),
Mobile varchar(max),
City varchar(max)
);you can verify your table is created or not with select command as:
select * from tb_ank;Step 3: Now create a procedure for insert data into the table as I created a procedure name ‘ap_tb_ank_insert‘. Command to create a procedure is as follows:
create procedure sp_tb_ank_insert
(
@Name varchar(max),
@Email varchar(max),
@Mobile varchar(max),
@City varchar(max))
as
begin
insert into tb_ank
(Name, Email, Mobile, City)
values
(@Name, @Email, @Mobile, @City)
end;Design a WebForm
Now, Open Your Visual Studio and open a new ASP.net website. After that Add a WebForm (i.e. your form named as WebForm1.aspx). You have to design a simple table within this WebForm the source code for the Table is as follows:
<form id="form1" runat="server">
<div>
<table class="auto-style1">
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label2" runat="server" Text="Email"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="Mobile"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtMobile" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="City"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
<asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" />
</td>
</tr>
</table>
</div>
</form>Your Design of the WebForm Must be look like below image after design this table:
Backend Coding of the WebForm
Now we code on the insert button. For the coding you have to double click on the insert button of the WebForm.aspx page and it jump to the WebForm1.aspx.cs page. Now you have to put the connection string within the namespace. The code looks like follows:
namespace WebApplicationNew
{
public partial class WebForm1 : System.Web.UI.Page
{
//Connection String Puts Here...
SqlConnection con= new SqlConnection(@"Data Source=LAPTOP-XXXXXXXX\SQLEXPRESS;Initial Catalog=crud;Integrated Security=True");
protected void btnInsert_Click(object sender, EventArgs e)
{
}
}
}Note that don’t miss to use following namespaces:
using System.Data;
using System.Data.SqlClient;Now, we code on the insert button. The code for the insert button looks like this:
protected void btnInsert_Click(object sender, EventArgs e)
{
try
{
checkconnection();
SqlCommand cmd = new SqlCommand("sp_tb_ank_insert",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Email",txtEmail.Text);
cmd.Parameters.AddWithValue("@Mobile",txtMobile.Text);
cmd.Parameters.AddWithValue("@City",txtCity.Text);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Response.Write("<script>alert('Data Inserted!')</script>");
}
else
{
Response.Write("<script>alert('Data Not Inserted!')</script>");
}
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}For clear data from the textboxes we also code for that on the clear button. The code is as follows:
protected void btnClear_Click(object sender, EventArgs e)
{
txtName.Text = "";
txtEmail.Text = "";
txtMobile.Text = "";
txtCity.Text = "";
}That’s all now whole code for your WebForm1.aspx.cs is looks like that:
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;
namespace myCRUD
{
public partial class WebForm1 : System.Web.UI.Page
{
SqlConnection con= new SqlConnection(@"Data Source=LAPTOP-XXXXXXXX\SQLEXPRESS;Initial Catalog=crud;Integrated Security=True");
protected void checkconnection()
{
if(con.State == System.Data.ConnectionState.Closed)
con.Open();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
btnCancel.Visible = false;
btnUpdate.Visible= false;
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
try
{
checkconnection();
SqlCommand cmd = new SqlCommand("sp_tb_ank_insert",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Email",txtEmail.Text);
cmd.Parameters.AddWithValue("@Mobile",txtMobile.Text);
cmd.Parameters.AddWithValue("@City",txtCity.Text);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Response.Write("<script>alert('Data Inserted!')</script>");
cleardata();
}
else
{
Response.Write("<script>alert('Data Not Inserted!')</script>");
}
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtName.Text = "";
txtEmail.Text = "";
txtMobile.Text = "";
txtCity.Text = "";
}
}
}
