Work with database in CodeBehind Framework

In this tutorial, we want to connect with a database by using Entity Framework.

By using Entity Framework, you will not need to deal with SQL issues to a large extent and you will only communicate with the database with C# codes.

In the Visual Studio Code project, we first need to open the project directory. Then we hold the shift button (only for Windows operating system) and then we right-click on the empty space and select Terminal (for Windows it is Power Shell) to open.

How to create a new ASP.NET Core project in Visual Studio Code is taught on the following page.

Enter the following code in the terminal to install the NuGet package for Entity Framework and MySql.

Note: Please note that you can also use packages from other databases in Entity Framework. These packages are available for every popular database. The following package is also used to work with the database in SQLServer:

To use the CodeBehind framework, its NuGet package must be installed; enter the following code in the terminal to install the NuGet package for CodeBehind.

DatabaseContext class

In the Explorer section that we added earlier on the right side, we right-click on the class directory and then select the New File option and then create a new file named DatabaseContext.cs.

We put the following codes in the DatabaseContext.cs file.

The code above is a simple structure that uses Entity Framework Core to work with the database. Below is a brief description of the components of this code:

1- ConnectionString Class:

- This class has a static property called Value which is used to store the connection string to the database.

- Set method assigns the connection string to Value.

2- DatabaseContext Class:

- This class inherits from DbContext and is designed to interact with the database.

- In the constructor of this class, the Database.EnsureCreated() method is called, which causes the database to be created if it does not exist, and if it exists, the tables are checked and if not, they are created.

- DbSet is defined as Content, which represents the ContentRow table in the database.

- The OnConfiguring method is used to configure the connection to the database using UseMySQL and the specific connection string specified in ConnectionString.Value.

3- ContentRow Class:

This class represents a row of the database table and contains three properties:

- id: A unique identifier for each row marked as Primary Key.

- title: title of the relevant content.

- text: the text of the relevant content.

Set ConnectionString

In this article, we store the connection string in the appsettings.json file, which is related to .NET Core projects, as below.

We also configure the Program.cs class as follows.

The following code from the Program.cs class reads the ConnectionStrings from the appsettings.json file once, and the connection strings are kept as long as the program is active, and the appsettings.json file is no longer referred to.

Run the project once (F5 key) to build the default CodeBehind framework files. This will add a series of ready-made templates to your project so that you can develop your new systems more quickly.

From here on in the tutorial, it is only necessary to create pages for data listing, data insertion, data editing and data deletion operations according to the MVC pattern.

Add content in database

To organize the pages, we create a directory named admin in the wwwroot directory, then we create a directory named content in wwwroot/admin path.

First, we create a directory named add in wwwroot/admin/content path. Then we add a View file named Default.aspx in it. Finally, we put the following codes in it

In this View file, we put a text input for the title, a textarea tag for the text and a button to send data in the form tag with the action path to /admin/content/add; we also defined a Controller called AddContentController. According to the codes below, we create the AddContentController Controller class.

The AddContentController Controller does not affect the page during initial execution and only executes the btn_Add_Click method when the btn_Add button is clicked. The btn_Add_Click method receives the title and text data and then adds it to the database. The codes for adding a new row in the Content table show the beauty of using the Entity Framework and do not need further explanation.

As shown in the above class code, using WebForms Core technology, an h3 tag is added at the end of the form tag, which shows "Content Was Add" in green.

The bottom image shows the screenshot of the add content page.

Add content View page

Content List View

To display the contents, we create a View file named Default.aspx in wwwroot/admin/contentpath, then, we put the following codes in it

According to the View file above, first a Controller named ContentController and a Model named ContentModel have been added in the attributes section of the page. If you look carefully, you will see that the name of the Model class is placed between two brackets; the reason is that we want to have a simple Model class and we don't need the attributes and methods in the CodeBehindModel interface. At the bottom, we have a form tag whose /admin/content path is placed in its action attribute. Inside the form tag, a table of the Content class is created using the foreach loop. In addition to the values ​​in each row of the table, a submit button (with row Id value) has been added to remove data from the table and submit button (with row Id value) has been added to edit content data in current row. The Model in this View has only one value called Content, which is a list of the ContentRow class.

The ContentController class below is a Controller whose main task is to display the content list and remove the content specified by the user.

According to the codes above, in the PageLoad method, it is first checked whether a button named btn_Delete has been pressed or not; if pressed, calls the btn_Delete_Click method; then checked whether a button named btn_GoToEdit has been pressed or not; if pressed, calls the btn_GoToEdit_Click method Otherwise, it creates a new content Model and adds all the content in the database to the Model, and then the corresponding view is loaded using the Model. The btn_Delete_Click method is also responsible for deleting the specified content. Using the Id of the delete button received from the form, it tries to find the row corresponding to this Id from the database and, if it exists, deletes it. Finally, the changes are saved and the current row in the user's browser is also deleted. Call the btn_GoToEdit_Click method causes the edit page to replace the current page. In the btn_GoToEdit_Click method, we used the SetViewPath method instead of calling the View method; the reason is that the View method only supports the physical path of the View, and we need to call the edit View path along with the data section. The SetViewPath method also supports query string data.

Note: In the content class, we called the edit page for the editing process. You must make sure that the name of the submitted forms in the requests of the current page is not the same as the page that is being called; Otherwise, a collision will occur.
Example: We have used btn_Edit form data in the content editing class, so we named the edit button on the content page btn_GoToEdit so as not to create a conflict.

The Model class below has a property named Content that holds a list of the ContentRow class.

The above Model class is initialized in the Controller and its values ​​are listed in the View.

The bottom image shows the screenshot of the content page (content list). The yellow and red circles are the submit buttons whose style has been changed. Clicking on the yellow button will display the content editing page. Clicking on the red button deletes the current row both in the database and on this page.

Content View page

We make the View for editing similar to the View for adding. In this View file, we put a text input for the title, a textarea tag for the text and a button to send data in the form tag with the action path to /admin/content/edit. In this file, we created a hidden input to keep the content ID inside the form tag. we also defined a Controller called EditContentController. The Section feature is also activated in this View file.

We also add a Model in the attributes section of the page to show the information of the current line on the page.

Edit content data

According to the codes below, we create the EditContentController Controller class.

Controller for edit content using CodeBehind; public partial class EditContentController : CodeBehindController { public new void PageLoad(HttpContext context) { if (!string.IsNullOrEmpty(context.Request.Form["btn_Edit"])) { btn_Edit_Click(context); return; } if (Section.Count() < 1) { IgnoreAll(); return; } int Id = Section.GetValue(0).ToNumber(); ContentRow model = new ContentRow(); using (var db = new DatabaseContext()) { var row = db.Content.SingleOrDefault(row => row.id == Id); if (row != null) { model.id = row.id; model.title = row.title; model.text = row.text; } } View(model); } private void btn_Edit_Click(HttpContext context) { int Id = context.Request.Form["hdn_Id"].ToNumber(); string Title = context.Request.Form["txt_Title"].ToString(); string Text = context.Request.Form["txt_Text"].ToString(); WebForms Form = new WebForms(); using (var db = new DatabaseContext()) { var row = db.Content.SingleOrDefault(row => row.id == Id); if (row != null) { row.id = Id; row.title = Title; row.text = Text; db.Content.Update(row); db.SaveChanges(); Form.SetBackgroundColor(InputPlace.Id("Row" + Id), "green"); } } IgnoreLayout = true; View("/admin/content/Default.aspx"); Control(Form); } }

According to the Controller class above, the PageLoad method is executed first. The code then checks if the edit button (btn_Edit) has been clicked on the request form. If yes, it calls the btn_Edit_Click method to perform the editing operation. If the form is requested for the first time (the edit button (btn_Edit) has not been clicked), it first checks that Section exists, otherwise it does not display the page. Then, according to the value of the first Section, the Id number is obtained and the current Id row from the content table is added from the information database in the created instance of the Model class, and finally the Model values ​​are set in the View.

Note: Please read the CodeBehind Framework tutorials from the beginning. We have fully explained Section in the CodeBehind Framework in previous tutorials.

The btn_Edit_Click method receives the title, text and Id data and then updates that data according to the Id value in the database. After the operation is completed, the path of the content list page is placed in the View method to return from the edit page to the content list page. Here, by using CodeBehind framework WebForms in a beautiful way, the background of the edited line is colored green.

The bottom image shows the screenshot of the content page after applying the edit from the edit content page. After the content is edited, the content page replaces the content edit page and the background color of the edited content row turns green.

Edit content View page

The following style has been added to the project to make the HTML content more beautiful.

For more practice, add a boolean data to the content table so that it is possible to enable and disable rows in the content list page. Also warn the user that the title and text are empty in the inputes related to inserting and editing content (using WebForms Core).