//article.cs
using system;

namespace news_articles.data
{
///

/// summary description for article.
///

public class article
{
private int _id; //文章編號
private string _author; //文章的作者
private string _topic; //文章的標題
private datetime _posttime; //文章的發表時間
private string _content; //文章內容

public int id
{
get { return _id;}
set { _id = value;}
}
public string author
{
get { return _author; }
set { _author = value; }
}
public string topic
{
get { return _topic; }
set { _topic = value; }
}
public string content
{
get { return _content; }
set { _content = value; }
}
public datetime posttime
{
get { return _posttime; }
set { _posttime = value; }
}
}
}

//articlecollection.cs
using system;
using system.collections;

namespace news_articles.data
{
///

/// 文章的集合類，繼承于 arraylist
///

public class articlecollection : arraylist
{
public articlecollection() : base()
{
}

public articlecollection(icollection c) : base(c)
{
}
}
}

//articledb.cs
using system;
using system.configuration;
using system.data;
using system.data.sqlclient;

namespace news_articles.data
{
/**////

/// 數據庫操作類，實現文章數據庫的讀取，插入，更新，刪除
///

public class articledb
{
private sqlconnection _conn; //sql server 數據庫連接
private string _articledb = "news_articles"; //sql server 文章數據庫表

/**////

/// 類的初始化，設置數據庫連接
///

public articledb()
{
_conn = new sqlconnection(configurationsettings.appsettings["connectionstring"]);
}

/**////

/// 打開數據庫連接
///

public void open()
{
if(_conn.state == connectionstate.closed)
_conn.open();
}

/**////

/// 關閉數據庫連接
///

public void close()
{
if(_conn.state == connectionstate.open)
_conn.close();
}

/**////

/// 讀取數據庫中所有的 文章
///

/// articlecollection
public articlecollection getarticles()
{
articlecollection articles = new articlecollection();
string sql = "select * from " + _articledb;
sqlcommand cmd = new sqlcommand(sql,_conn);
sqldatareader dr = cmd.executereader();
while(dr.read())
{
article art = populatearticle(dr);
articles.add(art);
}
dr.close();
return articles;
}

/**////

/// 給定一個文章編號， 讀取數據庫中的一篇文章
///

/// article
public article getarticle(int articleid)
{
string sql = "select * from " + _articledb + "where id='" + articleid + "'";
sqlcommand cmd = new sqlcommand(sql,_conn);
sqldatareader dr = cmd.executereader();
article article = populatearticle(dr);
dr.close();
return article;
}

/**////

/// 更新數據庫記錄，注意需要設定文章的編號
///

///
public void updatearticle(article article)
{
string sql = "update " + _articledb +" set [email protected],[email protected],[email protected],[email protected]"
+ " where id = @articleid";
sqlcommand cmd = new sqlcommand(sql,_conn);

cmd.parameters.add("@articleid",sqldbtype.int,4).value = article.id;
cmd.parameters.add("@topic",sqldbtype.nvarchar,100).value = article.topic;
cmd.parameters.add("@author",sqldbtype.nvarchar,100).value = article.author;
cmd.parameters.add("@content",sqldbtype.ntext).value = article.content;
cmd.parameters.add("@posttime",sqldbtype.datetime).value = article.posttime;

cmd.executenonquery();

}


/**////

/// 取出數據庫中特定作者發表的文章
///

///
/// articlecollection
public articlecollection getarticlesbyauthor(string author)
{
string sql = "select * from " + _articledb +" where author='" + author + "'";
sqlcommand cmd = new sqlcommand(sql, _conn);

articlecollection articlecollection = new articlecollection();

sqldatareader dr = cmd.executereader();

while (dr.read())
{
article a = populatearticle(dr);
articlecollection.add(a);
}
dr.close();
return articlecollection;

}


/**////

/// 刪除給定編號的一篇文章
///

///
public void deletearticle(int articleid)
{
string sql = "delete from " + _articledb + " where id='" + articleid + "'";
sqlcommand cmd = new sqlcommand(sql, _conn);
cmd.executenonquery();
}




/**////

/// 通過 sqldatareader 生成文章對象
///

///
///
private article populatearticle(sqldatareader dr)
{
article art = new article();

art.id = convert.toint32(dr["id"]);
art.author = convert.tostring(dr["author"]);
art.topic = convert.tostring(dr["topic"]);

art.content = convert.tostring(dr["content"]);
art.posttime= convert.todatetime(dr["posttime"]);

return art;
}



/**////

/// 增加一篇文章到數據庫中，返回文章的編號
///

///
/// 剛剛插入的文章的編號
public int addpost(article article)
{
string sql = "insert into " + _articledb +"(author,topic,content,posttime)"+
"values(@author, @topic, @content, @posttime) "+
"select @postid = identity";
sqlcommand cmd = new sqlcommand(sql,_conn);
cmd.parameters.add("@postid",sqldbtype.int,4);
cmd.parameters["@postid"].direction = parameterdirection.output;

cmd.parameters.add("@author",sqldbtype.nvarchar,100).value = article.author;
cmd.parameters.add("@topic",sqldbtype.nvarchar,400).value = article.topic;
cmd.parameters.add("@content",sqldbtype.text).value = article.content;
cmd.parameters.add("@posttime",sqldbtype.datetime).value = article.posttime;

cmd.executenonquery();

article.id = (int)cmd.parameters["@postid"].value;
return article.id;

}
}
}