DataList之数据操作

原创

shawnhnd 2009-07-17 13:56:49 博主文章分类：.NET ©著作权

文章标签 职场休闲 DataList 文章分类 .Net 后端开发

©著作权归作者所有：来自51CTO博客作者shawnhnd的原创作品，请联系作者获取转载授权，否则将追究法律责任

背景：VisualStudio2005

实现：DataList之数据读取、编辑、删改等功能

界面：

HTML:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="datalist.aspx.cs" Inherits="datalist" %>

<!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>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DataList ID="DataList1" runat="server" Style="position: static;" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" Width="345px" OnItemCommand="DataList1_ItemCommand" OnEditCommand="DataList1_EditCommand" OnCancelCommand="DataList1_CancelCommand" OnUpdateCommand="DataList1_UpdateCommand" OnDeleteCommand="DataList1_DeleteCommand">
            <ItemTemplate>
                 
                <asp:LinkButton ID="lBtnShowDetails" runat="server" Style="left: -15px;
                    position: static; top: 0px" Width="55px" CommandName="select" >Details</asp:LinkButton>
                <asp:LinkButton ID="lBtnEdit" runat="server" CommandName="edit" Width="31px">Edit</asp:LinkButton>
                <asp:LinkButton ID="lBtnDelete" runat="server" CommandName="delete" Width="42px" OnClientClick="return confirm( '你确定要删除吗?');">Delete </asp:LinkButton><%# DataBinder.Eval(Container.DataItem,"LastName") %><%# DataBinder.Eval(Container.DataItem,"FirstName") %>
            </ItemTemplate>
            <SelectedItemTemplate>
            Name:<%# DataBinder.Eval(Container.DataItem, "LastName")%>
                 <%# DataBinder.Eval(Container.DataItem,"FirstName") %>
                 <br />
            Birth:<%# DataBinder.Eval(Container.DataItem,"BirthDate","{0:D}") %>
                 <br />
            City:<%# DataBinder.Eval(Container.DataItem,"City") %>
                 <br />
            Address:<%# DataBinder.Eval(Container.DataItem,"Address") %>
                <br />
                <asp:Button ID="btnExit" runat="server" OnClick="btnExit_Click" Text="Exit" Width="44px" />
            </SelectedItemTemplate>
            <FooterStyle BackColor="#CCCCCC" Font-Bold="True" Font-Italic="False" Font-Overline="False"
                Font-Strikeout="False" Font-Underline="False" ForeColor="Black" HorizontalAlign="Center" Font-Size="10pt" />
            <SelectedItemStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <AlternatingItemStyle BackColor="Gainsboro" Font-Bold="False" Font-Italic="False" Font-Overline="False"
                Font-Strikeout="False" Font-Underline="False" />
            <ItemStyle BackColor="#EEEEEE" ForeColor="Black" />
            <HeaderTemplate>
                Employees Information
            </HeaderTemplate>
            <HeaderStyle BackColor="#000084" Font-Bold="True" Font-Italic="False" Font-Overline="False"
                Font-Strikeout="False" Font-Underline="False" ForeColor="White" HorizontalAlign="Center" />
            <EditItemTemplate>
                <%# DataBinder.Eval(Container.DataItem,"LastName") %>
                <%# DataBinder.Eval(Container.DataItem,"FirstName") %>
                <br />
                <%# DataBinder.Eval(Container.DataItem,"City") %>
                <asp:TextBox ID="txtCity" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"City") %>' Width="94px"></asp:TextBox><br />
                <asp:LinkButton ID="LinkButton2" runat="server" CommandName="update" Width="40px" OnClientClick="return confirm('你确定保存吗？');">Save</asp:LinkButton>
                <asp:LinkButton ID="LinkButton1" runat="server" CommandName="cancel">Cancel</asp:LinkButton>
            </EditItemTemplate>
        </asp:DataList>
             <br />
        <table style="width: 345px">
            <tr>
                <td align="center" style="width: 40px; height: 23px">
        <asp:Label ID="Label1" runat="server" Text="Total: " Width="35px"></asp:Label>
                </td>
                <td align="center" style="width: 56px; height: 23px">
                     <asp:Label
            ID="lblTotal" runat="server" Text="Label" Width="7px" Font-Bold="True" ForeColor="#0000C0"></asp:Label></td>
                <td align="center" style="width: 53px; height: 23px">
                    <asp:Label
                ID="Label3" runat="server" Text="Current: " Width="43px"></asp:Label></td>
                <td align="left" style="width: 160px; height: 23px">
                    <asp:Label
                    ID="lblCurrent" runat="server" Text="Label" Width="42px" Font-Bold="True" ForeColor="#0000C0"></asp:Label></td>
            </tr>
            <tr>
                <td align="center" style="width: 40px; height: 21px">
                    <asp:Button
                        ID="btnFirst" runat="server" OnClick="btnFirst_Click"
                        Text="First" Width="38px" /></td>
                <td align="center" style="width: 56px; height: 21px">
                    <asp:Button ID="btnPrevious" runat="server" OnClick="btnPrevious_Click" Text="Previous" Width="55px" /></td>
                <td align="center" style="width: 53px; height: 21px">
                    <asp:Button ID="btnNext"
                                runat="server" OnClick="btnNext_Click" Text="Next" Width="55px" /></td>
                <td align="left" style="width: 160px; height: 21px">
                    <asp:Button
                                    ID="btnLast" runat="server" OnClick="btnLast_Click"
                                    Text="Last" Width="38px" /></td>
            </tr>
        </table>

    </div>
    </form>
</body>
</html>

CODE:

            using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class datalist : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //初始化时设置当前页为第一页
            lblCurrent.Text = "1";
            bindDataToDatalist();
        }
    }
    private void bindDataToDatalist()
    {
        int currentPage = Convert.ToInt32(lblCurrent.Text);
        SqlConnection con = DB.createCon();
        //初始化SqlDataAdapter 实例
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand("select * from employees", con);
        DataSet ds = new DataSet();
        sda.Fill(ds, "emp");
        //DataList控件在绑定数据时，应先将DataKeyField属性设置为数据表的主键。
        this.DataList1.DataKeyField = "EmployeeID";
        PagedDataSource ps = new PagedDataSource();
        ps.DataSource = ds.Tables["emp"].DefaultView;
        //启用分页
        ps.AllowPaging = true;
        //每页显示的项数
        ps.PageSize = 2;
        //获取当前页的索引，从0开始
        ps.CurrentPageIndex = currentPage - 1;
        this.btnFirst.Enabled = true;
        this.btnPrevious.Enabled = true;
        this.btnNext.Enabled = true;
        this.btnLast.Enabled = true;
        if (currentPage == 1)
        {
            btnFirst.Enabled = false;
            btnPrevious.Enabled = false;
        }
        //所有项所需的总页数
        if (currentPage == ps.PageCount )
        {
            btnLast.Enabled = false;
            btnNext.Enabled = false;
        }
        //this.DataList1.DataSource = ds.Tables["emp"];
        //将lblTotal设置为总页数
        this.lblTotal.Text = Convert.ToString(Convert.ToInt32(ps.PageCount));
        this.DataList1.DataSource = ps;
        this.DataList1.DataBind();

    }
    protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName == "select")
        {
           //获取DataList控件中的选定项的索引；获取DataList.Item控件中的DataListItem对象的索引
            this.DataList1.SelectedIndex = e.Item.ItemIndex;
            this.bindDataToDatalist();
        }
    }
    protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
    {
        //设置选中行的索引为当前选择行的索引
        this.DataList1.EditItemIndex = e.Item.ItemIndex;
        this.bindDataToDatalist();
    }

    protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
    {
        //设置控件的编辑项的索引为-1，即取消编辑
        this.DataList1.EditItemIndex = -1;
        //取消选择项
        this.DataList1.SelectedIndex = -1;
        this.bindDataToDatalist();
    }
    protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        //查找索引对应的主键值，由DataKeys集合利用索引值取得各数据的索引值
        string empID = this.DataList1.DataKeys[e.Item.ItemIndex].ToString();
        //在编辑的行中查找对应的要更改的城市的名字，用FindControl()查找txtCity控件
        string city = ((TextBox)(e.Item.FindControl("txtCity"))).Text;
        SqlConnection con = DB.createCon();
        SqlCommand cmd = new SqlCommand("update employees_new set City='" + city + "' where EmployeeID='" + empID + "'", con);
        con.Open();
        cmd.ExecuteNonQuery();
        //设置选中行的索引为-1，取消该数据项的选择
        this.DataList1.EditItemIndex = -1;
        this.bindDataToDatalist();

    }
    protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
    {
        string empID = this.DataList1.DataKeys[e.Item.ItemIndex].ToString();
        SqlConnection con = DB.createCon();
        SqlCommand cmd = new SqlCommand("delete from employees_new where EmployeeID='" + empID + "'", con);
        con.Open();
        //对连接执行T-SQL语句，并返回受影响的行数
        cmd.ExecuteNonQuery();
        this.DataList1.EditItemIndex = -1;
        this.bindDataToDatalist();
    }

    protected void btnFirst_Click(object sender, EventArgs e)
    {
        this.lblCurrent.Text = "1";
        this.bindDataToDatalist();
    }
    protected void btnPrevious_Click(object sender, EventArgs e)
    {
        this.lblCurrent.Text = Convert.ToString(Convert.ToInt32(this.lblCurrent.Text) - 1);
        this.bindDataToDatalist();
            }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        this.lblCurrent.Text = Convert.ToString(Convert.ToInt32(this.lblCurrent.Text) + 1);
        this.bindDataToDatalist();
    }
    protected void btnLast_Click(object sender, EventArgs e)
    {
        this.lblCurrent.Text = this.lblTotal.Text;
        this.bindDataToDatalist();
    }

   //退出查看详细信息模式
    protected void btnExit_Click(object sender, EventArgs e)
    {
        this.DataList1.SelectedIndex = -1;
        this.bindDataToDatalist();
    }

}

运行效果：