sqlserver和c# 事物

最新推荐文章于 2024-10-09 20:11:39 发布

转载最新推荐文章于 2024-10-09 20:11:39 发布 · 299 阅读

SQLServer 专栏收录该内容

12 篇文章

订阅专栏

本文介绍在SQL和C#中如何使用事务确保数据一致性，包括基本的事务操作及批量插入示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql的事务

 1 sql
 2 create database model
 3 go
 4 use model
 5 go
 6 create table Stu(
 7 id int ,
 8 name varchar(200)
 9 )
10 go
11 select * from Stu
12 go
13 begin transaction cc
14 begin 
15  --print @@Trancount
16  insert into Stu (id,name) values(1,'33')
17  insert into Stu (id,name) values('sd','66')    
18  if @@error>0 
19     rollback transaction cc
20     
21  else 
22     commit transaction cc  
23 end 
24 go

c#的事务

创建一个应用台控件程序

 1 static void Main(string[] args)
 2         {
 3             using (SqlConnection sqlcon = new SqlConnection(@"server=192.168.3.11;database=model;uid=sa;pwd="))
 4             {
 5                 sqlcon.Open();
 6                 SqlTransaction  objtran;
 7                 objtran = sqlcon.BeginTransaction("Method");
 8                 SqlCommand objcmd = new SqlCommand();
 9                 objcmd.Connection = sqlcon;
10                 objcmd.Transaction = objtran;                
11                 try
12                 {
13                     objcmd.CommandText = "insert into Stu (id,name) values(1,'33')";                    
14                     object a = objcmd.ExecuteScalar();                    
15                     objcmd.CommandText = "insert into Stu (id,name) values(2,'66')";                    
16                     a = objcmd.ExecuteScalar();  
17                     objcmd.ExecuteScalar(); 
18                     Console.WriteLine(a.ToString());                   
19                     objtran.Commit();
20 
21                 }
22                 catch(Exception ex)
23                 {
24                     objtran.Rollback();
25                     Console.WriteLine(ex.Message);
26                     Console.ReadLine();
27                 }
28             }
29             Console.ReadLine();
30         }

批量插入

public void SaveTcpData(DataFormateModel
 DFM)

       {

           using (SqlConnection
 _strCon = new SqlConnection(SqlHelper.SqlConnStr))

           {

               _strCon.Open();

               using (SqlTransaction
 _sqlTransaction = _strCon.BeginTransaction("Method"))

               {

                   SqlCommand
 _sqlCommand = new SqlCommand();

                   _sqlCommand.Connection
 = _strCon;

                   _sqlCommand.Transaction
 = _sqlTransaction;

                   try

                   {

                       _sqlCommand.CommandText
 = "insert
 into PointTcpData (PointTcpData_pointID,PointTcpData_X,PointTcpData_Y,PointTcpData_Z,SavaeTime) values ("

                                                   +
 DFM.PointID + "," +
 DFM.PointX + "," +
 DFM.PointY + "," +
 DFM.PointZ + ",'" +
 DateTime.Now.ToString() + "') 
 select   @@IDENTITY   as   'Identity'";      

                       int _identityID
 = Convert.ToInt32(_sqlCommand.ExecuteScalar());

                       DataTable
 _dt = new DataTable();

                       _dt.Columns.Add(new DataColumn("PointTcpDataAttach_ID",typeof(int)));

                       _dt.Columns.Add(new DataColumn("PointTcpDataID",typeof(int)));

                       _dt.Columns.Add(new DataColumn("PointTcpDataAttach_Gold",typeof(float)));

                       _dt.Columns.Add(new DataColumn("PointTcpDataAttach_Max",typeof(float)));

                       _dt.Columns.Add(new DataColumn("PointTcpDataAttach_Std",typeof(float)));

 

                       DFM.OtherInfo.ForEach(x
 =>

                           {

                               DataRow
 _dr = _dt.NewRow();

                               _dr["PointTcpDataID"]
 = _identityID;

                               _dr["PointTcpDataAttach_Gold"]
 = x.Gold;

                               _dr["PointTcpDataAttach_Max"]
 = x.Max;

                               _dr["PointTcpDataAttach_Std"]
 = x.Std;

                               _dt.Rows.Add(_dr);

                           });

                       using (SqlBulkCopy
 _sqlcopy = new SqlBulkCopy(_strCon,
 SqlBulkCopyOptions.Default, _sqlTransaction))

                       {

                           _sqlcopy.BatchSize
 = _dt.Rows.Count;

                           _sqlcopy.DestinationTableName
 = "PointTcpDataAttach";

                           _sqlcopy.WriteToServer(_dt);

                       }

 

                       _sqlTransaction.Commit();

                   }

                   catch (Exception
 ex)

                   {

                       _sqlTransaction.Rollback();

                   }

               }

           }

       }