C#和sqlserver的事物

sql的事务

sql
create database model
go
use model
go
create table Stu(
id int ,
name varchar(200)
)
go
select * from Stu
go
begin transaction cc
begin 
 --print @@Trancount
 insert into Stu (id,name) values(1,'33')
 insert into Stu (id,name) values('sd','66')    
 if @@error>0 
    rollback transaction cc
    
 else 
    commit transaction cc  
end 
go

c#的事务

创建一个应用台控件程序

static void Main(string[] args)
        {
            using (SqlConnection sqlcon = new SqlConnection(@"server=192.168.3.11;database=model;uid=sa;pwd="))
            {
                sqlcon.Open();
                SqlTransaction  objtran;
                objtran = sqlcon.BeginTransaction("Method");
                SqlCommand objcmd = new SqlCommand();
                objcmd.Connection = sqlcon;
                objcmd.Transaction = objtran;                
                try
                {
                    objcmd.CommandText = "insert into Stu (id,name) values(1,'33')";                    
                    object a = objcmd.ExecuteScalar();                    
                    objcmd.CommandText = "insert into Stu (id,name) values(2,'66')";                    
                    a = objcmd.ExecuteScalar();  
                    objcmd.ExecuteScalar(); 
                    Console.WriteLine(a.ToString());                   
                    objtran.Commit();

                }
                catch(Exception ex)
                {
                    objtran.Rollback();
                    Console.WriteLine(ex.Message);
                    Console.ReadLine();
                }
            }
            Console.ReadLine();
        }

批量插入

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();
                   }
               }
           }
       }