using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Transactions;


public partial class Lab8
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void zadanie1()
    {
        WindowsIdentity newIdentity = null;
        WindowsImpersonationContext newContext = null;
        try
        {
            // zmiana tozsamosci uzytkownika
            newIdentity = SqlContext.WindowsIdentity;
            newContext = newIdentity.Impersonate();
            if (newContext != null)
            {
               using (SqlConnection oConn =
                    new SqlConnection(@"Data Source=172.20.42.64;
                                    Initial Catalog=AdventureWorks2008;
                                    User ID=ALem;
                                    Password=Passw0rd;"))
                {
                    SqlCommand oCmd = new SqlCommand("SELECT * FROM Person.Person",
                                                     oConn);
                    oConn.Open();

                   SqlDataReader oRead =
                    oCmd.ExecuteReader(CommandBehavior.CloseConnection);

                   // przywracamy kontekst tozsamosci
                    newContext.Undo();

                   // wyniki metoda Send
                    SqlContext.Pipe.Send(oRead);
                }
            }
            else
            {
                throw new Exception("zmiana tozsamosci ");
            }
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send(ex.Message.ToString());
        }
        finally
        {
            if (newContext != null)
            {
                newContext.Undo();
            }
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void zadanie2()
    {
        using (TransactionScope oTran = new TransactionScope())
        {
            using (SqlConnection oConn = new SqlConnection("context connection=true;"))
            {
                oConn.Open();
                SqlCommand oCmd =
                new SqlCommand("INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Adrian', 60)", oConn);
                oCmd.ExecuteNonQuery();
                oCmd.CommandText = "INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Adrian', 70)";
                oCmd.ExecuteNonQuery();
                oCmd.CommandText = "INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Ktos', 80)";
                oCmd.ExecuteNonQuery();
                oTran.Complete();
            }
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
        public static void zadanie3(string nazwa)
        {
            SqlParameter param = new SqlParameter("@nazwa", nazwa);
            using(TransactionScope oTran = new TransactionScope())
            {
                using(SqlConnection oConn = new SqlConnection("context connection=true;"))
                {
                    oConn.Open();
                    SqlCommand update = new SqlCommand("UPDATE AdventureWorks2018.dbo.Konta SET value = -1 * value WHERE name = @nazwa", oConn);
                    var returnValue = update.ExecuteNonQuery();
                    using(SqlConnection remConn = new SqlConnection(@"Data Source=172.20.42.64;
                                                                    Initial Catalog=AdventureWorks2008;
                                                                    User ID=ALem;
                                                                    Password=Passw0rd;"))
                    {
                        returnValue = 0;
                        remConn.Open();
                        SqlCommand updateRemote = new SqlCommand("UPDATE AdventureWorks2018.dbo.Konta SET value = -1 * value WHERE name = @nazwa",
                            remConn);
                        returnValue = updateRemote.ExecuteNonQuery();
                    }
                }
                oTran.Complete();
            }
        }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void zadanie4()
    {
        System.Transactions.CommittableTransaction oTran =
        new CommittableTransaction();
        using (SqlConnection oConn = new SqlConnection("context connection=true"))
        {
            try
            {
                SqlCommand oCmd = new SqlCommand();
                oConn.Open();
                //przekazujemy obiekt CommittableTransaction
                oConn.EnlistTransaction(oTran);
                oCmd.Connection = oConn;
                // insert nr 1
                oCmd.CommandText = "INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Ktos', 80)";
                SqlContext.Pipe.ExecuteAndSend(oCmd);

                // insert nr 2
                oCmd.CommandText = "INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Ktos', 80)";
                SqlContext.Pipe.ExecuteAndSend(oCmd);
                // insert nr 3

                oCmd.CommandText = "INSERT INTO AdventureWorks2008.dbo.Konta VALUES ('Ktos', 80)";
                SqlContext.Pipe.ExecuteAndSend(oCmd);
            }
            catch (SqlException ex)
            {
                oTran.Rollback();
            }
            finally
            {
                oConn.Close();
            }
        }
    }

};