Author: Willmove
Home page: http://www.amuhouse.com
E-mail: [email protected]
Disclaimer: This is the author's original work. Please indicate the source when reprinting.
ASP.NET generally uses SQL Server as the backend database. Generally, ASP.NET database operation sample programs use separate data access, which means that each page writes code to connect to the database, access data, and close the database. This method brings some disadvantages. One is that if your database changes, you must change the database connection code page by page.
The second disadvantage is code redundancy. A lot of code is repeated and unnecessary.
Therefore, I tried to implement ASP.NET-type data access through a consistent database operation class.
Let's take the news release system found on general websites as an example. It requires an article database. We name this database News_Articles. The news release system involves publishing news, displaying articles, managing articles, etc.
An article generally has a title, author, publication time, content, and we need to number them. We write it as a class called Article class, the code is as follows:
//Article.cs
using System;
namespace News_Articles.Data
{
/// <summary>
/// Summary description for Article.
/// </summary>
public class Article
{
private int _id; //Article number
private string _author; //The author of the article
private string _topic; //The title of the article
private DateTime _postTime; //The publication time of the article
private string _content; //Article 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; }
}
}
}
Then we write an article collection class ArticleCollection
The code is as follows
Program code
//ArticleCollection.cs
using System[color=#0000ff];
using System.Collections;
namespace News_Articles.Data
{
/// <summary>
/// Collection class of articles, inherited from ArrayList
/// </summary>
public class ArticleCollection : ArrayList
{
public ArticleCollection() : base()
{
}
public ArticleCollection(ICollection c) : base(c)
{
}
}
}[/color]
This class is equivalent to a DataSet in ASP.NET (actually the two are very different). It is very simple. Its main purpose is to collect many articles so that DataGrid or DataList can be used as a data source in the ASP.NET page. Show article.
Now we can implement the operation on the News_Articles database. As I said, this is a database operation class. Might as well name it ArticleDb. The implementation is as follows:
Program code
//ArticleDb.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace News_Articles.Data
{
/**//// <summary>
/// Database operation class, realizing reading, inserting, updating and deleting of article database
/// </summary>
public class ArticleDb
{
private SqlConnection _conn; //SQL Server database connection
private string _articledb = "News_Articles"; //SQL Server article database table
/**//// <summary>
/// Initialization of the class, setting up the database connection
/// </summary>
public ArticleDb()
{
_conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
}
/**//// <summary>
/// Open database connection
/// </summary>
public void Open()
{
if(_conn.State == ConnectionState.Closed)
_conn.Open();
}
/**//// <summary>
/// Close the database connection
/// </summary>
public void Close()
{
if(_conn.State == ConnectionState.Open)
_conn.Close();
}
/**//// <summary>
/// Read all articles in the database
/// </summary>
/// <returns>ArticleCollection</returns>
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;
}
/**//// <summary>
/// Given an article number, read an article in the database
/// </summary>
/// <returns>Article</returns>
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;
}
/**//// <summary>
/// Update database records, please note that you need to set the article number
/// </summary>
/// <param name="article"></param>
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();
}
/**//// <summary>
/// Get the articles published by a specific author in the database
/// </summary>
/// <param name="author"></param>
/// <returns>ArticleCollection</returns>
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;
}
/**//// <summary>
/// Delete an article with a given number
/// </summary>
/// <param name="articleID"></param>
public void DeleteArticle(int articleID)
{
string sql = "Delete FROM " + _articledb + " Where ID='" + articleID + "'";
SqlCommand cmd = new SqlCommand(sql, _conn);
cmd.ExecuteNonQuery();
}
/**//// <summary>
/// Generate article object through SqlDataReader
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
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;
}
/**//// <summary>
/// Add an article to the database and return the article number
/// </summary>
/// <param name="article"></param>
/// <returns>The number of the article just inserted</returns>
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;
}
}
}
The basic framework is already out. If we want to display data from the article database News_Artices in an ASP.NET page, we only need to add a DataGrid or DataList and then bind the data source. For example, add a DataGrid in Default.aspx, name it ArticlesDataGrid, and add
program code
in the background code Default.aspx.cs
using News_Articles.Data;
And add the following code in Page_Load:
program code
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();
}
In this way, all articles in the article database can be read.
If you need to delete an article, add the following code:
Program code
//Delete the article numbered 1
myArticleDb.DeleteArticle(1);
Insert an article, the code is as follows:
program code
//Insert a new article without specifying the article number. The article number will be returned by SQL Server after the article number is inserted successfully.
Article newArticle = new Article();
newArticle.Author = "Willmove";
newArticle.Topic = "Test inserting a new article";
newArticle.Content = "This is the content of the article I wrote";
newArticle.PostTime = DateTime.Now;
int articleId = myArticleDb.AddPost(newArticle);
Update an article with the following code:
program code
//Update an article, please note that you need to specify the article number
Article updateArticle = new Article();
updateArticle.ID = 3; //Note that you need to specify the article number
updateArticle.Author = "Willmove";
updateArticle.Topic = "Test update data";
updateArticle.Content = "This is the content of the article I updated";
updateArticle.PostTime = DateTime.Now;
myArticleDb.UpdateArticle(updateArticle);
The above is just a framework, and there are many details about the specific implementation that are not listed. But based on the above framework, you can more easily write code for database operations. Another suggestion is to write the above SQL statement for database access as a database stored procedure, such as adding an article:
Program Code
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
Appendix 1: Fields of News_Articles database
Program code
field name describes whether the data type length can be null
ID article number int 4 no
Topic article title nvarchar 100 no
Author authornvarchar 100 is
Content article content ntext 16 No
PostTime publication time datetime 8 No
The default value of PostTime can be set to (getutcdate())
The SQL statement is
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
Appendix 2: News_Articles project source code description: Before opening the project file News_Articles.csproj, you need to set the virtual path News_Articles, or change the settings in News_Articles.csproj.webinfo. For normal operation, SQL Server must be installed and the article database News_Articles must be installed. There are SQL text files in the root directory of the project source code.