Exercise
SQL to text
Objetive
You must create a C# program that is capable of parsing SQL INSERT commands and extracting their data into separate lines of text, as follows. If the input file contains 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:
Table: people
name: smith, pedro
address: your street
age: 23
Table: people
name: juan
address: calle cinco, 6
age: 24
Table: cities
code: a
name: alicante
Example 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();
}
}
}