Inserting New Rows into database from a Text File

Question

Let me start by saying this is my first real c# application, so I know there are probably more efficient ways of doing things, but its my first attempt. Anyway the purpose of the application is to read in data from a comma seperated file, seperate each element of that comma seperated file, and then import those elements into rows in a database. I am currently stuck on my try and catch phrase where it goes to the catch everytime so it never connects to my database. Any and all tips, hints, or help would be greatly appreciated.

Current Error: An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Connection must be valid and open

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;
using System.Data.SqlClient; 
namespace Form1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (File.Exists(@"C:\Users\E15913\Downloads\testfile3.txt"))
                File.Delete(@"C:\Users\E15913\Downloads\testfile3.txt");
            var reader = new StreamReader(File.OpenRead(@"C:\Users\E15913\Downloads\ALMGrade.csv"));
            using (StreamReader readFile = new StreamReader(@"C:\Users\E15913\Downloads\ALMGrade.csv"))
            {
                string newline = "\n";
                string line;
                string[] row;
                int i = 0;

                while ((line = readFile.ReadLine()) != null)
                {

                    i++;
                    row = line.Split(',');
                    System.IO.File.AppendAllText(@"C:\Users\E15913\Downloads\testfile3.txt", row[0].ToString() + Environment.NewLine + row[1].ToString() + Environment.NewLine + row[2].ToString() + Environment.NewLine + row[3].ToString() + Environment.NewLine + row[4].ToString() + Environment.NewLine + row[5].ToString() + Environment.NewLine + row[6].ToString() + Environment.NewLine + row[7].ToString() + Environment.NewLine);
                }

                    string MyConString = "SERVER=10.100.135.220;" + "DATABASE=ak_steel;" + "UID=pickler;" + "PASSWORD=csmrecession09;";

                        string _recDateTimeMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        using (MySqlConnection connection = new MySqlConnection(MyConString))
                        {
                            MySqlCommand cmd = new MySqlCommand("insert into p4gradetable_copy (Grade, CoilingTemp, GaugeRange, WidthRange, MeshCassette1, MeshCassette2, XBow, Elongation ) VALUES(" + '"' + line + '"' + "," + '"' + line + '"' + "," + '"' + line + '"' + "," + '"' + line + '"' + "," + '"' + line + '"' + "," + '"' + line + '"' + "," + '"' + line + '"');
                            cmd.CommandType = CommandType.Text;

                            cmd.Parameters.AddWithValue("@Grade", line);
                            cmd.Parameters.AddWithValue("@CoilingTemp", line);
                            cmd.Parameters.AddWithValue("@GaugeRange", line);
                            cmd.Parameters.AddWithValue("@WidthRange", line);
                            cmd.Parameters.AddWithValue("@MeshCassette1", line);
                            cmd.Parameters.AddWithValue("@MeshCassette2", line);
                            cmd.Parameters.AddWithValue("@XBow", line);
                            cmd.Parameters.AddWithValue("@Elongation", line);

                            connection.Open();
                            cmd.ExecuteNonQuery();
                            label1.Text = "It Works";
                        }




            }
        }
}
}

you are eating the very helpful exception message on why it wont connect with the very UNhelpful "why it wont work". Get rid of the try catch and deal with the error — Ňɏssa Pøngjǣrdenlarp, Feb 19 '15 at 16:17
I did as you suggested and recieved the error An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll. It took to me the connection.Open() line. — osxoverkill, Feb 19 '15 at 16:21
I found [this](http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-csv-file-records-into-sql-server-database-using-as/) was a helpful pointer when I did something similar to this — McShep, Feb 19 '15 at 17:27

score 0 · Accepted Answer · 2015-02-19T17:40:11.590

0

Your problem is that you are creating an SqlConnection that is used for Sql Server. You must create a MySqlConnection and make sure to link the MySql client dll to your project.

Also ensure to create a MySqlCommand instead of SqlCommand.

The difference is Sql.. Classes are used for SqlServer database. MySql classes are used for MySql database.

bind parameters:

var cmd = new     MySqlCommand("insert into p4gradetable_copy (Grade, CoilingTemp ) VALUES(@Grade, @CoilingTemp)";

just specifying 2 columns for illustration purpose.

edited Feb 19 '15 at 17:40

answered Feb 19 '15 at 16:36

Any chance you could show me what you are talking about? I really do not know the difference in reference between the two. – osxoverkill Feb 19 '15 at 16:43
You must install MySql Data client for .Net on your machine. The dll that you must link is MySql.Data.Client dll. This is the data provider for MySql database that you must reference to your project. Include the using clause MySql.Data.MySqlClient in your code. Then replace the SqlConnetion in your code with MySqlConnection – Feb 19 '15 at 16:49
I went to the SQL website and downloaded the mysql-connect-net file and added it as a reference for my current project. I still do not see the client file you are talking about however. In addition I have updated my code to how it is currently after listening to your all advice. – osxoverkill Feb 19 '15 at 17:01
What is the name of the file that you have added then? Have you download MySql client for .Net? I have updated my answer as well. – Feb 19 '15 at 17:06
See this link to add to your project, is vb will be similar for c#: http://stackoverflow.com/questions/1102281/how-do-i-add-a-reference-to-the-mysql-connector-for-net – Feb 19 '15 at 17:09
That is the link i originally used to setup my connection and set up my reference file. Intellisense auto fills in the SqlClient part so i believe I have it set up correctly. – osxoverkill Feb 19 '15 at 17:15
No you must use MySql client, not SqlClient – Feb 19 '15 at 17:20
3rd from the bottom I have that included in my using namespaces. – osxoverkill Feb 19 '15 at 17:26
Almost there, change your sql insert statement value clause must be: values(@grade, etc.) that is specifying the placeholders for the bind parameters that you add to the command in your code later on – Feb 19 '15 at 17:28
This is the first one where i really dont understand your suggestion. Could you modify your answer on this one and paste the line you are talking about and the correction maybe? – osxoverkill Feb 19 '15 at 17:32
Thank you for the example i Made those changes and that part runs fine, however I am still getting the errors i edited on my main post. The unhandled exception and that the connection must me valid and open. – osxoverkill Feb 19 '15 at 17:55

Inserting New Rows into database from a Text File

1 Answers1