asp.net the GridView query, pagination, edit update, delete instance code

asp.net the GridView query, pagination, edit update, delete instance code _ Practical Tips

Last Update:2017-01-19 Source: Internet

Author: User

Tags trim create database

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

1.A, Operation effect Chart

1.B, source code
/app_data/sql-basic.sql

Copy Code code as follows:

Use master

Go

if exists (SELECT * from sysdatabases where name= ' db1 ')

Begin

Drop Database DB1

End

Go

Create DATABASE DB1

Go

Use DB1

Go

-- ================================

--ylb:1, category table

-- ================================

CREATE TABLE Category

(

CategoryID int Identity (1,1) primary key,--number "PK"

CategoryName varchar NOT null--name

)

INSERT into category (CategoryName) VALUES (' beverages ')
INSERT into category (CategoryName) values (' Staple food ')
INSERT into category (CategoryName) VALUES (' non-staple food ')
INSERT into category (CategoryName) VALUES (' vegetables ')

-- ================================
--ylb:2, Product table
-- ================================
CREATE TABLE Product
(
ProductID int Identity (1001,1) primary key,--number "PK"
ProductName varchar (20),--Name
UnitPrice Numeric (7,2),--Unit price
Special varchar Check (special in (' Special offer ', ' not special '),--is special "C"
CategoryID int foreign key references category (CategoryID)--category number "FK"
)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 1 ', 12.6, ' specials ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 2 ', 12.6, ' not special ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 3 ', 12.6, ' not special ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 4 ', 12.6, ' not special ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 5 ', 12.6, ' specials ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 6 ', 12.6, ' specials ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 7 ', 12.6, ' specials ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Cola 8 ', 12.6, ' specials ', 1)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Steamed buns 1 ', 12.6, ' specials ', 2)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Tofu 1 ', 12.6, ' Specials ', 3)

Insert into product (Productname,unitprice,special,categoryid) VALUES (' Wax gourd 1 ', 12.6, ' Specials ', 4)

SELECT * FROM category
Select Productid,productname,unitprice,special,categoryid from Product

, 2
/app_code/
/app_code/dbconnection.cs

Copy Code code as follows:

Using System.Data.SqlClient;
<summary>
Summary description of DbConnection
Data Connection Class
</summary>
public class DbConnection
{
SqlConnection con = null;

Public DbConnection ()
{
To create a Connection object
con = new SqlConnection ("server=.;D ATABASE=DB1; Uid=sa;pwd=sa ");
}

<summary>
Data connection Objects
</summary>
Public SqlConnection Con
{
get {return con;}
set {con = value;}
}
}

/app_code/categoryinfo.cs
/app_code/categoryoper.cs
/app_code/productinfo.cs

Copy Code code as follows:

Using System;

<summary>
Summary description of ProductInfo
Product entity Classes
</summary>
public class ProductInfo
{
1,attributes
int productId;
String ProductName;
Decimal UnitPrice;
String Special;
int CategoryID;

Public ProductInfo ()
{
//
TODO: Add constructor logic here
//
}
3,

<summary>

Product number "PK"

</summary>

public int ProductId

{

get {return productId;}

set {productId = value;}

}

<summary>

Product Name

</summary>

public string ProductName

{

get {return productName;}

set {productName = value;}

}

<summary>

Unit Price

</summary>

Public decimal UnitPrice

{

get {return unitprice;}

set {UnitPrice = value;}

}

<summary>

is special "C" (special, not special)

</summary>

public string Special

{

get {return special;}

set {special = value;}

}

<summary>

Class numbering "FK"

</summary>

public int CategoryID

{

get {return CategoryID;}

set {CategoryID = value;}

}

}

/app_code/productoper.cs

Copy Code code as follows:

Using System;

Using System.Collections.Generic;

Using System.Data.SqlClient;
<summary>
Summary description of Productoper
</summary>
public class Productoper
{
<summary>
1,getall
</summary>
<returns></returns>
public static ilist<productinfo> GetAll ()
{
ilist<productinfo> dals = new list<productinfo> ();
String sql = "Select Productid,productname,unitprice,special,categoryid from Product ORDER by productId Desc";

1, create the Connection object
SqlConnection con = new DbConnection (). Con;
2, create the Command object
SqlCommand cmd = con. CreateCommand ();

3, pay the SQL statement to the Command object
Cmd.commandtext = SQL;

4, open the data connection

Con. Open ();

Try

{

using (SqlDataReader SDR = cmd. ExecuteReader ())

{

while (SDR. Read ())

{

ProductInfo dal = new ProductInfo ()

{

ProductId = SDR. GetInt32 (0),

ProductName = SDR. GetString (1),

UnitPrice = SDR. Getdecimal (2),

Special = SDR. GetString (3),

CategoryID = SDR. GetInt32 (4)

};

                     Dals. Add (DAL);
               }
           }
       }
        finally
        {
          //, turn off data connections (release resources)
             con. Close ();
       }
        return dals;
   }

public static void Add (ProductInfo dal)
{
String sql = INSERT INTO Product (Productname,unitprice,special,categoryid) VALUES (@productName, @unitprice, @special, @categoryId) ";

SqlConnection con = new DbConnection (). Con;
SqlCommand cmd = con. CreateCommand ();

        cmd.commandtext = SQL;
       //with parameters
        cmd. Parameters.Add (New SqlParameter ("@productName", Dal. ProductName));
        cmd. Parameters.Add (New SqlParameter ("@unitprice", Dal. UnitPrice));
        cmd. Parameters.Add (New SqlParameter ("@special", Dal. Special));
        cmd. Parameters.Add (New SqlParameter ("@categoryId", Dal. CategoryID));

Con. Open ();
Try
{
Cmd. ExecuteNonQuery ();
}
finally {
Con. Close ();
}

}
public static void Update (ProductInfo dal)
{
String sql = "Update Product set productname= @productName, unitprice= @unitprice, special= @special, categoryid=@ CategoryID where productid= @productId ";

SqlConnection con = new DbConnection (). Con;
SqlCommand cmd = con. CreateCommand ();

Cmd.commandtext = SQL;

collocation parameters

Cmd. Parameters.Add (New SqlParameter ("@productName", Dal. ProductName));

Cmd. Parameters.Add (New SqlParameter ("@unitprice", Dal. UnitPrice));

Cmd. Parameters.Add (New SqlParameter ("@special", Dal. Special));

Cmd. Parameters.Add (New SqlParameter ("@categoryId", Dal. CategoryID));

Cmd. Parameters.Add (New SqlParameter ("@productId", Dal. PRODUCTID));

Con. Open ();

Try

{

Cmd. ExecuteNonQuery ();

}

Finally

{

Con. Close ();

}

}
public static void Delete (int productId)
{
String sql = "Delete Product where productid= @productId";

SqlConnection con = new DbConnection (). Con;
SqlCommand cmd = con. CreateCommand ();

        cmd.commandtext = SQL;
       //with parameters
        cmd. Parameters.Add (New SqlParameter ("@productId", productId));
        con. Open ();
        try
        {
           cmd. ExecuteNonQuery ();
       }
        finally
        {
           con. Close ();
       }

}
Public Productoper ()
{
//
TODO: Add constructor logic here
//
}
}

, 8
/default.aspx

Copy Code code as follows:

<%@ Page language= "C #" autoeventwireup= "true" codefile= "Default.aspx.cs" inherits= "_default"%>

<! 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> Admin Page </title>

</head>

<body>

<form id= "Form1" runat= "Server" >

<div>

<asp:hyperlink id= "hlcreate" runat= "Server" text= "Add" navigateurl= "create.aspx" ></asp:HyperLink>

<asp:gridview id= "gvwproduct" runat= "Server" autogeneratecolumns= "False"

onrowcancelingedit= "Gvwproduct_rowcancelingedit"

Onrowdatabound= "Gvwproduct_rowdatabound" onrowdeleting= "gvwproduct_rowdeleting"

Onrowediting= "Gvwproduct_rowediting"

onrowupdating= "gvwproduct_rowupdating" width= "700px" allowpaging= "True"

onpageindexchanging= "Gvwproduct_pageindexchanging" pagesize= "5" >

<Columns>

<asp:templatefield headertext= "Product number" >

<EditItemTemplate>

<asp:label id= "Label6" runat= "server" text= ' <%# Bind ("productId")%> ' ></asp:Label>

</EditItemTemplate>

<ItemTemplate>

<asp:label id= "Label1" runat= "server" text= ' <%# Bind ("productId")%> ' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:templatefield headertext= "Product Name" >

<EditItemTemplate>

<asp:textbox id= "TextBox2" runat= "server" text= ' <%# Bind ("ProductName")%> ' ></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>

<asp:label id= "Label2" runat= "server" text= ' <%# Bind ("ProductName")%> ' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:templatefield headertext= "Unit Price" >

<EditItemTemplate>

<asp:textbox id= "TextBox3" runat= "server" text= ' <%# Bind ("UnitPrice")%> ' ></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>

<asp:label id= "Label3" runat= "server" text= ' <%# Bind ("UnitPrice")%> ' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:templatefield headertext= "Specials" >

<EditItemTemplate>

<asp:radiobuttonlist id= "RadioButtonList1" runat= "Server"

repeatdirection= "Horizontal" repeatlayout= "Flow" >

<asp:ListItem> Specials </asp:ListItem>

<asp:ListItem> Non-Specials </asp:ListItem>

</asp:RadioButtonList>

</EditItemTemplate>

<ItemTemplate>

<asp:label id= "Label4" runat= "server" text= ' <%# Bind ("special")%> ' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:templatefield headertext= "category number" >

<EditItemTemplate>

<asp:dropdownlist id= "DropDownList1" runat= "Server" >

</asp:DropDownList>

</EditItemTemplate>

<ItemTemplate>

<asp:label id= "Label5" runat= "server" text= ' <%# Bind ("CategoryID")%> ' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:commandfield showeditbutton= "True"/>

<asp:commandfield showdeletebutton= "True"/>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

/default.aspx.cs

Copy Code code as follows:

Using System;

Using System.Web.UI.WebControls;

public partial class _default:system.web.ui.page

{

<summary>

1, display products

</summary>

private void Bind ()

{

Gvwproduct.datasource = Productoper.getall ();

Gvwproduct.databind ();

}

protected void Page_Load (object sender, EventArgs e)

{

if (! IsPostBack)

{

Bind ();

}

}

protected void Gvwproduct_rowdeleting (object sender, Gridviewdeleteeventargs e)

{

Delete a row of data

Label ProductIdLabel = (label) Gvwproduct.rows[e.rowindex]. FindControl ("Label1");

int productId = Convert.ToInt32 (Productidlabel.text);

Call Delete method
Productoper.delete (PRODUCTID);

       //Update data
        Bind ();
   }
    protected void Gvwproduct_rowdatabound (object sender, GridViewRowEventArgs e)
    {
        if (E.row.rowtype = = Datacontrolrowtype.datarow)
        {
            //To cell, add click event
             E.row.cells[6]. Attributes.Add ("onclick", "return Confirm" (' You're sure you want to delete the row data!) ')");
       }
   }
    protected void gvwproduct_rowediting (object sender, GridViewEditEventArgs e)
   {

Label Speciallabel = (label) Gvwproduct.rows[e.neweditindex]. FindControl ("Label4");
Label Categoryidlabel = (label) Gvwproduct.rows[e.neweditindex]. FindControl ("Label5");

Enter edit mode
Gvwproduct.editindex = E.neweditindex; (Normal mode <-) watershed (-> edit mode)

Update data
Bind ();

        RadioButtonList specialradiobuttonlist = (RadioButtonList) Gvwproduct.rows[e.neweditindex]. FindControl ("RadioButtonList1");
        DropDownList categoryiddropdownlist = (DropDownList) gvwproduct.rows [E.neweditindex]. FindControl ("DropDownList1");
        specialradiobuttonlist.selectedvalue = Speciallabel.text;
        Categoryiddropdownlist.datasource = Categoryoper.getall ();
        Categoryiddropdownlist.datatextfield = "CategoryName";
        Categoryiddropdownlist.datavaluefield = "CategoryID";
        Categoryiddropdownlist.databind ();
        categoryiddropdownlist.selectedvalue = categoryidlabel.text;

}
protected void Gvwproduct_rowcancelingedit (object sender, Gridviewcancelediteventargs e)
{
Cancel edit mode
Gvwproduct.editindex =-1;

Update data
Bind ();
}
protected void Gvwproduct_rowupdating (object sender, Gridviewupdateeventargs e)
{
Update data

       //1, preparing criteria
        Label ProductIdLabel = (Label) Gvwproduct.rows[e.rowindex]. FindControl ("Label6");
        textbox Productnametextbox = (textbox) Gvwproduct.rows[e.rowindex]. FindControl ("TextBox2");
        textbox Unitpricetextbox = (textbox) Gvwproduct.rows[e.rowindex]. FindControl ("TextBox3");
        RadioButtonList specialradiobuttonlist = (RadioButtonList) Gvwproduct.rows[e.rowindex]. FindControl ("RadioButtonList1");
        DropDownList categoryiddropdownlist = (DropDownList) gvwproduct.rows [E.rowindex]. FindControl ("DropDownList1");

ProductInfo dal = new ProductInfo () {
Productid=convert.toint32 (Productidlabel.text),
Productname=productnametextbox.text,
Unitprice=convert.todecimal (Unitpricetextbox.text),
Special=specialradiobuttonlist.selectedvalue,
Categoryid=convert.toint32 (Categoryiddropdownlist.selectedvalue)
};
2, calling the method
Productoper.update (DAL);

Cancel edit mode
Gvwproduct.editindex =-1;

Update data
Bind ();

}
protected void Gvwproduct_pageindexchanging (object sender, Gridviewpageeventargs e)
{
Gvwproduct.pageindex = E.newpageindex;

Update data
Bind ();
}
}

/create.aspx

Copy Code code as follows:

<%@ Page language= "C #" autoeventwireup= "true" codefile= "Create.aspx.cs" inherits= "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> Add Page </title>

</head>

<body>

<form id= "Form1" runat= "Server" >

<div>

<asp:hyperlink id= "Hldefault" runat= "Server" text= "Product List" navigateurl= "~/default.aspx" ></asp:HyperLink>

<fieldset>

<legend> Add Products </legend>

<table width= "500px" >

<tr>

<td> Product Name </td>

<td>

<asp:textbox id= "Txtproductname" runat= "Server" ></asp:TextBox>

</td>

<td></td>

</tr>

<tr>

<td> Price </td>

<td>

<asp:textbox id= "Txtunitprice" runat= "Server" ></asp:TextBox>

</td>

<td></td>

</tr>

<tr>

<td> whether special </td>

<td>

<asp:radiobuttonlist id= "rblspecial" runat= "Server"

repeatdirection= "Horizontal" repeatlayout= "Flow" >

<asp:ListItem> Specials </asp:ListItem>

<asp:listitem selected= "True" > Non-Specials </asp:ListItem>

</asp:RadioButtonList>

</td>

<td></td>

</tr>

<tr>

<td> category </td>

<td>

<asp:dropdownlist id= "dropcategory" runat= "Server" >

</asp:DropDownList>

</td>

<td></td>

</tr>

<tr>

<td></td>

<td>

<asp:button id= "Btnadd" runat= "Server" text= "Add" onclick= "btnAdd_Click"/>

</td>

<td></td>

</tr>

</table>

</fieldset>

</div>

</form>

</body>

</html>

/create.aspx.cs

Copy Code code as follows:

Using System;

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

{

<summary>

1. List of categories

</summary>

private void Bind ()

{

Dropcategory.datasource = Categoryoper.getall ();

Dropcategory.datatextfield = "CategoryName";

Dropcategory.datavaluefield = "CategoryID";

Dropcategory.databind ();

}

protected void Page_Load (object sender, EventArgs e)

{

if (! IsPostBack)

{

Bind ();

}

}

protected void btnAdd_Click (object sender, EventArgs e)

{

ProductInfo dal = new ProductInfo () {

Productname=txtproductname.text.trim (),

Unitprice=convert.todecimal (TxtUnitprice.Text.Trim ()),

Special=rblspecial.selectedvalue,

Categoryid=convert.toint32 (Dropcategory.selectedvalue)

};

Call Add method
Productoper.add (DAL);

Response.Redirect ("~/default.aspx");
}
}

Author: Ylbtech
Source: http://ylbtech.cnblogs.com/

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More