CODEWITHSUNDEEP
c#databasevisual-studio-2010ms-accesstypes
sean
sean

Reputation: 9268

What is the correct data type to use for a number

I am new to MS Access 2007 and I am creating an application using Visual Studio 2010 C#. I am wondering what datatype should I use in each parameter I am creating.

For example:

command.Parameters.Add(new OleDbParameter("?company_name", OleDbType.VarChar));

This is a data type for characters(correct me if I am wrong).

My question is, what is the data type for numbers(example: zip code and telephone number(221-0019)) to use is correct to use the numeric data type?

P.S.: I already have my table in MS Access

Upvotes: 3

Views: 4724

Answers (2)

Adam Ralph
Adam Ralph

Reputation: 29955

Use whatever datatype fits your data.

E.g. if you require a column to store whole number between 0 to 100 use a field with a DataType of Number with a FieldSize of Byte. For a whole number between 0 and 10,000 use Integer. For a fractional number between -1,000,000,000 and 1,000,000,000 use Single. See the MSAcess help for DataType and FieldSize to see what is appropriate for your data.

Then, when manipulating the database from C#, use the equivalent OleDbType. See the help for OldDbType to decide which one is equivalent to the datatype in the MSAccess column.

E.g. (as per above examples) OleDbType.UnsignedTinyInt, OleDbType.SmallInt, OleDbType.Single

Upvotes: 1

James
James

Reputation: 641

There are a few things to think about when storing data values as numbers or characters. You should be aware of the following issues.

  1. What will you do if someone decides to enter non numeric characters in the zip or telephone number? dealing with the above example, you will need to remove all non numeric characters for a phone number like (222) 221-0019. The better solution would be to scrub the data before entering it into the database, but storing it as a character can still have its perks.
  2. Sorting. Please note that ordering characters vs numbers can produce some undesired results. note that the numbers 1, 11, and 2 are sorted differently alphabetically vs numerically. Sorted alpha, the correct order is 1, 11, 2. Numerically, they are sorted as 1, 2, 11. This is a consideration when deciding to store a data value as a number or a character field.
  3. Space. Storing these as an integer will take 4 bytes. If you store them as a character, this will take 5 bytes for a 5 digit zip, 10 if using unicode. You probably wont save an astronomical amount of space, all things considered, so 1 and 2 should be bigger considerations.

Databases are about storing data. You should really think about the intention of the values being stored. Typically, you only want to store things as numbers if they are actual numerical values that you would expect to have numerical operations. For example, money, age, number of years employed.

As for identifiers that are typically all numbers, for example social security numbers, street numbers, phone numbers, these really arent numbers at all. They are "character" values that just happen to only use numbers. The reason you would want to store them as characters is that when bringing them in from the database, if you expect to be doing primarily character based operations, you will have to deal with continually casting them from numeric to character values.

Weigh the pros and cons. Good Luck!

Upvotes: 1

Related Questions