怎样操作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。