系统字典的设计与ASP.net的实现:
Oracle数据库表的设计:
-- Create table
create table SYS_DICT
(
编号 NUMBER(16) not null,
分类名称 VARCHAR2(64),
内容 VARCHAR2(64),
上级编号 NUMBER(16)
)
tablespace HHSCINFO
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DICT
add constraint 编号 primary key (编号)
using index
tablespace HHSCINFO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
页面代码:
对应的cs文件:
Oracle数据库表的设计:
-- Create table
create table SYS_DICT
(
编号 NUMBER(16) not null,
分类名称 VARCHAR2(64),
内容 VARCHAR2(64),
上级编号 NUMBER(16)
)
tablespace HHSCINFO
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_DICT
add constraint 编号 primary key (编号)
using index
tablespace HHSCINFO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
页面代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SysDict.aspx.cs" Inherits="InterFace_SysDict_SysDict" %>
<!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="formSysDict" runat="server">
<div>
<asp:TreeView ID="TreeViewSysDict" runat="server" ShowLines="True" OnSelectedNodeChanged="TreeViewSysDict_SelectedNodeChanged">
</asp:TreeView>
编号
<asp:TextBox ID="txtID" runat="server" ReadOnly="True"></asp:TextBox><br />
分类名称
<asp:TextBox ID="txtCatName" runat="server" ReadOnly="True"></asp:TextBox><br />
内容 <asp:TextBox ID="txtContent" runat="server" ReadOnly="True"></asp:TextBox><br />
上级编号<asp:TextBox ID="txtParentID" runat="server" ReadOnly="True" BackColor="White"></asp:TextBox><br />
<br />
<asp:Button ID="btnNew" runat="server" Text="新增" OnClick="btnNew_Click" Enabled="False" />
<asp:Button ID="btnModify" runat="server" Text="修改" Enabled="False" OnClick="btnModify_Click" />
<asp:Button ID="btnDel" runat="server" Text="删除" Enabled="False" OnClick="btnDel_Click" />
<asp:Button ID="btnSubmit" runat="server" Text="确定" OnClick="btnSubmit_Click" Enabled="False" />
<asp:Button ID="btnCancel" runat="server" Text="取消" Enabled="False" OnClick="btnCancel_Click" />
<asp:Label ID="lblBtnClickFlag" runat="server" Text="0"></asp:Label>
<asp:Label ID="lblMsg" runat="server" Text="lblMsg" ForeColor="Red"></asp:Label>
<asp:Label ID="lblOldContent" runat="server"></asp:Label></div>
</form>
</body>
</html>
<!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="formSysDict" runat="server">
<div>
<asp:TreeView ID="TreeViewSysDict" runat="server" ShowLines="True" OnSelectedNodeChanged="TreeViewSysDict_SelectedNodeChanged">
</asp:TreeView>
编号
<asp:TextBox ID="txtID" runat="server" ReadOnly="True"></asp:TextBox><br />
分类名称
<asp:TextBox ID="txtCatName" runat="server" ReadOnly="True"></asp:TextBox><br />
内容 <asp:TextBox ID="txtContent" runat="server" ReadOnly="True"></asp:TextBox><br />
上级编号<asp:TextBox ID="txtParentID" runat="server" ReadOnly="True" BackColor="White"></asp:TextBox><br />
<br />
<asp:Button ID="btnNew" runat="server" Text="新增" OnClick="btnNew_Click" Enabled="False" />
<asp:Button ID="btnModify" runat="server" Text="修改" Enabled="False" OnClick="btnModify_Click" />
<asp:Button ID="btnDel" runat="server" Text="删除" Enabled="False" OnClick="btnDel_Click" />
<asp:Button ID="btnSubmit" runat="server" Text="确定" OnClick="btnSubmit_Click" Enabled="False" />
<asp:Button ID="btnCancel" runat="server" Text="取消" Enabled="False" OnClick="btnCancel_Click" />
<asp:Label ID="lblBtnClickFlag" runat="server" Text="0"></asp:Label>
<asp:Label ID="lblMsg" runat="server" Text="lblMsg" ForeColor="Red"></asp:Label>
<asp:Label ID="lblOldContent" runat="server"></asp:Label></div>
</form>
</body>
</html>
对应的cs文件:
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.OracleClient;
using System.Drawing;
using InforSys.App_Code.Database;
namespace InforSys.InterFace.SysDict
{
public partial class SysDict : System.Web.UI.Page
{
private static string DICT_ID = "编号";
private static string CAT_NAME = "分类名称";
private static string CONTENT = "内容";
private static string PARENT_DICT_ID = "上级编号";
private static string SYS_DICT = "SYS_DICT";
/// <summary>
/// 判断某个Button被点击的标记
/// </summary>
protected void Page_Load(object sender, EventArgs e)
{
// 信息提示用
lblMsg.Text = null;
if (!Page.IsPostBack)
{
// 初始化功能树
InitFuncTree();
// 遍历树
AllOverTree(TreeViewSysDict.Nodes);
}
}
private void InitFuncTree()
{
AbsDB conn = DBConn.GetDBConn();
String strSql = "select " + DICT_ID + "," + CAT_NAME + "," + CONTENT + "," + PARENT_DICT_ID + " from " + SYS_DICT;
//String strSql = "select * from sys_dict where 分类名称='角色类型' or 内容='角色类型' or 编号=1";
DataSet ds = conn.ExeSqlForDataSet(strSql);
this.ViewState["ds"] = ds;
AddTree(0, (TreeNode)null);
}
//递归添加树的节点
private void AddTree(int PARENT_DICT_ID, TreeNode pNode)
{
DataSet ds = (DataSet)this.ViewState["ds"];
DataView dvTree = new DataView(ds.Tables[0]);
//过滤PARENT_DICT_ID,得到当前的所有子节点
dvTree.RowFilter = "[上级编号] = " + PARENT_DICT_ID;
foreach (DataRowView Row in dvTree)
{
TreeNode Node = new TreeNode();
//添加根节点
if (pNode == null)
{
// ̀添加当前节点的子节点 一个为值,一个为显示
Node.Value = Row[DICT_ID].ToString();
Node.Text = Row[CONTENT].ToString();
TreeViewSysDict.Nodes.Add(Node);
Node.Expanded = true;
//从根节点开始递归
AddTree(Int32.Parse(Row[DICT_ID].ToString()), Node);
}
else
{
// ̀添加当前节点的子节点 一个为值,一个为显示
Node.Value = Row[DICT_ID].ToString();
//̀添加当前节点的子节点
Node.Text = Row[CONTENT].ToString();
Node.Expanded = true;
//if (pNode.Depth <= 1)
{
pNode.ChildNodes.Add(Node);
}
//再次递归
AddTree(Int32.Parse(Row[DICT_ID].ToString()), Node);
}
}
}
protected void AllOverTree(TreeNodeCollection tnc)
{
foreach (TreeNode node in tnc)
{
if (node.ChildNodes.Count != 0)
{
AllOverTree(node.ChildNodes);
}
else
{
//
}
}
}
protected void TreeViewSysDict_SelectedNodeChanged(object sender, EventArgs e)
{
// 编号
txtID.Text = this.TreeViewSysDict.SelectedNode.Value;
// 分类名称
if (this.TreeViewSysDict.SelectedNode.Depth != 0)
{
txtCatName.Text = this.TreeViewSysDict.SelectedNode.Parent.Text;
// 上级编号
txtParentID.Text = this.TreeViewSysDict.SelectedNode.Parent.Value;
}
else
{
txtCatName.Text = this.TreeViewSysDict.SelectedNode.Text;
// 上级编号
txtParentID.Text = "0";
}
// 内容
txtContent.Text = this.TreeViewSysDict.SelectedNode.Text;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
}
protected void btnNew_Click(object sender, EventArgs e)
{
lblBtnClickFlag.Text = "1";
txtContent.ReadOnly = false;
if (this.TreeViewSysDict.SelectedNode.Depth != 0)
{
txtParentID.Text = this.TreeViewSysDict.SelectedNode.Value;
int i = this.TreeViewSysDict.SelectedNode.Depth - 1;
TreeNode tempNode = new TreeNode();
tempNode = this.TreeViewSysDict.SelectedNode;
while(i > 0)
{
tempNode = tempNode.Parent;
i--;
}
txtCatName.Text = tempNode.Text;
}
else
{
txtParentID.Text = "1";
txtCatName.Text = "系统字典";
}
//根据现有的数字确定接下来的数
int maxValue = GetDictID(TreeViewSysDict.SelectedNode.Value);
txtID.Text = Convert.ToString(maxValue + 1);
txtContent.Text = null;
txtContent.BackColor = System.Drawing.Color.AliceBlue;
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnModify.Enabled = false;
btnDel.Enabled = false;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
TreeNode Node = new TreeNode();
//lblBtnClickFlag.Text 1表示new 2表示modify 3表示del
if (lblBtnClickFlag.Text.Equals("1"))
{
if (InsertDB() > 0)
{
//TreeNode Node = new TreeNode();
Node.Value = txtID.Text;
//̀添加当前节点的子节点
Node.Text = txtContent.Text;
Node.Expanded = true;
this.TreeViewSysDict.SelectedNode.ChildNodes.Add(Node);
}
txtContent.BackColor = System.Drawing.Color.White;
}
else if (lblBtnClickFlag.Text.Equals("2"))
{
if (UpdateDB())
{
this.TreeViewSysDict.SelectedNode.Value = txtID.Text;
//̀添加当前节点的子节点
this.TreeViewSysDict.SelectedNode.Text = txtContent.Text;
Node.Expanded = true;
btnModify.Enabled = true;
}
txtContent.BackColor = System.Drawing.Color.White;
}
else if (lblBtnClickFlag.Text.Equals("3"))
{
if (DelDB())
{
//删除节点
this.TreeViewSysDict.SelectedNode.Parent.ChildNodes.Remove(this.TreeViewSysDict.SelectedNode);
txtID.Text = null;
txtCatName.Text = null;
txtParentID.Text = null;
txtContent.Text = null;
}
}
//重新复位
lblBtnClickFlag.Text = "0";
btnSubmit.Enabled = false;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
btnCancel.Enabled = false;
}
private int InsertDB()
{
// 成功插入返回标志
int regSuccessFlag = 0;
// 首先需要检查该记录是否存在与此分类中
if (!HasRecord(txtCatName.Text.Trim(), txtContent.Text.Trim()))
{
AbsDB conn = DBConn.GetDBConn();
string sqlStr = "Insert into sys_dict values(" + txtID.Text.Trim() + ",'" + txtCatName.Text.Trim() + "','" + txtContent.Text.Trim() + "'," + txtParentID.Text.Trim() + ")";
regSuccessFlag = conn.ExeSql(CommandType.Text, sqlStr, null);
}
return regSuccessFlag;
}
private bool HasRecord(string catName, string content)
{
bool hasRecord = false;
AbsDB conn = DBConn.GetDBConn();
String sqlStr = "select 内容 from sys_dict where 分类名称 = '" + catName + "' and 内容 = '" + content + "' ";
OracleDataReader reader = conn.ExecuteReader(CommandType.Text, sqlStr, null);
while (reader.Read())
{
hasRecord = true;
break;
}
return hasRecord;
}
private int GetDictID(string dictID)
{
int DictID = 0;
AbsDB conn = DBConn.GetDBConn();
int tempID = Convert.ToInt32(dictID);
String sqlStr = "select 编号 from sys_dict order by 编号 desc";
OracleDataReader reader = conn.ExecuteReader(CommandType.Text, sqlStr, null);
DictID = tempID;
while (reader.Read())
{
DictID = Convert.ToInt32(reader.GetInt32(0));
break;
}
return DictID;
}
protected void btnCancel_Click(object sender, EventArgs e)
{
txtID.Text = null;
txtCatName.Text = null;
txtContent.Text = null;
txtParentID.Text = null;
btnCancel.Enabled = false;
btnSubmit.Enabled = false;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
}
protected void btnModify_Click(object sender, EventArgs e)
{
if (txtID.Text.Equals("1"))
{
lblMsg.Text = "系统字典项不可修改!";
}
else
{
txtContent.ReadOnly = false;
txtContent.Enabled = true;
txtContent.BackColor = System.Drawing.Color.AliceBlue;
lblBtnClickFlag.Text = "2";
// 如果不是孩子节点 需要同时更新相关其它孩子父节点的值
if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
// 用lblOldContent保存临时分类名称值
lblOldContent.Text = txtContent.Text;
}
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnModify.Enabled = false;
btnNew.Enabled = false;
btnDel.Enabled = false;
}
}
/// <summary>
/// 需要开启事务进行处理 更新字编号对应的内容和内容对应的分类名称
/// </summary>
/// <returns></returns>
private bool UpdateDB()
{
bool UPD_SUCESS_FLAG = true;
if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
OracleConnection conn = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["DBConnStr"]);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
string updSql = "update sys_dict set 内容='" + txtContent.Text.Trim() + "' where 编号=" + txtID.Text.Trim() + " ";
string updCatSql = "update sys_dict set 分类名称 = '" + txtContent.Text.Trim() + "' where 分类名称 = '" + lblOldContent.Text.Trim() + "' ";
try
{
ExecuteNonQuery(trans, CommandType.Text, updSql, null);
ExecuteNonQuery(trans, CommandType.Text, updCatSql, null);
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new ApplicationException(e.Message);
UPD_SUCESS_FLAG = false;
}
finally
{
conn.Close();
}
}
else
{
UPD_SUCESS_FLAG = false;
AbsDB conn = DBConn.GetDBConn();
string updSql = "update sys_dict set 内容='" + txtContent.Text.Trim() + "' where 编号=" + txtID.Text.Trim() + " ";
int returnFlag = conn.ExeSql(CommandType.Text, updSql, null);
if (returnFlag > 0)
{
UPD_SUCESS_FLAG = true;
}
}
return UPD_SUCESS_FLAG;
}
protected void btnDel_Click(object sender, EventArgs e)
{
if (txtID.Text.Equals("1"))
{
lblMsg.Text = "系统字典项不可删除!";
}
else if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
lblMsg.Text = "有孩子选项不可删除!";
}
else
{
lblBtnClickFlag.Text = "3";
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnNew.Enabled = false;
btnModify.Enabled = false;
btnDel.Enabled = false;
}
}
private bool DelDB()
{
bool DEL_SUCCESS_FLAG = false;
AbsDB conn = DBConn.GetDBConn();
string updSql = "delete from sys_dict where 编号='" + txtID.Text.Trim() + "' ";
int returnFlag = conn.ExeSql(CommandType.Text, updSql, null);
if (returnFlag > 0)
{
DEL_SUCCESS_FLAG = true;
}
return DEL_SUCCESS_FLAG;
}
private int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
}
}
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.OracleClient;
using System.Drawing;
using InforSys.App_Code.Database;
namespace InforSys.InterFace.SysDict
{
public partial class SysDict : System.Web.UI.Page
{
private static string DICT_ID = "编号";
private static string CAT_NAME = "分类名称";
private static string CONTENT = "内容";
private static string PARENT_DICT_ID = "上级编号";
private static string SYS_DICT = "SYS_DICT";
/// <summary>
/// 判断某个Button被点击的标记
/// </summary>
protected void Page_Load(object sender, EventArgs e)
{
// 信息提示用
lblMsg.Text = null;
if (!Page.IsPostBack)
{
// 初始化功能树
InitFuncTree();
// 遍历树
AllOverTree(TreeViewSysDict.Nodes);
}
}
private void InitFuncTree()
{
AbsDB conn = DBConn.GetDBConn();
String strSql = "select " + DICT_ID + "," + CAT_NAME + "," + CONTENT + "," + PARENT_DICT_ID + " from " + SYS_DICT;
//String strSql = "select * from sys_dict where 分类名称='角色类型' or 内容='角色类型' or 编号=1";
DataSet ds = conn.ExeSqlForDataSet(strSql);
this.ViewState["ds"] = ds;
AddTree(0, (TreeNode)null);
}
//递归添加树的节点
private void AddTree(int PARENT_DICT_ID, TreeNode pNode)
{
DataSet ds = (DataSet)this.ViewState["ds"];
DataView dvTree = new DataView(ds.Tables[0]);
//过滤PARENT_DICT_ID,得到当前的所有子节点
dvTree.RowFilter = "[上级编号] = " + PARENT_DICT_ID;
foreach (DataRowView Row in dvTree)
{
TreeNode Node = new TreeNode();
//添加根节点
if (pNode == null)
{
// ̀添加当前节点的子节点 一个为值,一个为显示
Node.Value = Row[DICT_ID].ToString();
Node.Text = Row[CONTENT].ToString();
TreeViewSysDict.Nodes.Add(Node);
Node.Expanded = true;
//从根节点开始递归
AddTree(Int32.Parse(Row[DICT_ID].ToString()), Node);
}
else
{
// ̀添加当前节点的子节点 一个为值,一个为显示
Node.Value = Row[DICT_ID].ToString();
//̀添加当前节点的子节点
Node.Text = Row[CONTENT].ToString();
Node.Expanded = true;
//if (pNode.Depth <= 1)
{
pNode.ChildNodes.Add(Node);
}
//再次递归
AddTree(Int32.Parse(Row[DICT_ID].ToString()), Node);
}
}
}
protected void AllOverTree(TreeNodeCollection tnc)
{
foreach (TreeNode node in tnc)
{
if (node.ChildNodes.Count != 0)
{
AllOverTree(node.ChildNodes);
}
else
{
//
}
}
}
protected void TreeViewSysDict_SelectedNodeChanged(object sender, EventArgs e)
{
// 编号
txtID.Text = this.TreeViewSysDict.SelectedNode.Value;
// 分类名称
if (this.TreeViewSysDict.SelectedNode.Depth != 0)
{
txtCatName.Text = this.TreeViewSysDict.SelectedNode.Parent.Text;
// 上级编号
txtParentID.Text = this.TreeViewSysDict.SelectedNode.Parent.Value;
}
else
{
txtCatName.Text = this.TreeViewSysDict.SelectedNode.Text;
// 上级编号
txtParentID.Text = "0";
}
// 内容
txtContent.Text = this.TreeViewSysDict.SelectedNode.Text;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
}
protected void btnNew_Click(object sender, EventArgs e)
{
lblBtnClickFlag.Text = "1";
txtContent.ReadOnly = false;
if (this.TreeViewSysDict.SelectedNode.Depth != 0)
{
txtParentID.Text = this.TreeViewSysDict.SelectedNode.Value;
int i = this.TreeViewSysDict.SelectedNode.Depth - 1;
TreeNode tempNode = new TreeNode();
tempNode = this.TreeViewSysDict.SelectedNode;
while(i > 0)
{
tempNode = tempNode.Parent;
i--;
}
txtCatName.Text = tempNode.Text;
}
else
{
txtParentID.Text = "1";
txtCatName.Text = "系统字典";
}
//根据现有的数字确定接下来的数
int maxValue = GetDictID(TreeViewSysDict.SelectedNode.Value);
txtID.Text = Convert.ToString(maxValue + 1);
txtContent.Text = null;
txtContent.BackColor = System.Drawing.Color.AliceBlue;
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnModify.Enabled = false;
btnDel.Enabled = false;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
TreeNode Node = new TreeNode();
//lblBtnClickFlag.Text 1表示new 2表示modify 3表示del
if (lblBtnClickFlag.Text.Equals("1"))
{
if (InsertDB() > 0)
{
//TreeNode Node = new TreeNode();
Node.Value = txtID.Text;
//̀添加当前节点的子节点
Node.Text = txtContent.Text;
Node.Expanded = true;
this.TreeViewSysDict.SelectedNode.ChildNodes.Add(Node);
}
txtContent.BackColor = System.Drawing.Color.White;
}
else if (lblBtnClickFlag.Text.Equals("2"))
{
if (UpdateDB())
{
this.TreeViewSysDict.SelectedNode.Value = txtID.Text;
//̀添加当前节点的子节点
this.TreeViewSysDict.SelectedNode.Text = txtContent.Text;
Node.Expanded = true;
btnModify.Enabled = true;
}
txtContent.BackColor = System.Drawing.Color.White;
}
else if (lblBtnClickFlag.Text.Equals("3"))
{
if (DelDB())
{
//删除节点
this.TreeViewSysDict.SelectedNode.Parent.ChildNodes.Remove(this.TreeViewSysDict.SelectedNode);
txtID.Text = null;
txtCatName.Text = null;
txtParentID.Text = null;
txtContent.Text = null;
}
}
//重新复位
lblBtnClickFlag.Text = "0";
btnSubmit.Enabled = false;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
btnCancel.Enabled = false;
}
private int InsertDB()
{
// 成功插入返回标志
int regSuccessFlag = 0;
// 首先需要检查该记录是否存在与此分类中
if (!HasRecord(txtCatName.Text.Trim(), txtContent.Text.Trim()))
{
AbsDB conn = DBConn.GetDBConn();
string sqlStr = "Insert into sys_dict values(" + txtID.Text.Trim() + ",'" + txtCatName.Text.Trim() + "','" + txtContent.Text.Trim() + "'," + txtParentID.Text.Trim() + ")";
regSuccessFlag = conn.ExeSql(CommandType.Text, sqlStr, null);
}
return regSuccessFlag;
}
private bool HasRecord(string catName, string content)
{
bool hasRecord = false;
AbsDB conn = DBConn.GetDBConn();
String sqlStr = "select 内容 from sys_dict where 分类名称 = '" + catName + "' and 内容 = '" + content + "' ";
OracleDataReader reader = conn.ExecuteReader(CommandType.Text, sqlStr, null);
while (reader.Read())
{
hasRecord = true;
break;
}
return hasRecord;
}
private int GetDictID(string dictID)
{
int DictID = 0;
AbsDB conn = DBConn.GetDBConn();
int tempID = Convert.ToInt32(dictID);
String sqlStr = "select 编号 from sys_dict order by 编号 desc";
OracleDataReader reader = conn.ExecuteReader(CommandType.Text, sqlStr, null);
DictID = tempID;
while (reader.Read())
{
DictID = Convert.ToInt32(reader.GetInt32(0));
break;
}
return DictID;
}
protected void btnCancel_Click(object sender, EventArgs e)
{
txtID.Text = null;
txtCatName.Text = null;
txtContent.Text = null;
txtParentID.Text = null;
btnCancel.Enabled = false;
btnSubmit.Enabled = false;
btnNew.Enabled = true;
btnModify.Enabled = true;
btnDel.Enabled = true;
}
protected void btnModify_Click(object sender, EventArgs e)
{
if (txtID.Text.Equals("1"))
{
lblMsg.Text = "系统字典项不可修改!";
}
else
{
txtContent.ReadOnly = false;
txtContent.Enabled = true;
txtContent.BackColor = System.Drawing.Color.AliceBlue;
lblBtnClickFlag.Text = "2";
// 如果不是孩子节点 需要同时更新相关其它孩子父节点的值
if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
// 用lblOldContent保存临时分类名称值
lblOldContent.Text = txtContent.Text;
}
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnModify.Enabled = false;
btnNew.Enabled = false;
btnDel.Enabled = false;
}
}
/// <summary>
/// 需要开启事务进行处理 更新字编号对应的内容和内容对应的分类名称
/// </summary>
/// <returns></returns>
private bool UpdateDB()
{
bool UPD_SUCESS_FLAG = true;
if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
OracleConnection conn = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["DBConnStr"]);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
string updSql = "update sys_dict set 内容='" + txtContent.Text.Trim() + "' where 编号=" + txtID.Text.Trim() + " ";
string updCatSql = "update sys_dict set 分类名称 = '" + txtContent.Text.Trim() + "' where 分类名称 = '" + lblOldContent.Text.Trim() + "' ";
try
{
ExecuteNonQuery(trans, CommandType.Text, updSql, null);
ExecuteNonQuery(trans, CommandType.Text, updCatSql, null);
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new ApplicationException(e.Message);
UPD_SUCESS_FLAG = false;
}
finally
{
conn.Close();
}
}
else
{
UPD_SUCESS_FLAG = false;
AbsDB conn = DBConn.GetDBConn();
string updSql = "update sys_dict set 内容='" + txtContent.Text.Trim() + "' where 编号=" + txtID.Text.Trim() + " ";
int returnFlag = conn.ExeSql(CommandType.Text, updSql, null);
if (returnFlag > 0)
{
UPD_SUCESS_FLAG = true;
}
}
return UPD_SUCESS_FLAG;
}
protected void btnDel_Click(object sender, EventArgs e)
{
if (txtID.Text.Equals("1"))
{
lblMsg.Text = "系统字典项不可删除!";
}
else if (this.TreeViewSysDict.SelectedNode.ChildNodes.Count != 0)
{
lblMsg.Text = "有孩子选项不可删除!";
}
else
{
lblBtnClickFlag.Text = "3";
btnSubmit.Enabled = true;
btnCancel.Enabled = true;
btnNew.Enabled = false;
btnModify.Enabled = false;
btnDel.Enabled = false;
}
}
private bool DelDB()
{
bool DEL_SUCCESS_FLAG = false;
AbsDB conn = DBConn.GetDBConn();
string updSql = "delete from sys_dict where 编号='" + txtID.Text.Trim() + "' ";
int returnFlag = conn.ExeSql(CommandType.Text, updSql, null);
if (returnFlag > 0)
{
DEL_SUCCESS_FLAG = true;
}
return DEL_SUCCESS_FLAG;
}
private int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
}
}
图形如下:
可以根据需要继续添加/修改/删除。
测试基本通过 !