MorkaLork Development

Interesting stuff I've picked up over the years...

Using the DataGridView

2009-04-16 18:01:21 | 382 views | Data datagrid datagridview database data table data source fill

The Basics



This is a short example of how to use the DataGridView. More will be added to this subject in the future, but for now, I will create a small application that can connect to a database, select a table and display it using a datagridview.

The winform



Our application will look like this:

imagehttp://admin.morkalork.com/uploads/images/DGV2.png

The controls are as follow(the names will have to be self-explanatory):


We will use the MySql.Data namespace to connect to our MySql database. This will of course differ depending on what datamanger you are using, so feel free to alter that part.


Getting into it



The first thing we will have to do is create a connection string:


string connectionString = "Data source=" + txtHost.Text + ";"
+ "Initial Catalog=" + txtDatabase.Text + ";"
+ "Username=" + txtUsername.Text + ";"
+ "Password=" + txtPassword.Text + ";";


This will, as we can see, use all the login-input boxes and select a database for us.

Next, we will have to create a command:


string ourCommandText = "SELECT * FROM " + txtTable.Text + ";";



After that, we create a data adapter containing our connection string and command:


MySqlDataAdapter myDataAdapter;
try {
myDataAdapter = new MySqlDataAdapter(ourCommandText, connectionString);
} catch (MySqlException ex) {
txtOutput.AppendText("Error:\r\n" + ex);
return;
}


We use try...catch here since the program will crash if login fails(or, actually, throw an exception).

After we created a data adapter we use the SqlCommandBuilder class to connect to the database and enter our command.


MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);


The important stuff after that is to create a data table, use the data adapter Fill() method to set the table, and enter the table into the binding source:


DataTable myTable = new DataTable();

try {
myDataAdapter.Fill(myTable);
} catch (MySqlException ex) {
txtOutput.AppendText("Error:\r\n" + ex);
return;
}

dbBindingSource.DataSource = myTable;


With the bindingsource set, we feed it to the datagridview:


myDatagridview.DataSource = dbBindingSource;


And now, if the program is used, it ought to work, and look something like this:

imagehttp://admin.morkalork.com/uploads/images/DGV1.png


In the next page you can review the entire code for the project.
[breakpoint]

The Full Code




using System;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

namespace UsingDataGridView
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}

void BtnConnectClick(object sender, EventArgs e)
{
//Create a connectionstring to use with the dataadapter
string connectionString = "Data source=" + txtHost.Text + ";"
+ "Initial Catalog=" + txtDatabase.Text + ";"
+ "Username=" + txtUsername.Text + ";"
+ "Password=" + txtPassword.Text + ";";

//Create a commandstring, select * from table
string ourCommandText = "SELECT * FROM " + txtTable.Text + ";";

//Create a dataadapter object using the 4th overload
txtOutput.AppendText("Creating DataAdapter...\r\n");
MySqlDataAdapter myDataAdapter;
try {
myDataAdapter = new MySqlDataAdapter(ourCommandText, connectionString);
} catch (MySqlException ex) {
txtOutput.AppendText("Error:\r\n" + ex);
return;
}
txtOutput.AppendText("DataAdapter successfully created!\r\n");

//Since we're doing this the quick way...
txtOutput.AppendText("Creating CommandBuilder...\r\n");
MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);

//We're gonna use a datatable since it's a winform
txtOutput.AppendText("Creating DataTable...\r\n");
DataTable myTable = new DataTable();

//Set the culture info to culture-independent
txtOutput.AppendText("Setting CultureInfo..\r\n");
myTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

//Fill the datatable with what we read from the database
txtOutput.AppendText("Filling DataTable...\r\n");
try {
myDataAdapter.Fill(myTable);
} catch (MySqlException ex) {
txtOutput.AppendText("Error:\r\n" + ex);
return;
}
txtOutput.AppendText("DataTable successfully filled!\r\n");

//Set the bindingsource.datasource to the table with got from the database
txtOutput.AppendText("Setting the bindingsource DataSource...\r\n");
dbBindingSource.DataSource = myTable;

//Enter the data into the datagridview
txtOutput.AppendText("Setting the DataGridView DataSource...\r\n");
myDatagridview.DataSource = dbBindingSource;

}
}
}



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.