C # Tutorial - Binding DataGridView ke Database

Binding DataGridView ke Database

Dalam tutorial ini, saya akan menunjukkan cara yang cukup sederhana untuk mengikat DataGridView NET ke database. Bentuk desainer memiliki beberapa dukungan untuk data yang mengikat, tapi saya menemukan melakukannya tanpa bentuk desainer adalah sedikit lebih mudah untuk memahami dan melaksanakan.Juga, ketika saya sedang mengembangkan aplikasi database desktop, skema database saya jarang 100% didefinisikan, dan desainer bentuk tidak mudah mendukung perubahan ke database.

Database saya akan gunakan untuk contoh kode akan menjadi database Access. Aku tahu database Access adalah bukan tipe database pilihan untuk pengembang - karena kecepatan dan skalabilitas mereka. Namun, untuk aplikasi database sederhana, Access sulit untuk mengalahkan - karena Anda tidak perlu menginstal apapun di luar mesin database. Pada kenyataannya, konsep yang ditampilkan dalam tutorial ini dapat digunakan dengan sejumlah database.

The first thing we'll need to do is generate a connection string to connect to our Access database. For simplicity, the database I'm using doesn't require any authentication. If your database has authentication, MSDN has some great documentation on how to accomplish that.

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

The connection string is broken up into two parts, a provider and a data source. The provider is the engine we're going to be using - in this case, Microsoft's Jet engine. The data source, for Access, is simply the path to the database file.

Now let's use the connection string and get some data from our database.

//create the connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

//create the database query
string query = "SELECT * FROM MyTable";

//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//fill the DataTable
dAdapter.Fill(dTable);

To keep the code simple, I've left out a lot of error handling. You'll definitely want to surround dAdapter.Fill with some exception handling. That call will fail for many different reasons - for instance the database isn't where the connection string says it is, or the query string is invalid SQL code.

So let's go through what this code is actually doing. The first thing to do is to create the connection string as described above. Then we need an SQL statement to execute on our database. This can be any SELECT statement you want. Next we create anOleDbDataAdapter which serves as a bridge between our DataTable and our database. An OleDbCommandBuilder comes next. This beautiful object automatically generates SQL insert, update, and delete statements to rectify changes made to our DataTable. Next we need to make a DataTable to hold the information retrieved from the database. And lastly, we call dAdapter.Fill(dTable) which executes our SQL query and fills dTablewith the results.

Now that we have a DataTable filled with database information, let's see how to synchronize it with a DataGridView.

//the DataGridView
DataGridView dgView = new DataGridView();

//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();

//set the BindingSource DataSource
bSource.DataSource = dTable;

//set the DataGridView DataSource
dgView.DataSource = bSource;

The BindingSource object is what will be keeping our DataTable synchronized with theDataGridView. So we set the DataSource of the BindingSource to dTable, then set theDataSource of the DataGridView to the BindingSource. Now when your program runs, the DataGridView should be filled with the results of your SQL query.

At point, any changes made by the user in the DataGridView will automically be made to the DataTable, dTable. Now we need a way to get the changes back into the database. All you have to do is call the Update function of the OleDbDataAdapter with theDataTable as the argument to accomplish this.

dAdapter.Update(dTable);

This call will use the OleDbCommandBuilder to create all of the necessary SQL code to synchronize your database with the changes made to dTable. But, when should you call this function? There's lots of different answers to that. If you have a save button, call it when the user pushes save. If you want the database updated in real-time, I like to call it on the DataGridView's Validating event.

The last topic to discuss is error handling. What happens when the user types something in the DataGridView that can't be put in the database - like text where a number is supposed to go? Because you're using DataBinding, it would take a lot of work to get the data type at each column and manually make sure the user typed in the correct thing. Fortunately, the DataGridView has an event, DataError, to handle that for you. Whenever the user enters something incorrectly, this event will be fired. From this event you can get the row and column index of the cell where the incorrect value was placed, and you can also cancel the event so you don't attempt to update the database with bad data.

That's all the code required to perform two-way data binding with an Access database.