Proposed exercise
You must create a C # program can analyze language INSERT SQL commands and extract your data to separate text lines, as follows: if the input file contains these three lines:
insert into people (name, address, age) values ('smith, pedro', 'the street', 23);
insert into people (name, address, age) VALUES ('john', 'street five, 6', 24);
insert into cities (code, name) values ('a', 'Alicante');
the resulting file on each line should have a field name, followed by "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:
people
name: smith, pedro
address: the street
Age: 23
people
name: john
address: street five, 6
Age: 24
cities
code: a
Name: alicante
The name of the file to be analyzed should be asked to the user.
Solution
using System;
using System.IO;
class SQLToText
{
static void Main()
{
string name = Console.ReadLine();
try
{
string line;
do
{
StreamReader fileSQL = File.OpenText(name);
line = fileSQL.ReadLine();
string table;
int positionFieldsBef, positionFieldsAft;
string[] fields;
int positionValuesBef, positionValuesAft;
string[] values;
if (line != null)
{
table = line.Substring(12).Split(' ')[0];
positionFieldsBef = line.IndexOf("(") + 1;
positionFieldsAft = line.IndexOf(")") - line.IndexOf("(") - 1;
fields = line.Substring(
positionFieldsBef, positionFieldsAft).Split( ',' ).Trim();
positionValuesBef = line.IndexOf("values (") + 9;
positionValuesAft = line.IndexOf(");") - line.IndexOf("values (") - 9;
values = line.Substring( positionValuesBef, positionValuesAft ).Split(',').Replace("'", "").Trim();
Console.WriteLine("Name Table");
Console.WriteLine("-------------------");
Console.WriteLine( table );
Console.WriteLine("Fields");
Console.WriteLine("-------------------");
int i = 0;
foreach (string field in fields)
{
Console.WriteLine(field + " = " values[i]);
i++;
}
}
fileSQL.Close();
}
while (line != null);
}
catch (Exception)
{
Console.WriteLine( "Error" );
}
}
}