A DataGridView with a Database as the DataSource

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:



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:



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



Back to Contents.