使用 C# 在数据库中插入记录时使用事务 [英] Using transactions when inserting records in the Database with C#

问题描述

我从未使用过 SQL 事务，就我而言，我知道我真的必须使用它们.我的应用程序的功能之一是将 Excel 文件转换为数据库.

I never have used SQL transactions, and in my case I know that I really will have to use them. One of the features of my App, is to convert an Excel file to a Database.

所以我从 Excel 中获取值，使用参数(要发送到 SP)构建一个字符串数组，其中一些值我将直接插入主表中，其他值我将插入辅助表中，然后我将获得 id 将代替主表中的这些值.我的意思是我有外键，这就是我这样做的原因.

So I get the values from the Excel, build an array of strings with the arguments (to be sent to the SP) and some of the values I will insert directly in the main table, and others I will insert in secondary tables, and then I will get id's who will be in place of those values in the main table. What I am saying is that I have foreign keys, and it's why I do this.

所以我认为我做得很完美，但没有交易.但是如果我在表中插入值时出现任何问题，我将不得不进行回滚.

So I think I am doing this perfectly but without transactions. But if anything goes wrong when I am inserting the values in the tables I will have to do the rollback.

所以在这个过程中我使用了 5 个存储过程，每个表一个.这些 SP 并不是此过程所独有的.它们将在应用程序中用于插入记录.

So in the process I am using 5 stored procedures, one for each table. These SP's aren't exclusive to this process. They will be used in the App to insert records.

但是我需要关于在哪里使用事务的帮助.我应该在 C#(应用程序端)还是在 SQL Server(服务器端)中执行此操作?

But I need help in where to use transaction. Should I do it in C# (app side) or in SQL Server (server side)?

现在我正在使用 C# 中的 SqlTransaction 对象，它以某种方式工作，因为如果我执行回滚，他确实会取消所有插入，但我在事务中使用的 ID 不再可用.只有当我删除所有表并再次创建它们时，这些 ID 才会再次可用.

For now I am using the SqlTransaction object from C# and it works in some way, because if I do the rollback he really cancels all the inserts, but the ID's that I have used in the transaction aren't available anymore. Only if I drop all the tables and create them again will those ID's be available again.

所以我认为我没有很好地使用事务，这就是为什么我需要帮助，以了解如何以及在何处更好地使用事务.

So I think that I am not using the transaction well and it's why I need help, to know how and where is better to use the transaction.

推荐答案

我认为您使用事务的方式是正确的，问题是 IDENTITY 列的种子没有回滚.

I think you are on the right track with how you are using transactions, the issue is that the seed for IDENTITY columns is not rolled back.

我猜你在使用 MS SQL Server?要在不重新创建表的情况下重置 IDENTITY 列，您可以使用 DBCC CHECKIDENT.

I'm guessing you are using MS SQL Server? To reset the IDENTITY columns without recreating the tables, you can use DBCC CHECKIDENT.

DBCC CHECKIDENT (table_name, RESEED, 99);

这将重置种子，以便分配的下一个标识值为 100.您当然必须在 BEGIN TRANSACTION 之前记录表的最高标识值.

This would reset the seed so that the next identity value assigned would be 100. You of course have to record the highest identity value for the table prior to the BEGIN TRANSACTION.

如果只有一个写入进程，我只会使用它.在多个作者的情况下，除非您先进行一些严重的表锁定，否则很可能会使事情变得繁荣.在有多位作者的情况下，我认为您最好编写代码的其余部分，以便标识列值中的漏洞"是可以接受的.

I would only use this if there is a single writer process. In a multiple writers situation, it is likely to make things go BOOM unless you do some serious table locking first. In the case of multiple writers I think you are much better off writing the rest o the code so "holes" in the identity column values are acceptable.

这篇关于使用 C# 在数据库中插入记录时使用事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！