写 C# 连 SQL Server 做增删这个事我第一次认真琢磨是在一个上位机项目里。当时每天要往数据库里写几千条设备参数还要在界面上删除历史记录表面看就是INSERT和DELETE两句话可真正跑到生产环境才发现连接串不对、外键冲突、批量插入超时、并发死锁每一个坑都能让你加班到半夜。后来我慢慢总结出一套固定的套路代码写得越来越稳。这篇就把 C# 联合 SQL Server 做增加和删除的完整思路、实操代码、踩坑经验一次说透适合刚开始碰 C# 数据库编程的新手也适合做上位机、进销存、中小型管理系统时想少走弯路的同行。1. 整体思路与方案选型1.1 增删操作的真实复杂度很多人觉得增删是数据库操作里最简单的两个动作毕竟 SQL 语法就那么几行。但放到真实业务里INSERT和DELETE要考虑的东西远不止执行一句 SQL。比如新增一条用户记录你得先想用户名是不是重复年龄字段是不是合法范围插入失败以后要不要记录日志删除一条订单得先判断订单状态能不能删有没有子表引用删完之后是彻底没掉还是留着作审计。我在实际项目里吃过亏有一回直接对着一张明细表执行 DELETE没有先检查主表状态结果用户把已结算的订单明细删了对账的时候数据怎么都对不上。从那以后我给自己定了一条规矩增删操作不是简单写 SQL而是要把校验、事务、约束、日志、权限这几个环节全部过一遍。把问题前置到设计阶段后面写代码就只是把方案翻译成 C# 而已。1.2 数据访问层选型ADO.NET、Dapper 还是 EF CoreC# 里连 SQL Server 做增删主流有三条路原生 ADO.NET、轻量 ORM Dapper、全家桶 EF Core。我自己的选型逻辑很直接看你对 SQL 的控制粒度要求和项目复杂度。ADO.NET 是底层的SqlConnection、SqlCommand这一套优点是不依赖第三方库执行计划可控性能上限高缺点是样板代码多表结构一变SQL 字符串和参数要手动跟着改。Dapper 是在 ADO.NET 之上封了一层可以继续手写 SQL但帮你简化了连接、映射这些重复工作非常适合做上位机这类希望代码干净又不想被复杂框架绑住的项目。EF Core 则适合业务模型复杂、团队不想手写 SQL 的情况但增删性能调优和 SQL 排查的难度都会上升一些。方案上手难度SQL 可控性性能适用场景ADO.NET中等完全可控高需要精细控制、底层驱动类项目Dapper低可控高上位机、工具类系统、中小型项目EF Core中等偏上弱一些中业务模型复杂、快速迭代的项目如果是从零开始做一个内部管理系统我一般优先选 Dapper因为写增删 SQL 很方便出问题也好排查。这篇文章里的核心代码还是用 ADO.NET 来演示因为它是所有方案的地基把参数化和事务这些基本功练牢了换到任何框架都不会慌。1.3 我固定的三步设计套路不管增还是删我动手写代码前都会过三关。第一关是把表结构和约束梳理清楚比如主键是自增列还是业务编号哪些字段有唯一索引哪些表有外键引用这一步决定了 SQL 怎么写。第二关是决定用物理操作还是逻辑操作删除尤其要提前判断不能到写代码时才开始纠结。第三关是确认这条数据变更要不要和其他表一起完成只要涉及多表写入就直接计划好事务边界。三步走看起来多花几分钟但能把后面 80% 的坑提前排掉。代码实现反而变成最简单的环节就是连接、参数、执行、用using释放资源再包一层异常处理罢了。2. 环境准备与数据库表设计2.1 先把 SQL Server 的环境理顺很多 C# 连不上 SQL Server 的问题根本不在代码而是环境没准备好。我第一次装完 SQL Server 之后代码里怎么连都报“网络相关或特定实例的错误”折腾半天发现是 SQL Server 服务没启动。所以开始写增删代码之前先打开 SQL Server 配置管理器确认这几点SQL Server 服务处于“正在运行”状态需要远程连接时SQL Server 网络配置里的TCP/IP协议已启用实例名要写对默认实例用.或localhost命名实例用主机名\\实例名如果是远程数据库Windows 防火墙需要放行 1433 端口或者用你实际设置的端口号。检查命令可以直接打开“运行”输入services.msc看服务状态或者用命令行sqlcmd -S . -E测试本机连接。环境通了再用 C# 连心里就有底了不然你永远分不清错误到底是网络层、协议层还是账号密码的问题。2.2 建一张示例表字段设计决定增删体验我演示用的表尽量贴近真实场景包含自增主键、业务字段、逻辑删除标记和创建时间。建表脚本如下CREATE TABLE dbo.UserInfo ( Id INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, Age INT NULL, IsDeleted BIT NOT NULL DEFAULT 0, CreateTime DATETIME NOT NULL DEFAULT GETDATE() ); CREATE UNIQUE INDEX UX_UserInfo_UserName ON dbo.UserInfo(UserName);这张表的设计有讲究。Id用自增列做物理主键方便 C# 里通过SCOPE_IDENTITY()拿到新插入记录的 Id这对后续日志、关联子表都很有用。UserName加唯一索引保证业务上不允许重复用户名但这样设计有个注意点如果你打算用逻辑删除把IsDeleted置 1来保留历史数据唯一索引会让“删过的用户名再也无法重新添加”这时候要么允许UserName带上业务后缀比如加了时间戳要么把唯一索引改成包含IsDeleted的过滤索引这个要根据业务权衡。IsDeleted这个字段不是随便加的。很多系统做了删除功能过几个月又要查历史数据如果没有逻辑删除标记神仙都找不回来。CreateTime给默认值GETDATE()C# 代码插入的时候就少写一个参数也避免不同机器时间不一致的问题。2.3 连接字符串三要素与常见坑C# 连接 SQL Server 全靠连接字符串我把它概括成三要素连哪里、连哪个库、怎么登录。代码里最常用的两种写法// 本机默认实例 Windows 身份验证 Server.;DatabaseDemoDb;Integrated SecurityTrue;TrustServerCertificateTrue; // 远程服务器 SQL Server 账号登录 Server192.168.1.10;DatabaseDemoDb;User Idsa;Password你的密码;TrustServerCertificateTrue;这里有几个容易踩的坑。Server.是小圆点表示本机默认实例好多人打成逗号或者写错机器名如果是默认端口 1433 也可以显式写成Server192.168.1.10,1433。Integrated SecurityTrue用的是当前 Windows 登录凭据适合本机开发但放到服务器上要搞清楚运行 IIS 或 Windows 服务的进程账号是什么不然经常出现“本地能连、部署后不能连”的怪问题。TrustServerCertificateTrue是为了方便本地开发时跳过证书验证生产环境要不要开得看你们数据库证书是怎么配的别无脑照抄。还有一个特别容易被忽略的坑连接字符串里如果有特殊字符比如密码带;或引号需要转义否则 .NET 解析连接串会出错。遇到这种情况我通常建议直接改用SqlConnectionStringBuilder来构造连接串让类库去处理转义比手写字符串安全得多。3. 添加操作从一条 Insert 到批量提交3.1 用参数化 SqlCommand 完成第一条插入添加操作最标准的写法是SqlConnection打开连接SqlCommand执行ExecuteNonQuery。下面这段代码是我做了很多次增删之后最终固定的模板using System; using System.Data.SqlClient; string connStr Server.;DatabaseDemoDb;Integrated SecurityTrue;TrustServerCertificateTrue;; string sql INSERT INTO dbo.UserInfo (UserName, Age) VALUES (UserName, Age);; using (SqlConnection conn new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(UserName, userName); cmd.Parameters.AddWithValue(Age, age); int rows cmd.ExecuteNonQuery(); Console.WriteLine($受影响行数{rows}); } }注意我用的是using包裹连接和命令。很多人写程序时只Open()忘记Close()短时间看不出问题但连接池很快会被耗尽然后就会出现“超时时间已到”的错误。using的意思是无论成功还是异常SqlConnection和SqlCommand都会被释放这是我在教训里换来的习惯。参数化这一点必须说清楚。直接用字符串拼接INSERT INTO ... VALUES ( userName )是非常危险的会导致 SQL 注入还会让userName带单引号的时候直接报语法错误。用UserName参数化以后数据库收到的永远是参数值不会把输入内容当 SQL 执行。除此之外SQL Server 还能缓存参数化语句的执行计划同样的插入语句反复执行时性能也更好。3.2 插入后拿回自增主键很多时候插入完还要知道新数据的 Id用来写日志、关联子表或者给前端返回。拿自增主键有讲究不能用SELECT IDENTITY因为它返回的是当前会话最后一次生成的所有标识值如果表上有触发器往别的表插了数据你拿到的可能就不是目标表的 Id。正确用法是SCOPE_IDENTITY()INSERT INTO dbo.UserInfo (UserName, Age) VALUES (UserName, Age); SELECT SCOPE_IDENTITY();C# 这边把“执行并返回单值”改成ExecuteScalarstring sql INSERT INTO dbo.UserInfo (UserName, Age) VALUES (UserName, Age); SELECT SCOPE_IDENTITY();; using (SqlConnection conn new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(UserName, userName); cmd.Parameters.AddWithValue(Age, age); object result cmd.ExecuteScalar(); int newId Convert.ToInt32(result); Console.WriteLine($新增 Id{newId}); } }这里有个细节ExecuteScalar把整批次 SQL 执行完返回第一行第一列的值正好就是SCOPE_IDENTITY()的结果。用Convert.ToInt32转型比直接(int)稳妥因为数据库返回的可能是decimal类型。不管在 ADO.NET 还是 Dapper 里只要你需要拿到刚插入记录的主键把INSERT和SELECT SCOPE_IDENTITY()放进同一个SqlCommand、同一个连接里执行就行。3.3 批量添加一条条 Insert 踩过的性能坑我最早做批量添加时写了个foreach循环每一条数据执行一次INSERT数据量小没问题但有一次要导入几万条记录跑了将近二十分钟客户端差点被用户投诉。问题就出在循环里频繁建立连接、每次单独提交、网络往返开销巨大。批量添加有三个层次的选择第一层用SqlBulkCopy。它是原生最直接的批量写入组件可以把DataTable整批塞给 SQL Server速度非常快。示例DataTable dt new DataTable(); dt.Columns.Add(UserName, typeof(string)); dt.Columns.Add(Age, typeof(int)); // dt.Rows.Add(...) 填充数据 using (SqlConnection conn new SqlConnection(connStr)) { conn.Open(); using (SqlBulkCopy bulk new SqlBulkCopy(conn)) { bulk.DestinationTableName dbo.UserInfo; bulk.ColumnMappings.Add(UserName, UserName); bulk.ColumnMappings.Add(Age, Age); bulk.WriteToServer(dt); } }SqlBulkCopy的性能优势非常明显但它也有脾气目标表如果有触发器、索引维护或者列结构变动写进去的行为可能和普通INSERT不一样所以用它之前必须把表结构、默认值、约束都确认清楚。如果你的数据量大到要用它建议先在一个测试表上试跑确认所有约束都正确触发。第二层如果有大量数据要做复杂校验、不能直接走列映射可以先插入临时表再用一条INSERT ... SELECT合并到目标表这样既能做转换又能减少日志和锁的冲突。第三层如果单次操作只有几十条也可以把多个VALUES拼成一个大的INSERT语句但要注意参数太多会触发 SQL Server 参数数量限制而且动态拼 SQL 本来就容易被注入所以我不推荐新手一上来就玩这个。3.4 事务保护主表和明细表必须同生共死添加操作只要涉及多张表就必须考虑事务。最典型的例子是新增订单往订单主表插一条再往订单明细表插多条。如果主表插入成功、明细表插入失败而没有事务数据库里就会留下一张没有明细的订单这种脏数据对账根本对不上。C# 里用SqlTransaction实现事务关键是把同一个事务对象传给每个SqlCommandusing (SqlConnection conn new SqlConnection(connStr)) { conn.Open(); SqlTransaction tx conn.BeginTransaction(); try { string sqlOrder INSERT INTO dbo.OrderInfo (OrderNo, UserId, TotalAmount) VALUES (OrderNo, UserId, TotalAmount); SELECT SCOPE_IDENTITY();; int orderId; using (SqlCommand cmd new SqlCommand(sqlOrder, conn, tx)) { // 添加参数 orderId Convert.ToInt32(cmd.ExecuteScalar()); } string sqlDetail INSERT INTO dbo.OrderDetail (OrderId, ProductId, Quantity) VALUES (OrderId, ProductId, Quantity);; using (SqlCommand cmd new SqlCommand(sqlDetail, conn, tx)) { // 循环添加明细并设置 cmd.Parameters.Clear() 或重建命令 } tx.Commit(); } catch { tx.Rollback(); throw; } }事务的本质是“要么全部成功要么全部回滚”但不要因此就把整个系统的操作都塞进一个大事务。事务期间数据库会持有锁事务越长锁的时间越久并发能力越差。我给自己定的原则是事务里只做数据库操作绝不放 HTTP 请求、文件读写或者长时间计算。4. 删除操作不只是执行一条 DELETE4.1 物理删除还是逻辑删除删除操作第一个决定是选物理删除还是逻辑删除。物理删除就是DELETE FROM ...直接把数据行从表里抹掉逻辑删除则是用一条UPDATE把IsDeleted改成 1数据还在只是业务上认为它“已删除”。角度物理删除逻辑删除数据可恢复基本不可恢复可随时恢复查询过滤不需要额外条件每次都要带WHERE IsDeleted0外键影响容易触发外键冲突不真删数据外键不会直接炸存储膨胀不会积累会积累需要定期归档适用场景日志、临时表、缓存类数据业务主数据、需要审计/历史追溯的数据我的习惯很简单业务数据能做逻辑删除就尽量做逻辑删除财务、订单、用户这种需要追溯历史的尤其重要性能数据、会话记录、临时导入的中间表可以直接物理删除。逻辑删除也有成本最明显的是所有查询都要记得过滤IsDeleted0经常会有人漏了条件导致列表里出现一堆“已删除”的数据我还见过有人统计报表时把废弃数据算进去的这个必须通过数据库视图或者查询封装来统一约束。4.2 参数化 DELETE 和逻辑删除的完整代码不管是物理删除还是逻辑删除代码结构几乎一样区别只是 SQL 语句。物理删除string sql DELETE FROM dbo.UserInfo WHERE Id Id;; using (SqlConnection conn new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(Id, id); int rows cmd.ExecuteNonQuery(); if (rows 0) { Console.WriteLine(记录不存在或已删除); } } }逻辑删除则对应string sql UPDATE dbo.UserInfo SET IsDeleted 1 WHERE Id Id;;这两段代码看着很像但物理删除必须格外小心WHERE条件千万不能丢。我有个同事当年调试时手一滑写成了DELETE FROM dbo.UserInfo一整张表瞬间清空连撤销的机会都没有。所以我在团队里立了一条规矩删除语句必须带着WHERE条件写完并且先改成SELECT COUNT(*)查一下影响范围确认没问题再改成DELETE。4.3 外键约束删除时最容易遇到的地雷物理删除最容易触发的错误是外键冲突。比如用户表被订单表引用你想删掉一个用户但订单表里还挂着这个用户的订单数据库会直接报错错误号是 547。这种错误不能假装看不见要么在事务里先把引用它的数据清掉要么业务上根本不让删改成停用状态。如果确实需要先删子表再删主表代码要放在一个事务里执行保证两个删除要么都成功要么都回滚不然会出现子表删了、主表没删掉的反常状态。另外我建议在代码里捕获SqlException的时候多判断一下错误号catch (SqlException ex) { if (ex.Number 547) { Console.WriteLine(该数据正被其他单据引用不能直接删除请改为停用。); } }这样用户看到的不是一堆英文错误而是你能解释的业务提示。不要动不动就在DELETE外键上设置ON DELETE CASCADE因为级联删除会很隐晦一个入口删数据连带把好几张表的数据都抹了出问题根本不知道是谁删的。我只有在确定整棵引用树都没有历史保留需求时才考虑级联删除。4.4 批量删除和防误删设计批量删除在管理后台里经常出现比如勾选多行记录一次删掉。最直接的做法是用IN条件DELETE FROM dbo.UserInfo WHERE Id IN (Id1, Id2, Id3);这里的参数数量是动态的我见过很多人把 Id 列表直接拼进 SQL这是典型的注入风险。更稳妥的方案是先把 Id 列表放入一个DataTable通过表值参数传进去或者代码里循环删除但次数多时要包在事务里。如果项目没有表值参数的基础设施最保守的做法就是在服务端校验每个 Id 都是纯整数再拼接IN列表但心里要清楚这是妥协方案。防误删设计上我坚持两个动作删除之前先查一遍数据把影响的行数显示出来让用户确认业务数据能走逻辑删除就走逻辑删除。开发阶段可以在 SQL 里用BEGIN TRAN; DELETE ...; ROLLBACK;模拟一遍看看实际会删掉多少行再放到代码里。这个习惯帮我挡住了不少“你觉得只删一行其实删了几百行”的事故。5. 常见问题与排查技巧实录5.1 连接类错误从错误 40 到 18456C# 操作 SQL Server 的第一类常见问题就是连接不上。我遇到的典型报错和排查路径如下SqlException: 无法打开登录所请求的数据库 DemoDb。登录失败错误号 4060一般是连接串里的数据库名不对或者登录账号对这个库没有权限先到 SSMS 里用同样账号试连一下能连上再回来看代码。用户 sa 登录失败错误号 18456常见于 SQL Server 只开了 Windows 身份验证模式sa 密码错误或者账号被禁用。你需要在 SQL Server 属性里把身份验证模式改成“SQL Server 和 Windows 身份验证模式”然后给 sa 设置密码并启用。还有一类是“在建立与服务器的连接时出错。在连接到 SQL Server 时TCP 提供程序: 由于目标计算机积极拒绝”错误号通常是 40。这种我只能说按 2.1 节里的检查项一个一个过服务启动了吗、TCP/IP 协议启用了吗、防火墙放行 1433 端口了吗、SQL Server 的 Browser 服务打开了吗。很多时候不是代码问题是环境问题。5.2 并发和性能唯一约束、死锁和删除慢并发插入最典型的报错是唯一索引冲突错误号 2627。比如用户名为 “admin” 的记录已经存在另一个连接又插入了 “admin”。这时候捕获到SqlException以后可以判断Number是 2601 或 2627然后给出“用户名已存在”的提示而不是把原始异常直接抛给用户。光靠先查再插并不能彻底避免并发冲突因为两个连接可能同时查到“不存在”然后同时插入真正兜底的一定是数据库唯一约束。死锁的问题我也踩过。一次系统里多个页面同时操作订单和用户表有的先更新订单再更新用户有的先更新用户再更新订单数据一多就出现死锁报错错误号 1205。解决思路很简单统一所有代码对多张表的访问顺序同时在事务里尽量保持小范围不让锁的范围无谓扩大。删除大表慢的问题通常是因为WHERE条件没有走索引或者一次删除的行数太多导致锁和日志膨胀。建议给删除条件字段建合适的索引批量删除时分批执行比如每次只删 1000 行循环提交避免一条超大事务把数据库拖垮。5.3 SQL Server 错误码速查表下面这些错误码是我在增删操作里看到最多的整理成表方便你排查错误号常见错误场景处理建议2627 / 2601插入或更新违反唯一约束捕获后在业务层提示“记录已存在”不要暴露原始 SQL 信息547DELETE/UPDATE 违反外键约束改为逻辑删除或先清理引用子表数据并给用户友好提示18456SQL Server 登录失败检查账号、密码、身份验证模式和用户状态4060无法打开指定数据库检查连接串数据库名和登录账号的库权限1205死锁统一多表访问顺序缩短事务时间合理加索引0x80131904.NET 层的 SqlException 包装看内部 InnerException 的详细消息再定位这些错误码在代码里捕获后不要只记ex.Message要学会用ex.Number做分支处理才能真正做到不把数据库底层错误直接甩给用户。5.4 我坚持的几条增删“军规”最后把我这几年踩坑总结出来的规则列一下新项目我会直接照着这个标准写。第一条任何 SQL 都参数化字符串拼接只允许出现在你自己拼的枚举值常量里。第二条删除必须带WHERE并且先看一眼影响行数业务成熟以后可以考虑做一个通用方法每次删除都检查受影响行数超过预期就拒绝执行。第三条涉及多张表写入必须用事务并且事务里不做跟数据库无关的事情。第四条业务主数据优先逻辑删除物理删除只留给临时数据或日志。第五条连接、命令、事务能用using就绝不用手写Close这条能在长期运行的服务里救你一命。这些规则不是捎带脚的“最佳实践”而是我熬夜排查线上数据问题攒下来的教训。比如“删全表”那一次之后我宁可多写一个Count(*)查询也不愿再面对清空的数据表。做 C# 联合 SQL Server 的增删也这么久了回头看看真正让项目稳的从来不是某个高级框架而是这些看起来笨拙的习惯。每写一条INSERT先问自己字段对不对、约束会不会炸、多表之间能不能保持一致每写一条DELETE再问自己条件带全了吗、数据还能不能找回来。把这两个问题问顺了你的增删代码基本就不会在线上出幺蛾子。如果哪天你在日志里看到2627或者547别慌先看一眼触发场景再用上面的思路一步步解大概率三五分钟就能落地。
