Thursday, 28 September 2006

More DB stuff

A database hs a number of tables. Each table has columns, which define data types. The table also has rows, which are where the data is stored. So basic db commands operate on one or more tables, and return rows of data. In c#, a dataset is filled by a tableadapter, which is bound to a datasource. The first task in using data sources is to define your data layout. what table will hold what types of data, and how do the tables interact, if at all.

An example is a music library. I have all the song data in a table. But artists also have their own table, storing their name, number of tracks total, and other artist specific data, that only needs to be recorded once, instead of with every song.
Songs point at the artist table through a foreign key. the artist table has a primary key 'artistid' which should be an 'int' type.

Wednesday, 27 September 2006

Tims quick guide to C# sql db access.

I struggled a bit trying to learn this stuff. There's one little trick that might make your life easier when jumping into data access.

First, create a regular C# project and form.

Now you'll want to add a new Database to your project. (make it file based for now i.e. TestDatabase.mdf). Then when it comes to a dialog box asking to 'copy this into your project', say NO.

If you say yes, every time you build the project, the 'working' DB is copied over by a backup DB... if it's an empty DB, then it looks like all data entry you've done is erased! This drove me nuts.

After you've added a db file, you'll have to use the database explorer to poke around in it. Connect to a new database and click on the Tables, right click, Add New Table. Then in your table, add Columns.

The first column you add will be a primary key, usually an 'int' type. For example, for my MP3 database, the first Column in the SongData table is "SongID". Double click on the table to see the columns show up in an editor. Right clicking on a column will give you an option for setting a primary key.

The primary key is used by the code generator for queries, and usually you'll use this key to cross reference data from other tables. For example, I have a SongData table that contains a ArtistID. Then I have an Artist table than has a primary key of AristID. From what I understand, the SQL server can more easily bind these two tables together with this kinda of relationship. For example, if I'm in the artist list, and I want to find all songs by an artist, the SQL server can do this search by using the artist primary key and the 'link' to the SongData's "ArtistID", to get a list of all songs.

Hmm, I need to redo this, really ya gotta start with database planning, and I should define the terms better.
Posted by tim at 5:51 PM in Coding in C# and C++
« January »
SunMonTueWedThuFriSat
    123
45678910
11121314151617
18192021222324
25262728293031