How do you insert a text value from a text file where the data is formatted line by line? For example if the text file has a long list of values, you would read each line, take the value by reading the entire line into a variable.

I recently did just this via the free visual studio express 2010 edition using the c# programming language.  The approach I used was to create a desktop application to make the connection to the MySQL database using dot net data connection libraries. I mentioned in a prior post a while back that there is library from dev mysql dot com you can download and install to allow you to interact with MySQL (search on “mysql dotnet connector”). Once you have that, you can build functions from visual studio to interact with MySQL.

First create a new console  project. Set a reference to the ‘MySQL.Data’ data library by going to ‘Solutions Explorer’ and selecting references. Then if the MySQL Data reference is not there, then right-click to bring up the context menu and ‘Add New’. In my own situation, i found ‘MySql.Data.dll’ was located in ‘C:\Program Files\MySQL\MySQL Connector Net 6.5.6\Assemblies\v4.0\MySql.Data.dll’.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestMySQLConnect
{
  class Program
  {
    static void Main(string[] args)
    {

      MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("SERVER=[ip address];DATABASE=[name of database];UID=[username];PWD=[password]");

      conn.Open();
      Console.WriteLine(string.Format("connection is '{0}'", conn.State));

        int counter = 0;
        string line;
        string the_word;
        string the_type;
        string sql;
        string the_type_temp;
        int typecount;
        int the_type_ctr;
        char[] delimiterChars = {'\\'};

        System.IO.StreamReader file = new System.IO.StreamReader(@"C:\folder\textfile.txt");
        while((line = file.ReadLine()) != null)
        {
           Console.WriteLine (line);
          string[] words = line.Split(delimiterChars);
          the_word=words[0];
          the_type=words[1];
          typecount = the_type.Length;
          the_type_ctr=0;

          MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("", conn); 

          cmd.CommandType=System.Data.CommandType.Text;

          while (the_type_ctr < typecount)
          {
            the_type_temp = the_type.Substring(the_type_ctr, 1);

            sql = string.Format(@"INSERT INTO [databasename].[tablename](field1,field2) VALUES ('{0}','{1}')", the_word.Replace(@"'", @"''"), the_type_temp);

            cmd.CommandText = sql;
            Console.WriteLine(sql);
            cmd.ExecuteNonQuery();

            the_type_ctr++;

          }

           counter++;
        }

        file.Close();

      conn.Close();

      Console.WriteLine(string.Format("connection is '{0}'", conn.State));

      Console.ReadLine(); //just pause the program so you can see any errors or success messages in the console

    }
  }
}

So, in the above code, I replaced some parts with brackets to indicate that the user will enter their own sql statements and connection parameters depending on your own connection and database set up.

Also, notice in this example we take a single line of data and end up splitting it into two parts using a delimiter character of ‘/’. Because of the C# language we use an escape character as in the following line of code

char[] delimiterChars = {'\\'};

If you wanted to change the values to handle comma delimited values you would adjust that line of code. Also notice that the sql insert statement contains two fields one for each of the values. Or if you have a single value then no delimiters are needed one could just comment out that section of code.

The above example also opens the connection before the loop starts. Then during the loop, the command text is set on the command object. This is because the data in the lines change, so we must change the sql. finally at the end the connection is closed. This is because it is too expensive to open and close the connection each time. It is much faster to just leave the connection open.

Also, notice that I included code that was applicable to my own situation where I wanted to make sure that I included a row of data even if multiple “types” were listed on a single line. Because of this there is in the above example an extra inner loop used to insert any multiple rows where there is more than one “type”.

Also, at the end , the program reads a line only because we want to pause it so we have a change to look at any console output (for debugging etc).