/**////
/// 给定一个文章编号, 读取数据库中的一篇文章
/// ///
Articlepublic 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 Topic=@topic,Author=@author,Content=@content,PostTime=@postTime"
+ " 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();
}
/**////
/// 取出数据库中特定作者发表的文章
/// ///
///
ArticleCollectionpublic 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;
}
}
}