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