怎样操作ASP.NET数据库增删改查？| ASP.NET数据库增删改查教程

在ASP.NET中进行数据库的增删改查（CRUD）操作，主要有两种主流方式：ADO.NET（原始数据库访问）和Entity Framework（ORM框架）,以下是详细指南：

ADO.NET 方式（原生SQL操作）

配置数据库连接

在Web.config中添加连接字符串：

<connectionStrings>
  <add name="MyDb" 
       connectionString="Server=.;Database=TestDB;User Id=sa;Password=123456;" 
       providerName="System.Data.SqlClient"/>
</connectionStrings>

创建实体类

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

CRUD操作示例

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class UserRepository
{
    private string connectionString = ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString;
    // 查询所有用户
    public List<User> GetAllUsers()
    {
        List<User> users = new List<User>();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string sql = "SELECT Id, Name, Email FROM Users";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                users.Add(new User()
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Email = reader["Email"].ToString()
                });
            }
        }
        return users;
    }
    // 新增用户
    public void AddUser(User user)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Name", user.Name);
            cmd.Parameters.AddWithValue("@Email", user.Email);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    // 更新用户
    public void UpdateUser(User user)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string sql = "UPDATE Users SET Name=@Name, Email=@Email WHERE Id=@Id";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Id", user.Id);
            cmd.Parameters.AddWithValue("@Name", user.Name);
            cmd.Parameters.AddWithValue("@Email", user.Email);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    // 删除用户
    public void DeleteUser(int id)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string sql = "DELETE FROM Users WHERE Id=@Id";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Id", id);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Entity Framework Core 方式（推荐）

安装NuGet包

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer

创建DbContext

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    public DbSet<User> Users { get; set; }
}

配置依赖注入（Startup.cs）

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("MyDb")));
}

CRUD操作示例

public class UserService
{
    private readonly AppDbContext _context;
    public UserService(AppDbContext context)
    {
        _context = context; // 依赖注入DbContext
    }
    // 查询所有用户
    public List<User> GetAllUsers() => _context.Users.ToList();
    // 新增用户
    public void AddUser(User user)
    {
        _context.Users.Add(user);
        _context.SaveChanges();
    }
    // 更新用户
    public void UpdateUser(User user)
    {
        _context.Users.Update(user);
        _context.SaveChanges();
    }
    // 删除用户
    public void DeleteUser(int id)
    {
        var user = _context.Users.Find(id);
        if (user != null)
        {
            _context.Users.Remove(user);
            _context.SaveChanges();
        }
    }
}

在Controller中使用（ASP.NET Core MVC示例）

public class UserController : Controller
{
    private readonly UserService _userService;
    public UserController(UserService userService)
    {
        _userService = userService;
    }
    // GET: /User
    public IActionResult Index()
    {
        var users = _userService.GetAllUsers();
        return View(users);
    }
    // POST: /User/Create
    [HttpPost]
    public IActionResult Create(User user)
    {
        if (ModelState.IsValid)
        {
            _userService.AddUser(user);
            return RedirectToAction("Index");
        }
        return View(user);
    }
    // POST: /User/Delete/5
    [HttpPost]
    public IActionResult Delete(int id)
    {
        _userService.DeleteUser(id);
        return RedirectToAction("Index");
    }
}

关键注意事项

1. 参数化查询：ADO.NET中必须使用Parameters防止SQL注入
2. 连接管理：使用using确保连接及时释放
3. 异步操作：EF Core支持async/await（如 ToListAsync()）
4. 事务处理：

   using (var transaction = _context.Database.BeginTransaction())
   {
     try {
       // 多个操作
       transaction.Commit();
     } catch {
       transaction.Rollback();
     }
   }

两种方式对比

根据项目需求选择合适的方式：追求开发效率选EF Core，追求极致性能或需复杂SQL优化选ADO.NET。