Let’s dive in!<\/p>\n
<\/a>What is MariaDB?<\/h2>\nMariaDB is an open-source fork of the MySQL relational database management system (RDBMS)<\/strong>. It is currently actively maintained by the open-source community, led by many of the original developers of the MySQL database. In comparison to MySQL, MariaDB offers additional features, such as Galera<\/a>, Sequences<\/a>, and the UUID data type<\/a>. Furthermore, MariaDB provides extra storage engines like ColumnStore<\/a>, MyRocks<\/a>, and Aria<\/a>.<\/p>\nNow that we know what MariaDB is, let\u2019s see how to download, install, and use it.<\/p>\n
<\/a>Preparation of MariaDB and ASP.NET Core App<\/h2>\nFirst, let’s download MariaDB from the MariaDB download page<\/a>. Next, we install it with the preselected default options on the installation menu. We need to make a note of the root user password we set during the installation.<\/strong><\/p>\nIt’s important to mention that the installation package includes the HeidiSQL app, a free GUI client for interacting with the MariaDB server<\/strong>. We can use this app to explore the databases we create.<\/p>\nNext up, let’s create an ASP.NET Core Web API project named UsingMariaDBWithASPNETCoreWebAPI<\/code>:<\/p>\ndotnet new webapi --name UsingMariaDBWithASPNETCoreWebAPI<\/code><\/p>\nTo learn how to do this from Visual Studio, please visit our guide on creating and configuring a new ASP.NET Core Web API project<\/a>.<\/p>\n<\/a>Setting Up the Entity Framework Core Library to Connect MariaDB with ASP.NET Core APP<\/h3>\nEntity Framework (EF) Core<\/a> is a library that we can utilize to access our database from our web API. When we use the EF Core library, we can choose to build our application using either the Database-First approach<\/a> or the Code-First approach<\/a>.<\/p>\nIn the Database-First approach, we design our database first and then use EF Core to generate the tables and the context. In the Code-First approach,\u00a0we create our models and context first, then we use EF Core to create the database schema and possibly add seed data to it. Here, we will use the Code-First approach.<\/p>\n
That said, let\u2019s now install the EF Core package from the operating system command line:<\/p>\n
dotnet add package Microsoft.EntityFrameworkCore<\/code><\/p>\nAs the next step, let’s install the Pomelo.EntityFrameworkCore.MySql<\/a> database provider:<\/p>\ndotnet add package Pomelo.EntityFrameworkCore.MySql -v 8.0.0-beta.2<\/code><\/p>\nThis NuGet package is the EF Core database provider for MariaDB and other MySQL-compatible databases.<\/p>\n
We utilize the version -v 8.0.0-beta.2<\/code> modifier to specify the version of the provider. Using lower versions of this provider with version 8.0.0 of the Microsoft.EntityFrameworkCore.Tools<\/code> package will cause an exception when we want to add our migrations.<\/p>\n<\/a>Creating the Model<\/h3>\nNow, let’s create a Models<\/code> folder in our project’s base directory. In this folder, let’s define our model:<\/p>\npublic class Student\r\n{\r\n public int Id { get; set; }\r\n public string? FirstName { get; set; }\r\n public string? LastName { get; set; }\r\n public string? Address { get; set; }\r\n}<\/pre>\n<\/a>Creating Our Context Class<\/h3>\nThe context class is an important part of our application that we use to communicate with our database. This class inherits the base EF Core DbContext<\/code> class that contains details for configuring and accessing the database.<\/p>\nLet’s define a Context class named ApplicationContext<\/code>\u00a0 in our Models<\/code> folder:<\/p>\npublic class ApplicationContext(DbContextOptions<ApplicationContext> options) : DbContext(options)\r\n{\r\n public DbSet<Student> Students { get; set; }\r\n\r\n protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n {\r\n modelBuilder.Entity<Student>().HasData(\r\n new Student\r\n {\r\n Id = 1,\r\n FirstName = \"John\",\r\n LastName = \"Doe\",\r\n Address = \"123 Main St\",\r\n },\r\n new Student\r\n {\r\n Id = 2,\r\n FirstName = \"Jane\",\r\n LastName = \"Doe\",\r\n Address = \"456 Oak St\",\r\n }\r\n );\r\n }\r\n}<\/pre>\nIn this ApplicationContext<\/code> class, we first establish a DbSet<Student><\/code> object named Students<\/code> to represent our entity. The DbSet<T><\/code> class provides methods for executing CRUD operations on the corresponding database tables in our database. Then, we override the OnModelCreating()<\/code> method, utilizing the HasData()<\/code> method to add seed data to our Students<\/code> table.<\/p>\nWith that, let\u2019s add our database connection string to the appsettings.json<\/code> file:<\/p>\n{\r\n \"Logging\": {\r\n \"LogLevel\": {\r\n \"Default\": \"Information\",\r\n \"Microsoft.AspNetCore\": \"Warning\"\r\n }\r\n },\r\n \"ConnectionStrings\": {\r\n \"StudentsConnection\": \"server=127.0.0.1;database=CodeMazeStudents;user=root;password=yourpassword;\"\r\n },\r\n \"AllowedHosts\": \"*\"\r\n}<\/pre>\nIn our connection string, we specify 127.0.0.1<\/code> (localhost) as the server. Then, we set the database name as CodeMazeStudents<\/code><\/strong>. If a database with that name exists on our server, we use it. Otherwise, MariaDB will create a new database for us. The password yourpassword<\/code> corresponds to the one we noted during the MariaDB installation.<\/p>\nNow, we will move to the Program.cs<\/code> file and register our ApplicationContext<\/code> class as a service:<\/p>\nbuilder.Services.AddDbContext<ApplicationContext>(options =>\r\n{\r\n var connectionString = builder.Configuration.GetConnectionString(\"StudentsConnection\");\r\n options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));\r\n});<\/pre>\nHere, we define the connection details for our ApplicationContext<\/code>. First, we call the GetConnectionString()<\/code> method, with the \"StudentsConnection\"<\/code> argument, to retrieve the connection string from our appsettings.json<\/code> file.<\/p>\nSubsequently, we configure our context to connect to a MySQL-compatible database by invoking the UseMySql()<\/code> method. Here, we pass in the returned connection string, and the server version is automatically detected using the ServerVersion.AutoDetect()<\/code> method.<\/p>\n<\/a>Adding Migration and Updating the Database<\/h3>\nWe use the EF Core migrations<\/a> feature to create and incrementally update our database schema based on our application’s models. To work with the migrations API, let’s install the Microsoft.EntityFrameworkCore.Tools<\/code> NuGet package:<\/p>\ndotnet add package Microsoft.EntityFrameworkCore.Tools<\/code><\/p>\nLet\u2019s now add our first migration:<\/p>\n
dotnet ef migrations add FirstMigration<\/code><\/p>\nAfter that, let’s create our database and schema:<\/p>\n
dotnet ef database update<\/code><\/p>\nWith everything set, we can now view our database with HeidiSQL:<\/p>\n
![\"Initial](\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/InitialStudentsDatabase.png\")
<\/a><\/p>\nNow that we have seen what our database looks like, let\u2019s move on to the data access of our application.<\/p>\n
<\/a>Integrating Our MariaDB Database With Our ASP.NET Core App<\/h2>\nNow, let’s define our repository pattern<\/a> logic for performing CRUD operations (Creating, Reading, Updating, and Deleting) on our CodeMazeStudents<\/code> database. After that, we’ll proceed to define the logic for our Web API controller.<\/p>\n<\/a>Creating the Repository Logic<\/h3>\nTo implement a simple repository pattern, first, let\u2019s create a Contracts<\/code> folder in our Models<\/code> folder. Then, in this folder, let\u2019s add an interface:<\/p>\npublic interface IDataRepository\r\n{\r\n Task<IEnumerable<Student>> GetAllAsync();\r\n Task<Student?> GetAsync(int id);\r\n Task AddAsync(Student entity);\r\n Task UpdateAsync(Student entityToUpdate, Student entity);\r\n Task DeleteAsync(Student entity);\r\n}<\/pre>\nThis interface defines various asynchronous CRUD methods for data operations in our application. We will create a concrete class that implements this interface and uses our ApplicationContext<\/code> to perform CRUD operations on our database.<\/p>\nFor this, let\u2019s create a DataRepository<\/code> folder in the Models<\/code> directory. In this folder, let\u2019s define a StudentRepository<\/code> class:<\/p>\npublic class StudentRepository(ApplicationContext context) : IDataRepository\r\n{\r\n readonly ApplicationContext _studentContext = context;\r\n\r\n public async Task AddAsync(Student entity)\r\n {\r\n _studentContext.Students.Add(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<Student?> GetAsync(int id) => await _studentContext.Students.FindAsync(id);\r\n\r\n public async Task UpdateAsync(Student entityToUpdate, Student entity)\r\n {\r\n entityToUpdate.FirstName = entity.FirstName;\r\n entityToUpdate.LastName = entity.LastName;\r\n entityToUpdate.Address = entity.Address;\r\n\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task DeleteAsync(Student entity)\r\n {\r\n _studentContext.Remove(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<IEnumerable<Student>> GetAllAsync()\r\n {\r\n return await _studentContext.Students.ToListAsync<Student>();\r\n }\r\n}<\/pre>\nHere, we add logic for creating, reading, updating, and deleting student data. Additionally, we implement a method to retrieve all students in our database.<\/p>\n
With that, let\u2019s open the Program.cs<\/code> file and register this class and the IDataRepository<\/code> interface as a service:<\/p>\nbuilder.Services.AddScoped<IDataRepository, StudentRepository>();<\/code><\/p>\nNice! Next up, let\u2019s create our Web API controller and add action methods to it.<\/p>\n
<\/a>Adding Our Controller<\/h3>\nControllers enable us to process HTTP requests, validate our models, and send back responses to our users.\u00a0<\/p>\n
So, let’s add a StudentsController<\/code> class to the Controllers<\/code> folder in our project:<\/p>\n[Route(\"api\/students\")]\r\n[ApiController]\r\npublic class StudentsController(IDataRepository dataRepository) : ControllerBase\r\n{\r\n private readonly IDataRepository _dataRepository = dataRepository;\r\n}<\/pre>\nNext, let’s define a GetAllStudents()<\/code> method in this controller:<\/p>\n[HttpGet]\r\npublic async Task<IActionResult> GetAllStudents()\r\n{\r\n var students = await _dataRepository.GetAllAsync();\r\n\r\n return Ok(students);\r\n}<\/pre>\nHere, we call our data repository’s GetAllAsync()<\/code> method to retrieve all the students in our database.<\/p>\nNow, let’s create a GetStudent()<\/code> action method:<\/p>\n[HttpGet(\"{id}\", Name = \"GetStudent\")]\r\npublic async Task<IActionResult> GetStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"Student not found.\");\r\n }\r\n\r\n return Ok(student);\r\n}<\/pre>\nFor this action method, we specify that it is an HttpGet<\/code> method with an \"{id}\"<\/code> template. We set the route name of this method to \"GetStudent\"<\/code>.<\/p>\nThen, we try to retrieve the student from the database by calling the GetAsync()<\/code> method with the provided id. If the student is null, we return a 404 Not Found<\/code> error. Otherwise, we call the Ok()<\/code> method that returns an OkObjectResult<\/code> object and a 200 OK<\/code> status code.<\/p>\nNext, let’s define a PostStudent()<\/code> method:<\/p>\n[HttpPost]\r\npublic async Task<IActionResult> PostStudent([FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n await _dataRepository.AddAsync(student);\r\n\r\n return CreatedAtRoute(\"GetStudent\", new { student.Id }, null);\r\n}<\/pre>\nIf the student that we want to add to our MariaDB database is null, we return a 400 Bad Request<\/code> error. If the student is not null, we add the student to our database and return a 201 Created<\/code> status code.<\/p>\nLet’s now create a PutStudent()<\/code> method in our controller:<\/p>\n[HttpPut(\"{id}\")]\r\npublic async Task<IActionResult> PutStudent(int id, [FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n\r\n var studentToUpdate = await _dataRepository.GetAsync(id);\r\n if (studentToUpdate is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.UpdateAsync(studentToUpdate, student);\r\n\r\n return NoContent();\r\n}<\/pre>\nOnce again, if the incoming student is null, we return a 400 Bad Request<\/code> error. Otherwise, we retrieve the existing student from the database. If we do not find the specified student, we return a 404 Not Found<\/code> response.<\/p>\nThen, if the existing student is not null, we invoke our data repository’s UpdateAsync()<\/code> method, passing in the existing student and the new student data. At the end, we return a 204 No Content<\/code> response.<\/p>\nFinally, let’s define a DeleteStudent()<\/code> action method:<\/p>\n[HttpDelete(\"{id}\")]\r\npublic async Task<IActionResult> DeleteStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.DeleteAsync(student);\r\n\r\n return NoContent();\r\n}<\/pre>\nIn this method, we first retrieve the student from our database. If the student is null, we return a 404 Not Found<\/code> response. Otherwise, we delete the student from our database and return a 204 No Content<\/code> response.<\/p>\nGreat! Our controller is now ready, and we can proceed to test our Web API and its interaction with our MariaDB database.<\/p>\n
<\/a>Testing the Web API With Postman<\/h2>\nNow, let’s test our StudentsController<\/code> endpoints with Postman<\/a>.<\/p>\nFirst, let’s visit our GET<\/code> https:\/\/localhost:7091\/api\/students<\/code> endpoint\u00a0and check the result:<\/p>\n[\r\n {\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n },\r\n {\r\n \"id\": 2,\r\n \"firstName\": \"Jane\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"456 Oak St\"\r\n }\r\n]<\/pre>\nNext, let’s see what our GET<\/code> https:\/\/localhost:7091\/api\/students\/1<\/code> endpoint returns:<\/p>\n{\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n}<\/pre>\nWith that, let’s look at the result of visiting our POST<\/code> endpoint:<\/p>\n![\"Post](\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/PostStudentEndpoint-1.png\")
<\/a><\/p>\nNow, let’s update our first student’s record:<\/p>\n
![\"Updating](\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/UpdateFirstStudent-1.png\")
<\/a><\/p>\nFinally, let’s delete a student from our database:<\/p>\n
![\"Deleting](\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/DeleteSeconndStudentEndpoint.png\")
<\/a><\/p>\nThat’s it. As we can see, all our endpoints are working perfectly.<\/p>\n
<\/a>Conclusion<\/h2>\nIn this article, we learned how to work with a MariaDB database in an ASP.NET Core Web API.<\/p>\n
Using EF Core’s code-first approach, we began our database design by defining our database model and ApplicationContext. Then, we created and applied our migrations. We also implemented all the CRUD operations.<\/p>\n
At the end, we used Postman to examine how our Web API interacts with our MariaDB database.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this article, we will discuss how to use a MariaDB database with an ASP.NET Core Web API. First, we will discuss what MariaDB is and how we include it in our application. Then, we will use the Entity Framework Core library to create a mapping between our Web API models and our MariaDB database. […]<\/p>\n","protected":false},"author":52,"featured_media":62187,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[2079],"tags":[898,926,1811,2049],"class_list":["post-104053","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-api","tag-net-core-web-api","tag-asp-net","tag-c","tag-mariadb","et-has-post-format-content","et_post_format-et-post-format-standard"],"yoast_head":"\n
Using MariaDB With ASP.NET Core Web API - Code Maze<\/title>\n<meta name=\"description\" content=\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using MariaDB With ASP.NET Core Web API - Code Maze\" \/>\n<meta property=\"og:description\" content=\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\" \/>\n<meta property=\"og:site_name\" content=\"Code Maze\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-23T11:18:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-31T14:52:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1100\" \/>\n\t<meta property=\"og:image:height\" content=\"620\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Januarius Njoku\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/cjaynjoku\" \/>\n<meta name=\"twitter:site\" content=\"@CodeMazeBlog\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Januarius Njoku\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":[\"Article\",\"BlogPosting\"],\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"},\"author\":{\"name\":\"Januarius Njoku\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18\"},\"headline\":\"Using MariaDB With ASP.NET Core Web API\",\"datePublished\":\"2024-01-23T11:18:03+00:00\",\"dateModified\":\"2024-01-31T14:52:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"},\"wordCount\":1468,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/code-maze.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"keywords\":[\".NET Core Web API\",\"ASP.NET\",\"C#\",\"MariaDb\"],\"articleSection\":[\"Web API\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\",\"url\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\",\"name\":\"Using MariaDB With ASP.NET Core Web API - Code Maze\",\"isPartOf\":{\"@id\":\"https:\/\/code-maze.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"datePublished\":\"2024-01-23T11:18:03+00:00\",\"dateModified\":\"2024-01-31T14:52:31+00:00\",\"description\":\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\",\"breadcrumb\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"width\":1100,\"height\":620,\"caption\":\"ASP.NET Core\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/code-maze.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using MariaDB With ASP.NET Core Web API\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/code-maze.com\/#website\",\"url\":\"https:\/\/code-maze.com\/\",\"name\":\"Code Maze\",\"description\":\"Learn. Code. Succeed.\",\"publisher\":{\"@id\":\"https:\/\/code-maze.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/code-maze.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/code-maze.com\/#organization\",\"name\":\"Code Maze\",\"url\":\"https:\/\/code-maze.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png\",\"width\":3511,\"height\":3510,\"caption\":\"Code Maze\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/CodeMazeBlog\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18\",\"name\":\"Januarius Njoku\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg\",\"caption\":\"Januarius Njoku\"},\"description\":\"Januarius Njoku is a mechanical engineer who is passionate about using technology to improve the design and performance of mechanical systems. With experience in programming with C# and Python, he is also well-versed in DevOps technologies such as Ansible, Docker, and Puppet. Moreover, having a knack for writing, he has authored several technical articles on C# and .NET technologies. He is always on the lookout for new challenges and opportunities to learn and grow in the field of engineering and technology.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/januariusnjoku\/\",\"https:\/\/x.com\/https:\/\/twitter.com\/cjaynjoku\"],\"url\":\"https:\/\/code-maze.com\/author\/njokujennaro\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using MariaDB With ASP.NET Core Web API - Code Maze","description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","og_locale":"en_US","og_type":"article","og_title":"Using MariaDB With ASP.NET Core Web API - Code Maze","og_description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","og_url":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","og_site_name":"Code Maze","article_published_time":"2024-01-23T11:18:03+00:00","article_modified_time":"2024-01-31T14:52:31+00:00","og_image":[{"width":1100,"height":620,"url":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","type":"image\/png"}],"author":"Januarius Njoku","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/cjaynjoku","twitter_site":"@CodeMazeBlog","twitter_misc":{"Written by":"Januarius Njoku","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":["Article","BlogPosting"],"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#article","isPartOf":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"},"author":{"name":"Januarius Njoku","@id":"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18"},"headline":"Using MariaDB With ASP.NET Core Web API","datePublished":"2024-01-23T11:18:03+00:00","dateModified":"2024-01-31T14:52:31+00:00","mainEntityOfPage":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"},"wordCount":1468,"commentCount":0,"publisher":{"@id":"https:\/\/code-maze.com\/#organization"},"image":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"thumbnailUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","keywords":[".NET Core Web API","ASP.NET","C#","MariaDb"],"articleSection":["Web API"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","url":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","name":"Using MariaDB With ASP.NET Core Web API - Code Maze","isPartOf":{"@id":"https:\/\/code-maze.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"image":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"thumbnailUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","datePublished":"2024-01-23T11:18:03+00:00","dateModified":"2024-01-31T14:52:31+00:00","description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","breadcrumb":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","width":1100,"height":620,"caption":"ASP.NET Core"},{"@type":"BreadcrumbList","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/code-maze.com\/"},{"@type":"ListItem","position":2,"name":"Using MariaDB With ASP.NET Core Web API"}]},{"@type":"WebSite","@id":"https:\/\/code-maze.com\/#website","url":"https:\/\/code-maze.com\/","name":"Code Maze","description":"Learn. Code. Succeed.","publisher":{"@id":"https:\/\/code-maze.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/code-maze.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/code-maze.com\/#organization","name":"Code Maze","url":"https:\/\/code-maze.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/#\/schema\/logo\/image\/","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png","width":3511,"height":3510,"caption":"Code Maze"},"image":{"@id":"https:\/\/code-maze.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/CodeMazeBlog"]},{"@type":"Person","@id":"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18","name":"Januarius Njoku","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/#\/schema\/person\/image\/","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg","caption":"Januarius Njoku"},"description":"Januarius Njoku is a mechanical engineer who is passionate about using technology to improve the design and performance of mechanical systems. With experience in programming with C# and Python, he is also well-versed in DevOps technologies such as Ansible, Docker, and Puppet. Moreover, having a knack for writing, he has authored several technical articles on C# and .NET technologies. He is always on the lookout for new challenges and opportunities to learn and grow in the field of engineering and technology.","sameAs":["https:\/\/www.linkedin.com\/in\/januariusnjoku\/","https:\/\/x.com\/https:\/\/twitter.com\/cjaynjoku"],"url":"https:\/\/code-maze.com\/author\/njokujennaro\/"}]}},"_links":{"self":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/users\/52"}],"replies":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/comments?post=104053"}],"version-history":[{"count":4,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053\/revisions"}],"predecessor-version":[{"id":104063,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053\/revisions\/104063"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/media\/62187"}],"wp:attachment":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/media?parent=104053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/categories?post=104053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/tags?post=104053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Now that we know what MariaDB is, let\u2019s see how to download, install, and use it.<\/p>\n
<\/a>Preparation of MariaDB and ASP.NET Core App<\/h2>\nFirst, let’s download MariaDB from the MariaDB download page<\/a>. Next, we install it with the preselected default options on the installation menu. We need to make a note of the root user password we set during the installation.<\/strong><\/p>\nIt’s important to mention that the installation package includes the HeidiSQL app, a free GUI client for interacting with the MariaDB server<\/strong>. We can use this app to explore the databases we create.<\/p>\nNext up, let’s create an ASP.NET Core Web API project named UsingMariaDBWithASPNETCoreWebAPI<\/code>:<\/p>\ndotnet new webapi --name UsingMariaDBWithASPNETCoreWebAPI<\/code><\/p>\nTo learn how to do this from Visual Studio, please visit our guide on creating and configuring a new ASP.NET Core Web API project<\/a>.<\/p>\n<\/a>Setting Up the Entity Framework Core Library to Connect MariaDB with ASP.NET Core APP<\/h3>\nEntity Framework (EF) Core<\/a> is a library that we can utilize to access our database from our web API. When we use the EF Core library, we can choose to build our application using either the Database-First approach<\/a> or the Code-First approach<\/a>.<\/p>\nIn the Database-First approach, we design our database first and then use EF Core to generate the tables and the context. In the Code-First approach,\u00a0we create our models and context first, then we use EF Core to create the database schema and possibly add seed data to it. Here, we will use the Code-First approach.<\/p>\n
That said, let\u2019s now install the EF Core package from the operating system command line:<\/p>\n
dotnet add package Microsoft.EntityFrameworkCore<\/code><\/p>\nAs the next step, let’s install the Pomelo.EntityFrameworkCore.MySql<\/a> database provider:<\/p>\ndotnet add package Pomelo.EntityFrameworkCore.MySql -v 8.0.0-beta.2<\/code><\/p>\nThis NuGet package is the EF Core database provider for MariaDB and other MySQL-compatible databases.<\/p>\n
We utilize the version -v 8.0.0-beta.2<\/code> modifier to specify the version of the provider. Using lower versions of this provider with version 8.0.0 of the Microsoft.EntityFrameworkCore.Tools<\/code> package will cause an exception when we want to add our migrations.<\/p>\n<\/a>Creating the Model<\/h3>\nNow, let’s create a Models<\/code> folder in our project’s base directory. In this folder, let’s define our model:<\/p>\npublic class Student\r\n{\r\n public int Id { get; set; }\r\n public string? FirstName { get; set; }\r\n public string? LastName { get; set; }\r\n public string? Address { get; set; }\r\n}<\/pre>\n<\/a>Creating Our Context Class<\/h3>\nThe context class is an important part of our application that we use to communicate with our database. This class inherits the base EF Core DbContext<\/code> class that contains details for configuring and accessing the database.<\/p>\nLet’s define a Context class named ApplicationContext<\/code>\u00a0 in our Models<\/code> folder:<\/p>\npublic class ApplicationContext(DbContextOptions<ApplicationContext> options) : DbContext(options)\r\n{\r\n public DbSet<Student> Students { get; set; }\r\n\r\n protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n {\r\n modelBuilder.Entity<Student>().HasData(\r\n new Student\r\n {\r\n Id = 1,\r\n FirstName = \"John\",\r\n LastName = \"Doe\",\r\n Address = \"123 Main St\",\r\n },\r\n new Student\r\n {\r\n Id = 2,\r\n FirstName = \"Jane\",\r\n LastName = \"Doe\",\r\n Address = \"456 Oak St\",\r\n }\r\n );\r\n }\r\n}<\/pre>\nIn this ApplicationContext<\/code> class, we first establish a DbSet<Student><\/code> object named Students<\/code> to represent our entity. The DbSet<T><\/code> class provides methods for executing CRUD operations on the corresponding database tables in our database. Then, we override the OnModelCreating()<\/code> method, utilizing the HasData()<\/code> method to add seed data to our Students<\/code> table.<\/p>\nWith that, let\u2019s add our database connection string to the appsettings.json<\/code> file:<\/p>\n{\r\n \"Logging\": {\r\n \"LogLevel\": {\r\n \"Default\": \"Information\",\r\n \"Microsoft.AspNetCore\": \"Warning\"\r\n }\r\n },\r\n \"ConnectionStrings\": {\r\n \"StudentsConnection\": \"server=127.0.0.1;database=CodeMazeStudents;user=root;password=yourpassword;\"\r\n },\r\n \"AllowedHosts\": \"*\"\r\n}<\/pre>\nIn our connection string, we specify 127.0.0.1<\/code> (localhost) as the server. Then, we set the database name as CodeMazeStudents<\/code><\/strong>. If a database with that name exists on our server, we use it. Otherwise, MariaDB will create a new database for us. The password yourpassword<\/code> corresponds to the one we noted during the MariaDB installation.<\/p>\nNow, we will move to the Program.cs<\/code> file and register our ApplicationContext<\/code> class as a service:<\/p>\nbuilder.Services.AddDbContext<ApplicationContext>(options =>\r\n{\r\n var connectionString = builder.Configuration.GetConnectionString(\"StudentsConnection\");\r\n options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));\r\n});<\/pre>\nHere, we define the connection details for our ApplicationContext<\/code>. First, we call the GetConnectionString()<\/code> method, with the \"StudentsConnection\"<\/code> argument, to retrieve the connection string from our appsettings.json<\/code> file.<\/p>\nSubsequently, we configure our context to connect to a MySQL-compatible database by invoking the UseMySql()<\/code> method. Here, we pass in the returned connection string, and the server version is automatically detected using the ServerVersion.AutoDetect()<\/code> method.<\/p>\n<\/a>Adding Migration and Updating the Database<\/h3>\nWe use the EF Core migrations<\/a> feature to create and incrementally update our database schema based on our application’s models. To work with the migrations API, let’s install the Microsoft.EntityFrameworkCore.Tools<\/code> NuGet package:<\/p>\ndotnet add package Microsoft.EntityFrameworkCore.Tools<\/code><\/p>\nLet\u2019s now add our first migration:<\/p>\n
dotnet ef migrations add FirstMigration<\/code><\/p>\nAfter that, let’s create our database and schema:<\/p>\n
dotnet ef database update<\/code><\/p>\nWith everything set, we can now view our database with HeidiSQL:<\/p>\n
<\/a><\/p>\nNow that we have seen what our database looks like, let\u2019s move on to the data access of our application.<\/p>\n
<\/a>Integrating Our MariaDB Database With Our ASP.NET Core App<\/h2>\nNow, let’s define our repository pattern<\/a> logic for performing CRUD operations (Creating, Reading, Updating, and Deleting) on our CodeMazeStudents<\/code> database. After that, we’ll proceed to define the logic for our Web API controller.<\/p>\n<\/a>Creating the Repository Logic<\/h3>\nTo implement a simple repository pattern, first, let\u2019s create a Contracts<\/code> folder in our Models<\/code> folder. Then, in this folder, let\u2019s add an interface:<\/p>\npublic interface IDataRepository\r\n{\r\n Task<IEnumerable<Student>> GetAllAsync();\r\n Task<Student?> GetAsync(int id);\r\n Task AddAsync(Student entity);\r\n Task UpdateAsync(Student entityToUpdate, Student entity);\r\n Task DeleteAsync(Student entity);\r\n}<\/pre>\nThis interface defines various asynchronous CRUD methods for data operations in our application. We will create a concrete class that implements this interface and uses our ApplicationContext<\/code> to perform CRUD operations on our database.<\/p>\nFor this, let\u2019s create a DataRepository<\/code> folder in the Models<\/code> directory. In this folder, let\u2019s define a StudentRepository<\/code> class:<\/p>\npublic class StudentRepository(ApplicationContext context) : IDataRepository\r\n{\r\n readonly ApplicationContext _studentContext = context;\r\n\r\n public async Task AddAsync(Student entity)\r\n {\r\n _studentContext.Students.Add(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<Student?> GetAsync(int id) => await _studentContext.Students.FindAsync(id);\r\n\r\n public async Task UpdateAsync(Student entityToUpdate, Student entity)\r\n {\r\n entityToUpdate.FirstName = entity.FirstName;\r\n entityToUpdate.LastName = entity.LastName;\r\n entityToUpdate.Address = entity.Address;\r\n\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task DeleteAsync(Student entity)\r\n {\r\n _studentContext.Remove(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<IEnumerable<Student>> GetAllAsync()\r\n {\r\n return await _studentContext.Students.ToListAsync<Student>();\r\n }\r\n}<\/pre>\nHere, we add logic for creating, reading, updating, and deleting student data. Additionally, we implement a method to retrieve all students in our database.<\/p>\n
With that, let\u2019s open the Program.cs<\/code> file and register this class and the IDataRepository<\/code> interface as a service:<\/p>\nbuilder.Services.AddScoped<IDataRepository, StudentRepository>();<\/code><\/p>\nNice! Next up, let\u2019s create our Web API controller and add action methods to it.<\/p>\n
<\/a>Adding Our Controller<\/h3>\nControllers enable us to process HTTP requests, validate our models, and send back responses to our users.\u00a0<\/p>\n
So, let’s add a StudentsController<\/code> class to the Controllers<\/code> folder in our project:<\/p>\n[Route(\"api\/students\")]\r\n[ApiController]\r\npublic class StudentsController(IDataRepository dataRepository) : ControllerBase\r\n{\r\n private readonly IDataRepository _dataRepository = dataRepository;\r\n}<\/pre>\nNext, let’s define a GetAllStudents()<\/code> method in this controller:<\/p>\n[HttpGet]\r\npublic async Task<IActionResult> GetAllStudents()\r\n{\r\n var students = await _dataRepository.GetAllAsync();\r\n\r\n return Ok(students);\r\n}<\/pre>\nHere, we call our data repository’s GetAllAsync()<\/code> method to retrieve all the students in our database.<\/p>\nNow, let’s create a GetStudent()<\/code> action method:<\/p>\n[HttpGet(\"{id}\", Name = \"GetStudent\")]\r\npublic async Task<IActionResult> GetStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"Student not found.\");\r\n }\r\n\r\n return Ok(student);\r\n}<\/pre>\nFor this action method, we specify that it is an HttpGet<\/code> method with an \"{id}\"<\/code> template. We set the route name of this method to \"GetStudent\"<\/code>.<\/p>\nThen, we try to retrieve the student from the database by calling the GetAsync()<\/code> method with the provided id. If the student is null, we return a 404 Not Found<\/code> error. Otherwise, we call the Ok()<\/code> method that returns an OkObjectResult<\/code> object and a 200 OK<\/code> status code.<\/p>\nNext, let’s define a PostStudent()<\/code> method:<\/p>\n[HttpPost]\r\npublic async Task<IActionResult> PostStudent([FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n await _dataRepository.AddAsync(student);\r\n\r\n return CreatedAtRoute(\"GetStudent\", new { student.Id }, null);\r\n}<\/pre>\nIf the student that we want to add to our MariaDB database is null, we return a 400 Bad Request<\/code> error. If the student is not null, we add the student to our database and return a 201 Created<\/code> status code.<\/p>\nLet’s now create a PutStudent()<\/code> method in our controller:<\/p>\n[HttpPut(\"{id}\")]\r\npublic async Task<IActionResult> PutStudent(int id, [FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n\r\n var studentToUpdate = await _dataRepository.GetAsync(id);\r\n if (studentToUpdate is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.UpdateAsync(studentToUpdate, student);\r\n\r\n return NoContent();\r\n}<\/pre>\nOnce again, if the incoming student is null, we return a 400 Bad Request<\/code> error. Otherwise, we retrieve the existing student from the database. If we do not find the specified student, we return a 404 Not Found<\/code> response.<\/p>\nThen, if the existing student is not null, we invoke our data repository’s UpdateAsync()<\/code> method, passing in the existing student and the new student data. At the end, we return a 204 No Content<\/code> response.<\/p>\nFinally, let’s define a DeleteStudent()<\/code> action method:<\/p>\n[HttpDelete(\"{id}\")]\r\npublic async Task<IActionResult> DeleteStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.DeleteAsync(student);\r\n\r\n return NoContent();\r\n}<\/pre>\nIn this method, we first retrieve the student from our database. If the student is null, we return a 404 Not Found<\/code> response. Otherwise, we delete the student from our database and return a 204 No Content<\/code> response.<\/p>\nGreat! Our controller is now ready, and we can proceed to test our Web API and its interaction with our MariaDB database.<\/p>\n
<\/a>Testing the Web API With Postman<\/h2>\nNow, let’s test our StudentsController<\/code> endpoints with Postman<\/a>.<\/p>\nFirst, let’s visit our GET<\/code> https:\/\/localhost:7091\/api\/students<\/code> endpoint\u00a0and check the result:<\/p>\n[\r\n {\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n },\r\n {\r\n \"id\": 2,\r\n \"firstName\": \"Jane\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"456 Oak St\"\r\n }\r\n]<\/pre>\nNext, let’s see what our GET<\/code> https:\/\/localhost:7091\/api\/students\/1<\/code> endpoint returns:<\/p>\n{\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n}<\/pre>\nWith that, let’s look at the result of visiting our POST<\/code> endpoint:<\/p>\n<\/a><\/p>\nNow, let’s update our first student’s record:<\/p>\n
<\/a><\/p>\nFinally, let’s delete a student from our database:<\/p>\n
<\/a><\/p>\nThat’s it. As we can see, all our endpoints are working perfectly.<\/p>\n
<\/a>Conclusion<\/h2>\nIn this article, we learned how to work with a MariaDB database in an ASP.NET Core Web API.<\/p>\n
Using EF Core’s code-first approach, we began our database design by defining our database model and ApplicationContext. Then, we created and applied our migrations. We also implemented all the CRUD operations.<\/p>\n
At the end, we used Postman to examine how our Web API interacts with our MariaDB database.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this article, we will discuss how to use a MariaDB database with an ASP.NET Core Web API. First, we will discuss what MariaDB is and how we include it in our application. Then, we will use the Entity Framework Core library to create a mapping between our Web API models and our MariaDB database. […]<\/p>\n","protected":false},"author":52,"featured_media":62187,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[2079],"tags":[898,926,1811,2049],"class_list":["post-104053","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-api","tag-net-core-web-api","tag-asp-net","tag-c","tag-mariadb","et-has-post-format-content","et_post_format-et-post-format-standard"],"yoast_head":"\n
Using MariaDB With ASP.NET Core Web API - Code Maze<\/title>\n<meta name=\"description\" content=\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using MariaDB With ASP.NET Core Web API - Code Maze\" \/>\n<meta property=\"og:description\" content=\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\" \/>\n<meta property=\"og:site_name\" content=\"Code Maze\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-23T11:18:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-31T14:52:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1100\" \/>\n\t<meta property=\"og:image:height\" content=\"620\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Januarius Njoku\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/cjaynjoku\" \/>\n<meta name=\"twitter:site\" content=\"@CodeMazeBlog\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Januarius Njoku\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":[\"Article\",\"BlogPosting\"],\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"},\"author\":{\"name\":\"Januarius Njoku\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18\"},\"headline\":\"Using MariaDB With ASP.NET Core Web API\",\"datePublished\":\"2024-01-23T11:18:03+00:00\",\"dateModified\":\"2024-01-31T14:52:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"},\"wordCount\":1468,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/code-maze.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"keywords\":[\".NET Core Web API\",\"ASP.NET\",\"C#\",\"MariaDb\"],\"articleSection\":[\"Web API\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\",\"url\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\",\"name\":\"Using MariaDB With ASP.NET Core Web API - Code Maze\",\"isPartOf\":{\"@id\":\"https:\/\/code-maze.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"datePublished\":\"2024-01-23T11:18:03+00:00\",\"dateModified\":\"2024-01-31T14:52:31+00:00\",\"description\":\"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.\",\"breadcrumb\":{\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png\",\"width\":1100,\"height\":620,\"caption\":\"ASP.NET Core\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/code-maze.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using MariaDB With ASP.NET Core Web API\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/code-maze.com\/#website\",\"url\":\"https:\/\/code-maze.com\/\",\"name\":\"Code Maze\",\"description\":\"Learn. Code. Succeed.\",\"publisher\":{\"@id\":\"https:\/\/code-maze.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/code-maze.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/code-maze.com\/#organization\",\"name\":\"Code Maze\",\"url\":\"https:\/\/code-maze.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png\",\"width\":3511,\"height\":3510,\"caption\":\"Code Maze\"},\"image\":{\"@id\":\"https:\/\/code-maze.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/CodeMazeBlog\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18\",\"name\":\"Januarius Njoku\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/code-maze.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg\",\"contentUrl\":\"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg\",\"caption\":\"Januarius Njoku\"},\"description\":\"Januarius Njoku is a mechanical engineer who is passionate about using technology to improve the design and performance of mechanical systems. With experience in programming with C# and Python, he is also well-versed in DevOps technologies such as Ansible, Docker, and Puppet. Moreover, having a knack for writing, he has authored several technical articles on C# and .NET technologies. He is always on the lookout for new challenges and opportunities to learn and grow in the field of engineering and technology.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/januariusnjoku\/\",\"https:\/\/x.com\/https:\/\/twitter.com\/cjaynjoku\"],\"url\":\"https:\/\/code-maze.com\/author\/njokujennaro\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using MariaDB With ASP.NET Core Web API - Code Maze","description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","og_locale":"en_US","og_type":"article","og_title":"Using MariaDB With ASP.NET Core Web API - Code Maze","og_description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","og_url":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","og_site_name":"Code Maze","article_published_time":"2024-01-23T11:18:03+00:00","article_modified_time":"2024-01-31T14:52:31+00:00","og_image":[{"width":1100,"height":620,"url":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","type":"image\/png"}],"author":"Januarius Njoku","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/cjaynjoku","twitter_site":"@CodeMazeBlog","twitter_misc":{"Written by":"Januarius Njoku","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":["Article","BlogPosting"],"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#article","isPartOf":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"},"author":{"name":"Januarius Njoku","@id":"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18"},"headline":"Using MariaDB With ASP.NET Core Web API","datePublished":"2024-01-23T11:18:03+00:00","dateModified":"2024-01-31T14:52:31+00:00","mainEntityOfPage":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"},"wordCount":1468,"commentCount":0,"publisher":{"@id":"https:\/\/code-maze.com\/#organization"},"image":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"thumbnailUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","keywords":[".NET Core Web API","ASP.NET","C#","MariaDb"],"articleSection":["Web API"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","url":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/","name":"Using MariaDB With ASP.NET Core Web API - Code Maze","isPartOf":{"@id":"https:\/\/code-maze.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"image":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage"},"thumbnailUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","datePublished":"2024-01-23T11:18:03+00:00","dateModified":"2024-01-31T14:52:31+00:00","description":"In this article, we discuss how to use a MariaDB database with an ASP.NET Core Web API and provide implementation for all the CRUD actions.","breadcrumb":{"@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#primaryimage","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2021\/12\/social-aspnetcore.png","width":1100,"height":620,"caption":"ASP.NET Core"},{"@type":"BreadcrumbList","@id":"https:\/\/code-maze.com\/aspnetcore-using-mariadb-with-web-api\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/code-maze.com\/"},{"@type":"ListItem","position":2,"name":"Using MariaDB With ASP.NET Core Web API"}]},{"@type":"WebSite","@id":"https:\/\/code-maze.com\/#website","url":"https:\/\/code-maze.com\/","name":"Code Maze","description":"Learn. Code. Succeed.","publisher":{"@id":"https:\/\/code-maze.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/code-maze.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/code-maze.com\/#organization","name":"Code Maze","url":"https:\/\/code-maze.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/#\/schema\/logo\/image\/","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2020\/01\/Code-Maze-Only-Logo-Transparent-HRez.png","width":3511,"height":3510,"caption":"Code Maze"},"image":{"@id":"https:\/\/code-maze.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/CodeMazeBlog"]},{"@type":"Person","@id":"https:\/\/code-maze.com\/#\/schema\/person\/c9d04eed48c29b9b10e3a0ec0fa77a18","name":"Januarius Njoku","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/code-maze.com\/#\/schema\/person\/image\/","url":"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg","contentUrl":"https:\/\/code-maze.com\/wp-content\/uploads\/2024\/01\/Januarius-Njoku-150x150.jpeg","caption":"Januarius Njoku"},"description":"Januarius Njoku is a mechanical engineer who is passionate about using technology to improve the design and performance of mechanical systems. With experience in programming with C# and Python, he is also well-versed in DevOps technologies such as Ansible, Docker, and Puppet. Moreover, having a knack for writing, he has authored several technical articles on C# and .NET technologies. He is always on the lookout for new challenges and opportunities to learn and grow in the field of engineering and technology.","sameAs":["https:\/\/www.linkedin.com\/in\/januariusnjoku\/","https:\/\/x.com\/https:\/\/twitter.com\/cjaynjoku"],"url":"https:\/\/code-maze.com\/author\/njokujennaro\/"}]}},"_links":{"self":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/users\/52"}],"replies":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/comments?post=104053"}],"version-history":[{"count":4,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053\/revisions"}],"predecessor-version":[{"id":104063,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/posts\/104053\/revisions\/104063"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/media\/62187"}],"wp:attachment":[{"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/media?parent=104053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/categories?post=104053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code-maze.com\/wp-json\/wp\/v2\/tags?post=104053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
It’s important to mention that the installation package includes the HeidiSQL app, a free GUI client for interacting with the MariaDB server<\/strong>. We can use this app to explore the databases we create.<\/p>\n Next up, let’s create an ASP.NET Core Web API project named To learn how to do this from Visual Studio, please visit our guide on creating and configuring a new ASP.NET Core Web API project<\/a>.<\/p>\n Entity Framework (EF) Core<\/a> is a library that we can utilize to access our database from our web API. When we use the EF Core library, we can choose to build our application using either the Database-First approach<\/a> or the Code-First approach<\/a>.<\/p>\n In the Database-First approach, we design our database first and then use EF Core to generate the tables and the context. In the Code-First approach,\u00a0we create our models and context first, then we use EF Core to create the database schema and possibly add seed data to it. Here, we will use the Code-First approach.<\/p>\n That said, let\u2019s now install the EF Core package from the operating system command line:<\/p>\n As the next step, let’s install the Pomelo.EntityFrameworkCore.MySql<\/a> database provider:<\/p>\n This NuGet package is the EF Core database provider for MariaDB and other MySQL-compatible databases.<\/p>\n We utilize the version Now, let’s create a The context class is an important part of our application that we use to communicate with our database. This class inherits the base EF Core Let’s define a Context class named In this With that, let\u2019s add our database connection string to the In our connection string, we specify Now, we will move to the Here, we define the connection details for our Subsequently, we configure our context to connect to a MySQL-compatible database by invoking the We use the EF Core migrations<\/a> feature to create and incrementally update our database schema based on our application’s models. To work with the migrations API, let’s install the Let\u2019s now add our first migration:<\/p>\n After that, let’s create our database and schema:<\/p>\n With everything set, we can now view our database with HeidiSQL:<\/p>\n Now that we have seen what our database looks like, let\u2019s move on to the data access of our application.<\/p>\n Now, let’s define our repository pattern<\/a> logic for performing CRUD operations (Creating, Reading, Updating, and Deleting) on our To implement a simple repository pattern, first, let\u2019s create a This interface defines various asynchronous CRUD methods for data operations in our application. We will create a concrete class that implements this interface and uses our For this, let\u2019s create a Here, we add logic for creating, reading, updating, and deleting student data. Additionally, we implement a method to retrieve all students in our database.<\/p>\n With that, let\u2019s open the Nice! Next up, let\u2019s create our Web API controller and add action methods to it.<\/p>\n Controllers enable us to process HTTP requests, validate our models, and send back responses to our users.\u00a0<\/p>\n So, let’s add a Next, let’s define a Here, we call our data repository’s Now, let’s create a For this action method, we specify that it is an Then, we try to retrieve the student from the database by calling the Next, let’s define a If the student that we want to add to our MariaDB database is null, we return a Let’s now create a Once again, if the incoming student is null, we return a Then, if the existing student is not null, we invoke our data repository’s Finally, let’s define a In this method, we first retrieve the student from our database. If the student is null, we return a Great! Our controller is now ready, and we can proceed to test our Web API and its interaction with our MariaDB database.<\/p>\n Now, let’s test our First, let’s visit our Next, let’s see what our With that, let’s look at the result of visiting our Now, let’s update our first student’s record:<\/p>\n Finally, let’s delete a student from our database:<\/p>\n That’s it. As we can see, all our endpoints are working perfectly.<\/p>\n In this article, we learned how to work with a MariaDB database in an ASP.NET Core Web API.<\/p>\n Using EF Core’s code-first approach, we began our database design by defining our database model and ApplicationContext. Then, we created and applied our migrations. We also implemented all the CRUD operations.<\/p>\n At the end, we used Postman to examine how our Web API interacts with our MariaDB database.<\/p>\n","protected":false},"excerpt":{"rendered":" In this article, we will discuss how to use a MariaDB database with an ASP.NET Core Web API. First, we will discuss what MariaDB is and how we include it in our application. Then, we will use the Entity Framework Core library to create a mapping between our Web API models and our MariaDB database. […]<\/p>\n","protected":false},"author":52,"featured_media":62187,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[2079],"tags":[898,926,1811,2049],"class_list":["post-104053","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-api","tag-net-core-web-api","tag-asp-net","tag-c","tag-mariadb","et-has-post-format-content","et_post_format-et-post-format-standard"],"yoast_head":"\nUsingMariaDBWithASPNETCoreWebAPI<\/code>:<\/p>\ndotnet new webapi --name UsingMariaDBWithASPNETCoreWebAPI<\/code><\/p>\n<\/a>Setting Up the Entity Framework Core Library to Connect MariaDB with ASP.NET Core APP<\/h3>\n
dotnet add package Microsoft.EntityFrameworkCore<\/code><\/p>\ndotnet add package Pomelo.EntityFrameworkCore.MySql -v 8.0.0-beta.2<\/code><\/p>\n-v 8.0.0-beta.2<\/code> modifier to specify the version of the provider. Using lower versions of this provider with version 8.0.0 of the Microsoft.EntityFrameworkCore.Tools<\/code> package will cause an exception when we want to add our migrations.<\/p>\n<\/a>Creating the Model<\/h3>\n
Models<\/code> folder in our project’s base directory. In this folder, let’s define our model:<\/p>\npublic class Student\r\n{\r\n public int Id { get; set; }\r\n public string? FirstName { get; set; }\r\n public string? LastName { get; set; }\r\n public string? Address { get; set; }\r\n}<\/pre>\n<\/a>Creating Our Context Class<\/h3>\n
DbContext<\/code> class that contains details for configuring and accessing the database.<\/p>\nApplicationContext<\/code>\u00a0 in our Models<\/code> folder:<\/p>\npublic class ApplicationContext(DbContextOptions<ApplicationContext> options) : DbContext(options)\r\n{\r\n public DbSet<Student> Students { get; set; }\r\n\r\n protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n {\r\n modelBuilder.Entity<Student>().HasData(\r\n new Student\r\n {\r\n Id = 1,\r\n FirstName = \"John\",\r\n LastName = \"Doe\",\r\n Address = \"123 Main St\",\r\n },\r\n new Student\r\n {\r\n Id = 2,\r\n FirstName = \"Jane\",\r\n LastName = \"Doe\",\r\n Address = \"456 Oak St\",\r\n }\r\n );\r\n }\r\n}<\/pre>\nApplicationContext<\/code> class, we first establish a DbSet<Student><\/code> object named Students<\/code> to represent our entity. The DbSet<T><\/code> class provides methods for executing CRUD operations on the corresponding database tables in our database. Then, we override the OnModelCreating()<\/code> method, utilizing the HasData()<\/code> method to add seed data to our Students<\/code> table.<\/p>\nappsettings.json<\/code> file:<\/p>\n{\r\n \"Logging\": {\r\n \"LogLevel\": {\r\n \"Default\": \"Information\",\r\n \"Microsoft.AspNetCore\": \"Warning\"\r\n }\r\n },\r\n \"ConnectionStrings\": {\r\n \"StudentsConnection\": \"server=127.0.0.1;database=CodeMazeStudents;user=root;password=yourpassword;\"\r\n },\r\n \"AllowedHosts\": \"*\"\r\n}<\/pre>\n127.0.0.1<\/code> (localhost) as the server. Then, we set the database name as CodeMazeStudents<\/code><\/strong>. If a database with that name exists on our server, we use it. Otherwise, MariaDB will create a new database for us. The password yourpassword<\/code> corresponds to the one we noted during the MariaDB installation.<\/p>\nProgram.cs<\/code> file and register our ApplicationContext<\/code> class as a service:<\/p>\nbuilder.Services.AddDbContext<ApplicationContext>(options =>\r\n{\r\n var connectionString = builder.Configuration.GetConnectionString(\"StudentsConnection\");\r\n options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));\r\n});<\/pre>\nApplicationContext<\/code>. First, we call the GetConnectionString()<\/code> method, with the \"StudentsConnection\"<\/code> argument, to retrieve the connection string from our appsettings.json<\/code> file.<\/p>\nUseMySql()<\/code> method. Here, we pass in the returned connection string, and the server version is automatically detected using the ServerVersion.AutoDetect()<\/code> method.<\/p>\n<\/a>Adding Migration and Updating the Database<\/h3>\n
Microsoft.EntityFrameworkCore.Tools<\/code> NuGet package:<\/p>\ndotnet add package Microsoft.EntityFrameworkCore.Tools<\/code><\/p>\ndotnet ef migrations add FirstMigration<\/code><\/p>\ndotnet ef database update<\/code><\/p>\n<\/a><\/p>\n<\/a>Integrating Our MariaDB Database With Our ASP.NET Core App<\/h2>\n
CodeMazeStudents<\/code> database. After that, we’ll proceed to define the logic for our Web API controller.<\/p>\n<\/a>Creating the Repository Logic<\/h3>\n
Contracts<\/code> folder in our Models<\/code> folder. Then, in this folder, let\u2019s add an interface:<\/p>\npublic interface IDataRepository\r\n{\r\n Task<IEnumerable<Student>> GetAllAsync();\r\n Task<Student?> GetAsync(int id);\r\n Task AddAsync(Student entity);\r\n Task UpdateAsync(Student entityToUpdate, Student entity);\r\n Task DeleteAsync(Student entity);\r\n}<\/pre>\nApplicationContext<\/code> to perform CRUD operations on our database.<\/p>\nDataRepository<\/code> folder in the Models<\/code> directory. In this folder, let\u2019s define a StudentRepository<\/code> class:<\/p>\npublic class StudentRepository(ApplicationContext context) : IDataRepository\r\n{\r\n readonly ApplicationContext _studentContext = context;\r\n\r\n public async Task AddAsync(Student entity)\r\n {\r\n _studentContext.Students.Add(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<Student?> GetAsync(int id) => await _studentContext.Students.FindAsync(id);\r\n\r\n public async Task UpdateAsync(Student entityToUpdate, Student entity)\r\n {\r\n entityToUpdate.FirstName = entity.FirstName;\r\n entityToUpdate.LastName = entity.LastName;\r\n entityToUpdate.Address = entity.Address;\r\n\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task DeleteAsync(Student entity)\r\n {\r\n _studentContext.Remove(entity);\r\n await _studentContext.SaveChangesAsync();\r\n }\r\n\r\n public async Task<IEnumerable<Student>> GetAllAsync()\r\n {\r\n return await _studentContext.Students.ToListAsync<Student>();\r\n }\r\n}<\/pre>\nProgram.cs<\/code> file and register this class and the IDataRepository<\/code> interface as a service:<\/p>\nbuilder.Services.AddScoped<IDataRepository, StudentRepository>();<\/code><\/p>\n<\/a>Adding Our Controller<\/h3>\n
StudentsController<\/code> class to the Controllers<\/code> folder in our project:<\/p>\n[Route(\"api\/students\")]\r\n[ApiController]\r\npublic class StudentsController(IDataRepository dataRepository) : ControllerBase\r\n{\r\n private readonly IDataRepository _dataRepository = dataRepository;\r\n}<\/pre>\nGetAllStudents()<\/code> method in this controller:<\/p>\n[HttpGet]\r\npublic async Task<IActionResult> GetAllStudents()\r\n{\r\n var students = await _dataRepository.GetAllAsync();\r\n\r\n return Ok(students);\r\n}<\/pre>\nGetAllAsync()<\/code> method to retrieve all the students in our database.<\/p>\nGetStudent()<\/code> action method:<\/p>\n[HttpGet(\"{id}\", Name = \"GetStudent\")]\r\npublic async Task<IActionResult> GetStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"Student not found.\");\r\n }\r\n\r\n return Ok(student);\r\n}<\/pre>\nHttpGet<\/code> method with an \"{id}\"<\/code> template. We set the route name of this method to \"GetStudent\"<\/code>.<\/p>\nGetAsync()<\/code> method with the provided id. If the student is null, we return a 404 Not Found<\/code> error. Otherwise, we call the Ok()<\/code> method that returns an OkObjectResult<\/code> object and a 200 OK<\/code> status code.<\/p>\nPostStudent()<\/code> method:<\/p>\n[HttpPost]\r\npublic async Task<IActionResult> PostStudent([FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n await _dataRepository.AddAsync(student);\r\n\r\n return CreatedAtRoute(\"GetStudent\", new { student.Id }, null);\r\n}<\/pre>\n400 Bad Request<\/code> error. If the student is not null, we add the student to our database and return a 201 Created<\/code> status code.<\/p>\nPutStudent()<\/code> method in our controller:<\/p>\n[HttpPut(\"{id}\")]\r\npublic async Task<IActionResult> PutStudent(int id, [FromBody] Student student)\r\n{\r\n if (student is null)\r\n {\r\n return BadRequest(\"Student is null.\");\r\n }\r\n\r\n var studentToUpdate = await _dataRepository.GetAsync(id);\r\n if (studentToUpdate is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.UpdateAsync(studentToUpdate, student);\r\n\r\n return NoContent();\r\n}<\/pre>\n400 Bad Request<\/code> error. Otherwise, we retrieve the existing student from the database. If we do not find the specified student, we return a 404 Not Found<\/code> response.<\/p>\nUpdateAsync()<\/code> method, passing in the existing student and the new student data. At the end, we return a 204 No Content<\/code> response.<\/p>\nDeleteStudent()<\/code> action method:<\/p>\n[HttpDelete(\"{id}\")]\r\npublic async Task<IActionResult> DeleteStudent(int id)\r\n{\r\n var student = await _dataRepository.GetAsync(id);\r\n if (student is null)\r\n {\r\n return NotFound(\"The Student record couldn't be found.\");\r\n }\r\n await _dataRepository.DeleteAsync(student);\r\n\r\n return NoContent();\r\n}<\/pre>\n404 Not Found<\/code> response. Otherwise, we delete the student from our database and return a 204 No Content<\/code> response.<\/p>\n<\/a>Testing the Web API With Postman<\/h2>\n
StudentsController<\/code> endpoints with Postman<\/a>.<\/p>\nGET<\/code> https:\/\/localhost:7091\/api\/students<\/code> endpoint\u00a0and check the result:<\/p>\n[\r\n {\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n },\r\n {\r\n \"id\": 2,\r\n \"firstName\": \"Jane\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"456 Oak St\"\r\n }\r\n]<\/pre>\nGET<\/code> https:\/\/localhost:7091\/api\/students\/1<\/code> endpoint returns:<\/p>\n{\r\n \"id\": 1,\r\n \"firstName\": \"John\",\r\n \"lastName\": \"Doe\",\r\n \"address\": \"123 Main St\"\r\n}<\/pre>\nPOST<\/code> endpoint:<\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a>Conclusion<\/h2>\n