using System;
using System.Data;
using System.Data.SqlClient;

namespace MARS
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	class Class1
	{
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main(string[] args)
		{
      MARS1();
      MARS2();
		}

		static void MARS1()
		{
			string connect_string = GetConnectStringFromConfigFile();
      connect_string += ";multipleactiveresultsets=true";
			SqlConnection conn = new SqlConnection(connect_string);
			conn.Open();
			SqlCommand cmd1 = new SqlCommand(
				"select * from authors", conn);
			SqlCommand cmd2 = new SqlCommand(
				"select * from jobs", conn);
			SqlDataReader rdr1 = cmd1.ExecuteReader ();
			// second resultset, same connection
			SqlDataReader rdr2 = cmd2.ExecuteReader ();
			while
			((rdr1.Read() == true && rdr2.Read() == true))
			{
				// write first column of each resultset
				Console.WriteLine (rdr1[0]);
				Console.WriteLine (rdr2[0]);
			}
			// clean everything up
			rdr1.Close();
			rdr2.Close();
			cmd1.Dispose();
			cmd2.Dispose();
			conn.Dispose();
		}

		static void MARS2()
		{
			string connect_string = GetConnectStringFromConfigFile();
			SqlConnection conn = new SqlConnection(connect_string);
			conn.Open();
			// one transaction
			SqlTransaction tx1 = conn.BeginTransaction();
			// this would fail
			//SqlTransaction tx2 = conn.BeginTransaction();
			SqlCommand cmd1 = new SqlCommand
			("update_authors_and_getresults", conn, tx1);
			// both SqlCommands must be enlisted
			SqlCommand cmd2 = new SqlCommand(
			"update_authors_and_getresults", conn, tx1);
			cmd1.CommandType = CommandType.StoredProcedure;
			cmd2.CommandType = CommandType.StoredProcedure;
			SqlDataReader rdr1 = cmd1.ExecuteReader();
			// second resultset, same connection
			SqlDataReader rdr2 = cmd2.ExecuteReader();
			while
			((rdr1.Read() == true && rdr2.Read() == true))
			{
			// write first column of each resultset
			Console.WriteLine (rdr1[0]);
			Console.WriteLine (rdr2[0]);
			}
			// commit one transaction (both authors & jobs)
			tx1.Commit();
			// but you cannot roll back just one or the other,
			// attempt to build second transaction failed
			// tx2.Rollback();
			// clean everything up
			rdr1.Close();
			rdr2.Close();
			cmd1.Dispose();
			cmd2.Dispose();
			conn.Dispose();
		}

		static string GetConnectStringFromConfigFile()
		{
			return "server=.;integrated security=sspi;database=pubs";
		}
	}
}
