厚道善良守信宽容诚实谦虚正直执着

使用ADO.Net访问数据(程序事例)

1 数据表

use test1

go

if exists (select * from dbo.sysobjects where

id = object_id(N'[dbo].[People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[People]

GO

CREATE TABLE [dbo].[People] (

[pkID] [int] IDENTITY (1, 1) NOT NULL ,

[FirstName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,

[LastName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

select * from People

insert

into People(FirstName,LastName)

values ('jia','haitian')

insert

into People(FirstName,LastName)

values ('jia1','haitian1')

insert

into People(FirstName,LastName)

values ('jia2','haitian2')

2 HTML UI 界面

<%@ 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">

</head>

<body>

<div>

<br>

<b>Edit People</b>

</font>

<asp:DataGrid id="People" runat="server"

BorderColor="green"

Width="640"

PageSize="5"

AllowPaging="true"

OnPageIndexChanged="Page_Grid"

BorderWidth="1"

CellPadding="3"

AutoGenerateColumns="false"

ShowHeader="true"

Visible="true"

OnEditCommand="People_Edit"

OnCancelCommand="People_Cancel"

OnUpdateCommand="People_Update"

OnDeleteCommand="People_Delete" OnItemDataBound="People_ItemDataBound">

<HeaderStyle BorderColor="White" BackColor="black"

ForeColor="White"

Font-Bold="True"

Font-Size="9" HorizontalAlign="Center"/>

<ItemStyle BorderColor=""

BackColor="#FFFFF0"

ForeColor="Black"

Font-Size="8"

Font-Bold="False" HorizontalAlign="Center"/>

<EditItemStyle BorderColor=""

BackColor="#FFFFF0"

ForeColor="Black"

Font-Size="7"

Font-Bold="False" HorizontalAlign="Center"/>

<asp:BoundColumn HeaderText="ID" ReadOnly="true" DataField="pkID"/>

<asp:TemplateColumn>

<b> First Name </b>

</HeaderTemplate>

<asp:Label ID="Label1"

Text='<%# DataBinder.Eval(Container.DataItem, "FirstName").ToString().Trim() %>' runat="server"/>

</ItemTemplate>

<asp:TextBox id="FirstName" Text='<%# DataBinder.Eval(Container.DataItem, "FirstName").ToString().Trim() %>' runat="server" Width="100%"/>

</EditItemTemplate>

</asp:TemplateColumn>

<asp:TemplateColumn>

</HeaderTemplate>

<asp:Label ID="Label2"

Width="200"

Text='<%# DataBinder.Eval(Container.DataItem, "LastName").ToString().Trim() %>' runat="server"/>

</ItemTemplate>

<asp:TextBox id="LastName" Text='<%# DataBinder.Eval(Container.DataItem, "LastName").ToString().Trim() %>' runat="server" Width="100%"/>

</EditItemTemplate>

</asp:TemplateColumn>

<asp:EditCommandColumn

ButtonType="LinkButton"

CancelText="Cancel"

EditText="Edit"

UpdateText="Update" />

<asp:ButtonColumn Text= "Delete" CommandName="Delete" ></asp:ButtonColumn>

</Columns>

</asp:DataGrid>

<br />

新增数据到数据表

<br />

FirstName:<asp:TextBox ID=txtFirstName runat=server></asp:TextBox>&nbsp:&nbsp:&nbsp:&nbsp:

LastName:<asp:TextBox ID=txtLastName runat=server ></asp:TextBox>

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

</div>

</form>

</body>

</html>

3 后台操作代码

using System;

using System.Data;

using System.Configuration;

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;

using System.Collections;

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

{

//make first sql

String sql = "";

String strCnn = "Data Source=.;Initial Catalog=test1;User Id=sa;Password=19791225;";

//create a datasource function

public ICollection CreateDataSource()

{

SqlConnection conn = new SqlConnection(strCnn);

SqlDataAdapter db_sqladaptor = new SqlDataAdapter(sql, conn);

DataSet ds = new DataSet();

db_sqladaptor.Fill(ds, "MyDataResult");

DataView myView = ds.Tables["MyDataResult"].DefaultView;

return myView;

}

protected void Page_Load(object sender, EventArgs e)

{

//myDeleteButton.Attributes.Add("onclick", "return confirm('您真的要删除此行吗？');");

strCnn = "Data Source=.;Initial Catalog=test1;User Id=sa;Password=19791225;";

if (!IsPostBack)

{

sql = "Select * FROM People";

People.DataSource = CreateDataSource();

People.DataBind();

}

public void Page_Grid(Object sender, DataGridPageChangedEventArgs e)

{

sql = "Select * FROM People";

// Set CurrentPageIndex to the page the user clicked.

People.CurrentPageIndex = e.NewPageIndex;

// Rebind the data.

People.DataSource = CreateDataSource();

People.DataBind();

}

public void People_Edit(Object sender, DataGridCommandEventArgs e)

{

sql = "Select * FROM People";

People.EditItemIndex = e.Item.ItemIndex;

People.DataSource = CreateDataSource();

People.DataBind();

}

public void People_Cancel(Object sender, DataGridCommandEventArgs e)

{

sql = "Select * FROM People";

People.EditItemIndex = -1;

People.DataSource = CreateDataSource();

People.DataBind();

}

public void People_Update(Object sender, DataGridCommandEventArgs e)

{

string FirstName = ((TextBox)e.Item.Cells[1].Controls[1]).Text;

string LastName = ((TextBox)e.Item.Cells[2].Controls[1]).Text;

SqlConnection connUpdate = new SqlConnection(strCnn);

connUpdate.Open();

String sql_edit = "UPDATE People " +

"SET FirstName = '" + FirstName.Replace("'", "''") + "'," +

"LastName = '" + LastName.Replace("'", "''") + "'" +

" WHERE pkID = " + e.Item.Cells[0].Text;

SqlCommand sqlCommandUpdate = new SqlCommand(sql_edit, connUpdate);

sqlCommandUpdate.ExecuteNonQuery();

connUpdate.Close();

sql = "Select * FROM People";

People.EditItemIndex = -1;

People.DataSource = CreateDataSource();

People.DataBind();

}

public void People_Delete(Object sender, DataGridCommandEventArgs e)

{

//Response.Write("您要删除的是：<font color=red>"+e.Item.Cells[0]+"</font>");

//Response.Write("javascript:return confirm('你确认要把待入库的物品入库吗?');");

SqlConnection connDel = new SqlConnection(strCnn);

connDel.Open();

String sql_Del = "DELETE FROM People " +

" WHERE pkID = " + e.Item.Cells[0].Text;

SqlCommand sqlCommandDel = new SqlCommand(sql_Del, connDel);

sqlCommandDel.ExecuteNonQuery();

connDel.Close();

sql = "Select * FROM People";

People.EditItemIndex = -1;

People.DataSource = CreateDataSource();

People.DataBind();

}

protected void People_ItemDataBound(object sender, DataGridItemEventArgs e)

{

if ((e.Item.ItemType != ListItemType.Footer) && (e.Item.ItemType != ListItemType.Header) && (e.Item.ItemType != ListItemType.Pager))

{

LinkButton ldeleterecord = (LinkButton)e.Item.Cells[4].Controls[0];

ldeleterecord.Attributes.Add("onclick", "javascript:return confirm('you will delete?');");

}

protected void btnSave_Click(object sender, EventArgs e)

{

//保存数据到数据库

string FirstName = txtFirstName.Text.Trim();

string LastName = txtLastName.Text.Trim();

SqlConnection connUpdate = new SqlConnection(strCnn);

connUpdate.Open();

String sql_insert = "insert into People(FirstName,LastName) " +

"values ('" + FirstName.Replace("'", "''") + "'," +

" '" + LastName.Replace("'", "''") + "')";

SqlCommand sqlCommandUpdate = new SqlCommand(sql_insert, connUpdate);

sqlCommandUpdate.ExecuteNonQuery();

connUpdate.Close();

sql = "Select * FROM People";

People.EditItemIndex = -1;

People.DataSource = CreateDataSource();

People.DataBind();

}

posted @ 2007-06-25 16:46 jhtchina 阅读(275) 评论(0) 收藏举报

刷新页面返回顶部

厚道 善良 守信 宽容 诚实 谦虚 正直 执着

使用ADO.Net访问数据(程序事例)

公告

厚道善良守信宽容诚实谦虚正直执着