不采用SqlDataSource来存储，改用后台DataSet调用数据来前台绑定
说明：平时开发都是用到SqlDataSource来存放数据连接字段及查询表结果，这样在连接Gridview等控件时非常好操作。
这次用工号查询取员工姓名，解决姓名为Null问题，决定不采用SqlDataSource来存储，改用后台DataSet。
这样就会出现如下几个问题，GridView控件的后台操作方法需要手写来处理。
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>姓名同步修改</title> </head> <body> <form id="form1" runat="server"> <div id="divGV" align="center"> <asp:GridView ID="gvStaffName" runat="server" AutoGenerateEditButton="True" onrowcancelingedit="gvStaffName_RowCancelingEdit" onrowediting="gvStaffName_RowEditing" onrowupdating="gvStaffName_RowUpdating" Width="720px" > </asp:GridView> </div> <asp:Label ID="lblID" runat="server" Text=""></asp:Label> </form> </body> </html>
1、定义公共参数：
SQLHelper sqlhelper = new SQLHelper(); string strID = "";
2、定义页面加载：protected void Page_Load(object sender, EventArgs e)
protected void Page_Load(object sender, EventArgs e) { Page.MaintainScrollPositionOnPostBack = true; gvStaffName.DataSource = sqlhelper.ExecuteQuery("select * from eLearningRecord"); gvStaffName.DataBind(); }
3、加载RowEditing编辑页面并保存取到的ID号
/// <summary> /// //加载RowEditing编辑页面并保存取到的ID号 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void gvStaffName_RowEditing(object sender, GridViewEditEventArgs e) { //加载RowEditing编辑页面 gvStaffName.EditIndex = e.NewEditIndex; gvStaffName.DataBind(); //输出行号ID Response.Write(e.NewEditIndex + 1 + "/"); strID = (e.NewEditIndex + 1).ToString(); lblID.Text = strID; }
4、取消操作，点击Cancel可以返回编辑页面
/// <summary> /// 取消操作，点击Cancel可以返回编辑页面 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void gvStaffName_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { gvStaffName.EditIndex = -1; gvStaffName.DataBind(); }
5、加载页面编辑操作RowUpdating
protected void gvStaffName_RowUpdating(object sender, GridViewUpdateEventArgs e) { DataSet dtResults = new DataSet(); DataSet dtResults_HR = new DataSet(); string strwebSql = "select * from eLearningRecord where id =" + lblID.Text + ""; //dtResults.Merge(sqlhelper.RQ_getFromStaffName("select * from eLearningRecord where id =" + (e.NewEditIndex + 1).ToString() + "")); dtResults = dtwebResult(strTxwebsvrSql); string strStaffName = ""; string strStaffName_HR = ""; string strStaffNo = ""; if (dtResults.Tables[0].Rows.Count > 0) { strStaffName = dtResults.Tables[0].Rows[0]["StaffName"].ToString(); strStaffNo = dtResults.Tables[0].Rows[0]["StaffNo"].ToString(); if (strStaffName == null || strStaffName.Length == 0) { //dtResults.Merge(sqlhelper.RunQuery_getStaffName("SELECT [StaffNo], [ChineseName] FROM [VHMS_StaffNameSection] where staffno =" + strStaffNo + "")); string strSql = "SELECT [StaffNo], [ChineseName] FROM [VHMS_StaffNameSection] where staffno =" + strStaffNo + ""; dtResults_HR = dtHRResult(strSql); if (dtResults_HR.Tables[0].Rows.Count > 0) { strStaffName_HR = dtResults_HR.Tables[0].Rows[0]["ChineseName"].ToString(); string strwebUpdate = "update eLearningRecord set StaffName = '" + strStaffName_HR + "' where StaffNo='" + strStaffNo + "'"; bool blUpdate = sqlhelper.bl_UpdateFromweb(strwebUpdate); if (blUpdate) { // Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "<script language=JavaScript>alert('姓名修改成功！');</script>"); } else { Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "<script language=JavaScript>alert('姓名修改失败，请重试！');</script>"); } } } } gvStaffName.DataSource = sqlhelper.ExecuteQuery("select * from eLearningRecord"); gvStaffName.DataBind(); //输出行号对应的员工姓名 strStaffName Response.Write(strStaffName + "/"); //输出HR表行号对应的员工姓名 strStaffName Response.Write(strStaffName_HR); //GridView点击更新后回到点击“编辑”前的样式 gvStaffName.EditIndex = -1; gvStaffName.DataBind(); } }
6、定义两个连接数据库的public 方法
/// <summary> /// 对HR表里的员工信息进来查找 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataSet dtHRResult(string strSql) { DataSet dtResults = new DataSet(); dtResults.Merge(sqlhelper.RunQuery_getStaffName(strSql)); return dtResults; } public DataSet dtwebResult(string strSql) { DataSet dtResults = new DataSet(); dtResults.Merge(sqlhelper.RQ_getFromwebStaffName(strSql)); return dtResults; }
