I have read the content of "SqlConnection.Close() method rolls back any pending transactions." in msdn library, and msdn library also said "SqlConnection.Close() method releases the connection to the connection pool, or closes the connection if connection pooling is disabled."
I created a stored procedure in Northwind db like this:create proc dbo.TestProc
as
begin transaction
delete dbo.[Order Details];
go I created some code to execute the stored procedure like this:
static public void Test()
{
string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = connectionString;
SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlconnection;
try
{
sqlconnection.Open();
sqlCommand.ExecuteNonQuery();
}
catch
{
}
finally
{
sqlconnection.Close();
}
}Clearly, the database connection is put into the pool after the Sqlconnection.Close() is invoked. But after the Sqlconnection.Close() is invoked, the transaction is still on the database connection, and at that time the connection is in the pool.
I have create a query like this:select open_tran
from dbo.sysprocesses as p
inner join dbo.sysdatabases as d
on p.dbid = d.dbid
where d.[name] = N'Northwind';and the result has only one row:open_tran
---------
1(1 row(s) affected)
I think the not completed(committed or rollbacked) transaction in the example procedure is not a pending transaction, because the Sqlconnection.Close() method is not rollback the transaction.
Thus, my question are:a) What is a pending transaction?
b) How to check transaction count in c# code, besides execute T-SQL batch include "@@error".
c) Is the not completed transaction in T-SQL only manual committed? Are there better methods to solve the not completed transaction problem?
I created a stored procedure in Northwind db like this:create proc dbo.TestProc
as
begin transaction
delete dbo.[Order Details];
go I created some code to execute the stored procedure like this:
static public void Test()
{
string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = connectionString;
SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlconnection;
try
{
sqlconnection.Open();
sqlCommand.ExecuteNonQuery();
}
catch
{
}
finally
{
sqlconnection.Close();
}
}Clearly, the database connection is put into the pool after the Sqlconnection.Close() is invoked. But after the Sqlconnection.Close() is invoked, the transaction is still on the database connection, and at that time the connection is in the pool.
I have create a query like this:select open_tran
from dbo.sysprocesses as p
inner join dbo.sysdatabases as d
on p.dbid = d.dbid
where d.[name] = N'Northwind';and the result has only one row:open_tran
---------
1(1 row(s) affected)
I think the not completed(committed or rollbacked) transaction in the example procedure is not a pending transaction, because the Sqlconnection.Close() method is not rollback the transaction.
Thus, my question are:a) What is a pending transaction?
b) How to check transaction count in c# code, besides execute T-SQL batch include "@@error".
c) Is the not completed transaction in T-SQL only manual committed? Are there better methods to solve the not completed transaction problem?
解决方案 »
- 请问查询某个日期的前一天。如何查
- 如何把sql server2005的数据库移植到 sql server2000(不采用数据导入导出方案)
- 请教,如何找出数据库中所有用户表的时间字段,把时间段中有周日的全部减一天
- 如何备份SQL Server 2005中的数据到SQL Server 2000
- 谁有有关SQL Server 2000 system table 系统表的资料,我需要各个系统表各个column代表的具体意思 ..
- 关于修改某一字段中的一两个字,如何实现?
- 我有一个字段为decimal,小数位数为2,在程序中取得这个字段的时候如果是整数,在整数后是.00,但是我希望有小数的时候才显示小数部分,请
- select * from table where intId in(这里varchar型的'1,2,3,4')怎么才能执行?
- sql6.5下执行一存储过程(其中包含有事务),出现如下错误信息,应该如何处理?谢谢!!
- SQL server 2000 订阅功能中 分发代理程序错误 登录失败: 未知的用户名或错误密码。
- 简单问题,解决速度上分,关于取第一天的问题
- 帮忙找找代码哪里运行不对了?
{
string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = connectionString;
SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlconnection;
SqlTransaction myTrans; // Start a local transaction,and it's a pending local transaction
myTrans = sqlconnection.BeginTransaction();
sqlCommand.Transaction = myTrans;
try
{
sqlconnection.Open();
sqlCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
//code....
}
}
}
finally
{
sqlconnection.Close();
}
}
I exactly think your code is feasible! Thank you very much!But, msdn library said: "Pending transactions started using Transact-SQL or BeginTransaction are automatically rolled back. "So, these are my question:a) How to start a pending transaction using Transact-SQL?
b) And the what's meaning of the word of pending? Is the word of Pending used to express a status of transactions, or express a kind of transactions that are started by some special ways?