knowinfoda.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

/// <summary>
/// 知识条实体的方法
/// </summary>
public class KnowInfoDA
{
    private const string SQL_INSERT_KNOWINFO = "INSERT INTO tb_Knowledge VALUES(@Knowledge_title,@Knowledge_content,@Knowledge_accessory,@Knowledge_issuetime,@Knowledge_pUsername)";
    private const string SQL_SELECT_KNOWINFOS = "SELECT Knowledge_title,Knowledge_content,Knowledge_accessary,Knowledge_pUsername,Knowledge_issuetime FROM tb_Knowledge WHERE Knowledge_id=@Knowledge";

    private const string PARAM_KNOW_ID = "@Knowledge_id";
    private const string PARAM_CATEGORY_NAME = "@Category_name";
    private const string PARAM_KNOW_TITLE = "@Knowledge_title";
    private const string PARAM_KNOW_CONTENT = "@Knowledge_content";
    private const string PARAM_KNOW_ACCESSORY = "@Knowledge_accessory";
    private const string PARAM_KNOW_ISSUETIME = "@Knowledge_issuetime";
    private const string PARAM_KNOW_PUSERNAME = "@Knowledge_pUsername";
    private const string SQL_SEARCH_BY_KEYWORD_NAME = 
        "SELECT c.* "
        + "FROM tb_Keyword as a INTER JOIN tb_Ref_Key2know as c on c.keyword_id = a.keyword_id "
        + "INNER JOIN tb_Knowledge as b on b.knowledge_id = c.knowledge_id "
        + "WHERE (1=1)";
    //for(int i=0;i<words.length;i++)
    //{
    //SQL_SEARCH_BY_KEYWORD_NAME=SQL_SEARCH_BY_KEYWORD_NAME+"and a.keyword=@Keyword_name"+i;
    ////a.keyword_name=@Keyword_name";
    ////}
    //private const string SQL_SELECT_BY_CATEGORY = "SELECT FROM WHERE =";


	public KnowInfoDA()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    ///<summary>
    ///添加知识条信息
    ///</summary>
    ///<param name="KnowInfo"</param>
    ///<returns>添加是否成功</returns>
    public bool InsertKnowInfo(KnowInfoEntity KnowInfo) 
    {
        StringBuilder strSQL = new StringBuilder();
        SqlParameter[] knowParams = GetParameters();
        SqlCommand cmd = new SqlCommand();

        // 依次给实体参数赋值
        knowParams[0].Value = KnowInfo.Knowledge_title;
        knowParams[1].Value = KnowInfo.Knowledge_content;
        knowParams[2].Value = KnowInfo.Knowledge_accessory;
        knowParams[3].Value = KnowInfo.Knowledge_issuetime;
        knowParams[4].Value = KnowInfo.Knowledge_pUsername;
        
        //遍历所有参数,并将参数添加到SqlCommand命令中
        foreach (SqlParameter parm in knowParams)
        {
            cmd.Parameters.Add(parm);
        }
        //取得数据库连接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            strSQL.Append(SQL_INSERT_KNOWINFO);
            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;
        }
    }
    /// <summary>
    /// 通过Knowledge_id查询一条知识条目信息
    /// </summary>
    /// <param name="Knowledge_id"></param>
    /// <returns></returns>
    public KnowInfoEntity GetKnowInfoByID(string Knowledge_id)
    {

        //Set up a return value
        KnowInfoEntity know = null;


        //Create a parameter
        SqlParameter parm = new SqlParameter(PARAM_KNOW_ID, SqlDbType.Int);
        //Bind the parameter
        parm.Value = Knowledge_id;

        //Execute the query	
        using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_KNOWINFOS, parm))
        {
            while (rdr.Read())
            {
                //给知识条目实体依次赋值，顺序要与知识条目实体的构造参数一样
                know = new KnowInfoEntity(rdr.GetString(0),rdr.GetString(1),rdr.GetString(2),rdr.GetString(3),rdr.GetDateTime(4),rdr.GetString(5));
            }
        }
        return know;
    }

    
    private static SqlParameter[] GetParameters()
    {
        //将SQL_INSERT_KNOWINFO做为哈希表缓存的键值
        SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_KNOWINFO);

        //首先判断缓存是否已经存在
        if (parms == null)
        {
            //缓存不存在的情况下，新建参数列表
            parms = new SqlParameter[] {
                   
                    new SqlParameter(PARAM_KNOW_TITLE, SqlDbType.NVarChar, 500),
                    new SqlParameter(PARAM_KNOW_CONTENT, SqlDbType.NVarChar, 4000),
                    new SqlParameter(PARAM_KNOW_ACCESSORY, SqlDbType.NVarChar, 100),
                    new SqlParameter(PARAM_KNOW_ISSUETIME, SqlDbType.DateTime, 20),
                    new SqlParameter(PARAM_KNOW_PUSERNAME, SqlDbType.NVarChar, 20)};
                  
                  

            //将新建的参数列表添加到哈希表中缓存起来
            SqlHelper.CacheParameters(SQL_INSERT_KNOWINFO, parms);
        }
        //返回参数数组
        return parms;
    }
}