Data Access in C#, using Visual Studio

Download This Project

With Visual Studio (VS) we gain a powerful set of tools that make data access in C# a matter of drag-and-drop, click, click, click, and a couple lines of code (and by couple I mean 2). So we significantly reduce the typing we do in turn for what I like to call drop-and-plop coding (i.e. you do a few drag-and-drop operations, and the IDE plops out a bunch of code). Basically for this tutorial I will run through the steps you need to connect your MS Access data to a DataGrid (or just about any other VS component) and have it show you the information you want. I assume that you have some idea how to code, but maybe never used the VS IDE before, and that you already have a database with tables and information that you want to show in your application. We will also primarily assume that we're writing this for a windows applicaiton, not a web application. I also assume that we're using the default VS IDE layout, with the default keyboard layout.

So here we go, first things first, we want to connect to our database. On the right of the screen we see a tool bar with all the different components that we can drop into our application and just above this is a tab for another tool bar labeled "Server Explorer" (If it is not there or you can't seem to find it CTRL+ALT+S will bring it up). In here you see two items in the tree: Servers, and Data Connections. Right click on Data Connections and select "Add Connection..."

After it thinks about weather or not it likes you, it will open up a little window labeled "Data Link Properties" and show you a tab labeled "Connection" For an MS Access database, this is the wrong type of connection, so click on the "Provider" tab and select one of the "Microsoft JET" providers, and click on the "Next >>" button.

This takes us back to the connection tab, but now with new and improved different properties. Now we simply have to select the location of our access database by either typing in the path and name in the first text box, or pressing the button to the right of the first text box and navigating our way to it. For this example I will use the traditional Microsoft sample database Northwind (you can find it at: [Office Installation Directory]\SAMPLES\Northwind.mdb)

Whew, the hardest part is done. Now we're just 7 clicks and 3 lines of code away from getting this thing up and running.

You'll notice on the Server Explorer our database is now there, clicking on the tree we can navigate down to the tables, views, and stored procedures that exist in the table.

So we simply click on the table or view and drag it onto our form. The IDE will automatically create two objects for our form: a connection and a data adapter.

Right click on the data adapter and select "Generate Dataset...", a form will come up, but for what we're doing just click "OK".

Now we drag-and-drop a datagrid component onto our form...

... and in the properties window we select the datasource from the provided dropdownlist.

The last thing we need to do is decide the timing, by this I mean when in the life of our program do we want the data to be pulled into our datagrid? For this simple application we will do this during the form construction. So we open up the code page

... and find the constructor (if we haven't renamed anything it will be called Form1() by default) and we add the following three lines of code AFTER the call to "InitializeComponent" (again we're assuming we haven't renamed anything yet):

oleDbConnection1.Open();
oleDbDataAdapter1.Fill(dataSet11);
oleDbConnection1.Close();

Volia...we're done. Now just run the code, and you will see that it pulls up all the data for the table you selected. It also has generated the appropriate update, insert, and delete statements so you can add/edit/and delete information right on your form and it will be saved back to the database automatically, but you will also have to connect the timing into your code as well.

PS > Why are the forms yellow, well I downgraded the image quality and size so that they would load faster (went from 100k to 20k) and afterall do you really need colors to understand?

Last Updated: 15 April 2005 9:54 AM
Up | Tutorial Main | Home | email author