使ASP.NET中的数据库操作变得简单

/**////

/// 给定一个文章编号， 读取数据库中的一篇文章
///
/// 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 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();

}

/**////

/// 取出数据库中特定作者发表的文章
///
///
/// 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;

}
}
}

using News_Articles.Data;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
ArticleDb myArticleDb = new ArticleDb();
myArticleDb.Open();
ArticleCollection articles = myArticleDb.GetArticles();
this.ArticlesDataGrid.DataSource = articles;
if(!Page.IsPostBack)
{
this.ArticlesDataGrid.DataBind();
}

myArticleDb.Close();
}

//删除编号为 1 的文章
myArticleDb.DeleteArticle(1);

//插入一篇新的文章,不需要指定文章编号，文章编号插入成功后由SQL Server返回。
Article newArticle = new Article();
newArticle.Author = "Willmove";
newArticle.Topic = "测试插入一篇新的文章";
newArticle.Content = "这是我写的文章的内容";
newArticle.PostTime = DateTime.Now;
int articleId = myArticleDb.AddPost(newArticle);

//更新一篇文章,注意需要指定文章的编号
Article updateArticle = new Article();
updateArticle.ID = 3; //注意需要指定文章的编号
updateArticle.Author = "Willmove";
updateArticle.Topic = "测试更新数据";
updateArticle.Content = "这是我更新的文章的内容";
updateArticle.PostTime = DateTime.Now;
myArticleDb.UpdateArticle(updateArticle);

Create PROCEDURE AddPost
(
@ID int OUTPUT,
@Author nvarchar(100),
@Topic nvarchar(100),
@Content ntext,
@PostTime datetime
)
AS
Insert INTO News_Articles(Author, Topic, Content, PostTime) VALUES (@Author, @Topic, @Content, @PostTime);
Select @ID = IDENTITY
GO

字段名 描述 数据类型 长度 是否可为空
ID 文章编号 int 4 否
Topic 文章标题 nvarchar 100 否
Author 作者 nvarchar 100 是
Content 文章内容 ntext 16 否
PostTime 发表时间 datetime 8 否

Create TABLE [News_Articles] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Topic] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Author] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PostTime] [datetime] NOT NULL CONSTRAINT [DF_News_Articles_PostTime] DEFAULT (getutcdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO