public ActionResult Index()
{
string strConnStr = ConfigurationManager.ConnectionStrings["HirenTestConnectionStr"].ConnectionString;
//Dapper test - Starts
using (var connection = new SqlConnection(strConnStr))
{
connection.Open();
// //insert
// connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new { Name = "TestPerson", Address = "TestAddress", PhoneNo = "TestPhoneNo" });
//
//
// //bulk insert test
// connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new List<Person> {
// new Person{ Name="BulkPerson1", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
// new Person{ Name="BulkPerson2", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
// new Person{ Name="BulkPerson3", PhoneNo = "BulkPhoneNo", Address="BulkAddress" },
// new Person{ Name="BulkPerson4", PhoneNo = "BulkPhoneNo", Address="BulkAddress" }
// });
//Get Data from person
var persons = connection.Query<Person>("select * from Person");
//Get Data From Person Note
var personNote = connection.Query<PersonNote>("select * from PersonNote");
//Get Data From storedProcedure
var PersonNoteFrmStoredProc = connection.Query<PersonNote>("[GetPersonNote]", param: new { strSearch = "note 1" }, commandType: CommandType.StoredProcedure);
//Join Example
/*
var sql = @"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();
*/
// var sql = @"select * from PersonNote as pn inner join Person as p on pn.PersonID = pn.ID";
// var joinPersonNote = connection.Query<Person, PersonNote, PersonNote>(sql, (per, perNot) => { perNot.person = per; return perNot; });
// var joinPersonNote1 = connection.Query<Person, PersonNote, PersonNote>(sql,splitOn: "PersonID" );
//Transaction with dapper starts
try
{
using (var transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
connection.EnlistTransaction(Transaction.Current);
for (int i = 0; i < 10; i++)
{
try
{
connection.Execute("insert into Person (Name,Address,PhoneNo) values(@Name,@Address,@PhoneNo)", param: new { Name = "TestTransaction" + i.ToString(), Address = "TestAddress", PhoneNo = "TestPhoneNo" });
//if i == 7 raise error
if (i == 7)
{
throw new Exception("Erorr.... Can't go ahead...");
}
}
catch (Exception)
{
Transaction.Current.Rollback();
break;
}
}
transactionScope.Complete();
transactionScope.Dispose();
}
}
catch (TransactionAbortedException ex)
{ }
catch (Exception)
{ }
//Transaction with dapper ends
}
//Dapper test - Ends
ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";
return View();
}
public SqlConnection GetConnection(CommittableTransaction transaction = null)
{
if (Connection.IsNull())
{
Connection = new SqlConnection(Runtime.Context.GetDefaultConnectionString());
if (transaction.IsInstance())
{
Connection.Open();
Connection.EnlistTransaction(transaction);
}
}
return Connection;
}
protected OperationResult <T> PerformDataBaseCall <T>(string sql, CommandType commandType, IEnumerable <KeyValuePair <string, object> > parameters, Func <SqlCommand, T> callback) =>
Get.OperationResult(() => {
try {
if (_disposed)
{
throw new System.ObjectDisposedException("This transaction scope has already been disposed.");
}
if (_connection == null)
{
_commitableTransaction = new System.Transactions.CommittableTransaction();
_connection = new SqlConnection(_connectionString.ToString());
_connection.Open();
_connection.EnlistTransaction(_commitableTransaction);
}
parameters = parameters ?? new KeyValuePair <string, object> [0];
var command = new SqlCommand {
Connection = _connection,
CommandTimeout = Timeout,
CommandText = sql,
CommandType = commandType
};
command.Parameters.AddRange(
parameters.Select(p => {
var parameter = new SqlParameter(p.Key, p.Value ?? DBNull.Value);
if (p.Value is string)
{
parameter.DbType = DbType.AnsiString;
}
else if (p.Value is XDocument)
{
parameter.DbType = DbType.Xml;
parameter.Value = new System.Data.SqlTypes.SqlXml(((XDocument)p.Value).CreateReader());
}
return(parameter);
}).ToArray()
);
return(callback(command));
}
catch {
FinishOperation(() => _commitableTransaction?.Rollback());
throw;
}
});
public override ManagedConnection Connect()
{
Action complete = () => { };
Action dispose = () => { numberOfManagedConnections--; };
try
{
if (Transaction.Current == null)
{
var tx = new TransactionScope(
TransactionScopeOption.RequiresNew,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted });
complete += tx.Complete;
dispose += tx.Dispose;
}
var connection = new SqlConnection(connectionString);
connection.InfoMessage += (obj, args) => OnMessage(args);
connection.Open();
connection.ChangeDatabase("tempdb");
complete = connection.Dispose + complete;
dispose = connection.Dispose + dispose;
connection.EnlistTransaction(Transaction.Current);
numberOfManagedConnections++;
return new ManagedConnection(connection, complete, dispose);
}
catch (Exception)
{
dispose();
throw;
}
}
private void button84_Click(object sender, EventArgs e)
{
CommittableTransaction tx = null;
try
{
using (tx = new CommittableTransaction())
{
using (SqlConnection conn1 = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString))
{
using (SqlCommand myCommand = new SqlCommand())
{
myCommand.Connection = conn1;
conn1.Open();
conn1.EnlistTransaction(tx);
//Restore database to near it's original condition so sample will work correctly.
myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)";
myCommand.ExecuteNonQuery();
//Insert the first record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
myCommand.ExecuteNonQuery();
//測試交易失敗用
//Insert the first record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
myCommand.ExecuteNonQuery();
//Insert the second record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')";
myCommand.ExecuteNonQuery();
tx.Commit();
MessageBox.Show("trans. Successfully");
}
}
}
}
catch (TransactionException ex)
{
MessageBox.Show(ex.Message);
tx.Rollback();
}
catch (Exception ex)
{
MessageBox.Show("trans. roll back " + ex.Message);
}
}
/// <summary>
/// Executes sql
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sql"></param>
/// <param name="commandTimeout"></param>
/// <param name="dtx"></param>
/// <returns></returns>
private static Exception ExecuteSql(string connectionString, string sql,IList<SqlParameter> parameters,out string output, int commandTimeout, DependentTransaction dtx)
{
Exception exception = null;
output = string.Empty;
try
{
if (dtx != null)
{
//if transaction - execute in transaction scope
using (TransactionScope scope = new TransactionScope(dtx))
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
//Make sure transaction is placed in DTC
IDtcTransaction t = TransactionInterop.GetDtcTransaction(Transaction.Current);
conn.Open();
int retryCount = 120;
while (retryCount > 0)
{
try
{
//Enlist to transaction. Will retry, because sometimes SQL server
//reports that transaction is used by another session.
//This happens not very often, but this is a protection
conn.EnlistTransaction(TransactionInterop.GetTransactionFromDtcTransaction(t));
break;
}
catch
{
retryCount--;
if (retryCount == 0)
throw;
Thread.Sleep(500);
}
}
using (SqlCommand cmd = conn.CreateCommand())
{
ExecuteSql(cmd, sql, parameters, out output, commandTimeout);
}
}
//commit if success
scope.Complete();
}
}
else
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//if not transactional, then just execute sql
ExecuteSql(cmd, sql, parameters, out output, commandTimeout);
}
}
}
}
catch (Exception ex)
{
exception = ex;
}
return exception;
}
public void EnlistTransaction(System.Transactions.Transaction transaction)
{
_connection.EnlistTransaction(transaction);
}
public void DbContext_can_be_initialized_without_promotion_to_distributed_transaction_inside_transaction_scope_if_the_context_type_has_been_previously_initialized_outside()
{
var connectionString = default(string);
using (var context = new TransactionTestsContext())
{
// force context initialization
ExtendedSqlAzureExecutionStrategy.ExecuteNew(() => context.Entities.Count());
connectionString = context.Database.Connection.ConnectionString;
}
// this only works for integrated security, or when password is persisted after connecting
// otherwise we can't connect to database during context initialization (password is gone from connection string)
if (DatabaseTestHelpers.IsIntegratedSecutity(connectionString) ||
DatabaseTestHelpers.PersistsSecurityInfo(connectionString))
{
ExtendedSqlAzureExecutionStrategy.ExecuteNew(
() =>
{
using (new TransactionScope())
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
connection.EnlistTransaction(Transaction.Current);
using (var context = new TransactionTestsContext(connection, false))
{
context.Entities.Count();
}
}
}
});
}
}
public void DbContext_can_be_initialized_without_promotion_to_distributed_transaction_inside_transaction_scope_if_the_context_type_has_been_previously_initialized_outside()
{
var connectionString = default(string);
using (var context = new TransactionTestsContext())
{
// force context initialization
context.Entities.Count();
connectionString = context.Database.Connection.ConnectionString;
}
using (new TransactionScope())
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
connection.EnlistTransaction(Transaction.Current);
using (var context = new TransactionTestsContext(connection, false))
{
context.Entities.Count();
}
}
}
}
public static void ListenerThreadProc(object queueListenerConfig)
{
QueueListenerConfig config = (QueueListenerConfig) queueListenerConfig;
while (!stopping)
{
TransactionOptions to = new TransactionOptions();
to.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
to.Timeout = TimeSpan.MaxValue;
CommittableTransaction tran = new CommittableTransaction(to);
try
{
using (var con = new SqlConnection(config.ConnectionString))
{
con.Open();
con.EnlistTransaction(tran);
byte[] message = ServiceBrokerUtils.GetMessage(config.QueueName, con, TimeSpan.FromSeconds(10));
if (message == null) //no message available
{
tran.Commit();
con.Close();
continue;
}
try
{
if (config.EnlistMessageProcessor)
{
using (var ts = new TransactionScope(tran))
{
config.MessageProcessor(message);
ts.Complete();
}
}
else
{
config.MessageProcessor(message);
}
}
catch (SqlException ex) //catch selected exceptions thrown by the MessageProcessor
{
config.FailedMessageProcessor(message, con, ex);
}
tran.Commit(); // the message processing succeeded or the FailedMessageProcessor ran so commit the RECEIVE
con.Close();
}
}
catch (SqlException ex)
{
System.Diagnostics.Trace.Write("Error processing message from " + config.QueueName + ": " + ex.Message);
tran.Rollback();
tran.Dispose();
Thread.Sleep(1000);
}
///catch any other non-fatal exceptions that should not stop the listener loop.
catch (Exception ex)
{
Trace.WriteLine("Unexpected Exception in Thread Proc for " + config.QueueName + ". Thread Proc is exiting: " + ex.Message);
tran.Rollback();
tran.Dispose();
return;
}
}
}
