Click here to Skip to main content
12,886,833 members (36,498 online)
Click here to Skip to main content

Stats

1.2M views
24.2K downloads
176 bookmarked
Posted 4 Jun 2008

Simple LINQ to SQL in C#

, 4 Jun 2008 CPOL
This article provides an introduction to employing LINQ to SQL within a Windows Forms application. The article will address the incorporation of LINQ to SQL into a WinForms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored proced
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;



namespace L2S_Northwind
{

    /// <summary>
    /// This class is used to demonstrate each of the
    /// queries defined in the accessor class
    /// </summary>
    public partial class frmMain : Form
    {
        // used to support take/skip example
        private int OrderPosition;



#region Constructor

        public frmMain()
        {
            InitializeComponent();

            // set order position to zero
            OrderPosition = 0;
        }

#endregion



#region  Full Table Requests

        private void employeesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Employee> emp = Accessor.GetEmployeeTable();
            dataGridView1.DataSource = emp;
        }

        private void shippersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Shipper> ship = Accessor.GetShipperTable();
            dataGridView1.DataSource = ship;
        }

        private void ordersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Order> orders = Accessor.GetOrderTable();
            dataGridView1.DataSource = orders;
        }

        private void employeeTerritoryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<EmployeeTerritory> empTerrs = Accessor.GetEmployeeTerritoryTable();
            dataGridView1.DataSource = empTerrs;
        }

        private void territoryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Territory> terrs = Accessor.GetTerritoryTable();
            dataGridView1.DataSource = terrs;
        }

        private void regionToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Region> regs = Accessor.GetRegionTable();
            dataGridView1.DataSource = regs;
        }

        private void customerToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Customer> cust = Accessor.GetCustomerTable();
            dataGridView1.DataSource = cust;
        }

        private void customerDemoToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<CustomerCustomerDemo> custdemo = Accessor.GetCustomerDemoTable();
            dataGridView1.DataSource = custdemo;
        }

        private void customerDemographicToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<CustomerDemographic> custdemograph = Accessor.GetCustomerDemographicTable();
            dataGridView1.DataSource = custdemograph;
        }


        private void orderDetailsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Order_Detail> ordDetails = Accessor.GetOrderDetailsTable();
            dataGridView1.DataSource = ordDetails;
        }

        private void productToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Product> prods = Accessor.GetProductTable();
            dataGridView1.DataSource = prods;
        }

        private void supplierProductToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Supplier> prods = Accessor.GetSupplierTable();
            dataGridView1.DataSource = prods;
        }

        private void categoToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Data.Linq.Table<Category> cats = Accessor.GetCategoryTable();
            dataGridView1.DataSource = cats;
        }

#endregion



#region Queries


        /// <summary>
        /// Find and display an employee by 
        /// the employee's ID
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void employeeByIDToolStripMenuItem_Click(object sender, EventArgs e)
        {

            Employee emp = Accessor.GetEmployeeById(1);

            StringBuilder sb = new StringBuilder();
            sb.Append("Employee 1: " + Environment.NewLine);
            sb.Append("Name: " + emp.FirstName + " " + emp.LastName + Environment.NewLine);
            sb.Append("Hire Date: " + emp.HireDate + Environment.NewLine);
            sb.Append("Home Phone: " + emp.HomePhone + Environment.NewLine);

            MessageBox.Show(sb.ToString(), "Employee ID Search");
        }


        /// <summary>
        /// Gets an Order by the order ID and
        /// displays information about the first
        /// single matching order.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void orderByIDToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Order ord = Accessor.GetOrderById(10248);

            StringBuilder sb = new StringBuilder();
            sb.Append("Order: " + Environment.NewLine);
            sb.Append("Order ID: " + ord.OrderID + Environment.NewLine);
            sb.Append("Date Shipped: " + ord.ShippedDate + Environment.NewLine);
            sb.Append("Shipping Address: " + ord.ShipAddress + Environment.NewLine);
            sb.Append("         City: " + ord.ShipCity + Environment.NewLine);
            sb.Append("         Region: " + ord.ShipRegion + Environment.NewLine);
            sb.Append("         Country: " + ord.ShipCountry + Environment.NewLine);
            sb.Append("         Postal Code: " + ord.ShipPostalCode + Environment.NewLine);
            sb.Append("Shipping Name: " + ord.ShipName + Environment.NewLine);

            MessageBox.Show(sb.ToString(), "Shipping Information");
        }



        /// <summary>
        /// Displays a list of employeess ordered by
        /// their dates of hire
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void employeesByHireDateToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Employee> emps = Accessor.GetEmployeesByHireDate();
            dataGridView1.DataSource = emps;
        }


        /// <summary>
        /// Displays all orders that match
        /// on Order ID
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersByIdToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Order> orders = Accessor.GetOrdersById(10248);
            dataGridView1.DataSource = orders;
        }

        /// <summary>
        /// Returns values based on joining the Order and
        /// Order_Details tables
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersAndDetailsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Accessor.OrdersAndDetailsResult> oad = Accessor.OrdersAndDetails();
            dataGridView1.DataSource = oad;
        }


        /// <summary>
        /// Query across entity set
        /// This example collections information from the orders table
        /// and the order_details table through the orders table
        /// entity reference to orders_details.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersAndDetailsEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Accessor.OrderandPricingResult> opr = Accessor.GetOrderAndPricingInformation();
            dataGridView1.DataSource = opr;
        }



        /// <summary>
        /// Retrieves values across an entity set to 
        /// display both order and pricing information 
        /// by filtering for an order ID
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Accessor.OrderandPricingResult> opr = Accessor.GetOrderAndPricingInformationByOrderId(10248);
            dataGridView1.DataSource = opr;
        }



        /// <summary>
        /// Displays to total dollar value of the selected order
        /// by multiplying each order product's unit cost by
        /// the units ordered, and then summing the total of each
        /// individual cost.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void orderValueByOrderIDToolStripMenuItem_Click(object sender, EventArgs e)
        {
            // get the dollar value
            decimal? d = Accessor.GetOrderValueByOrderId(10248);

            // convert the decimal value to currency
            string dollarValue = string.Format("{0:c}", d);
            
            // display the dollar value
            MessageBox.Show("The total dollar value of order 10248 is " +
                dollarValue, "Order 10248 Value");
        }



        /// <summary>
        /// Displays the top five orders in the order table
        /// on first selection and then increments up by
        /// five orders to show the list five orders
        /// at a time
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void getTopFiveOrdersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                // get the top five orders starting at the current position
                List<Order> ords = Accessor.GetTopFiveOrdersById(OrderPosition);
                dataGridView1.DataSource = ords;

                // increment the formwide variable used to
                // keep track of the position within the 
                // list of orders
                OrderPosition += 5;

                // change the text in the menu strip item
                // to show that it will retrieve the next
                // five values after the current position 
                // of th last value shown in the grid
                getTopFiveOrdersToolStripMenuItem.Text = "Get Next Five Orders";
            }
            catch
            {
                MessageBox.Show("Cannot increment an higher, starting list over.");
                OrderPosition = 0;
            }
        }

#endregion



#region Insert Update Delete


        private void insertOrUpdateCustomerToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                Accessor.InsertOrUpdateCustomer("AAAAA", "BXSW", "Mookie Carbunkle", "Chieftain",
                    "122 North Main Street", "Wamucka", "DC", "78888", "USA", "244-233-8977",
                    "244-438-2933");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

        private void deleteCustomerToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                Accessor.DeleteCustomer("AAAAA");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }


#endregion



#region Stored Procedures

        /// <summary>
        /// Execute stored procedure: Sales By Year
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void salesByYearToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DateTime start = new DateTime(1990, 1, 1);
            DateTime end = new DateTime(2000, 1, 1);

            List<Sales_by_YearResult> result = Accessor.SalesByYear(start, end);
            dataGridView1.DataSource = result;
        }



        /// <summary>
        /// Execute stored procedure: Ten Most Expensive Products
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tenMostExpensiveProductsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Ten_Most_Expensive_ProductsResult> result = Accessor.TenMostExpensiveProducts();
            dataGridView1.DataSource = result;
        }


#endregion


#region Housekeeping

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

#endregion

    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

salysle
Software Developer (Senior)
United States United States
No Biography provided

You may also be interested in...

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 4 Jun 2008
Article Copyright 2008 by salysle
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid