0

I have a error with my code, when i start test with my web api and it shows object cannot be cast from dbnull change to other types and i have ticked the null box in sql server database. i don't want to change anything in database. The Mobile and datetime columns are null in sql server. I used asp.net web api 2 to do this project.

My Questions: How to solve the error without doing anything in the database?

here is my code:

        public IHttpActionResult Get()
        {
            List<TestClass> draft = new List<TestClass>();
            string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            string sqlquery = "Select * From tblTest";
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
            SqlDataReader sdr = sqlcomm.ExecuteReader();
            while (sdr.Read())
            {
                draft.Add(new TestClass()
                    {
                        UserId = Convert.ToInt32(sdr.GetValue(0)),
                        Name = sdr.GetValue(1).ToString(),
                        Mobile = sdr.GetValue(2).ToString(),
                        Access = Convert.ToInt32(sdr.GetValue(3)),
                        Date= Convert.ToDateTime(sdr.GetValue(4))
                    });
            }
            return Ok(draft);
        }

My database in below:

UserId Name     Mobile    Access  Date
11     John     NULL      2       2012-01-02 00:00:00.000
24     Fred     34786584  5       NULL
56     Emily    18375555  0       2014-03-04 00:00:00.000
76     Lydia    NULL      4       2015-09-08 00:00:00.000
87     Anna     12313147  5       2020-11-21 00:00:00.000
90     Mimi     27184641  1       NULL
Charlieface
  • 52,284
  • 6
  • 19
  • 43
user19613128
  • 97
  • 9
  • 1
    Does this answer your question? [SQL Data Reader - handling Null column values](https://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values) – Yong Shun Jul 25 '22 at 06:46
  • is there anyway that can use DBNull for my all columns? – user19613128 Jul 25 '22 at 06:53
  • Any specific readon you're using the old old datareader instead of an ORM framework like Dapper or Entity Framework? – JHBonarius Jul 25 '22 at 07:03
  • 1
    i don't want to use entity framework – user19613128 Jul 25 '22 at 07:04
  • Hmm, I think just do the `DBNull.Value` checking for those NULLABLE columns. Another concern is that you should specify the columns to be returned in the query. So it guarantees the order of the columns that are to be returned (only). – Yong Shun Jul 25 '22 at 07:04
  • _"i don't want to use entity framework"_. And then you come here asking why stuff doesn't work ¯\\_(ツ)_/¯? You know you are making your own life difficult reinventing the wheel? You are not even disposing the disposables. On another note, [avoid `select *`](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful). – JHBonarius Jul 25 '22 at 07:07
  • Entity framework is extension in visual studio, and i got lots of system working on the same visual studio, when i update the data and i need to update the visual studio – user19613128 Jul 25 '22 at 07:13
  • `Mobile = sdr["Mobile"] as string` etc. Also you are missing `using` blocks to close the connection and reader – Charlieface Jul 25 '22 at 08:19
  • @allmuhuran Thank you, do you thank there is any possible way to deal with datetime and int in DBNull? i am trying to get this code to solved – user19613128 Jul 25 '22 at 08:30
  • @allmhuran And I've been spending the greater half of my previous year fixing all kinds of performance and maintenance problems in a legacy ADO.net application. There was one employee in the company who understood it (as he made all of it), and he retired. By replacing it with a combination of Dapper and EF, now everybody can maintain it. And the performance is just as good as it was before, probably even better. When I read somebody getting data with `Select *`, I already see where this is heading... – JHBonarius Jul 25 '22 at 08:43
  • The first comment has a link to the answer you need @user19613128. The problem is not on the database side, that's fine. The problem is that sometimes one or more of the values coming back can be a database null. Your `TestClass` needs to be able to handle this by having fields that are nullable, that is to say, instead of having a field like `DateTime MyDateTimeField` (which cannot be null, because it is a struct), you have `DateTime? MyDateTimeField`. The `?` means "this field can be null". Then when reading from the `SqlDataReader` you check for `DbNull.Value`, per the linked question. – allmhuran Jul 25 '22 at 10:02

3 Answers3

1

You can check using IsDBNull:

 Date = sdr.IsDBNull(4) ? null : Convert.ToDateTime(sdr.GetValue(4))

if Date column has type 'datetime' or 'datetime2' you can use GetDateTime:

Date = sdr.IsDBNull(4) ? null : sdr.GetDateTime(4)
Alexandr S
  • 309
  • 4
  • 10
0

you are trying to convert a null value into DateTime which causes an error. Make your date nullable by adding a ? sign after the DateTime property.

Mahfuj Ur Rahman
  • 51
  • 4
0

Check by DateTime.TryParseExact

DateTime.TryParseExact("YOURVALUE","yyyyMMdd",CultureInfo.InvariantCulture,DateTimeStyles.None, out resultvalue)?resultvalue.toString():"";

moa7amed
  • 16
  • 4