Create,Read,Update,Delete CRUD Operations using C#,Asp.Net,SQL Server Part 1
Bangalore: Hi Friends In web application development most of the application must have to use Add,Update,Delete,Search operations.
Tools:Visual Studio 2010,SQL Server Management Studio
Create a New empty web project under c#.
add a Home.aspx page and other 4 WebForms for CRUD Operations. and Design your Home pag like this so that you can see all operation in home page itself.
CREATE
In this application we are going to discuss about Insertion or Creation operation Only.
So from Home page on Create Button click you will redirect into Create.Aspx Page.
Design your Create.aspx form like this.
So your Form Having username,email,company,mobile fields and corresponding textboxes for entering values on Submit button click you have to insert all these values into SQL Server Database. on clear Button click you have to clear all fields. For clearing all fields we have used a javascript.
So design a table in your SQL Server like this.
And in code behind write the logic for submit button click.
We are getting connection string from web.config file so declare connection string in one place no need to declare every where.
Add this connection string in web.config file after <system.web></system.web> tag
Here In our code We have used Queries Inside our code itself for easy understanding. In real application development we will not write queries inside our code. we will use stored procedures and we will call that procedures inside our code.
For easy learning purpose we have wrote the queries inside our code.
Please go through the Create.Aspx.cs code behind code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace CRUD
{
public partial class Create : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public string getConstring()
{
string constr = ConfigurationManager.ConnectionStrings["constrng"].ConnectionString;
return constr;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string uname = txtUserName.Text;
string email = txtEmail.Text;
string comapany = txtCompany.Text;
string Mobile = txtMobile.Text;
string str = getConstring();
SqlConnection con = new SqlConnection(str);
con.Open();
SqlCommand cmd=new SqlCommand("insert into crud(username,email,company,mobile) values('"+uname+"','"+email+"','"+comapany+"','"+Mobile+"')",con);
int result=cmd.ExecuteNonQuery();
if (result==1)
{
ScriptManager.RegisterStartupScript(this, GetType(), "showAlert", "alert('Data Inserted Succesfully')", true);
txtCompany.Text = "";
txtEmail.Text = "";
txtMobile.Text = "";
txtUserName.Text = "";
}
}
}
}
Please write to us If you are facing any difficulties.
In Part 2 we will describe about Update Operations. Part 2
In Part 3 we will describe about delete operations Part 3
Tools:Visual Studio 2010,SQL Server Management Studio
Create a New empty web project under c#.
add a Home.aspx page and other 4 WebForms for CRUD Operations. and Design your Home pag like this so that you can see all operation in home page itself.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Home.aspx.cs" Inherits="CRUD.Home" %>
<!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>CRUD</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<a href="Create.aspx">Create</a>
</div>
<div>
<a href="Read.aspx">Read</a>
</div>
<div>
<a href="Update.aspx">Update</a>
</div>
<div>
<a href="Delete.aspx">Delete</a>
</div>
</form>
</body>
</html>
<!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>CRUD</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<a href="Create.aspx">Create</a>
</div>
<div>
<a href="Read.aspx">Read</a>
</div>
<div>
<a href="Update.aspx">Update</a>
</div>
<div>
<a href="Delete.aspx">Delete</a>
</div>
</form>
</body>
</html>
CREATE
In this application we are going to discuss about Insertion or Creation operation Only.
So from Home page on Create Button click you will redirect into Create.Aspx Page.
Design your Create.aspx form like this.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Create.aspx.cs" Inherits="CRUD.Create" %>
<!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>Create</title>
<style type="text/css">
.width
{
height:40px;
}
</style>
<script language="javascript" type="text/javascript">
function empty() {
document.getElementById("txtUserName").value = "";
document.getElementById("txtEmail").value = "";
document.getElementById("txtCompany").value = "";
document.getElementById("txtMobile").value = "";
return true;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="500px">
<tr class="width">
<td nowrap="nowrap">UserName
</td>
<td><asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td>
<td nowrap="nowrap">Email</td>
<td><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> </td>
</tr>
<tr class="width">
<td nowrap="nowrap">CompanyName</td>
<td><asp:TextBox ID="txtCompany" runat="server"></asp:TextBox></td>
<td nowrap="nowrap">Mobile</td>
<td><asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="3" align="right"><asp:Button ID="btnSubmit" Text="Submit"
runat="server" onclick="btnSubmit_Click" /> </td>
<td><asp:Button ID="btnCancel" Text="Clear" runat="server" OnClientClick="javascript:empty()" CausesValidation="false" /> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
<!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>Create</title>
<style type="text/css">
.width
{
height:40px;
}
</style>
<script language="javascript" type="text/javascript">
function empty() {
document.getElementById("txtUserName").value = "";
document.getElementById("txtEmail").value = "";
document.getElementById("txtCompany").value = "";
document.getElementById("txtMobile").value = "";
return true;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="500px">
<tr class="width">
<td nowrap="nowrap">UserName
</td>
<td><asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td>
<td nowrap="nowrap">Email</td>
<td><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> </td>
</tr>
<tr class="width">
<td nowrap="nowrap">CompanyName</td>
<td><asp:TextBox ID="txtCompany" runat="server"></asp:TextBox></td>
<td nowrap="nowrap">Mobile</td>
<td><asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="3" align="right"><asp:Button ID="btnSubmit" Text="Submit"
runat="server" onclick="btnSubmit_Click" /> </td>
<td><asp:Button ID="btnCancel" Text="Clear" runat="server" OnClientClick="javascript:empty()" CausesValidation="false" /> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
So your Form Having username,email,company,mobile fields and corresponding textboxes for entering values on Submit button click you have to insert all these values into SQL Server Database. on clear Button click you have to clear all fields. For clearing all fields we have used a javascript.
So design a table in your SQL Server like this.
And in code behind write the logic for submit button click.
We are getting connection string from web.config file so declare connection string in one place no need to declare every where.
Add this connection string in web.config file after <system.web></system.web> tag
<connectionStrings ><add name="constrng" connectionString="Data Source=localhost; Initial Catalog=search; Integrated Security=true;" providerName="System.Data.SqlClient"/></connectionStrings>
Here In our code We have used Queries Inside our code itself for easy understanding. In real application development we will not write queries inside our code. we will use stored procedures and we will call that procedures inside our code.
For easy learning purpose we have wrote the queries inside our code.
Please go through the Create.Aspx.cs code behind code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace CRUD
{
public partial class Create : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public string getConstring()
{
string constr = ConfigurationManager.ConnectionStrings["constrng"].ConnectionString;
return constr;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string uname = txtUserName.Text;
string email = txtEmail.Text;
string comapany = txtCompany.Text;
string Mobile = txtMobile.Text;
string str = getConstring();
SqlConnection con = new SqlConnection(str);
con.Open();
SqlCommand cmd=new SqlCommand("insert into crud(username,email,company,mobile) values('"+uname+"','"+email+"','"+comapany+"','"+Mobile+"')",con);
int result=cmd.ExecuteNonQuery();
if (result==1)
{
ScriptManager.RegisterStartupScript(this, GetType(), "showAlert", "alert('Data Inserted Succesfully')", true);
txtCompany.Text = "";
txtEmail.Text = "";
txtMobile.Text = "";
txtUserName.Text = "";
}
}
}
}
In Part 2 we will describe about Update Operations. Part 2
In Part 3 we will describe about delete operations Part 3
.jpg)
Hi,i used this code,but i m facing the below problem while executing,kindly hepl and reply fast.
ReplyDeleteerror :
Incorrect syntax near '-'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '-'.
Source Error:
Line 45: //con.Close();
Line 46: SqlCommand cmd=new SqlCommand("insert into User_table(Username,CompName,Mobile,E-mail) values("+uname+","+cname+","+mob+","+email+")",con);
Line 47: int result=cmd.ExecuteNonQuery();
Line 48: if (result==1)
Line 49: {
Source File: e:\ASPNETP\crud poeratn\Create.aspx.cs Line: 47
SqlCommand cmd = new SqlCommand("insert into crud(username,email,company,mobile) values('" + uname + "','" + email + "','" + comapany + "','" + Mobile + "')", con);
DeleteChange your insert command with this because you have to pass parameters as string as per database you have missed the single quotes
Hi
ReplyDeleteplease use store procedure
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 WebPro.InsertForm
{
public partial class InsertForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
String strConnString = ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand(); // this for comamnd to insert data
cmd.CommandType = CommandType.StoredProcedure; // using store procedure
cmd.CommandText = "AddEmployee";
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();
cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
lblMessage.Text = "Record inserted successfully";
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
txtFirstName.Text = "";
txtLastName.Text = "";
txtBirthDate.Text = " ";
txtCity.Text = " ";
txtCountry.Text = " ";
}
}
}
}