Why my code not working

Question

1.00/5 (1 vote)

See more:

The code is executed and I do not receive any error but it does not show any information in the database table. I want send data from my csv file to my database

What I have tried:

C#

using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Data.SqlClient;

namespace DataBase
{
    public class SeedData
    {
        static void Main(string[] args)
        {
            var lineNumber = 0;
            using (SqlConnection conn = new SqlConnection(@"Server=.\SQLEXPRESS; Integrated Security=True"))
            {

                conn.Open();
                using (StreamReader reader = new StreamReader(@"..\..\..\DBData\IMDBMovieData.csv"))
                {
                    while (!reader.EndOfStream)
                    {
                        var line = reader.ReadLine();
                        if (lineNumber != 0)
                        {
                            var values = line.Split(',');

                            var sql = "INSERT INTO RentalMoviesDatabase.dbo.Movie VALUES ('" + values[0] + "','" + values[1] + "','" + values[2] + "','" + values[3] + "', '" + values[4] + "','" + values[5] + "','" + values[6] + "','" + values[7] + "')";

                            var cmd = new SqlCommand();
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            cmd.ExecuteNonQuery();
                        }
                       
                         
                    }

                    lineNumber++;
                   
                }
                conn.Close();

            }

            Console.WriteLine("Done!");
        }
    }
   
}

Posted 23-Nov-20 6:08am

Banana Life

Updated 23-Nov-20 7:04am

PIEBALDconsult

v2

Add a Solution

Comments

Richard MacCutchan 23-Nov-20 12:18pm

Have you checked that your splitting of the string returns the correct values, and number of variables? Have you used the debugger to check that your SQL statement succeeds?

And, please, do not post success type messages ("Done!") unless you are actually checking that it is done.

Richard MacCutchan 23-Nov-20 12:47pm

I have a better idea, you do the debugging and tell us the result.

PIEBALDconsult 23-Nov-20 12:24pm

Maybe your system is saving you from yourself?

Richard Deeming 24-Nov-20 1:16am

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2020-11-23T07:04:00

NEVER, NEVER, NEVER use string concatenation to build an SQL query. You open yourself up to SQL Injection attacks. Google for "SQL Injection Attack" to find out why what you did is so bad.

This is bad:

C#

var sql = "INSERT INTO RentalMoviesDatabase.dbo.Movie VALUES ('" + values[0] + "','" + values[1] + "','" + values[2] + "','" + values[3] + "', '" + values[4] + "','" + values[5] + "','" + values[6] + "','" + values[7] + "')";

ALWAYS use parameters to pass in your values. Google for "C# sql paramaterized queries".