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