SQL to text Learn programming with C# Sharp exercises

Lesson:

File Management


Exercise:

SQL to text 48


Objetive:

You must create a C# program capable of parsing SQL language INSERT commands and extracting their data to separate lines of text, as follows: if the input file contained these three lines:

insert into people (name, address, age) values ("smith, pedro", "your street", 23);

insert into people (name, address, age) values ("juan", "calle cinco, 6", 24);

insert into cities (code, name) values ("a" "alicante");

The resulting file should have on each line the name of a field, followed by a "colon" and its value. In addition, each record must be preceded by the name of the table and followed by a blank line, like this:

persons name: smith, pedro address: your street age: 23

persons name: juan Address: 5th Street, 6 age: 24

cities code: a name: alicante


Code:

using System;
using System.IO;
namespace SQL2text
{
    class Program
    {
        static void Main(string[] args)
        {
            StreamReader ficheroEntrada = null;
            string linea;
            string nombre;

            Console.WriteLine("Not enough parameters!");
            Console.Write("Enter file name: ");
            nombre = Console.ReadLine();

            try
            {
                ficheroEntrada = File.OpenText(nombre);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            try
            {
                do
                {
                    linea = ficheroEntrada.ReadLine();
                    if (linea != null)
                    {
                        Console.WriteLine();
                        string tableName = linea.Substring(12).Split(' ')[0];
                        string[] campo = linea.Substring(linea.IndexOf("(") + 1,
                                linea.IndexOf(")") - linea.IndexOf("(") - 1).Split(',');
                        string[] valores = linea.Substring(linea.IndexOf("values (") + 9,
                                linea.IndexOf(");") - linea.IndexOf("values (") - 9).Split(',');

                        Console.WriteLine(tableName);
                        for (int i = 0; i < campo.Length; i++)
                        {
                            Console.Write(campo[i].Trim() + ": ");
                            Console.WriteLine(valores[i].Trim().Replace("'", ""));
                        }
                    }
                } while (linea != null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.ReadLine();
        }
    }
}