110,534
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [Menu]
(
[id] [int] NOT NULL,
[name] [varchar](50) NOT NULL,
[pid] [int] NOT NULL,
[imageUrl] [nvarchar](50) NULL,
[navigateUrl] [nvarchar](100) NULL
)
GO
public class SqlHelper
{
/// <summary>
/// 无参构造方法。
/// </summary>
public SqlHelper() { }
/// <summary>
/// 连接字符串(从配置文件取得)。
/// </summary>
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
/// <summary>
/// 按照Sql命令返回查询结果的方法
/// </summary>
/// <param name="cmdType">命令类型(存储过程或Sql语句)</param>
/// <param name="cmdText">命令文本(Sql语句)</param>
/// <param name="cmdParams">命令参数(Sql语句用到的参数)</param>
/// <returns>查询到的SqlDataReader对象</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
//如果在这出现异常,那么CommandBehavior.CloseConnection将不能关闭连接
//所以在catch里面关闭连接
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParams);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
System.GC.Collect();
return reader;
}
catch (SqlException ex)
{
conn.Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 处理命令的方法
/// </summary>
/// <param name="cmd">SqlCommand对象</param>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction对象</param>
/// <param name="cmdText">Sql语句</param>
/// <param name="cmdParams">语句中出现参数</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection connection, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
cmd.Connection = connection;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParams != null)
{
foreach (SqlParameter param in cmdParams)
{
cmd.Parameters.Add(param);
}
}
}
}
/// <summary>
/// Menu 的摘要说明
/// </summary>
public class Menu
{
public Menu()
{
}
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private int pid;
public int Pid
{
get { return pid; }
set { pid = value; }
}
private string imageUrl;
public string ImageUrl
{
get { return imageUrl; }
set { imageUrl = value; }
}
private string navigateUrl;
public string NavigateUrl
{
get { return navigateUrl; }
set { navigateUrl = value; }
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.AddNode(this.TreeView1.Nodes, 0);
foreach (TreeNode tn in this.TreeView1.Nodes)
{
this.AddNode(tn.ChildNodes, Convert.ToInt32(tn.Value));
}
}
}
/// <summary>
/// 添加节点
/// </summary>
/// <param name="tnCollection">节点集合</param>
/// <param name="pid">菜单父编号</param>
private void AddNode(TreeNodeCollection tnCollection, int pid)
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, "SELECT * FROM [Menu] WHERE [pid] = " + pid.ToString(), null))
{
while (reader.Read())
{
Menu menu = new Menu();
menu.Id = reader.GetInt32(0);
menu.Name = reader.GetString(1);
//这里只获取菜单的Name和Value,其他的根据需要可以自己获取。
TreeNode node = new TreeNode();
//这里只设置节点的Name和Value,其他的根据需要可以自己设置。
node.Value = menu.Id.ToString();
node.Text = menu.Name;
tnCollection.Add(node);
}
}
}
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<asp:TreeView ID="TreeView1" runat="server">
</asp:TreeView>
</form>
</body>
</html>
private void Form1_Load(object sender, EventArgs e)
{
this.AddNode(this.treeView1.Nodes, 0);
foreach (TreeNode tn in this.treeView1.Nodes)
{
this.AddNode(tn.Nodes, Convert.ToInt32(tn.Tag));
}
}
/// <summary>
/// 添加节点
/// </summary>
/// <param name="tnCollection">节点集合</param>
/// <param name="pid">菜单父编号</param>
private void AddNode(TreeNodeCollection tnCollection, int pid)
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, "SELECT * FROM [Menu] WHERE [pid] = " + pid.ToString(), null))
{
while (reader.Read())
{
Menu menu = new Menu();
menu.Id = reader.GetInt32(0);
menu.Name = reader.GetString(1);
//这里只获取菜单的Name和Value,其他的根据需要可以自己获取。
TreeNode node = new TreeNode();
//这里只设置节点的Name和Value,其他的根据需要可以自己设置。
node.Tag = menu.Id;
node.Text = menu.Name;
tnCollection.Add(node);
}
}
}