Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Datasets are the basis of most of the drag-drop designer tools in Visual Studio. The dataset acts like an in-memory disconnected representation of a database. As you can imagine, creating a dataset by hand is a tedious process. Fortunately for us, Visual Studio contains a tool for generating datasets. These types of Datasets are known as strongly typed datasets in some circles. The benefit of using the tool is it quickly creates all of the Dataset parts. The benefit of having a strongly typed Dataset is that all the columns are correctly named and typed, so a column in the database that contains a double value will have a column in the dataset of type double.
I'm inside Visual Studio, and I've opened a Solution called CreatingDataSets, which contains one project. This project contains a Windows Form with three buttons and a list Box. I'm going to ask Visual Studio to create a typed dataset for me by going to my project and adding a database. I'm going to choose add an existing item, and then I'm going to go to my Desktop open my Exercise Files folder and scroll down to the Assets section.
You need to be sure that your filter inside this dialog is set to All Files, so that you can see the database file. This is the database file I'm looking for, northwnd.mdf; I'm going to click on Add. Now, Visual Studio recognizes that it can generate a dataset from this database, so it asks me, would I like to create a dataset or an Entity Data model. I'm going to create the simpler of the two, which is the dataset, and then I want to click on Next.
It just went out and talked to that database and has retrieved the list of all of the resources that are available in that database. Here is the listing of the tables. Within the tables, I can see things like the different column names. There is also a section that shows the Stored Procedures inside that database. I'm just going to take a couple of tables from the database. I'm going to take the Products table and the Shippers table, and I'm going to leave the dataset name the same.
Next, I'm going to click on Finish. Now, inside my app.config file, that wizard generated this connectionString. I can then use this connectionString to connect to the database. In fact, all I need to do is talk to the dataset, and it automatically will connect and talk to the database. Let me show you what I'm talking about. I'm going to go to this Fill DataSet button and double-click on it.
I'm going to declare an instance of the products table by typing "var products", the name of my dataset, which was "northwndDataSet.ProductsDataTable". This is the line of code where I'm going to create the TableAdapter. And as you can see there is a ProductsTableAdapter and a ShippersTableAdapter, I'm working with the products, so I am going to choose this one here.
The TableAdapters job is to talk to the database. This is where it does the Select queries, the Delete queries, the Update queries, and the Insert queries. So next, I'm going to ask the TableAdapter to fill the products table. So I say ta.Fill and then I pass in the table that I want to fill, which in my case would be that instance of the products class.
And then I'll tell my listBox to use the ProductName when it's showing the data. And lastly, I need to make sure that I fill my listBox. My listBox is called listBox1. I choose DataSource =, and then I'm going to assign the products table. Now, I see, I forgot to put a set of parentheses after this new line.
It's nice to have IntelliSense. I'll press F5 to run the application and Y to dismiss that dialog, and I'll click on the Fill DataSet, and I'm now using that strongly typed dataset. You can see down, here in this example, I can also use the strongly typed access. The product has a UnitPrice property on it, which I can use to set or retrieve the unit price of a particular product. But I'd rather show you for the rest of the demo is how to create my own extra query against the database.
So I'm going to open this XSD file that was created for me when I generated the dataset. This contains a designer surface for making changes to my dataset. I'll double-click on this file, and here you can see the Products table and the Shippers table, and then at the bottom you see the ProductsTableAdapter. Remember, this is what gets the data out of the database and puts it into the products table, and takes the changes of the data and puts it back into the data source. So I'm going to come down here on the bottom, and I'm going to add a new query.
I like to choose SQL statements, a SELECT query. It's already stubbed in the code for a basic query. And I'm just going to come down here and say, Where UnitPrice > some parameter value @price. So now this is a parameterized query. I'll pass this piece of information in, and it will return all the products that are greater than this price.
I'll click on Next. I'm going to call my method that is going to be on my TableAdapter GetDataByPrice, and I'll click on Next and Finish. Notice there is a new row here now. There is a GetDataByPrice. So now I'll go back to my form, and I'll go to this Fill Products by Price, and I just need to write two lines of code. var ta = new.
I need that same TableAdapter, and now that TableAdapter has my new query on it, so I'm going to say listBox. DataSource = ta.GetDataByPrice. And watch what happens when I hit the open parenthesis. Over here it says I need a price parameter to give to the query, so I'm going to use 40M--M for decimal--and then I need to uncomment this line of code and this line of code.
Here is a little trick. I'm going to highlight all four lines and then do Ctrl+K, Ctrl+U. Press F5 to run the application, choose to save the data, and there are all the products that are greater than $40. Now you have the general idea how to create a dataset. You still need to write code to work with the data, however. Watch the Binding movie in this chapter to see how to use the drag and drop designer to quickly set up data bindings in your user interface.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 103154 Viewers
61 Video lessons · 89835 Viewers
71 Video lessons · 73452 Viewers
56 Video lessons · 105000 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.