logo

ASP.NET: Creating a Project And Connecting To Data

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.

image

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:

image

If you press F5 at this point it will compile and run the app in your browser and you should see this:

image

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).

image

We need to replace the existing connection string with this one:

<connectionStrings>
<add name="ZooConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Zoo;Persist Security Info=True;User ID=sa;Password=YourP4ssH3re" providerName="System.Data.SqlClient"/>
</connectionStrings>

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.

image

You'll then get an empty DataSet. Right-click anywhere within it and choose to add a new TableAdapter, like so:

image

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:

image

Tell it you want to use SQL Statements:

image

Then tell it just to get all the rows from our Animals table:

image

Then just tell it to return a DataTable from the method called GetAllAnimals:

image

You will then end up with a DataSet with a single Table Adapter on it, as below:

image

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:

image

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:

image

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:

image

Howza!

Summary

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.

Comments

  1. I'm certainly interesting in knowing more about using the TableAdapters, as back when I was fiddling with .Net I always ended up using direct SQL - but that was some versions ago.

    I'm thinking specifically in an update that requires a relationship, etc.

  2. Hi Jake,

    I still bleed yellow and fortunatellty, despite the market situation, I'm working with Lotus Technologies. Xpages is something we are pushing right now and the way to go for us.

    But I have to say that your articles, no matter which the topic is , are quite interesting and I predict that you'll keep the majority of us as a faithfull readers community. :-)

    • avatar
    • Ferdy
    • Tue 1 Feb 2011 06:14 AM

    Jake, nice posts in the series so far, very straightforward and easy to follow. Just some tiny remarks:

    - Good that you mention to change the SQL user in production. In addition it may be a good idea to encrypt the connection string. There is a standard way to do it (forgot how).

    - You have chosen datasets for database connectivity, which is a classic lightweight approach, perfectly fit for these articles. Still, I wonder if it would make sense that you tell the readers about the other options, such as LINQ to SQL and EF, and particularly when to use which.

    - The same applies to the view engine, whether to use webforms, MVC or Razor and when. The default approach seems to be a moving target in the .NET world.

    With some light architectural considerations I think your articles will be more unique compared to other beginners articles. Most tutorials only tell how to do things, not why or why that specific way.

    Good luck. I realize it is a lot of work to produce such articles.

      • avatar
      • Jake Howlett
      • Tue 1 Feb 2011 06:30 AM

      Hi Ferdy,

      I'm in no place to argue for one over the other really, as what I'm describing here is the one way in which I've grown used to doing it. Until I try the same approach using LINQ-2-SQL or MVC then i can't really argue for or against them.

      So, to add to my previous disclaimer, not only am I not saying this is the best way, it's definitely not the *only* way ;-)

      Show the rest of this thread

    • avatar
    • Nick
    • Tue 1 Feb 2011 06:15 AM

    About imports....Are you getting into Namespaces? And putting classes into Namespaces? I don't want to step on your toes. But it makes it easier to code your 'Zoo', and 'Animals'.

    If not, I will post an example of how I break mine up.

      • avatar
      • Jake Howlett
      • Tue 1 Feb 2011 06:28 AM

      I'll start to cover that tomorrow when I add a class called Animal to the "Zoo" namespace.

      Still, I'll welcome your example.

      Show the rest of this thread

  3. This is great stuff Jake. Thanks so much. Coding in VS seems very intuitive. The type ahead stuff is very cool!

    • avatar
    • Erik Brooks
    • Tue 1 Feb 2011 06:57 AM

    Good series, Jake.

    I may be missing something as I haven't seen VS 2010 but for about 5 minutes, but why all the hooplah about the script editor in VS? Has nobody here ever edited in a modern IDE (Dreamweaver, Eclipse, etc)?

      • avatar
      • Jake Howlett
      • Tue 1 Feb 2011 07:03 AM

      The one aspect in particular about VS that I love over all the other IDEs is the type-ahead. It's amazing. It's quicker than me (which isn't saying much I guess) whereas type-ahead in all Eclipse-based IDEs I've used is constantly trying to keep if with you. Often it just can't keep up. In Domino Designer I actually find type-ahead to slow me down as I wait for the spinny circle to keep up what I'm typing. It gets in the way of coding and slows me down! On the other hand VS's type-ahead actually helps me code faster.

      Show the rest of this thread

    • avatar
    • Michael
    • Tue 1 Feb 2011 07:25 AM

    I always found the zillions of project templates of those tools (same goes for IBM RAD) a little strange and, as there are far too much, confusing : do you know the difference between Web, Web MVC2, Web MVC3, empty MVC2 ... (btw, a MVC3 project can not be empty ?)

      • avatar
      • Jake Howlett
      • Wed 2 Feb 2011 03:04 AM

      Hi Michael,

      Read all the comments since posted above and you should get a better idea what's what.

  4. Great article again. I like the use of TableAdapters.

    Im currently using Xpages to display data from MySQL and I find it clunky. The approach in ASP is much nicher imho.

    • avatar
    • Sam
    • Tue 1 Feb 2011 06:19 PM

    OK, I'm at a loss. I'm using Microsoft Visual Web Developer 2010 Express, and for the life of me, I can't figure out how to create a DataSet. I see you've called up an Add New Item dialog box, but I don't see that in any of the menus or options on my version above. Help!!

      • avatar
      • Jake Howlett
      • Wed 2 Feb 2011 02:59 AM

      Try right-clicking on "Zoo" at the very top of the list of files in the Solution Explorer pane. Then from the "Add" menu choose "New Item".

      Show the rest of this thread

    • avatar
    • Martin Perrie
    • Wed 2 Feb 2011 04:32 AM

    Great start to this series of articles. Looking forward to the next installments.

    In SQL Server the "sa" user was disabled. Not sure whether that is expected in a new install and should really be using a different user?

    Will you be covering the use of stored procedures on the database to access and update data?

      • avatar
      • Jake Howlett
      • Wed 2 Feb 2011 04:34 AM

      It's been a while since I did a fresh install, but wouldn't be surprised if sa account is disabled by default (as a security measure). As mentioned some where else - it's an idea to create a new user per-db and not use "sa" once live).

      Yes, I'll use an SP later on.

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:


About This Page

Written by Jake Howlett on Tue 1 Feb 2011

Share This Page

# ( ) '

Comments

The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content