<connectionStrings>
<add name="myConnectionString" connectionString="data source=.; initial catalog=myDB; user id=sa; 
password=sa"/>
</connectionStrings>

string name = txtName.Text;
string email = txtEmail.Text;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlTransaction myTransaction = null;
try
{
    myConnection.Open();
         myTransaction = myConnection.BeginTransaction();
         SqlCommand myCommand = new SqlCommand();
         myCommand.CommandType = CommandType.StoredProcedure;
         myCommand.Connection = myConnection;
         myCommand.CommandText = "maillist_insert";
         myCommand.Transaction = myTransaction;
         SqlParameter myParameter1 = new SqlParameter("@uname", name);
         SqlParameter myParameter2 = new SqlParameter("@email", email);
         myCommand.Parameters.Add(myParameter1);
         myCommand.Parameters.Add(myParameter2);
         int rowsAffected = myCommand.ExecuteNonQuery();
         myTransaction.Commit();
         lblMsg.Text = "Transaction Committed";
}
catch (SqlException ex)
{
    myTransaction.Rollback();
         lblMsg.Text = "Transaction Rollbacked due to " + ex.Message;
 }
finally
{
    myConnection.Close();
}