?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;
/// <summary>
/// dbCon 的摘要說明
/// </summary>
public class dbCon
{
	public dbCon()
	{
		//
		// TODO: 在此處添加構造函數邏輯
		//
	}

    int newsId;
    string newsTitle;//標題
    string newsKey;//主鍵 
    string newsSortId;//類型
    string newsContent;//內容
    string newsAuthor;//作者

    #region 封裝屬性
    public int NewsId
    {
        get { return newsId; }
        set { newsId = value; }
    }

    public string  NewsTitle
    {
        get { return newsTitle; }
        set { newsTitle = value; }
    }

    public string  NewsKey
    {
        get { return newsKey; }
        set { newsKey = value; }
    }

    public string NewsSortId
    {
        get { return newsSortId; }
        set { newsSortId = value; }
    }

    public string NewsContent
    {
        get { return newsContent; }
        set { newsContent = value; }
    }

    public string NewsAuthor
    {
        get { return newsAuthor; }
        set { newsAuthor = value; }
    }
    #endregion
    
    public SqlConnection NewsConnection()
    {
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=News;Integrated Security=True");
        return con;
    }

    #region  添加新聞
    /// <summary>
    /// 新增新聞
    /// </summary>
    /// <param name="dbcon">新聞數據類型</param>
    /// <returns></returns>
    public bool AddNews(dbCon dbcon)
    {
        try
        {
            SqlConnection con = this.NewsConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText="insert into newsT(newsTitle,newsKeys,newsAuthor,newsContent,newsSortId) values(@newsTitle,@newsKeys,@newsAuthor,@newsContent,@newsSortId)";
            cmd.Connection=con;
            cmd.Parameters.Add("@newsTitle",SqlDbType .VarChar ,50).Value=dbcon.NewsTitle;
            cmd.Parameters.Add ("@newsKeys",SqlDbType .VarChar,50).Value=dbcon.NewsKey;
            cmd.Parameters.Add("@newsAuthor", SqlDbType.Char).Value = dbcon.NewsAuthor;
            cmd.Parameters.Add ("@newsContent",SqlDbType .VarChar,100).Value=dbcon.NewsContent;
            cmd.Parameters.Add ("@newsSortId",SqlDbType .Char).Value=dbcon.NewsSortId;
            cmd.ExecuteNonQuery();
            con.Close();
            return true ;
        }
        catch 
        {
            return false;
        }
    }
    #endregion
 

    #region  查看所有新聞
    /// <summary>
    /// 新聞表
    /// </summary>
    /// <returns>DataSet(NewsT)</returns>
    public DataSet AllNews()
    {
        SqlConnection con = this.NewsConnection();
        SqlCommand cmd = new SqlCommand();        
        cmd.CommandText = "SELECT [newsId], [newsTitle], [newsKeys], [newsSortName], [newsContent], [newsAuthor] FROM [NewsT] inner join NewsSortT on  NewsT.newsSortId=NewsSortT.newsSortId ";
        cmd.Connection = con;
        SqlDataAdapter sdr = new SqlDataAdapter(cmd);
        DataSet ds=new DataSet();
        sdr.Fill (ds,"NewsT");
        return ds;
    }
    #endregion 

    #region  根據編號查詢新聞具體信息
   
    public DataSet SelNewsT(dbCon db)
    {
        SqlConnection con = this.NewsConnection();
        SqlCommand cmd = new SqlCommand();         
        cmd.CommandText = "SELECT [newsId], [newsTitle], [newsKeys], [newsSortName], [newsContent], [newsAuthor] FROM [NewsT] inner join NewsSortT on  NewsT.newsSortId=NewsSortT.newsSortId where newsId=@newsId";
        cmd.Connection = con;
        cmd.Parameters.Add("@newsId", SqlDbType.Char).Value = db.NewsId;
        SqlDataAdapter sdr = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sdr.Fill(ds, "SelNews");
        return ds;
    }
    #endregion

    #region  修改新聞

    public bool updateNews(dbCon dbcon)
    {
        try
        {
            SqlConnection con = this.NewsConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "update NewsT set newsTitle=@newsTitle,newsKeys=@newsKeys,newsSortId=@newsSortId,newsContent=@newsContent,newsAuthor=@newsAuthor where newsId=@newsId";
            cmd.Connection = con;
            cmd.Parameters.Add("@newsId", SqlDbType.Int).Value = dbcon.NewsId;
            cmd.Parameters.Add("@newsTitle", SqlDbType.VarChar, 50).Value = dbcon.NewsTitle;
            cmd.Parameters.Add("@newsKeys", SqlDbType.VarChar, 50).Value = dbcon.NewsKey;
            cmd.Parameters.Add("@newsAuthor", SqlDbType.Char).Value = dbcon.NewsAuthor;
            cmd.Parameters.Add("@newsContent", SqlDbType.VarChar, 100).Value = dbcon.NewsContent;
            cmd.Parameters.Add("@newsSortId", SqlDbType.Char).Value = dbcon.NewsSortId;
            string s = cmd.CommandText;
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
    #endregion

    #region  刪除新聞

    public bool deleteNews(dbCon dbcon)
    {
        try
        {
            SqlConnection con = this.NewsConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from NewsT where newsId=@newsId";
            cmd.Parameters.Add("@newsId", SqlDbType.Int).Value = dbcon.NewsId;
            cmd.Connection = con;  
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
    #endregion

    #region  查詢新聞種類
    
    public DataSet AllNewsSort()
    {
            SqlConnection con = this.NewsConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select newsSortId ,newsSortName from NewsSortT";
            cmd.Connection = con;
            SqlDataAdapter sdr=new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sdr.Fill(ds, "newsSort"); 
            return ds;        
    }
    #endregion



    
}