cateda.cs

可以实现图书信息的管理笑话寻和山下湖

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;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

/// <summary>
/// CateDA 的摘要说明
/// </summary>
public class CateDA
{
    private const string SQL_INSERT_CATEGORY = "INSERT INTO tb_CATEGORY VALUES(@Category_name,@Category_level,@Category_parent_id)";

    //private const string SQL_DELETE_CATEGORY = "DELETE FROM tb_CATEGORY WHERE Category_id = @Category_id";
    private const string PARAM_CATEGORY_ID = "@Category_id";
    private const string PARAM_CATEGORY_NAME = "@Category_name";
    private const string PARAM_CATEGORY_LEVEL = "@Category_level";
    private const string PARAM_CATEGORY_PARENT_ID ="@Category_parent_id";
    
	public CateDA()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    public bool InsertCateInfo(CateEntity cate)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlParameter[] cateParams = GetParameters();
        SqlCommand cmd = new SqlCommand();

        // 依次给实体参数赋值
        
            cateParams[0].Value = cate.Category_name;
            cateParams[1].Value = cate.Category_level;
            cateParams[2].Value = cate.Category_parent_id;
       
       

        //遍历所有参数,并将参数添加到SqlCommand命令中
        foreach (SqlParameter parm in cateParams)
        {
            cmd.Parameters.Add(parm);
        }
        //取得数据库连接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            strSQL.Append(SQL_INSERT_CATEGORY);
            conn.Open();
            //设置Sqlcommand命令的属性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //执行添加的SqlCommand命令
            int val = cmd.ExecuteNonQuery();
            //清空SqlCommand命令中的参数
            cmd.Parameters.Clear();
            //判断是否添加成功，注意返回的是添加是否成功，不是影响的行数
            if (val > 0)
                return true;
            else
                return false;
        }
    }
    private static SqlParameter[] GetParameters()
    {
        //将SQL_INSERT_CATEGORY做为哈希表缓存的键值
        SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_CATEGORY);

        //首先判断缓存是否已经存在
        if (parms == null)
        {
            //缓存不存在的情况下，新建参数列表
            parms = new SqlParameter[] {
                   
                    new SqlParameter(PARAM_CATEGORY_NAME, SqlDbType.NVarChar, 500),
                    new SqlParameter(PARAM_CATEGORY_LEVEL, SqlDbType.Int),
                    new SqlParameter(PARAM_CATEGORY_PARENT_ID, SqlDbType.Int),
                   };



            //将新建的参数列表添加到哈希表中缓存起来
            SqlHelper.CacheParameters(SQL_INSERT_CATEGORY, parms);
        }
        //返回参数数组
        return parms;
    }
    /// <summary>
    /// 绑定类别树
    /// </summary>
    /// <param name="treeView"></param>
    /// <param name="isExpanded"></param>
    /// <param name="sSelectedData"></param>
    public void BindCategoryTreeView(TreeView treeView, bool isExpanded, string sSelectedData)
    {
       
        CateDA cateda = new CateDA();

        DataTable dataTable = SystemTools.ConvertDataReaderToDataTable(cateda.GetCategorys());
        treeView.Nodes.Clear();     ///清空树的所有节点

        DataRow[] rowList = dataTable.Select("Category_parent_id='-1'");
        if (rowList.Length <= 0) return;

        ///创建根节点
        TreeNode rootNode = new TreeNode();
        ///设置根节点属性
        rootNode.Text = rowList[0]["Category_name"].ToString();
        rootNode.Value = rowList[0]["Category_id"].ToString(); ///设置根节点的Key值
        rootNode.Expanded = isExpanded;
        rootNode.Selected = true;

        ///添加根节点
        treeView.Nodes.Add(rootNode);

        ///创建其他节点
        CreateChildNode(rootNode, dataTable, isExpanded, sSelectedData);
    }
    public SqlDataReader GetCategorys()
    {

        BaseAccess ba = new BaseAccess();
        ///定义保存从数据库获取的结果的DataReader
        SqlDataReader dr = null;

        try
        {
            ///执行存储过程
            ba.RunProc("Proc_GetCategorys", out dr);
        }
        catch (Exception ex)
        {
            ///抛出执行数据库异常
            //SystemError.CreateErrorLog(ex.Message);
            //throw new Exception(ex.Message, ex);
        }

        ///返回从数据库获取的结果
        return (dr);
    }

    private void CreateChildNode(TreeNode parentNode, DataTable dataTable, bool isExpanded, string sSelectedData)
    {
        ///选择数据时，添加了排序表达式OrderBy
        DataRow[] rowList = dataTable.Select("Category_parent_id='" + parentNode.Value + "'", "OrderBy");
        foreach (DataRow row in rowList)
        {   ///创建新节点
            TreeNode node = new TreeNode();
            ///设置节点的属性
            node.Text = row["Category_name"].ToString();
            node.Value = row["Category_id"].ToString();
            node.Expanded = isExpanded;
            if (node.Value == sSelectedData)
            {
                node.Selected = true;
            }

            parentNode.ChildNodes.Add(node);
            ///递归调用，创建其他节点
            CreateChildNode(node, dataTable, isExpanded, sSelectedData);
        }
    }
    /// <summary>
    /// 添加一个类别
    /// </summary>
    /// <param name="sDesn"></param>
    /// <param name="nParentID"></param>
    /// <returns></returns>
    public int AddCategory(string sDesn, int nParentID, int nOrderBy)
    {
       
        BaseAccess ba = new BaseAccess();
        //创建访问数据库的参数			
        SqlParameter[] paramList = 
              {
                ba.CreateInParam("@Category_name",SqlDbType.VarChar,200,sDesn),
                ba.CreateInParam("@Category_parent_id",SqlDbType.Int,4,nParentID),
                ba.CreateInParam("@Category_orderby",SqlDbType.Int,4,nOrderBy)    
              };

        try
        {
            ///执行存储过程
            return (ba.RunProc("Proc_AddCategory", paramList));
        }
        catch (Exception ex)
        {
            /////抛出执行数据库异常
            //SystemError.CreateErrorLog(ex.Message);
            //throw new Exception(ex.Message, ex);
            return -1;
        }
    }

    public void UpdateCategory(int nCategoryID, string sDesn)
    {
        CateDA cateda = new CateDA();
        BaseAccess ba = new BaseAccess();
       //创建访问数据库的参数			
        SqlParameter[] paramList = {
	 ba.CreateInParam("@Category_id",SqlDbType.Int,4,nCategoryID),
     ba.CreateInParam("@Category_name",SqlDbType.VarChar,200,sDesn)	
        }; 
        try
        {
            ///执行存储过程
            ba.RunProc("Proc_UpdateCategory", paramList);
        }
        catch (Exception ex)
        {
            /////抛出执行数据库异常
            //SystemError.CreateErrorLog(ex.Message);
            //throw new Exception(ex.Message, ex);
        }
    }

    public void DeleteCategory(int nCategoryID)
    {
        ///定义类SQLHelper
        BaseAccess ba = new BaseAccess();

        ///创建访问数据库的参数			
        SqlParameter[] paramList = {
										   ba.CreateInParam("@Category_id",SqlDbType.Int,4,nCategoryID)
									   };

        try
        {
            ///执行存储过程
            ba.RunProc("Proc_DeleteCategory", paramList);
        }
        catch (Exception ex)
        {
            /////抛出执行数据库异常
            //SystemError.CreateErrorLog(ex.Message);
            //throw new Exception(ex.Message, ex);
        }
    }

    public void UpdateCategoryOrder(int nCategoryID, string sMoveFlag)
    {
        BaseAccess ba = new BaseAccess();

        ///创建访问数据库的参数			
        SqlParameter[] paramList = {
										   ba.CreateInParam("@CategoryID",SqlDbType.Int,4,nCategoryID),
                                           ba.CreateInParam("@MoveFlag",SqlDbType.VarChar,20,sMoveFlag)										   
									};

        try
        {
            ///执行存储过程
            ba.RunProc("Proc_UpdateCategoryOrder", paramList);
        }
        catch (Exception ex)
        {
            /////抛出执行数据库异常
            //SystemError.CreateErrorLog(ex.Message);
            //throw new Exception(ex.Message, ex);
        }
    }

}