Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

C# and Databases - Part 2 - Creating a new Database and Table


  • Please log in to reply
No replies to this topic

#1 sam_coder


    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 23 February 2011 - 05:18 AM

I had stuck a really high-level tutorial in this section, regarding databases and C#. I didn't get much of a response, and I think that's because databases are more intimidating than I even realized.
So, this is an attempt to provide a more complete, end to end explanation. Before we can get started, I'm going to explain my setup, you should try and match this as closely as possible.
I'm running with,
• Microsoft Visual Studio 2008 Professional
• Microsoft SQL Server Express 2008
• Microsoft SQL Server Management Studio Express 2008

In truth, you could replace Visual Studio with 2005, 2008 or 2010. You could replace SQL Server with 2005. And the specific editions don't really matter. If something throughout here doesn't work, just comment, and we can see if we can't work through it.

Let's start by creating a new Database. Open SQL Server Management Studio (express); and log in. You will likely be logging in with Integrated Security. I will talk more about Mixed Authentication later, or in another tutorial, but this is what we will use for now.

You're object explorer is on the left hand side of the screen, and will be our primary focus. Right click Databases, and select "New Database". Call it "Super", and press OK. You should now be able to see the "Super" database at the bottom of the list of databases.

You can expand the database, and see that it's broken down into several sections. You should concern yourself with Tables for now. That's what this tutorial will focus on.

If you right click "Tables", you will have the option to create a brand new table. If you select new table, you will be presented with a grid in the centre of the screen. This is where you can define the schema (pronounced 'skeema').

The schema is simply the structure of the table. You need to consider, what you're storing, how you will be retrieving data.

There are limitless ways of imagining data being organized, but it's helpful to break data down into a few different styles. You basically have these types of tables.
• Tables that house records that are managed individually (like people, inventory, etc)
• Tables that hold aggregate data, or data that you generally won't look at and manage one record at a time. (like scores or other data that collectively generates averages that are shown in your application)
• Tables that provide structure, lets say.. Where you have one table with people, and another table with vehicles, and you need to describe to the application that some cars are used by some people. People can use each others cars. (that' more complex a scenario than you might initially think)
• Data that generally won't be accessed directly from your application, like logging data, or auditing records.

You get the idea; this rabbit hole goes pretty deep.

I find a great table to start with, is one that collects personal information; mostly because it's easy to conceptualize, and it's easy to fill it in with either fake or real data.

So, start filling in your table schema. Under Column Name, enter 'id', and in Data Type, enter 'int'. Next, uncheck 'Allow Nulls', and look for the toolbar icon that looks like a gold key. Press it, and you should see a key appear to the right of id. (I will explain this VERY IMPORTANT implication in a second.

Now, to the bottom, you will see the Column Properties. Scroll down that property list, and see if you can find a column called "Identity Specification". Hit the + sign beside it, and set "Is Identity" to Yes. Leave both the "Identity Increment" and "Identity Seed" to 1.

So, this is your table's primary key. The primary key is important, because it guarantees you can identify any one specific record in that table, no matter what. But indexes and keys are really out of scope of this tutorial. Bug me later, and I'll write 300 pages on the topic.

Now, let's add a few more columns. Under Column Name, on the next row, add 'name_first', and then Data Table, set varchar(50). and uncheck Allow Nulls.

add 'name_last', 'varchar(50)', uncheck Allow Nulls.

add 'email', 'varchar(75)', and leave Allow Nulls checked.

Ok, so we have a few things happening here. We will collect people's first and last names, and email addresses if they provide them. I'll show you how to handle no information as a non-problem.

Hit the save button on your tool bar, and you will be presented with a dialog to name your table. Just call it 'people'. You should now see your people table.

I could jump right to code right now, but it's important for you to understand how this table works. So right click that table, and select edit top 200 rows. This will open a spreadsheet view of the table. And this is a really nice way to imagine the data being stored.

Select the name_first column, and enter 'Sammy', press the Tab button, and enter 'Samsam', press the Tab button and enter 'something@somewhere.com'.

You may notice a few red orbs, that's ok, that indicates a dirty field. This means that the field is different since you opened it, and it must be committed before anyone else will be able to see it. Press Tab again to move to the next row, and commit the changes. The orbs should go away, and the id should now say 1.

You can keep entering records, and in fact, I'll recommend you add a few more, and enter some without an email address. You can play here for a moment, and get a feeling for how the table behaves.

I'm cutting this tutorial off here, as Part 2, and will continue on Part 3.

Part 3: http://forum.codecal...on-objects.html

Edited by sam_coder, 23 February 2011 - 10:38 AM.
Adding Link to part 3

  • 0

Also tagged with one or more of these keywords: authentication

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download