How to Use DataList in asp.net, and edit, update, and delete data in pages (stored procedure creation)

 

Using System;

Using System. Collections. Generic;

Using System. Linq;

Using System. Web;

Using System. Web. UI;

Using System. Web. UI. WebControls;

Using System. Configuration;

Using System. Data. SqlClient;

Using System. Data;

Public partial class _ Default: System. Web. UI. Page

{

Protected void Page_Load (object sender, EventArgs e)

{

If (! IsPostBack)

BindProduct ("1 ");

}

Private void BindProduct (string pageindex)

{

String str = ConfigurationManager. ConnectionStrings ["studentCnn"]. ConnectionString;

Using (SqlConnection sqlCnn = new SqlConnection (str ))

{

SqlDataAdapter da = new SqlDataAdapter ("sp_Student_Select_by_Page_rowNumber", sqlCnn );

Da. SelectCommand. Parameters. AddWithValue ("@ pageIndex", pageindex );

Da. SelectCommand. Parameters. Add ("@ pageCount", SqlDbType. Int). Direction = ParameterDirection. Output;

Da. SelectCommand. Parameters. AddWithValue ("@ pageSize", 2 );

Da. SelectCommand. CommandType = CommandType. StoredProcedure;

DataSet ds = new DataSet ();

Da. Fill (ds );

This. DataList1.DataSource = ds. Tables [0]. DefaultView;

This. DataList1.DataBind ();

This. HiddenField1.Value = pageindex;

This. HiddenField2.Value = da. SelectCommand. Parameters ["@ pageCount"]. Value. ToString ();

}

} // Bind data to www.2cto.com

Protected void DataList1_ItemCommand (object source, DataListCommandEventArgs e)

{

If (e. CommandName = "buy ")

Response. Write (e. CommandArgument. ToString ());

}

Protected void DataList1_EditCommand (object source, DataListCommandEventArgs e)

{

This. DataList1.EditItemIndex = e. Item. ItemIndex;

This. BindProduct (this. HiddenField1.Value );

} // Edit

Protected void DataList1_CancelCommand (object source, DataListCommandEventArgs e)

{

This. DataList1.EditItemIndex =-1;

This. BindProduct (this. HiddenField1.Value );

} // Cancel

Protected void DataList1_UpdateCommand (object source, DataListCommandEventArgs e)

{

String name = (e. Item. FindControl ("TextBox1") as TextBox). Text;

String sex = (e. Item. FindControl ("TextBox2") as TextBox). Text;

String age = (e. Item. FindControl ("TextBox3") as TextBox). Text;

String str = ConfigurationManager. ConnectionStrings ["studentCnn"]. ConnectionString;

Using (SqlConnection sqlCnn = new SqlConnection (str ))

{

SqlCommand sqlcmm = sqlCnn. CreateCommand ();

Sqlcmm. CommandText = "update student set sname = @ sname, sex = @ sex, age = @ age where sid = @ sid ";

Sqlcmm. Parameters. AddWithValue ("@ sname", name );

Sqlcmm. Parameters. AddWithValue ("@ sex", sex );

Sqlcmm. Parameters. AddWithValue ("@ age", age );

Sqlcmm. Parameters. AddWithValue ("@ sid", e. CommandArgument );

SqlCnn. Open ();

Sqlcmm. ExecuteNonQuery ();

}

This. DataList1.EditItemIndex =-1;

This. BindProduct (this. HiddenField1.Value );

} // Update

Protected void DataList1_DeleteCommand (object source, DataListCommandEventArgs e)

{

String str = ConfigurationManager. ConnectionStrings ["studentCnn"]. ConnectionString;

Using (SqlConnection sqlCnn = new SqlConnection (str ))

{

SqlCommand sqlcmm = sqlCnn. CreateCommand ();

Sqlcmm. CommandText = "delete from student where sid = @ sid ";

Sqlcmm. Parameters. AddWithValue ("@ sid", e. CommandArgument );

SqlCnn. Open ();

Sqlcmm. ExecuteNonQuery ();

}

This. BindProduct (this. HiddenField1.Value );

} // Delete

Protected void Button6_Click (object sender, EventArgs e)

{

This. BindProduct ("1 ");

} // Home Page

Protected void Button9_Click (object sender, EventArgs e)

{

Int count = Convert. ToInt32 (this. HiddenField2.Value );

This. BindProduct (count. ToString ());

} // End page

Protected void Button7_Click (object sender, EventArgs e)

{

Int index = Convert. ToInt32 (this. HiddenField1.Value );

If (index> 1)

Index --;

This. BindProduct (index. ToString ());

} // Previous Page

Protected void Button8_Click (object sender, EventArgs e)

{

Int index = Convert. ToInt32 (this. HiddenField1.Value );

Int count = Convert. ToInt32 (this. HiddenField2.Value );

If (index <count)

Index ++;

This. BindProduct (index. ToString ());

}

} // Next page

Create a stored procedure:

Alter procedure sp_Student_Select_by_Page_rowNumber

@ PageSize int, -- number of records per page

@ PageCount int output, -- total number of pages

@ PageIndex int -- Index Number of the current page

 

AS

BEGIN

Declare @ totalRecords int

Select @ totalRecords = count (sid) from student

If (@ totalRecords % @ pageSize = 0)

Set @ pageCount = @ totalRecords/@ pageSize;

Else

Set @ pageCount = @ totalRecords/@ pageSize + 1;

With temp as (select row_number () over (order by sid) as id, * from student)

Select * from temp where id between (@ pageIndex-1) * @ pageSize + 1 and @ pageIndex * @ pageSize

Return @ totalRecords

End

Author: haitaoDoit