02-Big Pear Blog System database design and the use of dapper

There is no doubt that the design of the database plays a vital role in a system. As we all know, system design is divided into two parts, or two stages, namely database design and functional design. Building a perfect system requires sufficient consideration, careful design, reasonable connection and close cooperation between the two phases. Database design is the foundation of the whole system, it is concerned with the realization of the system function, stability, extensibility, and so on, the importance of database design is visible.

So good database design should have what kind of basic conditions, there are roughly these points:

1, fully reflect the needs of the system

Whether it is database design or functional design, our ultimate goal is to achieve customer business needs, so the first criterion of database design is to meet the business requirements.

2, ensure the accuracy and consistency of data

The database is designed with the primary foreign key, non-null, limited, unique index and so on to ensure the robustness of the data.

3, improve the efficiency of data query

A good system to achieve fluency, the role of information is to improve people's productivity, improve efficiency, so must ensure that the system's operating efficiency, reduce the waiting time for users, through a reasonable table structure, arrange the physical storage partition, increase the index and other ways to improve the data read efficiency.

4. Good extensibility

A good product will continue to expand and upgrade to adapt to constantly updated business needs, if you want to expand the business, must first involve the expansion of the database, in the database design, it is important to follow the database design specifications, we usually will be the database specification to the third paradigm, all the physical model to achieve the paradigm requirements , the principle of extension is to try not to change the original table structure, once involved in the original table structure, there will be code rewriting the danger.

As for the specification of database design, we can not ignore every point, the specification of the Convention, the field specification, the table structure specification and the use of the specification, including table naming, field naming and so on, which will have a great impact on the development of the future system. Below I for this simple blog system, database design, which inevitably there will be unreasonable places, please criticize correct, of course, the design may not be too perfect, only to achieve basic functions.

Usually we use the database design tools generally have two, one is PowerDesigner, the other is Erstudio, I personally used to use Erstudio, each has the advantages of it, I just used to use Erstudio only, no need to care, to see personal habits.

Again, I am the database design here is not a complete blog system design, only to achieve some of the features of the blog design, and even the current database design is not necessarily all involved in the development, we first look at the logical data model, divided into two parts, part of the system users, the other part is about the content of the blog

The structure is relatively simple, needless to say, the following is the blog related tables:

Simply say the following about the blog related tables, first there are categories of blogs, there are tags, these we should be very familiar with, and then the content of the blog, the collection of blogs, as well as browse the blog, we all know about a blog, other people can comment on this blog, you can also reply to other people's comments, In the comments reply to this table, the ID of the reply, may be the ID of the blog, it may be someone else's comment ID, corresponding to the type of reply, 1 for the blog comments, 2 for comments reply. The reply status is deleted. The relationship between the tables should be relatively simple, not much to say. In addition, in this blog system, I only realize the blog additions and deletions to check the function, the other later have time to do, the purpose is mainly to learn. NET cross-platform.

Already database script and design put on GitHub, below we look at the use of dapper, mainly is the database connection and additions and deletions to check, and we usually use the ADO similar. So what exactly is dapper, dapper a lightweight ORM framework where we add dapper to our service layer, which is the data access layer in this project, and we introduce it as a nuget package, you can see its interpretation, a high Performance Micro-orm, a highly efficient and lightweight ORM framework, can be installed directly by clicking Install. After the installation is complete, let's test how to use dapper to manipulate the database in the project, I create a database operation class, take the blog's category table as an example: BlogCategService.cs, first create a public connection class, that is, get our database connection string, Note that Dapper is capable of supporting multiple databases, and we will use SQL Server as an example to create a class ConnectionFactory.cs that gets the database connection.

<summary>///get SQL Server database connection//</summary>///<returns></returns>public static DbConnection getopensqlconnection () {     var conn = new SqlConnection ("server=.; Uid=sa;pwd=666666;database=dayaliblogdb ");     Conn. Open ();     Return conn; }

Here we add a method of inserting data into the Blogcategservice class, with the following code:

<summary>///Insert Blog category///</summary>///<returns> Insert Blog category id</returns>public int Insertcateg ( ) {    using (var conn = connentionfactory.getopensqlconnection ())    {        var resId = conn. Query<int> (@ "INSERT into [dbo].[ T_blog_catelog] (catelogname,createuser,updateuser) VALUES (@CatelogName, @CreateUser, @UpdateUser); "+            " Select  @ @IDENTITY ", new {catelogname =" Linux ", CreateUser = 1, UpdateUser = 1}). First ();        return resId;}    }

After the Operation class is created, we reference our model layer and service layer in the Web project

Creating a test controller on the Web tier calls the method we just created to insert the big class of blogs, TestController.cs

Then run our web application,

Ok, our test was successful! We put this method of inserting again to modify, increase the parameters, that is, we want to insert the data class, first we add the model layer of our blog class entity, I have been created here, we directly modify the method, do not add a reference to the model at the service layer, modify the insertion method as follows:

public int Insertcateg (T_blog_catelog categry) {    using (var conn = connentionfactory.getopensqlconnection ())    {        var resId = conn. Query<int> (@ "INSERT into [dbo].[ T_blog_catelog] (catelogname,createuser,updateuser) VALUES (@CatelogName, @CreateUser, @UpdateUser); "+            " Select  @ @IDENTITY ", categry). First ();        return resId;}    }

Then modify our controller inside the method, instead of the reference, not much to say, directly compiled through:

Then query, modify, delete the method, the blog inside no longer mention, a bit of a waste of time, I will tidy up after the submission of code.

This blog will write so much content. Good night! Start a new week again!!! fighting!!!

02-Big Pear Blog System database design and the use of dapper