本人学习ASP.NET也是刚刚开始,尽管如此但还是按耐不住要把以前用ASP开发的基于WEB的进销存及OA系统用ASP.NET重写,此次重写将不仅是代码部份重写,数据库也将重新优化(主要是将会大量采用存储过程).我会将开发过程的大部分笔记和大家一起分享,本人才粗学浅,C#也是刚刚开始学习.如果有什么不对请大家不要见笑,还望多指教.
GridView应用一
本文将实现Gridview上添加/编辑/删除
点序号排序后
添加
编辑
删除
先看数据结构(上表的序号为Sequence字段作为排序用,不是UnitID编号)
代码部分:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="DBOperate" %><%@ Import Namespace="NgcOA.Msg" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script src="../Js/GridViewHelper.js" type="text/javascript"></script><script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
} protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.Header) AddGlyph(GridView1, e.Row); if (e.Row.RowType == DataControlRowType.DataRow) { string KeyValue = GridView1.DataKeys[e.Row.RowIndex].Value.ToString(); if (KeyValue == "0" && EditIndex == -1) // we are not editing e.Row.Attributes.Add("isadd", "1"); //string RowID = Convert.ToString(System.Web.UI.DataBinder.Eval(e.Row.DataItem, "UnitID")); //string Url = "Details.aspx?ID=" + RowID; // create the Url to be executed when the "+" is clicked (YOU WILL CUSTOMIZE THIS TO YOUR NEEDS) //e.Row.Attributes.Add("href", Url); // link to details //e.Row.Attributes.Add("open", "0"); // used by the detail table expander/contracter //e.Row.Attributes.Add("hascontent", "0"); // used to prevent excessive callbacks to the server } } public int EditIndex = -1; void AddGlyph(GridView grid, GridViewRow item) { if (grid.AllowSorting == false) return; Label glyph = new Label(); glyph.EnableTheming = false; glyph.Font.Name = "webdings"; glyph.Font.Size = FontUnit.XSmall; glyph.Text = (grid.SortDirection == SortDirection.Ascending ? "5" : " 6"); // Find the column you sorted by for (int i = 0; i < grid.Columns.Count; i++) { string colExpr = grid.Columns[i].SortExpression; if (colExpr != "" && colExpr == grid.SortExpression) { item.Cells[i].Controls.Add(glyph); } } } protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { string KeyValue = GridView1.DataKeys[e.RowIndex].Value.ToString(); if (KeyValue != "0") return; // key value of 0 indicates the insert row DBObject myDb = new DBObject(); myDb.SelectString = "SELECT UnitName FROM Base_ProductUnit WHERE UnitName='" + ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[1]).Text + "'"; myDb.GetDR(); if (myDb.SelectEmpty()) { SqlDataSource ds = (SqlDataSource)FindControl(GridView1.DataSourceID); SqlConnection conn = new SqlConnection(ds.ConnectionString); conn.Open(); string s = ds.InsertCommand; SqlCommand c = new SqlCommand(s, conn); SqlParameter p; foreach (System.Collections.DictionaryEntry x in e.NewValues) { p = new SqlParameter("@" + x.Key, x.Value); c.Parameters.Add(p); } c.ExecuteNonQuery(); } else { string msg = Message.Show("您要添加的记录已经存在，无法无成此次操作！"); Response.Write(msg); } myDb.DBClose(); } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { EditIndex = e.NewEditIndex;
}</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>无标题页</title> <link href="../Css/System.css" rel="STYLESHEET" type="text/css" /></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="BaseUnitDS" OnRowCreated="GridView1_RowCreated" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" DataKeyNames="UnitID" Width="180px" PageSize="20"> <Columns> <asp:CommandField ButtonType="Image" CancelImageUrl="~/Images/Cancel.gif" DeleteImageUrl="~/Images/Delete.gif" EditImageUrl="~/Images/Edit.gif" ShowDeleteButton="True" ShowEditButton="True" UpdateImageUrl="~/Images/Update.gif"> <HeaderStyle BackColor="#E0E0E0" HorizontalAlign="Left" VerticalAlign="Top" /> <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="40px" /> </asp:CommandField> <asp:BoundField DataField="UnitID" HeaderText="编号" InsertVisible="False" ReadOnly="True" SortExpression="UnitID" Visible="False" /> <asp:TemplateField HeaderText="序号" SortExpression="Sequence"> <EditItemTemplate> <asp:TextBox ID="EditSequence" CssClass="base_tb" runat="server" MaxLength="15" Text='<%# Bind("Sequence") %>' Width="40px"></asp:TextBox> <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="EditSequence" ErrorMessage="*" Operator="DataTypeCheck" Type="Integer"></asp:CompareValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="60px" /> <HeaderStyle HorizontalAlign="Left" VerticalAlign="Top"/> <ItemTemplate> <asp:Label ID="Sequence" runat="server" Text='<%# Bind("Sequence") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="单位名称" SortExpression="UnitName"> <EditItemTemplate> <asp:TextBox ID="EditUnitName" CssClass="base_tb" runat="server" MaxLength="15" Text='<%# Bind("UnitName") %>' Width="40px"></asp:TextBox> <asp:RequiredFieldValidator ID="UnitNameRequiredFieldValidator" runat="server" ControlToValidate="EditUnitName" Display="Dynamic" ErrorMessage="此处不能为空!" SetFocusOnError="True"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="80px"/> <HeaderStyle HorizontalAlign="Left" VerticalAlign="Top"/> <ItemTemplate> <asp:Label ID="UnitName" runat="server" Text='<%# Bind("UnitName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <HeaderStyle BackColor="#E0E0E0" /> </asp:GridView> </div> <asp:SqlDataSource ID="BaseUnitDS" runat="server" ConnectionString="<%$ ConnectionStrings:NgcDataConnectionString %>" SelectCommand="WM_ProductUnit_Select" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE [Base_ProductUnit] SET [UnitName]=@UnitName,[Sequence]=@Sequence WHERE [UnitID]=@UnitID" DeleteCommand="DELETE FROM [Base_ProductUnit] WHERE [UnitID]=@UnitID" InsertCommand="INSERT INTO [Base_ProductUnit] ([UnitName],[Sequence]) VALUES (@UnitName,@Sequence)"> </asp:SqlDataSource> </form> <script type="text/javascript"> GridViewHelper.Init(document.all.GridView1, 100, 0); </script></body></html> 注明一下一些用到的js或类
GridViewHelper.js
出处:http://www.thecodeproject.com/aspnet/MasterDetail.asp
代码如下:(这个js很有用,可以实现Gridview上增加新建记录的图标,嵌套从表及自适应高度和宽度,产生标题的tooltips等)
function GridViewHelperClass(){ function GridViewInit(idGrid, ExtraWidth, ExtraHeight) { var oTBODY = FindTBODY(idGrid); for(var i=1; ; i++) { try { if(oTBODY.childNodes[i].getAttribute("isadd") == null) continue; // content looks like: // <input type="button" value="Edit" onclick="javascript:__doPostBack('GridView1','Edit$0')" /> <input type="button" value="Delete" onclick="javascript:__doPostBack('GridView1','Delete$0')" /> // replace Edit with Add, remove Delete //oTBODY.childNodes[i].childNodes[0] 某行的第一个单元 if(oTBODY.childNodes[i].childNodes[0].childNodes[0].type=="text") oTBODY.childNodes[i].childNodes[0].childNodes[0].value="Add"; else oTBODY.childNodes[i].childNodes[0].childNodes[0].src="../Images/Add.gif"; var txt = oTBODY.childNodes[i].childNodes[0].innerHTML; var j = txt.indexOf(" "); oTBODY.childNodes[i].childNodes[0].innerHTML = txt.slice(0, j); } catch(e) { break; } } // put delete confirmations in, skip the header row for(var i=1; ; i++) { try { var ctl=oTBODY.childNodes[i].childNodes[0].childNodes[2]; if(oTBODY.childNodes[i].getAttribute("isadd") == "1") continue; /*window.alert(i+": "+ctl.outerHTML); window.alert(i+": "+ctl.onclick); window.alert(i+": "+ctl.tagName);*/ if(ctl.tagName == "INPUT") { var onc = ctl.onclick.toString(); // window.alert(onc); // uncomment this to see what the onclick actually contains // if(onc.indexOf("Delete$") == -1) // continue; // don't want to add confirm to "update cancel" var j = onc.indexOf("__do"); var k = onc.indexOf(")", j)+1; onc="if(confirm('您确定要执行此操作?') == false) return(false);"+onc.slice(j, k); ctl.onclick = onc; ctl.outerHTML = ctl.outerHTML; // if you don't do this then the onclick will not work. it is probably related to how the onclick is actually defined (see window.alert above) // window.alert(ctl.outerHTML); } } catch(e) { break; } } InitDrillDown(idGrid); ResizeMe(idGrid, ExtraWidth, ExtraHeight); } this.Init = GridViewInit; function ResizeMe(idGrid, ExtraWidth, ExtraHeight) { if(window.frameElement != null) { if(window.frameElement.tagName == "IFRAME") { // we are in an iframe // set the width to be really wide so there is no column wrapping window.frameElement.width = 10000; // now calculate the width required and set the frame width to it (plus a fudge factor) // window.alert("before "+GridViewHelper.CalcWidth(document.all.GridView1)+" "+ExtraWidth); window.frameElement.width = GridViewHelper.CalcWidth(idGrid)+ExtraWidth; // set the frame height to height of the generated document. // window.alert("height: "+document.body.scrollHeight); window.frameElement.height = document.body.scrollHeight+ExtraHeight; return; } } // get the container around the grid var Parent = idGrid.offsetParent; // make the parent really wide so that no columns will wrap Parent.style.width = "10000px"; // calcuate the real width var RealWidth = GridViewHelper.CalcWidth(idGrid)+100; // set the parent width back to nothing Parent.style.width = ""; //set the grid to the size it needs to be idGrid.width=""+RealWidth; } // change the onclick function for the select buttons function InitDrillDown(idGrid) { var oTBODY= FindTBODY(idGrid); for(var i=0; ; i++) { try { var ctl=oTBODY.childNodes[i].childNodes[0]; var selectctl = ctl.childNodes[ctl.childNodes.length-1] if(selectctl.tagName == "INPUT") { var onc = selectctl.onclick.toString(); // window.alert(onc); // uncomment this to see what the onclick actually contains if(onc.indexOf("Select$") == -1) continue; // probably an Add row line onc = "return(GridViewHelper.DrillDownOrUp(this));" selectctl.onclick = onc; selectctl.outerHTML = selectctl.outerHTML; // if you don't do this then the onclick will not work. it is probably related to how the onclick is actually defined (see window.alert above) } } catch(e) { break; } } } function GetParentObject(o, tagName) { srcElem = o; //crawl up to find the table while (srcElem.tagName != tagName) srcElem = srcElem.parentElement; return(srcElem); } function RowObjectToIndex(oTR) { if(oTR == null) return(-1); var oTABLE = GetParentObject(oTR, "TABLE"); // find the row index of our row var i; for(i=0; i<oTABLE.rows.length; i++) { if(oTABLE.rows[i] == oTR) { return(i); } } } function DrillDownOrUpX(This) { var oRow = GetParentObject(This, "TR"); // window.alert("oRow: "+oRow.outerHTML); var RowIndex = RowObjectToIndex(oRow) var oTable = GetParentObject(This, "TABLE"); // window.alert("in drill: open='"+oRow.open+"' hascontent='"+oRow.hascontent+"'"); var oPlusMinus = oRow.firstChild.childNodes[4]; if(oRow.open == "1") { var DetailsRow = oTable.rows[RowIndex+1]; DetailsRow.style.display="none"; oRow.open = "0"; var Gif = oPlusMinus.src; var iii = Gif.lastIndexOf("/"); Gif = Gif.slice(0, iii)+"/Plus.gif"; oPlusMinus.src = Gif; return(false); } if(oRow.hascontent == "1") { var DetailsRow = oTable.rows[RowIndex+1]; DetailsRow.style.display="block"; oRow.open = "1"; var Gif = oPlusMinus.src; var iii = Gif.lastIndexOf("/"); Gif = Gif.slice(0, iii)+"/Minus.gif"; oPlusMinus.src = Gif; return(false); } var ColumnCount = oRow.cells.length; // need to add the row var NewRow = oTable.insertRow(RowIndex+1); var NewCell = NewRow.insertCell(0); NewCell.setAttribute("colSpan", ColumnCount.toString()); var CellContent = "<table cellpadding='0' cellspacing='0'>"+ "<tr><td><iframe src='"+oRow.href+"' frameborder='0' width='100%' height='200'></iframe></td></tr>"+ "</table>"; // window.alert(CellContent); // window.prompt("", oRow.href); NewCell.innerHTML = CellContent; // window.alert("NewRow: "+NewRow.outerHTML); oRow.open = "1"; oRow.hascontent = "1"; var Gif = oPlusMinus.src; var iii = Gif.lastIndexOf("/"); Gif = Gif.slice(0, iii)+"/Minus.gif"; oPlusMinus.src = Gif; // window.alert("oRow: "+oRow.outerHTML); return(false); } this.DrillDownOrUp = DrillDownOrUpX; function FindTBODY(idGrid) { if(idGrid.firstChild.tagName == "TBODY") return(idGrid.firstChild); // there is a caption, so go down one more level return(idGrid.firstChild.nextSibling); } function CalcWidth(idGrid) { var oTBODY=FindTBODY(idGrid); var oTR = oTBODY.firstChild; // get the first row object var oLastCell = oTR.cells[oTR.cells.length-1]; var kb=0; var r = oLastCell; while(r) { kb+=r["offsetLeft"]; r=r.offsetParent } kb += oLastCell.offsetWidth; return kb; } this.CalcWidth = CalcWidth; function AddToolTips(idGrid, ToolTips) { var oTBODY=FindTBODY(idGrid); var oTR = oTBODY.firstChild;// get the first row object which contains the column titles if(ToolTips.length > oTR.children.length) ToolTips.length = oTR.children.length; for(var i=0; i<ToolTips.length; i++) { var oChild = oTR.children[i]; // window.alert("OOO: "+oChild.outerHTML); oChild.title = ToolTips[i]; } } this.AddToolTips = AddToolTips;}var GridViewHelper = new GridViewHelperClass();
<%@ Import Namespace="DBOperate" %>
借用网上一个网友的数据库控制类，自己实在懒得写，碰巧看到也就拿来自己用了(在判断添加新数据时是否重复用到)
using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web.UI.WebControls;namespace DBOperate{ /**/ /// <summary> /// 创建一个数据库对象，以实现数据操作。 /// </summary> public class DBObject { private string ConnectionString; private SqlConnection objConn; private string objSelectString; private string objInsertString; private string objUpdateString; private string objDeleteString; private SqlDataReader objDR; private SqlCommand objCmd; private string objErrMessage = null; private string tempErrMessage = null; private SqlDataAdapter objDA; private DataSet objDS; private DataView objDW; private GridView objGV; private string objViewRowFilter = null; private string objViewSort = null; public DBObject() { // // TODO: 在此处添加构造函数逻辑 // DBConn(); } private void DBConn() { ConnectionString = ConfigurationManager.ConnectionStrings["NgcDataConnectionString"].ConnectionString; objConn = new SqlConnection(ConnectionString); objConn.Open(); } public void DBClose() { objConn.Close(); } public string SelectString { set { objSelectString = value; } } public string InsertString { set { objInsertString = value; DBInsert(); } } public string UpdateString { set { objUpdateString = value; DBUpdate(); } } public string DeleteString { set { objDeleteString = value; DBDelete(); } } public string ErrMessage { get { tempErrMessage = objErrMessage; objErrMessage = null; return tempErrMessage; } } public SqlDataReader DR { get { GetDR(); return objDR; } } public void DRClose() { objDR.Close(); } public void GetDR() { try { using (objCmd = new SqlCommand(objSelectString, objConn)) { objDR = objCmd.ExecuteReader(); } } catch (System.Exception e) { objErrMessage += "数据读取出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } public void objDRRead() { objDR.Read(); } public bool SelectEmpty() { if (objDR.Read()) return false; else return true; } public string GetDRItem(string objDRItem) { return objDR[objDRItem].ToString(); } public DataSet DS { get { try { using (objDA = new SqlDataAdapter(objSelectString, objConn)) { objDS = new DataSet(); objDA.Fill(objDS); } } catch (System.Exception e) { objErrMessage += "数据读取出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } return objDS; } } public DataView DW { get { try { using (objDA = new SqlDataAdapter(objSelectString, objConn)) { objDS = new DataSet(); objDA.Fill(objDS); objDW = new DataView(objDS.Tables[0]); objDW.RowFilter = objViewRowFilter; objDW.Sort = objViewSort; } } catch (System.Exception e) { objErrMessage += "数据读取出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } return objDW; } } public GridView DGridBindDS { set { objGV = value; BindDS(); } } public GridView DGridBindDR { set { objGV = value; BindDR(); } } public string ViewRowFilter { set { if (objViewRowFilter == null) { objViewRowFilter = value; } else { objViewRowFilter += " and "; objViewRowFilter += value; } } } public string ViewSort { set { objViewSort = value; } } private void BindDR() { try { using (objCmd = new SqlCommand(objSelectString, objConn)) { objDR = objCmd.ExecuteReader(); objGV.DataSource = objDR; objGV.DataBind(); objDR.Close(); } } catch (System.Exception e) { objErrMessage += "数据读取出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } private void BindDS() { try { using (objDA = new SqlDataAdapter(objSelectString, objConn)) { objDS = new DataSet(); objDA.Fill(objDS); objGV.DataSource = objDS; objGV.DataBind(); } } catch (System.Exception e) { objErrMessage += "数据读取出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } private void DBInsert() { using (objCmd = new SqlCommand(objInsertString, objConn)) { try { objCmd.ExecuteNonQuery(); } catch (System.Exception e) { objErrMessage += "数据插入出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } } private void DBUpdate() { using (objCmd = new SqlCommand(objUpdateString, objConn)) { try { objCmd.ExecuteNonQuery(); } catch (System.Exception e) { objErrMessage += "数据更新出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } } private void DBDelete() { using (objCmd = new SqlCommand(objDeleteString, objConn)) { try { objCmd.ExecuteNonQuery(); } catch (System.Exception e) { objErrMessage += "数据删除出错："; objErrMessage += "<br>"; objErrMessage += e.Message; objErrMessage += "<br>"; } } } }}
<%@ Import Namespace="NgcOA.Msg" %>
弹出消息的类，也是用网上现成的．
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;namespace NgcOA.Msg{ /// <summary> /// Message 的摘要说明 /// </summary> public class Message { public Message() { // // TODO: 在此处添加构造函数逻辑 // } public static string Show(string strMsg) { //这里0就指strMsg这东西,1就是指\这东西. return String.Format("<script language={1}javascript{1}>alert({1}{0}{1});</script>", strMsg, "\""); } }}此外还将用到一个存储过程
CREATE PROCEDURE WM_ProductUnit_Select ASdeclare @num intbeginset @num=(SELECT isnull(MAX(Sequence),0) FROM Base_ProductUnit)+1SELECT '0' AS [UnitID], '' AS [UnitName], str(@num) AS [Sequence]UNIONSELECT [UnitID], [UnitName], [Sequence]FROM [Base_ProductUnit] ORDER BY [Sequence] DESCendGO下次将介绍实现如下的嵌套效果