DataGridView

The DataGridView is a control designed to be bound to database tables. You will find the DataGridView in the Data group on your Toolbox. In this section, we will use the DataGridView to build a SQL interface for the Northwind Trader database. This application is surprisingly simple using the classes provided by ADO.NET.

Figure 15.5

To create the user interface, place a TextBox on the form and set its Multiline property to true. Then, add two buttons, setting their Text properties to Query and Update. Finally, place a DataGridView component on the form. Your form might look something like the one in Figure 15.5.
Since we are going to connect to a Microsoft Access database, we need to use the OleDb classes. At the top of the file, you should add the following using statement to gain access to the System.Data.OleDb namespace.

using System.Data.OleDb;

The Form’s Load event should create an OleDbConnection object, an OleDbCommand object, and a DataSet object. Each of these objects should be stored as members of your Form class.

private void Form1_Load(object sender, EventArgs e) {
   // connect to the database
   string conString =
      @"Provider=Microsoft.JET.OLEDB.4.0;"
      + @"data source=c:\data\Northwind.mdb";
 
   // create an open the connection          
   conn = new OleDbConnection(conString);
   command = conn.CreateCommand();
 
   // create the DataSet
   DataSet ds = new DataSet();
   }

Next, you need to implement the select button’s Click handler. The method should start by clearing the DataSource property of the DataViewGrid and creating a fresh DataSet. Then, it should reopen the connection. Set the Command object’s CommandText property to the Text from the TextBox control. Then, create an adapter with the command and fill the new DataSet. Finally, set the DataGridView’s DataSource property to the first table in the DataSet’s Table collection.

private void button1_Click(object sender, EventArgs e) {
   // clear the grids data source
   dataGridView1.DataSource = null;
 
   // create a new DataSet
   ds = new DataSet();
 
   // open the connection
   conn.Open();
 
   // run the query
   command.CommandText = textBox1.Text;
   adapter = new OleDbDataAdapter(command);
   adapter.Fill(ds);
    
   // close the connection
   conn.Close();
 
   // set the grid's data source
   dataGridView1.DataSource = ds.Tables[0];
   }

The last step is to implement the Click event handler for the Update button.

private void button2_Click(object sender, EventArgs e) {
   // clear the grids data source
   dataGridView1.DataSource = null;
 
   // open the connection
   conn.Open();
 
   // run the query
   command.CommandText = textBox1.Text;
 
   int affected = command.ExecuteNonQuery();
   MessageBox.Show ("There were " + affected + " rows affected");
 
   // close the connection
   conn.Close();
   }

The update button sets the DataGridView’s DataSource property to null to clear the grid. Then, it reopens the connection and sets the command object’s CommandText property to whatever text is in the TextBox. This time the command is executed by the ExecuteNonQuery method. The ExecuteNonQuery method runs a command that updates or deletes records. It returns the number of rows affected by the command. Finally, the method displays a message box showing the number of rows affected and then closes the connection. Figure 15.6 shows a SELECT statement being executed on the database.

Figure 15.6

Figure 15.7 shows the update button being used to change the ContactTitle from Guru to Fool.

Figure 15.7

To verify that our update actually worked, we can run the query again.

Figure 15.8