DotNetWebTutor.com                                                    Link to Us

Databases

ADO.Net (Active Data Objects) gives you many classes to access and manipulate data

BindingSource-
Handles the communication between your program and the database

BindingNavigator Control
A toolstrip that has data navigation controls on it

DataGridView Control
Allows you to view or edit data in a spreadsheet format

Example

• Create a new project
• Add an access database to your project
• Select the data menu
• Select Add New DataSource

• Select the database icon and click Next

• Select New Connection

• If Microsoft Access Database File is not listed in the DataSource click the Change button

• Choose Microsoft Access
• Click OK

• Click Browse to find your database file

• Click OK

• Click the Next button

• Visual Studio ask you to copy the file into your project. Click Yes

• Next it asks you to save the connection string. Click next to save your connection in a connection string

• Select the your database table you want to access and click Finish

• It adds 3 files to your project
• App.config
• The database
• And XSD file representing your dataset

The database get copied in two places.

1. Your project folder
2. The debug folder

Changes made to the database will be made to the copy in your debug folder because that is where the executable is located

• Drag a DataGridView control (from toolbox) to the form

• Click on the smart tag

• Choose your database table as the datasource
• Drag a BindingNavigator to the form

• Change the BindingSource property to your database binding source\

• Test it
• Now you can navigate through your records and add and delete from it
• You can also edit inside the grid by double clicking inside a cell and changing it

This only makes changes in the dataset.
The dataset is just an in memory representation of your database

You have to add some more code to actually change the real database

• In the component tray select the dataset component and click on the smart tag

• Select Edit in Dataset Designer
• Right-click on the tableAdapter
• Select Configure

• Click Next

• Click Finish

• Add a button control
• Drag it on top of the BindingsNavigator Control
• Change the text to Update

=============Alternative========================

• Add the button to the BindingsNavigator control

Chose an image for the button which will be doing the update to the database
=============Alternative========================

• Add this code to the button-click event handler:

Try
Me.SurveyBindingSource.EndEdit()
Me.SurveyTableAdapter.Update(Me.SurveyDataSet.Survey)
MessageBox.Show("Database was updated")
Me.SurveyTableAdapter.Fill(Me.SurveyDataSet.Survey)
Catch ex As Exception
MessageBox.Show("Problem updating database")
End Try  

Explanation:
Me.SurveyBindingSource.EndEdit()
EndEdit tells the dataset you are done making changes

Me.SurveyTableAdapter.Update(Me.SurveyDataSet.Survey)
Update method of the tableAdapter accepts the survey table of the dataset and updates the actual Access database

Me.SurveyTableAdapter.Fill(Me.SurveyDataSet.Survey)
Finally, The fill method refreshes your database from the Access database. This is the same line that is in the page_load that originally  loads your dataGridView

 

When you test it and make changes and click Update you may notice that once you run it again your changes will not be made. Remember that When you added the database it added it to two places. In your project files and in the debug directory.

Every time you run your application from Visual Studio it recopies the database from your project to the debug, thus erasing any changes. To see the changes Open your project folder with Windows Explorer and go to the bin folder and then the debug. Run the EXE file and make changes. Close it and run it again and you will see your changes.