dotnet materials : CURD OPERATIONS ON ASP.NET GRID USING SQL SERVER TABLE & sp'S

1.I have the table Customer with the schema as follows

I have already inserted few records in the table.

Stored Procedure for Select, Insert, Update and Delete

Select

USE [Customers]

/****** Object: StoredProcedure [dbo].[SP_GETCustomerDATA] Script Date: 12/02/2016 08:52:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SP_GETCustomerDATA]

BEGIN

select * from customer

END

Insert

USE [Customers]

/****** Object: StoredProcedure [dbo].[SP_ADDCustomerDATA] Script Date: 12/02/2016 08:53:26 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SP_ADDCustomerDATA]

(@Name varchar(50),

@Country varchar(50))

BEGIN

INSERT INTO [Customers].[dbo].[Customer]

([Name],[Country])

VALUES

(@Name,@Country);

END

Update

USE [Customers]

/****** Object: StoredProcedure [dbo].[SP_UPDATEcustomerDATA] Script Date: 12/02/2016 08:54:02 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SP_UPDATEcustomerDATA]

(@Customerid int,

@Name varchar(50),

@Country varchar(50))

BEGIN

UPDATE Customer

set

Name=@Name,

Country=@Country

where Customerid=@Customerid

END

Delete

USE [Customers]

/****** Object: StoredProcedure [dbo].[SP_DELETECustomerDATA] Script Date: 12/02/2016 08:54:24 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SP_DELETECustomerDATA]

(@Customerid int

)

BEGIN

delete from customer where Customerid=@Customerid

END

Prepare Entity class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace DeptEntity

{

public class Customer

{

public int Customerid { get; set; }

public string Name { get; set; }

public string Country { get; set; }

}

wEB.CONFIG

<add name="DBCON"

connectionString="Data Source=VASU-PC;Initial Catalog=Customers;User ID=sa;Password=vasu"

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

Prepare Dal class using entity

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using CustomerEntity;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace DAL

{

public class CustomerData

{

string constr = ConfigurationManager.ConnectionStrings["DBCON"].ConnectionString;

public int AddCustomerData(Customer cst)

{

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("SP_ADDCustomerDATA", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@Name", cst.Name));

cmd.Parameters.Add(new SqlParameter("@Country", cst.Country));

cmd.ExecuteNonQuery();

con.Close();

return 0;

}

public void DeleteCustomer(int Customerid)

{

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("SP_DELETECustomerDATA", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@Customerid", Customerid));

cmd.ExecuteNonQuery();

con.Close();

}

public List<Customer> GetCustometData()

{

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("SP_GETCustomerDATA", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

List<Customer> objList = new List<Customer>();

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

Customer objData = new Customer();

objData.Customerid = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);

objData.Name = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1]);

objData.Country = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[2]);

objList.Add(objData);

}

return objList;

}

public int UpdateCustomerData(Customer cst)

{

SqlConnection con = new SqlConnection(constr);

con.Open();

SqlCommand cmd = new SqlCommand("SP_UPDATEcustomerDATA", con);

cmd.CommandType = CommandType.StoredProcedure;

// Customer cst = new Customer();

cmd.Parameters.Add(new SqlParameter("@Customerid", cst.Customerid));

cmd.Parameters.Add(new SqlParameter("@Name", cst.Name));

cmd.Parameters.Add(new SqlParameter("@Country", cst.Country));

cmd.ExecuteNonQuery();

con.Close();

return 0;

}

Grid.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Customers.aspx.cs" Inherits="CustomerGrid.Customers" %>

<!DOCTYPE html>

</head>

<body>

<div>

<table>

<tr>

<asp:TextBox ID="txtname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<asp:TextBox ID="txtCountry1" runat="server"></asp:TextBox>

<br />

<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />

</td>

</tr>

<tr>

<td >

 </td>

</tr>

</table>

</div>

<div>

<asp:GridView ID="Grid1" runat="server" CellPadding="4" AllowPaging="True" PageSize="5"

ForeColor="#333333" AutoGenerateColumns="false"

DataKeyNames="Customerid"

OnPageIndexChanging="Grid1_PageIndexChanging"

OnRowDeleting="Grid1_RowDeleting"

OnRowEditing="Grid1_RowEditing"

OnRowUpdating="Grid1_RowUpdating"

OnRowCancelingEdit="Grid1_RowCancelingEdit">

<%-- AutoGenerateDeleteButton="true" AutoGenerateEditButton="true"--%>

<asp:TemplateField HeaderText="CustomerID" SortExpression="Customerid">

<asp:TextBox ID="txtCustomerid" Width="100px" runat="server" Text='<%# Bind("Customerid") %>'>

</asp:TextBox> </ItemTemplate>

<asp:Label ID="Label1" runat="server" Text='<%# Bind("Customerid") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="NAME" SortExpression="DeptName">

<asp:TextBox ID="txtName" Width="100px" runat="server" Text='<%# Bind("Name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtFName1" runat="server" Width="100px"></asp:TextBox>

</FooterTemplate>

<asp:Label ID="Label2" runat="server" Text='<%# Bind("Name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="COUNTRY" SortExpression="Country">

<asp:TextBox ID="txtContry" Width="100px" runat="server" Text='<%# Bind("Country") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label3" runat="server" Text='<%# Bind("Country") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit" ShowHeader="False">

<asp:LinkButton ID="LinkButton3" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit"></asp:LinkButton>

</ItemTemplate>

<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"

Text="Update"></asp:LinkButton>

<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"></asp:LinkButton>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Delete">

<asp:LinkButton ID="LinkButton4" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using DAL;

using CustomerEntity;

namespace CustomerGrid

{

public partial class Customers : System.Web.UI.Page

{

CustomerData obj = new CustomerData();

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

LoadGrid();

}

protected void LoadGrid()

{

Grid1.DataSource = obj.GetCustometData();

Grid1.DataBind();

}

protected void btnSave_Click(object sender, EventArgs e)

{

Customer obj1 = new Customer();

obj1.Name = txtname.Text;

obj1.Country = txtCountry1.Text;

obj.AddCustomerData(obj1);

LoadGrid();

}

protected void Grid1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

int Customerid = Convert.ToInt32(Grid1.DataKeys[e.RowIndex].Value.ToString());

obj.DeleteCustomer(Customerid);

LoadGrid();

}

protected void Grid1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

Grid1.PageIndex = e.NewPageIndex;

LoadGrid();

}

protected void Grid1_RowEditing(object sender, GridViewEditEventArgs e)

{

Grid1.EditIndex = e.NewEditIndex;

LoadGrid();

}

protected void Grid1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

int id = Convert.ToInt32(Grid1.DataKeys[e.RowIndex].Value.ToString());

string Name = ((TextBox)Grid1.Rows[e.RowIndex].FindControl("txtName")).Text;

TextBox country =(TextBox)Grid1.Rows[e.RowIndex].FindControl("txtContry");

string conty = country.Text;

Customer obj1 = new Customer();

obj1.Customerid = id;

obj1.Name = Name;

obj1.Country = conty;

obj.UpdateCustomerData(obj1);

Grid1.EditIndex = -1;

LoadGrid();

}

protected void Grid1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

Grid1.EditIndex = -1;

LoadGrid();

}

dotnet materials

Saturday, 3 December 2016

CURD OPERATIONS ON ASP.NET GRID USING SQL SERVER TABLE & sp'S

wEB.CONFIG

No comments:

Post a Comment