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);

Set a breakpoint on GetVaue(0) and query the return value in the Immediate Windows in VS, it will tell you what the type is. (i.e. dbReader.GetValue(0).GetType()) — Dai
– Dai, Commented Oct 13, 2014 at 22:40
Are you sure securityLevel is a tinyInt? from the error you are getting it sounds like it is a int. How are you checking that it is a tinyInt, also can you show your connection string? If you are using AttachDBFilename the database you are making schema changes to is likely not the database you are using in your program. — Scott Chamberlain
– Scott Chamberlain, Commented Oct 13, 2014 at 23:12

Solomon Rutzky · Accepted Answer · 2014-10-14 15:49:21Z

9

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)

edited Oct 14, 2014 at 15:49

answered Oct 14, 2014 at 1:27

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nehi_d Over a year ago

Thank you. I will try this. Since I am using only the 0 - 4 values, would a char(1) not use less space? If so, why would I not use this?

Solomon Rutzky Over a year ago

@nehi_d : a CHAR(1) = 1 byte, and TINYINT = 1 byte. There is no difference in physical space taken. But most likely the TINYINT would perform better in JOINs and WHERE clauses. It is possible that CHAR(1) might do as well IF you used a binary collation (such as SQL_General_BIN or Latin1_General_BIN) when you create the column (or alter it with that definition), but it seems like it really is a number, so best to keep it that way. It also makes it better for doing ORDER BY [SecurityLevel] or WHERE [SecurityLevel] > 3 (or something like that).

Solomon Rutzky Over a year ago

@nehi_d : were you able to test this out? Did it work?

ChaseMedallion · Accepted Answer · 2014-10-14 00:15:29Z

I wrote the following LinqPad (against SqlServer express) to verify that you can read in a tinyint as a byte using .NET and SqlServer:

var cb = new SqlConnectionStringBuilder { DataSource = @".\Sqlexpress", InitialCatalog = "Medallion_OData_Tests_CustomersContext251990930203", IntegratedSecurity = true };
using (var c = new SqlConnection(cb.ConnectionString))
{
    c.Open();   

    var cmd = c.CreateCommand();
    cmd.CommandText = "SELECT CAST(1 AS tinyint)";
    var reader = cmd.ExecuteReader();
    reader.Read();
    reader.GetByte(0).Dump();
}

This suggests to me that the actual securityLevel column in your database is not of the type TINYINT. To verify, why not temporarily modify your select query to include a CAST to TINYINT as in my example? If this works, that will confirm that the table schema is the problem.

Other ways to check the actual table schema include querying sys.columns or highlighting the name of the table in SqlServer Management Studio and hitting ALT+F1.

Rimas · Accepted Answer · 2014-10-14 18:58:06Z

2

I think safest way is to use Convert.ToByte Method:

level = Convert.ToByte(dbReader.GetValue(0));

It converts from many value types to byte.

edited Oct 14, 2014 at 18:58

answered Oct 14, 2014 at 18:11

Rimas

6,0332 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

tinyint in SQL Server to byte in C#

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related