using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace w30
{
public partial class Form1 : Form
{
SqlConnection con = new System.Data.SqlClient.SqlConnection(
@"Data Source=PROBOOK\SQL2012EXPRESS;Initial Catalog=test4;Integrated Security=True");
DataClasses1DataContext ctx = new DataClasses1DataContext();
public Form1()
{
InitializeComponent();
con.Open();
}
private void CreateTables_Click(object sender, EventArgs e)
{
new SqlCommand(@"create table Customers(id int primary key,
totalpurchase real, name nvarchar(48))", con).ExecuteNonQuery();
new SqlCommand(@"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();
new SqlCommand(@"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();
new SqlCommand(@"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();
}
private void InnerJoin_Click(object sender, EventArgs e)
{
string s =@"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 = "";
DataTable dt = new DataTable("Table1");
dt.Load(new SqlCommand(s, con).ExecuteReader());
dataGridView2.DataSource = dt;
int x = 3;
//where c.id > x
var Q1 = from c in ctx.Customers join o in ctx.Orders on c.id equals o.customer_id select new { c.id, c.name, c.totalpurchase, o.order_id, o.description };
foreach (var q in Q1)
textBox2.Text += q.id + " " + q.name + " " + q.totalpurchase + " " + q.order_id + " " + q.description + "\r\n";
var Q2 = 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 (var q in Q1)
textBox3.Text += q.id + " " + q.name + " " + q.totalpurchase + " " + q.order_id + " " + q.description + "\r\n";
// dataGridView2.DataSource = Q2;
}
private void LeftOuterJoin_Click(object sender, EventArgs e)
{
string s = @"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();
DataTable dt = new DataTable("Table1");
dt.Load(new SqlCommand(s, con).ExecuteReader());
dataGridView2.DataSource = dt;
var Q1 = from c in ctx.Customers
join o in ctx.Orders on c.id equals o.customer_id
into x
from o in x.DefaultIfEmpty()
select new { c.id, c.name, c.totalpurchase, order_id = (o == null ? 0 : o.order_id),
description = (o == null ? " " : o.description) };
foreach (var q in Q1)
textBox2.Text += q.id + " " + q.name + " " + q.totalpurchase + " " + q.order_id + " " + q.description + "\r\n";
var Q3 = from c in ctx.Customers
join o in ctx.Orders on c.id equals o.customer_id
into x
select new { c.id, c.name, c.totalpurchase, o = x };
foreach (var q in Q3)
{
if (q.o.Count()>0)
foreach (var q2 in q.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";
}
var Q4 = ctx.Customers.GroupJoin(ctx.Orders, c => c.id, o => o.customer_id,
(c, o) => new { c, o });
foreach (var q in Q4)
{
if (q.o.Count() > 0)
foreach (var q2 in q.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";
}
}
}
}