Now our database is setup and running (SQL Server leaves a task running (the server) in the background on your PC at all times), let's connect to it from an ASP.NET application.
First off, launch Visual Web Developer Express 2010 (AKA Visual Studio or simply VS) and click on New Project. Make sure Visual C# (why bother with VB!?) is selected and then select the Web section from inside there.
From the list of project templates choose ASP.NET Web Application and call the project "Zoo", as above, then press Ok.
It will then create all the files needed for a basic web-site, as you can see below:
If you press F5 at this point it will compile and run the app in your browser and you should see this:
Notice there's a Login link that provides some basic account-management processes. The rest of it is static content.
Connecting To Our Data
To connect to the database we created yesterday we need to add a "connection string" to the Web.Config file. In the Solution Explorer pane of VS find the Web.Config file (normally right at the bottom and open it up).
We need to replace the existing connection string with this one:
<add name="ZooConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Zoo;Persist Security Info=True;User ID=sa;Password=YourP4ssH3re" providerName="System.Data.SqlClient"/>
Note: In production you'd want to add a new user (rather than using the "sa" account) in SQL Server and update the connection string.
Adding a DataSet
Now we're ready to tell the ASP.NET app about our database. To do this we use a DataSet to define the backend tables and the relationships between them.
You'll then get an empty DataSet. Right-click anywhere within it and choose to add a new TableAdapter, like so:
You'll then have to fill in a Wizard. First thing to do is choose the connection string (should default to the one we added to our Web.config file), like so:
Tell it you want to use SQL Statements:
Then tell it just to get all the rows from our Animals table:
Then just tell it to return a DataTable from the method called GetAllAnimals:
You will then end up with a DataSet with a single Table Adapter on it, as below:
And that's it. We're ready to start accessing the data from our app now!
Using Data In Our Application
Let's test our new Table Adapter by adding a data grid to the home page. Open up the Default.aspx file and make it look like this:
Notice we've added a GridView, which is like a table-based view of data. Now we need to bind some data to it. We do this in the Page_Load event and we code for that in the "code behind" file.
To get to the code-behind file, right-click inside the Default.aspx file and choose "View Code". This will open the Default.aspx.cs file, which is where we write the C# ("see sharp") code associated with the Default.aspx page.
Here's the code we need:
Notice I added a "using" statement (equivalent of import in Java). It loads the classes for the TableAdapters in our Zoo.xsd DataSet. That's why we can then refer to objects based on the AnimalsTableAdapter and use the method called GetAllAnimals, which we defined earlier, to fetch a table of data and bind it to the GridView.
With the above code in place (I purposely used an image so you need to type it in, hoping it will give you a taste for how amazing coding is in VS!) press F5 and you should see this in the browser:
In practice I don't use GridViews, but, for the sake of a simple demo I've used one here. Hopefully it shows how easy it is to fetch and bind data to objects once the DataSet is in place and TableAdapters are defined. We've done most of the donkey work for now.
We'll be using the TableAdapters for all connections to the database, whether that's to query data or insert, update or delete it. They're very powerful and I much prefer using them over using hard-coded SQL from with ASPX or code-behind files.
If I'm losing any of you already or you have questions, please speak up and I'll try my best to answer.
Next? Let's create a C#-based object/class to define the Animals.