简单的GridView动态增加行并保存到数据库

最新推荐文章于 2014-03-28 22:15:06 发布

原创最新推荐文章于 2014-03-28 22:15:06 发布 · 1.1w 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#dataset #textbox #server #asp #string #object

本文介绍了一个使用ASP.NET中GridView控件进行数据展示及增删改查操作的示例。具体包括通过按钮触发添加空行、删除选中行、保存更改到数据库等功能。

这是个简单的示例关于GridView的操作

数据库表

create table OrderInfo
(
OrderNo nvarchar(25),--订单编号
OrderQuantity decimal--订单数量
primary key (OrderNo)
)

<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script language="javascript"> function AddNewRow() { if (event.keyCode == 13 && event.srcElement.type == "text") { document.getElementById("btnHiddenClick").click(); } } document.onkeydown = function() { if (event.keyCode == 13) { event.keyCode = 9; } } </script> </head> <body> <form id="form1" runat="server"> <div> <div> <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" /> <asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" /> <asp:Button ID="btnHiddenClick" runat="server" Text="Add" Style="display: none" mce_Style="display: none" OnClick="btnHiddenClick_Click" /> <asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" /> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> 订单编号 <asp:TextBox ID="txtOrderNo" runat="server"></asp:TextBox> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" Width="100%" OnRowCommand="GridView1_RowCommand" OnPreRender="GridView1_PreRender" ForeColor="#333333" GridLines="None"> <RowStyle BackColor="#EFF3FB" /> <Columns> <asp:TemplateField HeaderText="选择"> <ItemTemplate> <asp:CheckBox ID="CheckBox1" runat="server" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="操作"> <ItemTemplate> <asp:LinkButton ID="linkDelete" runat="server" Text="刪除" CommandName="d" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="订单编号"> <ItemTemplate> <asp:TextBox ID="txtOrderNo" runat="server" Text='<%# DataBinder.Eval(Container,"DataItem.OrderNo") %>'></asp:TextBox> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="订单数量"> <ItemTemplate> <asp:TextBox ID="txtOrderQuantity" runat="server" Text='<%# DataBinder.Eval(Container,"DataItem.OrderQuantity") %>'></asp:TextBox> </ItemTemplate> </asp:TemplateField> </Columns> <FooterStyle BackColor="#507CD1" ForeColor="White" Font-Bold="True" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#2461BF" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </div> </form> </body> </html>

public partial class test : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { Cache.Remove("OrderInfo"); this.BindPageData(""); } } private SqlCommand InsertCommand { get { SqlCommand insertCommand = new SqlCommand(); insertCommand.CommandText = "insert into OrderInfo(OrderNo,OrderQuantity) values(@OrderNo,@OrderQuantity)"; insertCommand.Parameters.Add(new SqlParameter("@OrderNo", SqlDbType.NVarChar, 0, ParameterDirection.Input, false, 0, 0, "OrderNo", DataRowVersion.Current, "")); insertCommand.Parameters.Add(new SqlParameter("@OrderQuantity", SqlDbType.Decimal, 0, ParameterDirection.Input, false, 0, 0, "OrderQuantity", DataRowVersion.Current, "")); return insertCommand; } } private SqlCommand UpdateCommand { get { SqlCommand updateCommand = new SqlCommand(); updateCommand.CommandText = "update OrderInfo set OrderNo=@OrderNo,OrderQuantity=@OrderQuantity where OrderNo=@OriginalOrderNo"; updateCommand.Parameters.Add(new SqlParameter("@OriginalOrderNo", SqlDbType.NVarChar, 0, ParameterDirection.Input, false, 0, 0, "OrderNo", DataRowVersion.Original, ""));//如果要修改主键要获取原来的值 updateCommand.Parameters.Add(new SqlParameter("@OrderNo", SqlDbType.NVarChar, 0, ParameterDirection.Input, false, 0, 0, "OrderNo", DataRowVersion.Current, "")); updateCommand.Parameters.Add(new SqlParameter("@OrderQuantity", SqlDbType.Decimal, 0, ParameterDirection.Input, false, 0, 0, "OrderQuantity", DataRowVersion.Current, "")); return updateCommand; } } private SqlCommand DeleteCommand { get { SqlCommand deleteCommand = new SqlCommand(); deleteCommand.CommandText = "delete from OrderInfo where OrderNo=@OrderNo "; deleteCommand.Parameters.Add(new SqlParameter("@OrderNo", SqlDbType.NVarChar, 0, ParameterDirection.Input, false, 0, 0, "OrderNo", DataRowVersion.Original, "")); return deleteCommand; } } public DataSet ExecuteDataSet(CommandType commandType, string sqlCommandText) { using (SqlConnection conn = new SqlConnection(@"server=.;database=Northwind;uid=sa;pwd=000000")) { DataSet ds = new DataSet(); try { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = commandType; cmd.CommandText = sqlCommandText; using (SqlDataAdapter dapter = new SqlDataAdapter(cmd)) { dapter.Fill(ds); } } } catch (SqlException ex) { throw ex; } return ds; } } public int UpdateDataSet(DataSet ds, string tableName, SqlCommand insertCommand, SqlCommand updateCommand, SqlCommand deleteCommand) { using (SqlConnection conn = new SqlConnection(@"server=.;database=Northwind;uid=sa;pwd=000000")) { conn.Open(); using (SqlTransaction transaction = conn.BeginTransaction()) { int rows = -1; if (insertCommand == null && updateCommand == null && deleteCommand == null) { return rows; } using (SqlDataAdapter dapter = new SqlDataAdapter()) { if (insertCommand != null) { insertCommand.Connection = conn; insertCommand.Transaction = transaction; dapter.InsertCommand = insertCommand; } if (updateCommand != null) { updateCommand.Connection = conn; updateCommand.Transaction = transaction; dapter.UpdateCommand = updateCommand; } if (deleteCommand != null) { deleteCommand.Connection = conn; deleteCommand.Transaction = transaction; dapter.DeleteCommand = deleteCommand; } try { rows = dapter.Update(ds, tableName); transaction.Commit(); } catch (SqlException ex) { transaction.Rollback(); throw ex; } return rows; } } } } private void SetCacheData(DataSet ds, string cacheName) { Cache.Remove(cacheName); Cache.Add(cacheName, ds, null, DateTime.Now.AddMinutes(20), TimeSpan.Zero, System.Web.Caching.CacheItemPriority.High, null); } private DataSet GetCacheData(string cacheName) { DataSet ds = Cache[cacheName] as DataSet; return ds; } public void ShowMessageBox(string msg) { Page.RegisterStartupScript("msg", "<mce:script language=/"javascript/" defer></mce:script>"); } private void AddNewRow(DataTable dt) { dt.Rows.Add(dt.NewRow()); } private bool SetRowData(DataTable dt) { int index = 0; string Qty = string.Empty, orderNo = string.Empty; foreach (DataRow row in dt.Rows) { if (row.RowState == DataRowState.Deleted) continue; orderNo = ((TextBox)this.GridView1.Rows[index].FindControl("txtOrderNo")).Text.Trim(); Qty = ((TextBox)this.GridView1.Rows[index].FindControl("txtOrderQuantity")).Text.Trim(); if (orderNo == string.Empty) { this.ShowMessageBox("第【" + (index + 1) + "】行的订单必须输入"); return false; } if (Qty == string.Empty) { this.ShowMessageBox("第【" + (index + 1) + "】行订单数量必须输入"); return false; } row["OrderNo"] = orderNo; row["OrderQuantity"] = Qty; index++; } return true; } private void BindPageData(string sqlWhere) {//绑定数据 string SqlCommandText = string.Format("select OrderNo,OrderQuantity from OrderInfo {0}", sqlWhere); DataSet ds = this.ExecuteDataSet(CommandType.Text, SqlCommandText); ds.Tables[0].TableName = "OrderInfo"; if (ds.Tables[0].Rows.Count == 0) { this.AddNewRow(ds.Tables[0]); } this.SetCacheData(ds, "OrderInfo"); this.GridView1.DataSource = ds.Tables["OrderInfo"].DefaultView; this.GridView1.DataBind(); } protected void btnAdd_Click(object sender, EventArgs e) { this.SetNewRow();//添加新行 } private void SetNewRow() { DataSet ds = this.GetCacheData("OrderInfo");//获取缓存数据 if (ds == null) return; if (!this.SetRowData(ds.Tables["OrderInfo"])) return;//检查数据填写是否完整 this.AddNewRow(ds.Tables["OrderInfo"]);//添加新行 this.SetCacheData(ds, "OrderInfo");//保存至缓存 this.GridView1.DataSource = ds.Tables["OrderInfo"].DefaultView;//重新绑定数据 this.GridView1.DataBind(); } private string Save(DataSet ds) { try { int i = this.UpdateDataSet(ds, "OrderInfo", this.InsertCommand, this.UpdateCommand, this.DeleteCommand); if (i == -1) { return "保存失败"; } } catch (Exception ex) { return ex.Message; } return string.Empty; } protected void btnSave_Click(object sender, EventArgs e) { DataSet ds = this.GetCacheData("OrderInfo");//获取缓存数据 if (ds == null) return; if (!this.SetRowData(ds.Tables["OrderInfo"])) return;//检查数据填写是否完整 string msg = this.Save(ds);//提交数据库 if (msg == string.Empty) { Cache.Remove("OrderInfo");//移除缓存 this.BindPageData(""); this.ShowMessageBox("保存成功"); } else { this.ShowMessageBox(msg); } } protected void btnDelete_Click(object sender, EventArgs e) { int count = 0; foreach (GridViewRow item in this.GridView1.Rows) { CheckBox chk = item.FindControl("CheckBox1") as CheckBox; if (chk.Checked) { this.DeleteRow(((TextBox)item.FindControl("txtOrderNo")).Text.Trim()); count++; //break; } } if (count == 0) { this.ShowMessageBox("请选择要删除的数据"); } } protected void btnSearch_Click(object sender, EventArgs e) { this.BindPageData(string.Format(" where OrderNo like'%{0}%'", this.txtOrderNo.Text.Trim())); } protected void btnHiddenClick_Click(object sender, EventArgs e) { this.SetNewRow();//回车事件增加行 } private void DeleteRow(string orderNo) { DataSet ds = this.GetCacheData("OrderInfo"); if (ds == null) return; DataRow[] rows = ds.Tables["OrderInfo"].Select(string.Format("OrderNo='{0}' ", orderNo)); if (rows.Length == 1) { rows[0].Delete(); } this.SetCacheData(ds, "OrderInfo"); this.GridView1.DataSource = ds.Tables["OrderInfo"].DefaultView; this.GridView1.DataBind(); } protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName.Equals("d")) { GridViewRow item = (e.CommandSource as LinkButton).Parent.NamingContainer as GridViewRow; string OrderNo = ((TextBox)item.FindControl("txtOrderNo")).Text.Trim(); this.DeleteRow(OrderNo); } } protected void GridView1_PreRender(object sender, EventArgs e) { if (this.GridView1.Rows.Count == 1) {//至少保留一行数据 ((LinkButton)this.GridView1.Rows[0].FindControl("linkDelete")).Enabled = false; } if (this.GridView1.Rows.Count > 0) {//最后一行添加回车增加行事件 ((TextBox)this.GridView1.Rows[this.GridView1.Rows.Count - 1].FindControl("txtOrderQuantity")).Attributes.Add("onkeydown", "AddNewRow()"); } } }

4 条评论

天冰晗 2012.12.14
如何在这个基础上进行分页排序啊，还请楼主明示。。

天冰晗 2012.12.06
谢谢楼主，受教啦

天冰晗 2012.12.06
[quote=erper6666]有个地方没看明白我对后台写前台东东不太清楚。 Page.RegisterStartupScri...[/quote]

erper6666 2012.03.02
有个地方没看明白我对后台写前台东东不太清楚。 Page.RegisterStartupScript("msg", "<mce:script language=/"javascript/" defer></mce:script>"); 这个都是在一行的还是怎么的有些没弄懂。