usingSystem;

usingSystem.Data;

usingSystem.Linq;

usingSystem.Windows.Forms;

usingSystem.Data.SqlClient;

 

namespacew30

{

    publicpartialclassForm1: Form

    {

 

           SqlConnectioncon = newSystem.Data.SqlClient.SqlConnection(

           @"Data Source=PROBOOK\SQL2012EXPRESS;Initial
Catalog=test4;Integrated Security=True");

           DataClasses1DataContextctx = newDataClasses1DataContext();

          

        publicForm1()

        {

            InitializeComponent();

            con.Open();

        }

 

        privatevoidCreateTables_Click(objectsender, EventArgse)

        {

            newSqlCommand(@"create table Customers(id int primary key,

                            totalpurchase real, name nvarchar(48))",
con).ExecuteNonQuery();

 

            newSqlCommand(@"insert into Customers
(id,totalpurchase,name) values

                           
(1,0.01,'Janek'),(2,0.01,'Jasiu'),(3,0.02,'Johny'),(4,0.02,'Franek'),(5,0.03,'Fransicso'),

                           
(6,0.03,'Szarik'),(7,0.01,'Burek'),(8,0.01,'Matylda'),(9,0.05,'Julia')",
con).ExecuteNonQuery();

 

            newSqlCommand(@"create table Orders (order_id int primary
key, sum real, description nvarchar(48),

                            customer_id int, foreign key (customer_id)
references Customers(id))", con).ExecuteNonQuery();

 

            newSqlCommand(@"insert into Orders
(order_id,sum,description,customer_id) values

               
(1,0.01,'XX',1),(2,0.01,'YY',1),(3,0.02,'ZZ',2),(4,0.02,'XX',2),(5,0.03,'YY',2),(6,0.03,'ZZ',3),

               
(7,0.01,'XX',4),(8,0.01,'YY',5),(9,0.01,'XX',4),(10,0.01,'TT',5),(11,0.11,'PP',4),(12,0.12,'SS',6)",
con).ExecuteNonQuery();

        }

 

        privatevoidInnerJoin_Click(objectsender, EventArgse)

        {

             strings =@"select Customers.id, Customers.name,
Customers.totalpurchase, Orders.order_id,

                        Orders.description from Customers join Orders on
Customers.id = Orders.customer_id";

 

           // SqlDataReader rd = new SqlCommand(s, con).ExecuteReader();

            // while (rd.Read())          

            //    textBox1.Text += rd["id"] + "  "+rd["name"] + " 
"+rd["totalpurchase"] + "  "+rd["order_id"] + "  "+rd["description"] +
"\r\n";

            //rd.Close();

 

             textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = "";

 

            DataTabledt = newDataTable("Table1");           

            dt.Load(newSqlCommand(s, con).ExecuteReader());

            dataGridView2.DataSource = dt;

            intx = 3;

                                                                                          
//where c.id > x

            varQ1 = fromc inctx.Customers joino inctx.Orders onc.id
equalso.customer_id selectnew{ c.id, c.name, c.totalpurchase,
o.order_id, o.description };

            foreach(varq inQ1)

                textBox2.Text += q.id + " "+  q.name + " "+
q.totalpurchase + " "+ q.order_id + " "+ q.description + "\r\n";         

          

 

            varQ2 = ctx.Customers.Join(ctx.Orders, c => c.id, o =>
o.customer_id, (c, o) => new{ c.id, c.name, c.totalpurchase, o.order_id,
o.description });

            foreach(varq inQ1)

                textBox3.Text += q.id + " "+ q.name + " "+
q.totalpurchase + " "+ q.order_id + " "+ q.description + "\r\n";

 

           // dataGridView2.DataSource = Q2;

        }

 

        privatevoidLeftOuterJoin_Click(objectsender, EventArgse)

        {

            strings = @"select Customers.id, Customers.name,
Customers.totalpurchase, Orders.order_id,

                        Orders.description from Customers left join
Orders on Customers.id = Orders.customer_id";

//            textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = "";

//            SqlDataReader rd = new SqlCommand(s, con).ExecuteReader();

//            while (rd.Read())

//                textBox1.Text += rd["id"] + "  " + rd["name"] + "  " +
rd["totalpurchase"] + "  " + rd["order_id"] + "  " + rd["description"] +
"\r\n";

//            rd.Close();

            DataTabledt = newDataTable("Table1");

            dt.Load(newSqlCommand(s, con).ExecuteReader());

            dataGridView2.DataSource = dt;

 

            varQ1 = fromc inctx.Customers

                     joino inctx.Orders onc.id equalso.customer_id

                         intox

                     fromo inx.DefaultIfEmpty()                   

                     selectnew{ c.id, c.name, c.totalpurchase, order_id
= (o == null? 0 : o.order_id),

                                 description = (o == null? " ":
o.description) };

 

            foreach(varq inQ1)

                textBox2.Text += q.id + " "+ q.name + " "+
q.totalpurchase + " "+ q.order_id + " "+ q.description + "\r\n";

 

 

           varQ3 = fromc inctx.Customers

                     joino inctx.Orders onc.id equalso.customer_id

                         intox                                        

                     selectnew{ c.id, c.name, c.totalpurchase, o = x };

 

            foreach(varq inQ3)

            {               

                if(q.o.Count()>0)              

                    foreach(varq2 inq.o)              

                        textBox1.Text += q.id + " "+ q.name + " "+
q.totalpurchase +q2.order_id + " "+ q2.description + "\r\n";

                else

                    textBox1.Text += q.id + " "+ q.name + " "+
q.totalpurchase + "\r\n";

            }

 

 

 

            varQ4 = ctx.Customers.GroupJoin(ctx.Orders, c => c.id, o =>
o.customer_id,

                       (c, o) => new{ c, o });

            foreach(varq inQ4)

            {

                if(q.o.Count() > 0)

                    foreach(varq2 inq.o)

                        textBox3.Text += q.c.id + " "+ q.c.name + " "+
q.c.totalpurchase + q2.order_id + " "+ q2.description + "\r\n";

                else

                    textBox3.Text += q.c.id + " "+ q.c.name + " "+
q.c.totalpurchase + "\r\n";

            }

        }

    }

}