Connexion to database SQL SERVER (2019)

sblb 1,171

Hi,
I've an Blazor Wasm application.
Until now I worked with a local database that I've created by migration.

"Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\...\\source\\repos\\DATASERVEUR\\Developers.mdf;Integrated Security=True;Connect Timeout=30"

With this connection all things is ok

Now I want to connect my application with the data base in sql server (2019)

Data Source=servername;Initial Catalog=Developers;Integrated Security=True;

the table dbo.Developers was created by import of the flat file (csv)

When I run my application I've a message :

InvalidCastException: Unable to cast object of type 'System.Byte' to type 'System.Int32'.

The get action where I've this message :

  [HttpGet]
        public async Task<IActionResult> Get()
        {           
            var devs = await _context.Developers.ToListAsync();
            return Ok(devs);
        }

Have you an idea how I can solve this problem?
thanks in advance

AgaveJoe 28,026 Reputation points

2022-04-23T12:08:18.293+00:00
Thanks to your the suggestion but my data is in CSV file.

My best guess is you created the table schema when importing the CSV file. Otherwise, I don't know how the entity types can be out of sync with the table definitions.

I recommend using the EF tooling rather than making up your own deployment process. Please make an effort to read the documentation provided in my initial post. The documentation clearly illustrates how to deploy migrations to a production database. It might be as simply as generating the SQL script that you can run in the new environment.

dotnet ef migrations script

The next step is importing the data from the CVS file.

8 answers

Erland Sommarskog 109.3K Reputation points MVP

2022-04-23T18:03:42.907+00:00
I'm not very good at using the Import Wizard. However, I was able to load your sample file with BULK INSERT:

BULK INSERT Developers FROM 'F:\FTP\temptab.csv' WITH (FIELDTERMINATOR = ';', FIRSTROW = 2)

This requires that SQL Server can access the file. If that is an issue, you can use BCP from the command line:

bcp tempdb.dbo.Developers in F:\FTP\temptab.csv -c -t; -S YOURSERVER -T F 2

Although to load the file through BCP, I had to change the columns AmdtC, AmdtP and IndiceP to permit NULL.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
sblb 1,171 Reputation points

2022-04-25T15:16:17.197+00:00
Thanks to your reply.
I had also tried the sql code with bulk insert but I had an error.
I think you have modified the datetime columns. because they are declared as "not null". So the colonm DAteSoldePr is null so you must have made a mistake

To make the column "not null" we can do this as follows:

public DateTime? DateCrea { get; set; }

But after I have a problem with the variable calcul e.g if (DateSoldePr.Year < 2000) because .Year is not defined for DateSoldePr = null

In my code I call the variable DateSoldePr and if I add ? it still brings a problem

Have you an idea?
Please sign in to rate this answer.
sblb 1,171 Reputation points

2022-04-25T16:03:28.003+00:00

I tried to put public DateTime? DateSoldePr = new DateTime?(); but it not a good way!!

Naomi 7,366 Reputation points

2022-04-25T17:55:23.48+00:00

What happens if you remove the computed column from the definition and leave date columns nullable?

Erland Sommarskog 109.3K Reputation points MVP

2022-04-25T21:16:02.587+00:00

I think you have modified the datetime columns. because they are declared as "not null". So the colonm DAteSoldePr is null so you must have made a mistake

Sir, I think that mistake is on your side. To wit, I took your CREATE TABLE statement above, and the column DateSoldePr is nullable in your post.

And, generally, if your input file is missing values for date/time columns, I think you need to define a default, or else you will never be able to import the file in any meaningful way. And, of course, that assumes that you can define meaningful default values at all - which is far from always the case!

As for the C# side of things, I leave that to others. I'm the SQL Server boy.

sblb 1,171 Reputation points

2022-04-26T10:26:40.617+00:00

Hi, I removed the computed and I've always the problem.

When I made the BLUK INSERT I obtained the message

Msg 4869, Level 16, Status 1, Line 4 Mass upload failed. NULL value expected in row of data file 2, column 22. The destination column (DateSoldePr) is set to NOT NULL.

My concern is how to make the column DateSoldePr null? Because by default the value is not null.

I tried to do public DateTime? DateCrea { get; set; } but after I have to add .Value but when I run my app there is a problem.

Have you an idea?

Erland Sommarskog 109.3K Reputation points MVP

2022-04-26T21:14:55.217+00:00

I was very confused by the error message, but I reliase now that it must have passed through Google translate or some such. The actual text of the error messages, as taken from sys.messages is

The bulk load failed. Unexpected NULL value in data file row %d, column %d. The destination column (%ls) is defined as NOT NULL.

Anyway, as I said previously, you either need to define a default value, or make the column nullable to be able to import that file.

Possibly, you should do like Joe suggests. Import into a staging table, and then take it from there. For instance, you can decide to ignore rows where there is a NULL in this column and others you want to make mandatory.

sblb 1,171 Reputation points

2022-04-27T06:53:08.41+00:00

You right I've followed the recommendations from AgaveJoe.
this means I've created staging table so I can define each columns.
I've created staging with DateSoldePr null.

You are right again, my VS is in French langage. Google is my friiend
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
sblb 1,171 Reputation points

2022-04-26T05:22:29.79+00:00

Right DateSildePr is with default value in class.
After migration DateSoldePr is not null.

Hep Guy, I never wrote that you have made a mistake. I only wrote that you have to modify my data to do bulk insert.

Thanks Sir
Please sign in to rate this answer.
AgaveJoe 28,026 Reputation points

2022-04-26T11:19:38.917+00:00

If you followed the openly published migration documentation form my initial post then the entity types should match the table schema. All that's left is importing the data which should be vrey simple.

As explained above, a standard data import process is to import data into a staging table first. Then writing an INSERT INTO script to move the data. If you want to use the SSMS import wizard then you MUST make sure the data types match (edit mapping).

Rather than using a computed column I would simple use a not mapped attribute on the property or an write an extension method.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Connexion to database SQL SERVER (2019)

8 answers

Your answer