Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
ADO.NET is the core library for accessing data from relational and ODBC databases. There are a number of specialized namespaces, but for today's example, I'm going to use the SQL client classes. I am going to start by showing you some basic ADO.NET code. I'll show you how to connect to a database. We'll execute queries against the database using the command object, and then I'll show you how to store data in a local cache, known as a DataSet. I am inside of Visual Studio, and I've opened a solution called WorkingWithAdo. Inside that is a project that already contains a database called northwind.mdf, and it contains a form, Form1.cs.
If I open up this user interface, I'll see there are three buttons and one list box. I am going to start by showing you how to connect to the database. I'll do that by double-clicking on this Connect button, which will take me to my C# code. To get a little more screen real estate, I am going to close this output window on the bottom of the screen. Before you do anything with the database, you need to create a connection. I am going to use this called SqlConnection. I then need to provide a ConnectionString that explains all the details about where to find the database.
In my case, it's going to look in a folder called DataDirectory, and it's going to look for a file called northwind.mdf. To open the connection, I'll take my connection object and call open. To close the connection, I'll call the Close method. And then to verify that the connection is open, I am going to type in "MessageBox.Show". Then I'm going to ask the connection for its current state, like this line of code. And then I'll copy this message box code and paste it after the conn.Close.
I am going to press F5 to run the application, and then say yes to this dialog. Then if I did everything correctly, when I click on this Connect To DB, I see the Open dialog and then immediately after that I see the Closed dialog. Once you've opened the connection, you can instruct the database to manipulate the data. You're probably familiar with the four basic operations you can do in a database, sometimes called the CRUD operation: you can create data, read the data, update the data, or delete the data.
In SQL, we use the insert, select, update, and delete queries. So what I am going to do on this second button click procedure down here is I am going to open my connection, and then I am going to declare a cmd object. Next, I specify the CommandText that I want to send over to the database.
This will be my SQL text. So I'll say Select * from shippers, which is the table I am interested in. Then I am going to tell the command object to use the connection to talk to the database, like so. Now, the rest of this code, I am going to uncomment, Ctrl+K, Ctrl+U. This executes the reader against the database, which is going to return some rows.
I checked to see if there are any rows in the reader. If there are rows, then I call Read, which gives me the first piece of data, and then I call reader.GetSqlString and I go get the column with the number 1 from the data-- that's the first column of data--and I am going to add that to the ListBox. Then I have this while loop that continues to read until it's empty. Then I close the reader, and then down here I've got another query against a database. I am going to do an update query.
So, I have another command text what I am going to call as Update Shippers Set CompanyName, or shipper id, and then I am going to create a random number here, and I'll execute that query. I don't need to iterate over the results, because I'm inserting data, or updating the data into the database. I'll run this application, press F5, and there is our shipper data. For my last demo, I want to talk about the DataSet. Ado contains this class called the DataSet.
This acts like an in memory disconnected representation of your database. Like a database, a DataSet can contain data tables consisting of rows and columns. You can also define data relationships, constraints, and other database-like constructs, but remember that the data set is stored locally and is in memory only. So this last button's code down here is going to create the DataSet. So here, I declare a DataSet. Think of this as an in memory database.
I am declaring a table like this shipper table here, and then I create what's called a Data Adapter. It's the DataAdapter's job to fill the table and to push the data back into the database. When I have the shipper data, I can then create a SelectCommand, which is like the command I created up here, and I am saying here is the Select statement. Now, unlike up above, this data adapter can hold the SelectCommand, and it also can hold text for the UpdateCommand, for the inserting of data, and for the deleting of data.
So it can do all four of those operations. I am doing the same thing down here with the productAdapter. I am creating a product. I am saying select start from product. I'm adding the Shippers table to the DataSet. I am adding the Products table to the DataSet, and then finally I'm calling Fill and say, "Here's the DataSet. Go out and get the data and put it inside the Shippers table." My last step is to take the data from the Products table and put it in the list box via the DataSource property.
Let's see if this works. Press F5, click the Fill DataSet, and there is all our product names from the database. Now, as you can imagine, this is a lot of work. I have to create all of my DataSets by hand. So there is a wizard that is part of Visual Studio that generates these DataSets on my behalf. That's the topic of the next movie in this chapter.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97238 Viewers
61 Video lessons · 84517 Viewers
71 Video lessons · 68723 Viewers
56 Video lessons · 101209 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.