C# read data from Microsoft SQL Server

Question

I'm new to Microsoft SQL Server and I have a few lines of code like this:

private void readItem()
{
    SqlDataReader reader = command.ExecuteReader();

    if (reader.Read())
    {
        btn.ItemID = reader.GetInt32(0);
        btn.ItemName = reader.GetString(1);
        btn.ItemPrice = reader.GetInt32(2);
        btn.ItemDiscount = reader.GetFloat(3);   // Throw exception

        itemPanel.Children.Add(btn);
    }   
}

I get this error:

System.InvalidCastException: 'Specified cast is not valid.'

I sure that both ItemDiscount and reader.GetFloat(3) are float, but the exception still happens. I couldn't figure what is wrong here.

Please help me with this problem. Thanks all

Database : https://i.stack.imgur.com/tcsmf.jpg

Can you show the class definition for the `btn` object? Is `ItemDiscount` a float? — Hambone, Oct 19 '17 at 20:08
Please show the line of data being processed when the exception hits. — Daniel, Oct 19 '17 at 20:09

Igor · Answer 1 · 2017-10-20T10:16:49.173

Seeing as the schema allows for null values you have to then also check for the presense of null values in your data. You can do that with IsDBNull

using(SqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        btn.ItemID = reader.GetInt32(0);
        if(!reader.IsDBNull(1))
            btn.ItemName = reader.GetString(1);
        if(!reader.IsDBNull(2))
            btn.ItemPrice = reader.GetInt32(2);
        if(!reader.IsDBNull(3))
            btn.ItemDiscount = reader.GetFloat(3);   // Throw exception


        itemPanel.Children.Add(btn);
    }
}

Also make sure you wrap your SqlDataReader instance in a using block. Also do this for any SqlConnection and SqlCommand instances (the latter type is not required).
I assume you know you are accessing everything based on the ordinal position it is returned in the query (schema does not matter), make sure your select specifies column names and do not use * as the column positions could change if you change your schema.
If you know that values should or can never be null then make sure you change the schema to not accept null values. It is best practice to constrain your schema as best you can which ensures more valid values for the data that it contains.

Edit

It seems you are not sure what types you should be using in reading your data from Sql Server to c#. See SQL Server Data Type Mappings for Sql types and to which c# types they map to. In the schema you have defined above those types map as:

Column      Sql Type         c# Type
ID          int       ->     int
Name        nvarchar  ->     string
Price       int       ->     int
Discount    float*    ->     Double
Image       image*    ->     byte[]
Description nvarchar  ->     string
Count       nchar     ->     string / char[]

Notes

image is deprecated in favor of varbinary(max)
For monetary values it is usually better to use decimal than float/double as it is more precise, see also this previous so question Difference between decimal, float and double in .NET?

@lover - it is also always a good idea to specify the column names in your SELECT statement, that order will match with the ordinal positions you are using and should your schema ever change (specifically the position of columns in your schema) your queries will not be affected. — Igor, Oct 19 '17 at 20:32

Viju · Answer 2 · 2017-10-19T20:10:40.177

1

You are reading data by order , Please share your select for the same.

Because based on the select statement reader.GetFloat(3) could be a description.

Better way would be Get the data by column name or alias that you have in the result set.

edited Oct 19 '17 at 20:10

answered Oct 19 '17 at 20:09

Viju

95
1
7

score 0 · Answer 3 · answered Oct 19 '17 at 20:42

if (reader.Read())
        {
            if (!reader.IsDBNull(0))
                btn.ItemID = (int)reader["ID"];
            if (!reader.IsDBNull(1))
                btn.ItemName = (string)reader["Name"];
            if (!reader.IsDBNull(2))
                btn.ItemPrice = (int)reader["Price"];
            if (!reader.IsDBNull(3))
                btn.ItemDiscount = (float)reader["Discount"];
            btn.Style = (Style)TryFindResource("itemLargeButton");
            itemPanel.Children.Add(btn);
        }

After fixed my code, exception still happen here

Output:

System.InvalidCastException: 'Specified cast is not valid.'

score 0 · Answer 4 · answered Oct 19 '17 at 21:34

two thing we need to consider when reading data from any source and converting them to native types.

1. Provide named or alias on the source, it facilities to map right columns to right variables.


 btn.ItemDiscount = reader["Discount"];


2. Try to validate the input type before conversion something like below in current scenario:

btn.ItemDiscount=  Single.TryParse(reader["Discount"], out float discount) ? discount : 0f;

C# read data from Microsoft SQL Server

4 Answers4

Edit