tinyint in SQL Server to byte in C#

Question

In database management and application development we must be mindful of space and memory requirements. I have always been taught to use the data type that uses the least amount of space for your needs.

In my scenario, I have a column in a table that stores values {0,1,2,3,4} (SQL Server 2012). For this I chose to use the tinyint datatype. These values are pulled from the database into a C# application. Currently, I cannot get this tinyint data type to convert to a byte. When I try, I get an error "Cannot implicitly convert int to byte". If I change the datatype in the application to an integer, I can pull it just fine. Same with a string.

For performance purposes, is it okay to use integers throughout my entire application where I would normally use byte? If not, how do you convert an integer to a byte?

This is the code that I use that gives an error:

string strSQL = "SELECT securityLevel FROM security WHERE employeeID=@empID;";

using (SqlConnection dbConn = new SqlConnection(connParam))
{
  dbConn.Open();

  byte level = 0;

  using (SqlCommand dbCommand = new SqlCommand(strSQL, dbConn))
  {

    dbCommand.CommandType = System.Data.CommandType.Text;
    dbCommand.Parameters.AddWithValue("@empID", "12345");

    using (SqlDataReader dbReader = dbCommand.ExecuteReader())
    {
      while (dbReader.Read())
      {
        level = dbReader.GetByte(0);
      }
    }

  }

  Console.WriteLine(level);
  Console.ReadLine();

}    

I have also tried:

level = (byte)dbReader.GetValue(0);

Answer 1

Yes, you were correct to pick TINYINT as the datatype if you are storing only 0 - 4.

Yes, TINYINT equates to a byte in .Net. You can see a list of mappings here: http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

No, you did not actually use TINYINT when creating the table, else you would not be getting this error. The error message is very specific about the source datatype being INT.

No, do not use INT to store these values. That is unnecessary and what you are attempting to do (i.e. TINYINT and byte) is perfectly valid and I have done it many times.

Assuming you do not have millions of rows of data in that table and constant hits against it, run the following:

ALTER TABLE [security] ALTER COLUMN [securityLevel] TINYINT NOT NULL;

(I am assuming that the column is currently NOT NULL and in that case, if you leave off the NOT NULL in the ALTER TABLE statement, it will change the field to TINYINT NULL. If the field isn't currently NOT NULL, then just leave that part off)