主要有两个页面
PastList.aspx 主表列表页面
PastView.aspx 主从表编辑页面
SqlHelper.cs 数据访问类和一个实体类一个枚举类
*********************************
PastList.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartList.aspx.cs" Inherits="PartList" %>
<!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:GridView ID="myGridView" runat=server AutoGenerateColumns=False HorizontalAlign=Center CellPadding="4" ForeColor="#333333" GridLines="None" Height="97px" Width="587px">
<Columns>
<asp:BoundField DataField="BigTypeName" HeaderText = "类别名称" />
<asp:BoundField DataField="BigTypeDescribe" HeaderText = "类别描述" />
<asp:TemplateField HeaderText="编辑">
<ItemTemplate>
<a href='PartView.aspx?ID=<%# Eval("ID") %>'>编辑</a>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<center><asp:Button ID="Button1" runat="server" Text="新增" OnClick="Button1_Click" /></center>
</div>
</form>
</body>
</html>
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;
public partial class PartList : System.Web.UI.Page
{
SqlHelper sqlHelper = new SqlHelper();
private static DataTable dtType;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
bindData(true);
}
/// <summary>
/// 数据绑定
/// </summary>
/// <param name="refresh"></param>
private void bindData(bool refresh)
{
if (refresh || dtType == null)
{
DataSet ds = sqlHelper.getDs("Select * from BigType order by TypeOrder", CommandType.Text, null, "BigType");
dtType = ds.Tables[0];
}
this.myGridView.DataSource = dtType.DefaultView;
this.myGridView.DataBind();
}
/// <summary>
/// 新增
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("PartView.aspx");
}
}
********************
PartView.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartView.aspx.cs" Inherits="PartView" %>
<!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>
<script language="javascript">
function checkFrm()
{
if(document.form1.txtBigTypeName.value.length==0)
{
alert("请输入类别名称");
document.form1.txtBigTypeName.focus();
return(false);
}
if(isNaN(document.form1.txtTypeOrder.value) || document.form1.txtTypeOrder.value.length==0)
{
alert("请输入数字");
document.form1.txtTypeOrder.focus();
return(false);
}
}
function checkDel(str)
{
if(!confirm("确认删除 "+str+" 吗?"))
return(false);
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<table border="1" align="center" style="border-collapse:collapse">
<tr>
<td align="right">类别名称:</td>
<td align="left"><asp:TextBox ID="txtBigTypeName" runat="server" MaxLength="50" Width="313px"></asp:TextBox></td>
</tr>
<tr>
<td align="right">类别描述:</td>
<td align="left"><asp:TextBox TextMode="MultiLine" Rows="7" Columns="50" ID="txtBigTypeDescribe" runat="server" MaxLength="500"></asp:TextBox></td>
</tr>
<tr>
<td align="right">类别排序:</td>
<td align="left"><asp:TextBox ID="txtTypeOrder" runat="server" MaxLength="50" Width="307px"></asp:TextBox></td>
</tr>
<tr>
<td align="center" colspan="2">
<asp:Button ID="BtSave" Text="保存" runat="server" OnClick="BtSave_Click" />
<asp:Button ID="BtAdd" Text="新增从记录" runat="server" OnClick="BtAdd_Click" />
</td>
</tr>
</table>
<asp:GridView ID="detailGridView" AutoGenerateColumns="False" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Height="79px" Width="527px" OnRowCancelingEdit="detailGridView_RowCancelingEdit" OnRowDeleting="detailGridView_RowDeleting" OnRowEditing="detailGridView_RowEditing" OnRowUpdating="detailGridView_RowUpdating" OnDataBound="detailGridView_DataBound">
<Columns>
<asp:BoundField DataField="ID" HeaderText = "编号" />
<asp:TemplateField HeaderText="小类名称">
<ItemTemplate>
<asp:Label ID="showSmallTypeName" runat="server" Text='<%# Eval("SmallTypeName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtSmallTypeName" runat="server" Text='<%# Eval("SmallTypeName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="描述">
<ItemTemplate>
<asp:Label ID="showSmallTypeDescribe" runat="server" Text='<%# Eval("SmallTypeDescribe") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtSmallTypeDescribe" runat="server" Text='<%# Eval("SmallTypeDescribe") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="排序">
<ItemTemplate>
<asp:Label ID="showSmallTypeOrder" runat="server" Text='<%# Eval("SmallTypeOrder") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtSmallTypeOrder" runat="server" Text='<%# Eval("SmallTypeOrder") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:TextBox ID="hidID" runat=server Text="" Visible=false></asp:TextBox>
</div>
</form>
</body>
</html>
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 PartView : System.Web.UI.Page
{
private int BigTypeID;
private string Sql;
SqlHelper sqlHelper = new SqlHelper();
private static DataTable dtSmallType = null;
protected void Page_Load(object sender, EventArgs e)
{
this.BtSave.Attributes.Add("onclick", "return checkFrm();");
if (!Page.IsPostBack)
{
BigTypeID = Convert.ToInt32(Request.QueryString["id"]);
this.hidID.Text = BigTypeID.ToString();
if (BigTypeID != 0)
{//表示修改状态
bindBigType();
bindData(true);
}
else
{//新增时,把架构给dtSmallType
fillTable();
}
}
else
{
BigTypeID = Convert.ToInt32(this.hidID.Text);
}
}
/// <summary>
/// 取从表的架构给 dtSmallType
/// </summary>
private void fillTable()
{
Sql = "Select * from SmallType where 1=0";
dtSmallType = sqlHelper.getDs(Sql, CommandType.Text, null, "SmallType").Tables[0];
}
/// <summary>
/// 绑定主表
/// </summary>
private void bindBigType()
{
Sql = "Select * from BigType where ID=" + BigTypeID;
SqlDataReader dr;
dr = sqlHelper.getDr(Sql, CommandType.Text, null);
while (dr.Read())
{
this.txtBigTypeName.Text = dr["BigTypeName"].ToString();
this.txtBigTypeDescribe.Text = dr["BigTypeDescribe"].ToString();
this.txtTypeOrder.Text = dr["TypeOrder"].ToString();
}
}
/// <summary>
/// 数据绑定,并且判断从表记录是否从数据库中从新读取
/// </summary>
/// <param name="refresh"></param>
private void bindData(bool refresh)
{
//绑定从表数据
if (refresh || dtSmallType == null)
{
Sql = "Select * from SmallType where BigTypeID=" + BigTypeID;
dtSmallType = sqlHelper.getDs(Sql, CommandType.Text, null, "SmallType").Tables[0];
}
this.detailGridView.DataSource = dtSmallType.DefaultView;
this.detailGridView.DataBind();
}
/// <summary>
/// 新增从表记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtAdd_Click(object sender, EventArgs e)
{
DataRow row = dtSmallType.NewRow();
row["ID"] = (int)getMaxIdInTable(dtSmallType, "ID") + 1;
if (this.BigTypeID != 0)
{ //表示修改主从记录
row["BigTypeID"] = BigTypeID;
}
dtSmallType.Rows.Add(row);
this.detailGridView.EditIndex = dtSmallType.Rows.Count - 1;
this.bindData(false);
}
/// <summary>
/// 保存主表修改
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtSave_Click(object sender, EventArgs e)
{
if (this.detailGridView.EditIndex > -1)
{
Response.Write("<script language=javascript>alert('请先结束从表编辑');</script>");
return;
}
BigTypeEntity entity = new BigTypeEntity();
entity.BigTypeName = this.txtBigTypeName.Text.ToString();
entity.BigTypeDescribe = this.txtBigTypeDescribe.Text.ToString();
entity.TypeOrder = Convert.ToInt16(this.txtTypeOrder.Text);
if (BigTypeID != 0)
{ //表示修改
entity.ID = BigTypeID;
sqlHelper.SaveBigTypeAndSmallType(entity, dtSmallType, EditMethod.Update);
}
else
{//表示新增
sqlHelper.SaveBigTypeAndSmallType(entity, dtSmallType, EditMethod.Insert);
BigTypeID = (int)sqlHelper.getScalar("Select Top 1 ID from BigType Order By ID Desc", CommandType.Text, null);
this.hidID.Text = BigTypeID.ToString();
}
this.bindData(true);
}
/// <summary>
/// GridView编辑事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void detailGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
this.detailGridView.EditIndex = e.NewEditIndex;
this.bindData(false);
}
/// <summary>
/// GridView撤消事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void detailGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
this.detailGridView.EditIndex = -1;
this.bindData(false);
}
/// <summary>
/// GridView更新事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void detailGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//保存在本地
int i = this.detailGridView.EditIndex;
string typeName = ((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeName")).Text.ToString();
string typeDescribe = ((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeDescribe")).Text.ToString();
int typeOrder = Convert.ToInt16(((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeOrder")).Text);
dtSmallType.Rows[i]["SmallTypeName"] = typeName;
dtSmallType.Rows[i]["SmallTypeDescribe"] = typeDescribe;
dtSmallType.Rows[i]["SmallTypeOrder"] = typeOrder;
this.detailGridView.EditIndex = -1;
this.bindData(false);
}
/// <summary>
/// GridView删除事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void detailGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int selectIndex = e.RowIndex;
//这里的 Cells[0] 对应的是编号列
int id = Convert.ToInt16(this.detailGridView.Rows[selectIndex].Cells[0].Text);
//从数据库中删除
string Sql;
Sql = "Delete from SmallType where ID=" + id;
sqlHelper.ExecuteSql(Sql, CommandType.Text, null);
//从内存中删除
DataRow[] dr = dtSmallType.Select("ID=" + id);
if(dr.Length > 0)
dtSmallType.Rows.Remove(dr[0]);
this.bindData(false);
}
/// <summary>
/// 得到指定表中关键字的最大值,此方法可以放在公共运行函数当中
/// </summary>
/// <param name="table"></param>
/// <param name="keyID"></param>
/// <returns></returns>
private object getMaxIdInTable(DataTable table, string keyID)
{
if (table.Rows.Count == 0)
return 0;
DataView dv = new DataView();
dv.Table = table;
dv.Sort = keyID + " Desc";
return dv[0][keyID];
}
protected void detailGridView_DataBound(object sender, EventArgs e)
{
}
}
******************
SqlHelper.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
public class SqlHelper
{
private const string ConnStr = "Data Source=127.0.0.1;Initial Catalog=HJ;User id=sa;Password=as;";
private SqlConnection Conn = null;
private SqlCommand Cmd = null;
private SqlDataAdapter Adp = null;
private DataSet ds = new DataSet();
/// <summary>
/// 返回 DataSet
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <param name="tableName"></param>
public DataSet getDs(string cmdText, CommandType cmdType, SqlParameter[] pars, string tableName)
{
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
Adp = new SqlDataAdapter(Cmd);
try
{
Conn.Open();
if (tableName == null || tableName == string.Empty)
Adp.Fill(ds);
else
Adp.Fill(ds, tableName);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return ds;
}
/// <summary>
/// 执行Sql语句,返回受影响的行数
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <returns></returns>
public int ExecuteSql(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
int res = 0;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
res = Cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return res;
}
/// <summary>
/// 返回 DataReader 对象
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <returns></returns>
public SqlDataReader getDr(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
SqlDataReader dr = null;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
}
return dr;
}
public object getScalar(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
object res = null;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
res = Cmd.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return res;
}
/// <summary>
/// 只为主从表保存测试使用
/// </summary>
/// <param name="entity">主表实体</param>
/// <param name="dtSmallType">从表的Table,包含状态</param>
/// <param name="method">方法,是否新增或者修改</param>
public void SaveBigTypeAndSmallType(BigTypeEntity entity, DataTable dtSmallType, EditMethod method)
{
int BigTypeID;
string Sql;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.CommandType = CommandType.Text;
Cmd.Connection = Conn;
Conn.Open();
SqlTransaction tran = Conn.BeginTransaction();
Cmd.Transaction = tran;
try
{
if (method == EditMethod.Insert)
{
//插入主表
Sql = "Insert Into BigType(BigTypeName,BigTypeDescribe,TypeOrder) values('" + entity.BigTypeName + "','" + entity.BigTypeDescribe + "'," + entity.TypeOrder + ")";
Cmd.CommandText = Sql;
Cmd.ExecuteNonQuery();
//取得刚才插入的 ID,如果主键是GUID,在这里处理就方便些
Cmd.CommandText = "Select Top 1 ID from BigType order By ID Desc";
BigTypeID = (int)Cmd.ExecuteScalar();
//插入从表
foreach (DataRow dr in dtSmallType.Rows)
{//新增状态下,从表所有行的DataRow属性都为Added
Sql = "Insert Into SmallType(SmallTypeName,SmallTypeDescribe,SmallTypeOrder,BigTypeID) ";
Sql += "values('" + dr["SmallTypeName"].ToString() + "','" + dr["SmallTypeDescribe"].ToString() + "'," + dr["SmallTypeOrder"].ToString() + "," + BigTypeID + ")";
Cmd.CommandText = Sql;
Cmd.ExecuteNonQuery();
}
}
else if (method == EditMethod.Update)
{
//修改主表
Sql = "Update BigType Set BigTypeName='" + entity.BigTypeName + "',BigTypeDescribe='" + entity.BigTypeDescribe + "',TypeOrder=" + entity.TypeOrder + " where ID=" + entity.ID.ToString();
Cmd.CommandText = Sql;
Cmd.ExecuteNonQuery();
//插入从表
foreach (DataRow dr in dtSmallType.Rows)
{
if (dr.RowState == DataRowState.Added)
{
Sql = "Insert Into SmallType(SmallTypeName,SmallTypeDescribe,SmallTypeOrder,BigTypeID) ";
Sql += "values('" + dr["SmallTypeName"].ToString() + "','" + dr["SmallTypeDescribe"].ToString() + "'," + dr["SmallTypeOrder"].ToString() + "," + entity.ID.ToString() + ")";
Cmd.CommandText = Sql;
Cmd.ExecuteNonQuery();
}
else if (dr.RowState == DataRowState.Modified)
{
Sql = "Update SmallType Set SmallTypeName='" + dr["SmallTypeName"].ToString() + "',SmallTypeDescribe='" + dr["SmallTypeDescribe"].ToString() + "',";
Sql += "SmallTypeOrder=" + dr["SmallTypeOrder"].ToString() + " where ID=" + dr["ID"].ToString();
Cmd.CommandText = Sql;
Cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception e)
{
tran.Rollback();
throw new Exception(e.ToString());
}
finally
{
tran.Commit();
Conn.Close();
}
}
}
public class BigTypeEntity
{
private int _ID;
private string _BigTypeName;
private string _BigTypeDescribe;
private int _TypeOrder;
public int ID
{
get { return this._ID; }
set { this._ID = value;}
}
public string BigTypeName
{
get { return this._BigTypeName; }
set { this._BigTypeName = value; }
}
public string BigTypeDescribe
{
get { return this._BigTypeDescribe; }
set { this._BigTypeDescribe = value; }
}
public int TypeOrder
{
get { return this._TypeOrder; }
set { this._TypeOrder = value; }
}
}
public enum EditMethod
{
Insert,
Update
}