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();
}
}
}
}
|
本文转自lpxxn博客园博客，原文链接：http://www.cnblogs.com/li-peng/archive/2012/01/30/2331897.html，如需转载请自行联系原作者
