VIBlend
Home Products Downloads Purchase Support Forum About Blog

WPF DataGrid - data binding to SQL Server Database

by viblend 21. December 2010 19:23

One of the most common data binding scenarios is to bind a DataGrid to a SQL Server Database and to load records from a specified data table. In this post, we will explain you how to set up basic data binding in VIBlend WPF DataGrid to the Customer table of the AdventureWorksLT Database.

So the first step would be to create a new WPF Application Project and then create a new instance of the DataGrid via drag and drop from the toolbox or create it programmatically. The DataGrid fields that will represent data source fields are created in XAML markup. Below you will see that we have very simple XAML for our WPF application which adds a DataGrid to the window

       <viblendDataGrid:DataGrid ItemsSource="{Binding Customer}" Name="dataGrid1" Margin="10">
            <viblendDataGrid:DataGrid.BoundFields>
                <viblendDataGrid:BoundField Text="First Name" DataField="FirstName" Width="70"/>
                <viblendDataGrid:BoundField Text="Last Name" DataField="LastName" Width="70"/>
                <viblendDataGrid:BoundField Text="Company Name" DataField="CompanyName" Width="150"/>
                <viblendDataGrid:BoundField Text="Phone" DataField="Phone" Width="100"/>
                <viblendDataGrid:BoundField Text="Email Address" DataField="EmailAddress" Width="100"/>
            </viblendDataGrid:DataGrid.BoundFields>
        </viblendDataGrid:DataGrid>

 Next, create a new AdventureWorksLTDataSet. In order to create it, do the following: Select Data->Add New DataSource->DataBase->DataSet. Add a connection to the AdventureWorksLT Database and Click the 'Next' button. Then expand the 'Tables' tree node, select Customer and click the 'Finish' button.



Note: The AdventureWorksLT Database is installed with the installation of the SQL Server. However, you can also download and install it from http://sqlserversamples.codeplex.com 
Once the Data Source is configured, create new instances of the AdventureWorksLTDataSet data set. and CustomerTableAdapter , then fill the Customer table with data. Finally, bind the VIBlend DataGrid for WPF to the Customer table.

C#

   public partial class MainWindow : Window
    {
        AdventureWorksLTDataSet dataSet = new AdventureWorksLTDataSet();
        AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter adapter = new AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter();
   
        public MainWindow()
        {
            InitializeComponent();
            adapter.Fill(dataSet.Customer);
            this.dataGrid1.DataContext = this;
        }

        public WpfApplication2.AdventureWorksLTDataSet.CustomerDataTable Customer
        {
            get
            {
                return this.dataSet.Customer;
            }
        }
    }

VB .NET

   Partial Public Class MainWindow
       Inherits Window
        Private dataSet As New AdventureWorksLTDataSet()
        Private adapter As New AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter()

        Public Sub New()
            InitializeComponent()
            adapter.Fill(dataSet.Customer)
            Me.dataGrid1.DataContext = Me
        End Sub

        Public ReadOnly Property Customer() As WpfApplication2.AdventureWorksLTDataSet.CustomerDataTable
            Get
                Return Me.dataSet.Customer
            End Get
        End Property
   End Class

 

Using VIBlend DataGridView for WinForms in Virtual Mode

by viblend 7. July 2009 18:35

VIBlend DataGridView for WinForms allows you to set the grid cells' source to Virtual mode. There are two steps to setup Virtual mode:

1. Set the cells' data source to Virtual. You can do that for a specific cell as well as for all cells under a row or a column:

  vDataGridView1.CellsArea.SetCellDataSource(column, GridCellDataSource.Virtual);

2. Implement a GridCellValueNeeded event handler. Whenever the data grid renders a cell it will raise the GridCellValueNeeded event:

    vDataGridView1.GridCellValueNeeded += new vDataGridView.GridCellValueNeededEventHandler(DataGridView1_GridCellValueNeeded);

    void DataGridView1_GridCellValueNeeded(object sender, GridCellValueNeededEventArgs args)

    {
      args.Value = "Some text";

     }

Here's a complete example which creates a list of 50,000 rows and sets up the grid in virtual mode:

using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using VIBlend.WinForms.Controls;
using VIBlend.WinForms.DataGridView;
using VIBlend.Utilities;

namespace VirtualModeDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        public class EmployeeSales
        {
            public EmployeeSales(string Name, DateTime Date, int ProductId, int Quantity)
            {
                this.Name = Name;
                this.Date = Date;
                this.ProductId = ProductId;
                this.Quantity = Quantity;
            }

            #region Private Members
            private string _name;
            private DateTime _date;
            private int _productid;
            private int _quantity;
            #endregion

            #region Properties
            public string Name
            {
                get { return _name; }
                set { _name = value; }
            }

            public DateTime Date
            {
                get { return _date; }
                set { _date = value; }
            }

            public string ProductName
            {
                get { return productNames[_productid, 0]; }

            }

            public double SalesAmount
            {
                get { return (double)_quantity * UnitPrice; }
            }

            public double UnitPrice
            {
                get
                {
                    double unitPrice = double.Parse(productNames[_productid, 1]);
                    return unitPrice;
                }
            }

            public int Quantity
            {
                get { return _quantity; }
                set { _quantity = value; }
            }

            public int ProductId
            {
                get { return _productid; }
                set { _productid = value; }
            }

            #endregion
        }


        // Implement the cell value needed event handler
        void DataGridView1_GridCellValueNeeded(object sender, GridCellValueNeededEventArgs args)
        {
            int rowIndex = int.Parse(args.RowItem.Caption) - 1;

            if (args.ColumnItem.Caption == "Employee Name")
                args.CellValue = lst[rowIndex].Name;
            else if (args.ColumnItem.Caption == "Transaction Date")
                args.CellValue = lst[rowIndex].Date;
            else if (args.ColumnItem.Caption == "Product Name")
                args.CellValue = lst[rowIndex].ProductName;
            else if (args.ColumnItem.Caption == "Quantity")
                args.CellValue = lst[rowIndex].Quantity;
            else if (args.ColumnItem.Caption == "Unit Price")
                args.CellValue = lst[rowIndex].UnitPrice;
            else if (args.ColumnItem.Caption == "Transaction Amount")
                args.CellValue = lst[rowIndex].SalesAmount;
        }

        #region Data
        string[] firstNames = new string[]
                {
                    "Andrew",
                    "Nancy",
                    "Shelley",
                    "Regina",
                    "Yoshi",
                    "Antoni",
                    "Mayumi",
                    "Ian",
                    "Peter",
                    "Lars",
                    "Petra",
                    "Martin",
                    "Sven",
                    "Elio",
                    "Beate",
                    "Cheryl",
                    "Michael",
                    "Guylène"
                };

        string[] lastNames = new string[]
                {
                    "Fuller",
                    "Davolio",
                    "Burke",
                    "Murphy",
                    "Nagase",
                    "Saavedra",
                    "Ohno",
                    "Devling",
                    "Wilson",
                    "Peterson",
                    "Winkler",
                    "Bein",
                    "Petersen",
                    "Rossi",
                    "Vileid",
                    "Saylor",
                    "Björn",
                    "Nodier"
                };

        static string[,] productNames = new string[,]
                {
                    {"Black Tea", "1.95"},
                    {"Green Tea", "1.95"},
                    {"Caffe Espresso", "1.45"},
                    {"Doubleshot Espresso", "1.75"},
                    {"Caffe Latte", "2.25"},
                    {"White Chocolate Mocha", "2.35"},
                    {"Cramel Latte", "2.35"},
                    {"Caffe Americano", "1.65"},
                    {"Cappuccino", "2.10"},
                    {"Espresso Truffle", "2.45"},
                    {"Espresso con Panna", "1.81"},
                    {"Peppermint Mocha Twist", "1.99"}
                };
        #endregion

        List<EmployeeSales> lst = new List<EmployeeSales>();

        private void Form1_Load(object sender, EventArgs e)
        {
            vDataGridView1.RowsHierarchy.AllowResize = true;
            vDataGridView1.RowsHierarchy.AllowDragDrop = false;
           
            vDataGridView1.ColumnsHierarchy.AllowResize = true;
            vDataGridView1.ColumnsHierarchy.AllowDragDrop = false;

            vDataGridView1.SelectionMode = vDataGridView.SELECTION_MODE.FULL_ROW_SELECT;
            vDataGridView1.MultipleSelectionEnabled = true;

            vDataGridView1.GridCellValueNeeded += new vDataGridView.GridCellValueNeededEventHandler(DataGridView1_GridCellValueNeeded);

            const int rowsToLoad = 50000;

            // Generate test data      
            Random rand = new Random();

            for (int i = 0; i < rowsToLoad; i++)
            {
                int productId = rand.Next(0, productNames.Length / 2 - 1);
                int quantity = rand.Next(1, 5);
                lst.Add(new EmployeeSales(
                    string.Format("{0} {1}", firstNames[rand.Next(0, firstNames.Length - 1)], lastNames[rand.Next(0, lastNames.Length - 1)]),
                    DateTime.Now.AddDays(-rand.Next(10, 100)),
                    productId,
                    quantity)
                    );
            }


            BindGridVirtualMode();
        }

        private void BindGridVirtualMode()
        {

            Cursor.Current = Cursors.WaitCursor;

            vDataGridView1.RowsHierarchy.Items.Clear();
            vDataGridView1.RowsHierarchy.SummaryItems.Clear();
            vDataGridView1.ColumnsHierarchy.Items.Clear();
            vDataGridView1.ColumnsHierarchy.SummaryItems.Clear();

            vDataGridView1.CellsArea.CellFormatting.ClearFormatProviders();
            vDataGridView1.CellsArea.CellFormatting.ClearFormatSettings();
            vDataGridView1.CellsArea.CellFormatting.SetFormatter("defaultNumberFormatter", null, "{0:#,###.####}");

            #region Prepare the grid columns
            vDataGridView1.ColumnsHierarchy.Fixed = true;

            vDataGridView1.ColumnsHierarchy.Items.Add("Employee Name");
            vDataGridView1.ColumnsHierarchy.Items.Add("Transaction Date");
            vDataGridView1.ColumnsHierarchy.Items.Add("Product Name");
            vDataGridView1.ColumnsHierarchy.Items.Add("Quantity");
            vDataGridView1.ColumnsHierarchy.Items.Add("Unit Price");
            vDataGridView1.ColumnsHierarchy.Items.Add("Transaction Amount");

            // Set the cells data source to Virtual for all columns
            foreach (HierarchyItem column in vDataGridView1.ColumnsHierarchy.Items)
                vDataGridView1.CellsArea.SetCellDataSource(column, GridCellDataSource.Virtual);

            vDataGridView1.CellsArea.CellFormatting.ClearFormatProviders();
            vDataGridView1.CellsArea.CellFormatting.ClearFormatSettings();


            vDataGridView1.CellsArea.CellFormatting.SetFormatter("dateFormatter", new System.Globalization.DateTimeFormatInfo(), "{0:dd-MMM-yyyy}");
            vDataGridView1.CellsArea.CellFormatting.SetFormatter("dollarFormatter", null, "${0:#.##}");

            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[1], "dateFormatter");
            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[4], "dollarFormatter");
            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[5], "dollarFormatter");

            vDataGridView1.ColumnsHierarchy.AutoResize();
            #endregion

            #region Prepare the grid rows
            for (int i = 0; i < lst.Count; i++)
                vDataGridView1.RowsHierarchy.Items.Add((i + 1).ToString());

            vDataGridView1.RowsHierarchy.SetColumnWidth(0, 30);
            vDataGridView1.RowsHierarchy.Fixed = true;
            #endregion

            vDataGridView1.Refresh();

            Cursor.Current = Cursors.Default;

        }
    }
}

About the author

Some text that describes me

Tag cloud

Recent comments

Comment RSS
Copyright © 2011 VIBlend  
ALL RIGHTS RESERVED  
 
Terms of Use | Privacy Policy
WinForms Controls Purchase Online About Us
       
DataGrid Navigation Pane Technical Support Blog
ScrollBar TreeView
ListBox ProgressBar Downloads Register
ComboBox Buttons
TabControl Editors Documentation Client Login

VIBlend Blog is powered by BlogEngine.NET