Experiment 5
Database and LINQ
1, Objective
1) ADO.NET
2) LINQ to SQL and LINQ to Entities
3) Data binding
2, Contents
(Copy the results or source codes after each exercise. Rename this document as “Id – Name.doc” and hand in it onto yvsou.com. )
1) Create an application which query a database with ADO.NET
A. GUI design: add three label, a combobox, a textbox, a button and a DataGridView to the form
B. Add OleDbConnection to the form and connect to it.
The access database is provided as a file named Northwind-EN.mdb. To connect to it, use the connection string as follows:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ Northwind-EN.mdb
C. Define a method GetAllGuests(). It retrieves all the data of table Customers and fill them in an instance of DataSet. When loading the form (Form1_Load method), call this method and assign the dataset to the data source of DataGridView, so that the grid view will display all the customers in database.
D. Define a method GetSelectedGuest() that return DataTable. It retrieves the customers whose value of column specified in Combobox equals to or likes the value specified in Textbox.
E. When clicking a line of DataGridView, handle the event of CellClick to query the table Orders to retrieve the number of orders related to the specified customer.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace exp5
{
public partial class Form1 : Form
{
OleDbConnection conn;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Northwind-EN.mdb");
conn.Open();
//label3.Text += conn.State == ConnectionState.Open;
//conn.Close();
}
public void GetAllGuests()
{
string str = "select * form Customers";
OleDbDataAdapter da = new OleDbDataAdapter(str, conn);
DataSet ds = new DataSet();
da.Fill(ds,"Customers");
dataGridView1.DataSource = ds.Tables["Customers"];
}
public void GetSelectedGuest()
{
string item = comboBox1.Text;
string value = textBox1.Text;
string str = "select* from customers where " + item + " like %" + value + "%";
OleDbDataAdapter da = new OleDbDataAdapter(str, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
dataGridView1.DataSource = ds.Tables["Customers"];
}
}
}
2) Rewrite the application in 1) using LINQ to SQL or LINQ to Entities.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace NorthwindQuery
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
NorthwindEntities ne = new NorthwindEntities();
IEnumerable<Customers> data;
SqlConnection conn;
DataSet ds;
private void Form1_Load(object sender, EventArgs e)
{
GetAllGuests();
}
private void GetAllGuests()
{
ne.Customers.ToList();//数据从DB取到内存
data = ne.Customers.Local;
dataGridView1.DataSource = data;
}
private void button1_Click(object sender, EventArgs e)
{
string column = comboBox1.Text;
string value = textBox1.Text;
var result = from customer in ne.Customers
where customer.CustomerID == value
select customer;
data = result.ToArray();
dataGridView1.DataSource = data;
}
private DataTable GetSelectedGuests()
{
string column = comboBox1.Text;
string value = textBox1.Text;
string strSql = "SELECT * FROM Customers WHERE "
+ column + " like '" + value + "'";
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
ds.Tables.Remove("Customers");
da.Fill(ds, "Customers");
return ds.Tables["Customers"];
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
if (conn != null)
conn.Close();
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
int idx = e.RowIndex;
Customers c = data.ToArray()[idx];
var result = from order in ne.Orders
where order.CustomerID == c.CustomerID
select order;
label3.Text = "Has orders: " + result.Count();
/*
string id = (string)ds.Tables["Customers"].Rows[idx][0];
string strSql = "SELECT count(*) FROM Orders WHERE "
+ "CustomerID = '" + id + "'";
SqlCommand cmd = new SqlCommand(strSql, conn);
int count = (int)cmd.ExecuteScalar();
label3.Text = "Has orders: " + count;*/
}
}
}
3) In last two experiments, a sales system is created based on the pre-defined data or data in files. In this week, with database “Northwind-EN.mdb”, we will rewrite the system using LINQ to SQL or LINQ to Entities.
The customer can login the system with his customer id.
The user can modify any field values of a product. Click “Save” to update the data of this product.
When the user clicks “Save Cart”, a new record of table “Orders” will be inserted and all the data of cart will be stored in table “Order Details”.
After a customer login, the related order details are displayed in “Products in cart” area.
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Runtime.Serialization.Formatters.Binary;
namespace SalesSystem
{
public partial class Form1 : Form
{
SqlConnection conn;
string customerName;
int current = 0;
List<Product> products = new List<Product>();
SortedDictionary<Product, int> cart = new SortedDictionary<Product, int>();
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{//login
LoginForm login = new LoginForm();
if (login.ShowDialog(this) == DialogResult.OK )
{
string name = login.textBox1.Text;
string strSql = "SELECT count(*) FROM Customers WHERE "
+ "CustomerID = '" + name + "'";
SqlCommand cmd = new SqlCommand(strSql, conn);
int count = (int)cmd.ExecuteScalar();
if (count <= 0)
{
MessageBox.Show("No customer is named " + name);
return;
}
customerName = name;
label1.Text = name + " logged in";
current = 0;
ReadCart();
DisplayProduct();
DisplayCart();
}
else
{
label1.Text = "Not logged in";
}
login.Dispose();
}
private void button2_Click(object sender, EventArgs e)
{//Previous
if (current > 0)
current--;
DisplayProduct();
}
private void button3_Click(object sender, EventArgs e)
{//Next
if (current < products.Count - 1)
current++;
DisplayProduct();
}
private void ReadCart()
{
string strSql = "SELECT * FROM Orders WHERE CustomerId = '" + customerName + "'";
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
List<int> ids = new List<int>();
while (dr.Read())
{
int orderid = (int)dr["OrderId"];
ids.Add(orderid);
if (ids.Count > 0)//get the first order only
break;
}
dr.Close();
foreach (int id in ids)
{
string strSql2 = "SELECT * FROM [Order Details] WHERE OrderId = " + id;
SqlCommand cmd2 = new SqlCommand(strSql2, conn);
SqlDataReader dr2 = cmd2.ExecuteReader();
while(dr2.Read())
{
int productid = (int)dr2["ProductID"];
var p = from prod in products
where prod.ID.Equals(productid.ToString())
select prod;
if (p.Count() > 0)
cart.Add(p.First(), (short)dr2["Quantity"]);
}
dr2.Close();
}
}
private void DisplayProduct()
{
if (current < 0 || current >= products.Count)
return;
Product product = products[current];
textBox1.Text = product.ID;
textBox2.Text = product.Name;
textBox3.Text = product.Supplier;
textBox4.Text = product.Category;
textBox5.Text = product.QuantityPerUnit;
textBox6.Text = product.UnitPrice.ToString();
textBox7.Text = product.UnitsInStock.ToString();
textBox8.Text = product.UnitsOnOrder.ToString();
textBox9.Text = product.ReorderLevel.ToString();
checkBox1.Checked = product.Discontinued;
}
private void DisplayCart()
{
string str = "";
foreach(var item in cart)
{
str += item.Key.Name;
str += " :\t";
str += item.Value;
str += "\n";
}
labelCart.Text = str;
}
private void Form1_Load(object sender, EventArgs e)
{
conn = new SqlConnection(
@"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True");
conn.Open();
ReadProducts();
}
private void ReadProducts()
{
string strSql = "SELECT * FROM Products";
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
Product product = new Product(
dr["ProductId"].ToString(),
(string)dr["ProductName"],
dr["SupplierID"].ToString(),
dr["CategoryID"].ToString(),
(string)dr["QuantityPerUnit"],
Convert.ToDecimal(dr["UnitPrice"]),
Convert.ToInt16(dr["UnitsInStock"]),
Convert.ToInt16(dr["UnitsOnOrder"]),
Convert.ToInt16(dr["ReorderLevel"]),
Convert.ToBoolean(dr["Discontinued"])
);
products.Add(product);
}
dr.Close();
}
private void button4_Click(object sender, EventArgs e)
{//add to cart
Product currentProduct = products[current];
if (!cart.ContainsKey(currentProduct))
cart.Add(currentProduct, 1);
else
cart[currentProduct]++;
DisplayCart();
}
private void button5_Click(object sender, EventArgs e)
{//checkout
decimal sum = 0;
foreach(var item in cart)
{
sum += item.Key.UnitPrice * item.Value;
}
MessageBox.Show("The total: " + sum, "Checkout");
}
private void button7_Click(object sender, EventArgs e)
{//save products
foreach(Product p in products)
{
string strSql = "UPDATE Products SET " +
" [ProductName] = '" + p.Name + "'," +
" [SupplierID] = " + p.Supplier +
" [CategoryID] = " + p.Category +
" [QuantityPerUnit] = '" + p.QuantityPerUnit + "'" +
" [UnitPrice] = " + p.UnitPrice +
" [UnitsInStock] = " + p.UnitsInStock +
" [UnitsOnOrder] = " + p.UnitsOnOrder +
" [ReorderLevel] = " + p.ReorderLevel +
" [Discontinued] = " + (p.Discontinued ? 1 : 0) +
" WHERE [ProductID] = " + p.ID ;
SqlCommand cmd = new SqlCommand(strSql, conn);
int count = cmd.ExecuteNonQuery();
if (count != 1)
throw new ApplicationException("Fail to update " + p.ID);
}
}
private void button8_Click(object sender, EventArgs e)
{//save cart
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
if (conn != null)
conn.Close();
}
private void button6_Click(object sender, EventArgs e)
{//save a product
if (current < 0 || current >= products.Count)
return;
Product product = products[current];
product.ID = textBox1.Text;
product.Name = textBox2.Text;
product.Supplier = textBox3.Text;
product.Category = textBox4.Text;
product.QuantityPerUnit = textBox5.Text;
product.UnitPrice = Convert.ToDecimal(textBox6.Text);
product.UnitsInStock = Convert.ToDouble(textBox7.Text);
product.UnitsOnOrder = Convert.ToDouble(textBox8.Text);
product.ReorderLevel = Convert.ToInt32(textBox9.Text);
product.Discontinued = checkBox1.Checked;
}
}
}