netcore入门案例：netcore api连接mysql的完整记事本接口示例

以下是一个使用 .NET Core API 连接 MySQL 数据库实现简单记事本功能的完整示例，包含创建、读取、更新和删除（CRUD）操作。

1. 创建 .NET Core Web API 项目

首先，打开命令行工具，使用以下命令创建一个新的 .NET Core Web API 项目：

dotnet new webapi -n NotePadAPI
cd NotePadAPI

2. 安装 MySQL 数据库驱动

在项目目录下，使用以下命令安装 MySQL 数据库驱动：

dotnet add package MySqlConnector

3. 配置数据库连接字符串

打开 appsettings.json 文件，添加 MySQL 数据库连接字符串：

{"ConnectionStrings": {"DefaultConnection": "Server=localhost;Database=notepad;Uid=your_username;Pwd=your_password;"},"Logging": {"LogLevel": {"Default": "Information","Microsoft.AspNetCore": "Warning"}},"AllowedHosts": "*"
}

请将 your_username 和 your_password 替换为你的 MySQL 用户名和密码。

4. 创建数据库和表

在 MySQL 中创建一个名为 notepad 的数据库，并创建一个名为 notes 的表：

CREATE DATABASE notepad;USE notepad;CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255) NOT NULL,content TEXT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5. 创建数据模型

在 Models 文件夹下创建 Note.cs 文件：

using System;namespace NotePadAPI.Models
{public class Note{public int Id { get; set; }public string Title { get; set; }public string Content { get; set; }public DateTime CreatedAt { get; set; }}
}

6. 创建数据库上下文

在 Data 文件夹下创建 NoteContext.cs 文件：

using Microsoft.Data.SqlClient;
using MySqlConnector;
using NotePadAPI.Models;
using System.Collections.Generic;
using System.Data;namespace NotePadAPI.Data
{public class NoteContext{private readonly string _connectionString;public NoteContext(string connectionString){_connectionString = connectionString;}public List<Note> GetAllNotes(){var notes = new List<Note>();using (var connection = new MySqlConnection(_connectionString)){var query = "SELECT * FROM notes";var command = new MySqlCommand(query, connection);connection.Open();var reader = command.ExecuteReader();while (reader.Read()){notes.Add(new Note{Id = reader.GetInt32("id"),Title = reader.GetString("title"),Content = reader.GetString("content"),CreatedAt = reader.GetDateTime("created_at")});}reader.Close();}return notes;}public Note GetNoteById(int id){using (var connection = new MySqlConnection(_connectionString)){var query = "SELECT * FROM notes WHERE id = @id";var command = new MySqlCommand(query, connection);command.Parameters.AddWithValue("@id", id);connection.Open();var reader = command.ExecuteReader();if (reader.Read()){return new Note{Id = reader.GetInt32("id"),Title = reader.GetString("title"),Content = reader.GetString("content"),CreatedAt = reader.GetDateTime("created_at")};}reader.Close();}return null;}public void CreateNote(Note note){using (var connection = new MySqlConnection(_connectionString)){var query = "INSERT INTO notes (title, content) VALUES (@title, @content)";var command = new MySqlCommand(query, connection);command.Parameters.AddWithValue("@title", note.Title);command.Parameters.AddWithValue("@content", note.Content);connection.Open();command.ExecuteNonQuery();}}public void UpdateNote(Note note){using (var connection = new MySqlConnection(_connectionString)){var query = "UPDATE notes SET title = @title, content = @content WHERE id = @id";var command = new MySqlCommand(query, connection);command.Parameters.AddWithValue("@title", note.Title);command.Parameters.AddWithValue("@content", note.Content);command.Parameters.AddWithValue("@id", note.Id);connection.Open();command.ExecuteNonQuery();}}public void DeleteNote(int id){using (var connection = new MySqlConnection(_connectionString)){var query = "DELETE FROM notes WHERE id = @id";var command = new MySqlCommand(query, connection);command.Parameters.AddWithValue("@id", id);connection.Open();command.ExecuteNonQuery();}}}
}

7. 创建控制器

在 Controllers 文件夹下创建 NotesController.cs 文件：

using Microsoft.AspNetCore.Mvc;
using NotePadAPI.Data;
using NotePadAPI.Models;
using System.Collections.Generic;namespace NotePadAPI.Controllers
{[ApiController][Route("api/[controller]")]public class NotesController : ControllerBase{private readonly NoteContext _context;public NotesController(NoteContext context){_context = context;}[HttpGet]public ActionResult<IEnumerable<Note>> GetAllNotes(){var notes = _context.GetAllNotes();return Ok(notes);}[HttpGet("{id}")]public ActionResult<Note> GetNoteById(int id){var note = _context.GetNoteById(id);if (note == null){return NotFound();}return Ok(note);}[HttpPost]public ActionResult<Note> CreateNote(Note note){_context.CreateNote(note);return CreatedAtAction(nameof(GetNoteById), new { id = note.Id }, note);}[HttpPut("{id}")]public IActionResult UpdateNote(int id, Note note){if (id != note.Id){return BadRequest();}_context.UpdateNote(note);return NoContent();}[HttpDelete("{id}")]public IActionResult DeleteNote(int id){_context.DeleteNote(id);return NoContent();}}
}

8. 配置服务

打开 Program.cs 文件，配置数据库上下文：

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using NotePadAPI.Data;var builder = WebApplication.CreateBuilder(args);// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();// Configure database context
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddSingleton(new NoteContext(connectionString));var app = builder.Build();// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{app.UseSwagger();app.UseSwaggerUI();
}app.UseHttpsRedirection();app.UseAuthorization();app.MapControllers();app.Run();