public void InsertSampleData（DataTable tempTable）
{
session.Transaction.Commit（）;
使用（var cmd = sqlConnection.CreateCommand（））使用（var sqlConnection = new SqlConnection（session.GetSessionImplementation（）
.Connection.ConnectionString））
）
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =dbo.insertData;
cmd.Parameters.Add（@ sItems，SqlDbType.Structured）;
var sqlParam = cmd.Parameters [@ Items];
sqlParam.Direction = ParameterDirection.Input;
sqlParam.TypeName =[dbo]。[DataItemType];
sqlParam.Value = tempTable;
cmd.ExecuteNonQuery（）;
$ b code $
$ b $或$ $
public void InsertSampleData（DataTable tempTable）
{
session.Transaction.Commit（）;
var connection = session.SessionFactory.OpenSession（）。GetSessionImplementation（）。Connection;
using（var sqlConnection =（SqlConnection）connection）
{
using（var cmd = sqlConnection.CreateCommand（））
{
cmd.CommandType = CommandType.StoredProcedure ;
cmd.CommandText =dbo.insertData;
cmd.Parameters.Add（@ sItems，SqlDbType.Structured）;
var sqlParam = cmd.Parameters [@ Items];
sqlParam.Direction = ParameterDirection.Input;
sqlParam.TypeName =[dbo]。[DataItemType];
sqlParam.Value = tempTable;
cmd.ExecuteNonQuery（）;
为什么使用SQLConnection你为什么不用Nhibernate Session来执行查询呢？你可以使用Nhibernate Session来完成你正在执行的任何操作。
pre $ session.CreateSQLQuery（< your sql>）
.SetParameters（）//和其他各种API来设置参数
.ExecuteUpdate（）;
My code looks like this:
public void InsertSampleData(DataTable tempTable)
{
session.Transaction.Commit();
var connection = session.GetSessionImplementation().Connection;
using (var sqlConnection = (SqlConnection)connection)
{
using (var cmd = sqlConnection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.insertData";
cmd.Parameters.Add("@sItems", SqlDbType.Structured);
var sqlParam = cmd.Parameters["@Items"];
sqlParam.Direction = ParameterDirection.Input;
sqlParam.TypeName = "[dbo].[DataItemType]";
sqlParam.Value = tempTable;
cmd.ExecuteNonQuery();
}
}
}
The code works fine but if I do not commit the transaction it throws an exception as shown
But I don't want the transaction to be commited yet before completing the whole operation. How can I achieve this??
解决方案
the issue is that the transaction was supposed to use has been committed and already has been disposed, thus either instantiate a new SqlConnection or get a new session and then run the query as part of the new session.
public void InsertSampleData(DataTable tempTable)
{
session.Transaction.Commit();
using (var sqlConnection = new SqlConnection(session.GetSessionImplementation()
.Connection.ConnectionString))
{
using (var cmd = sqlConnection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.insertData";
cmd.Parameters.Add("@sItems", SqlDbType.Structured);
var sqlParam = cmd.Parameters["@Items"];
sqlParam.Direction = ParameterDirection.Input;
sqlParam.TypeName = "[dbo].[DataItemType]";
sqlParam.Value = tempTable;
cmd.ExecuteNonQuery();
}
}
}
or
public void InsertSampleData(DataTable tempTable)
{
session.Transaction.Commit();
var connection = session.SessionFactory.OpenSession().GetSessionImplementation().Connection;
using (var sqlConnection = (SqlConnection)connection)
{
using (var cmd = sqlConnection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.insertData";
cmd.Parameters.Add("@sItems", SqlDbType.Structured);
var sqlParam = cmd.Parameters["@Items"];
sqlParam.Direction = ParameterDirection.Input;
sqlParam.TypeName = "[dbo].[DataItemType]";
sqlParam.Value = tempTable;
cmd.ExecuteNonQuery();
}
}
}
BTW, why are using SQLConnection why arent you exexurting the query using the Nhibernate Session, you could do the everything that you are doing right now using the Nhibernate Session.
session.CreateSQLQuery(<your sql>)
.SetParameters() //and various other API to set your parameters
.ExecuteUpdate();
这篇关于如何通过不提交当前会话事务将DataTable作为参数传递给存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！