logo

ASP.NET: Setting Up The Database

If you followed my lead on Friday you'll have installed everything you need to developer in ASP.NET.

Now - assuming you remembered to include it as part of the install - launch SQL Server Management Studio. First thing you'll see is a login screen, as below:

image

In the Server Name field put COMPUTERNAME\SQLEXPRESS or try LOCALHOST\SQLEXPRESS if you don't know your computer's name. Enter the password you chose during install and press connect.

Once connected. Right click the Databases folder in the left-hand pane and choose New Database. You'll see a box, like below, in which you can name it Zoo and press OK.

image

Now, find the Tables folder within your new database, right click it and add a new Table. Add some columns, like below.

image

These columns will likely change as we go along, but, for now, we just want to get up and running.

With these rudimentary columns in place, we need to configure the table's "ID" column. First thing we need to do is make it a Primary Key. To do this, right click the animal_id column and choose Set Primary Key.

image

Now, we need to tell the ID column to "auto increment". In MySQL tools, such as the brilliant HeidiSQL, this is fairly obvious how to achieve. In SQL Server it took me a while to work out that you need to find the "Identity Specification" section of the Column Properties and change "Is Identity" to Yes.

image

For those of you unfamiliar with SQL, a column that is an identity gets auto-incremented each time you add a row. When you add the first row, the ID column value is 1. For the second row it's 2. Etc. You don't need to specify these values, the SQL system does it for you.

Now, we need to add a Database Diagram to define relationships between tables. It's not really essential at this point (where we have no inter-table relationships to define) but it will come in handy later on in helping build a DataSet in Visual Studio.

On the Database Diagrams section of your database right click and add a new diagram. Add the Animals table it and save (name isn't important really).

image

Before we finish let's quickly add some data. Right click the table and choose "Edit Top 200 Rows". You can then type in "raw" values in to each column. Press the down arrow to move to a new row.

Note that you don't need to add values to the animal_id column as it will auto increment these for us. Just leave its value as NULL.

image

There's no need to save the table after entering values. As you go the values and entries are automatically updated to the database!

In the next post we'll create a new project in Visual Studio, connect it to SQL Server and then build a DataSet.

At this point it might be worth pointing out that I don't proclaim to be an ASP.NET expert. Nor am I - by any means - a SQL (or SQL Server) guru. I'm somewhere above average, if I dare say so.

What I'm describing here is the point I'm at in my learnings. It's the approach I'm using at the moment and is constantly changing as I learn more and better ways of doing things. I'm not saying it's the best way, but it's the way I'm happy doing things after about 18 months at it. In that time I've made quite a few "mistakes", so at least if you follow along you should avoid making any of them.

If along the way you think you know a better way or see me making glaring mistakes. Please, please say.

Comments

    • avatar
    • Jan Sandholm
    • Mon 31 Jan 2011 04:25 AM

    Are date fields covered later in the localization/i18n phase? If so, disregard :). For now my db only accepts mm/dd/yyyy, I'd like it to accept dd.mm.yyyy. Is that something that is usually handled in the db level or ASP.NET C# level?

      • avatar
      • Jake Howlett
      • Mon 31 Jan 2011 10:01 AM

      The dataset handles converting the date stored in sql in to a c# DateTime object. From there you can format as you like and, yes, i'll try and cover internationalisation, which is a breeze.

      You can still fall fowl of 12/5 being stored as 5/dec if not careful though. I'll try and cover that too.

      Show the rest of this thread

    • avatar
    • Nick
    • Mon 31 Jan 2011 10:25 AM

    What about type? Can't you have another database called 'types', with that incremental column, and have the different types listed? Using the relational aspect of it. So, if you change it from Elephant, to African Elephant, and create a new one for Asian Elephant, all records would be updated by just updating the type table.

      • avatar
      • Jake Howlett
      • Mon 31 Jan 2011 03:37 PM

      Another database? Or table?

      Yeah, I agree. Completely. The above is meant as more of a quick quide to getting some data in there ready for the nest step.

      The structure of the tables will change as we go forward.

      Show the rest of this thread

  1. If you haven't experimented with Linq-to-sql, or the MS Entity Framework yet, you're missing a wonderful couple of things that make storage and retrieval of data much easier.

    Once you stumble through those, you might find something like the T4 toolbox (code generation tools), that will, once again, change your perspective on things. If you are going down that route, visit Oleg Sych's blog (olegsych.com).

    Also, as your taste for the MS development tools grows, you may want to check out what the higher-end VS editions can do for you ( think about putting your create table scripts in source control, or comparing one DB to another and generating change scripts ).

    Any way you want to look at it, there is a wonderful world of development tools and troubles in front of you. VS and .Net are not perfect (nor is any development platform), but it is a whole new set of challenges, and will for quite some time feel like you've been liberated from a very restrictive environment.

      • avatar
      • Jake Howlett
      • Mon 31 Jan 2011 03:53 PM

      Not really, but I do plan to.

      I've messed a little with LINQ and liked what I saw.

      What I wanted to do was learn the platform from the "ground up", rather than only learning some abstraction tool and not understanding what's truly going on.

      Getting better at LINQ and looking at EF are both high on my to-do list.

      Show the rest of this thread

Your Comments

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


About This Page

Written by Jake Howlett on Mon 31 Jan 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