A DataGridView with a Database as the DataSource

From IronPython Cookbook

This example was inspired by this Chinese Blog Entry, but has been heavily adapted.

It is an example of Windows Forms databinding, using a DataGridView. With the example you can connect to any database (by specifying a connection string) and execute an SQL query. The results of the query are used to populate the DataGridView.

Before executing the query, the form will look like:

An Unpopulated DataGridView

In order to use this example, you need access to a database. In order to create this example, I installed MySQL Community Server. I went through the tutorial and created a new database called 'example' with a table called 'pet'. I populated the database with the data from this example.

I created a user called 'test', with a password 'testpass', by executing the following commands in the mysql console:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'test'@'localhost' IDENTIFIED BY 'testpass';
flush privileges;

As well as installing MySQL I also needed to install the Windows ODBC driver.

This example uses the ODBC classes, from this example. Particularly notice the (short) setDataSource at the end of the example. This is the code that executes the query and sets the result in the grid - the rest of the code is just user interface boilerplate!

import clr
clr.AddReference('System.Windows.Forms')
clr.AddReference('System.Drawing')
clr.AddReference('System.Data')

from System.Windows.Forms import (
    Form, Panel, Label,
    TextBox, DockStyle, Button,
    ScrollBars, Application, DataGridView,
    DataGridViewColumnHeadersHeightSizeMode,
    MessageBox, MessageBoxButtons,
    MessageBoxIcon
)

from System.Drawing import (
    Point, Size,
    Font, FontStyle,
    GraphicsUnit
)

from System.Data import DataSet
from System.Data.Odbc import OdbcConnection, OdbcDataAdapter

conString = """\
DRIVER={MySQL ODBC 3.51 Driver};
SERVER=localhost;
DATABASE=example;
USER=test;
PASSWORD=testpass;
OPTION=3;
""".replace('\n', '\r\n')

query = "select * from pet"

class DataGridViewQueryForm(Form):

        def __init__(self):
            self.Text = 'DataGridView Query Form'
            self.ClientSize = Size(642, 255)
            self.MinimumSize = Size(500, 200)
            
            self.setupDataGridView()
            self.setupQueryPanel()
        
        
        def setupDataGridView(self):            
            self._dataGridView1 = DataGridView()
            self._dataGridView1.AllowUserToOrderColumns = True
            self._dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            self._dataGridView1.Dock = DockStyle.Fill
            self._dataGridView1.Location = Point(0, 111)
            self._dataGridView1.Size = Size(506, 273)
            self._dataGridView1.TabIndex = 3
            
            self.Controls.Add(self._dataGridView1)
        
        
        def setupQueryPanel(self):
            self._panel1 = Panel()
            self._textBox1 = TextBox()
            self._label1 = Label()
            self._label2 = Label()
            self._textBox2 = TextBox()
            self._button1 = Button()
            
            self._panel1.Controls.Add(self._button1)
            self._panel1.Controls.Add(self._textBox2)
            self._panel1.Controls.Add(self._label2)
            self._panel1.Controls.Add(self._label1)
            self._panel1.Controls.Add(self._textBox1)
            self._panel1.Dock = DockStyle.Top
            self._panel1.Location = Point(0, 0)
            
            self._textBox1.Location = Point(12, 36)
            self._textBox1.Multiline = True
            self._textBox1.ScrollBars = ScrollBars.Vertical
            self._textBox1.Size = Size(247, 60)
            self._textBox1.TabIndex = 0
            self._textBox1.Text = conString
            
            self._label1.AutoSize = True
            self._label1.Font = Font('Verdana', 9.75, FontStyle.Bold, GraphicsUnit.Point, 0)
            self._label1.Location = Point(13, 13)
            self._label1.Size = Size(137, 16)
            self._label1.Text = 'Connection String'
            
            self._label2.AutoSize = True
            self._label2.Font = Font('Verdana', 9.75, FontStyle.Bold, GraphicsUnit.Point, 0)
            self._label2.Location = Point(315, 13)
            self._label2.Size = Size(52, 16)
            self._label2.Text = 'Query'
            
            self._textBox2.Location = Point(318, 36)
            self._textBox2.Size = Size(159, 20)
            self._textBox2.TabIndex = 1
            self._textBox2.Text = query
            
            self._button1.Font = Font('Verdana', 9.75, FontStyle.Bold, GraphicsUnit.Point, 0)
            self._button1.Location = Point(318, 72)
            self._button1.Size = Size(75, 23)
            self._button1.TabIndex = 2
            self._button1.Text = 'Execute'
            self._button1.UseVisualStyleBackColor = True
            self._button1.Click += self.setDataSource
            
            self.Controls.Add(self._panel1)


        def setDataSource(self, sender, event):
            query = self._textBox2.Text
            conString = self._textBox1.Text
            connection = OdbcConnection(conString)
            try:
                sa = OdbcDataAdapter(query, connection)
                ds = DataSet()
                sa.Fill(ds)
                self._dataGridView1.DataSource = ds.Tables[0]
            except Exception, e:
                MessageBox.Show(
                    'Failed to Execute Query: Error was %s' % (e,),
                    "Failed to Execute Query",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error
                )


Application.Run(DataGridViewQueryForm())

The connection string format for MySQL is:

conString = """\
DRIVER={MySQL ODBC 3.51 Driver};
SERVER=localhost;
DATABASE=example;
USER=test;
PASSWORD=testpass;
OPTION=3;
"""

A populated grid looks like:


A Populated DataGridView

If an error occurs whilst executing the query, a message box will pop up with the error message. It will look something like:

A Failed Query


Back to Contents.

TOOLBOX
LANGUAGES