using System;
using System.IO;
using System.Collections.Generic;
namespace SQLProcess
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!" + DateTime.Now.ToString("yyyyMMdd"));
Dictionary<string, string> tables = new Dictionary<string, string>();
Dictionary<string, string> columns = new Dictionary<string, string>();
//ReadFile();
try
{
// Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (StreamReader sr = new StreamReader(@"C:UsersstepwanDesktopSQLProcessSQLQuery12.sql"))
{
String line;
// Read and display lines from the file until the end of
// the file is reached.
var isTable=false;
while ((line = sr.ReadLine()) != null)
{
if(line.IndexOf("FROM")>-1){
isTable=true;
}
if(!isTable){
var dotIndex= line.IndexOf(".");
if(dotIndex>-1){
var tableAs=line.Substring(0,dotIndex);
//Console.WriteLine("tableAs:"+tableAs);
var spaceIndex=line.IndexOf(" ",dotIndex);
if(spaceIndex>-1){
var len = spaceIndex;
var colunmName=line.Substring(0,len).Trim();
if(!columns.ContainsKey(colunmName))
columns.Add(colunmName,"");
Console.WriteLine("columnName:"+colunmName.Trim());
}
}
}
else
{
//from
if(line.IndexOf(" FROM ")>-1){
var fromIndex=line.IndexOf(" FROM ")+6;
var spaceIndex= line.IndexOf(" ",fromIndex);
if(spaceIndex>-1)
{
var len =spaceIndex-fromIndex;
var tableName=line.Substring(fromIndex,len).Trim();
var len2=line.Length -spaceIndex;
var tableAs =line.Substring(spaceIndex,len2).Trim();
Console.WriteLine($"tableName:{tableName};tableAs:{tableAs} ,spaceIndex:{spaceIndex},secondeSpaceIndex:{spaceIndex}");
if(!tables.ContainsKey(tableName))
{
tables.Add(tableName,tableAs);
}
}
}
else
{
if(line.IndexOf(" JOIN ")>-1)
{
var joinIndex=line.IndexOf(" JOIN ")+6;
var onIndex=line.IndexOf(" ON ");
if(onIndex>-1){
var tabelLen=onIndex-joinIndex;
var tableAs=line.Substring(joinIndex,tabelLen).Trim();
var tabelArray=tableAs.Split(' ');
if(tabelArray.Length>0){
var tableName=tabelArray[0].Trim();
var tabelAsName=tabelArray[1].Trim();
Console.WriteLine($"tableName:{tableName},{tabelAsName}");
if(!tables.ContainsKey(tableName))
{
tables.Add(tableName,tabelAsName);
}
}
var conditionLen=line.Length-(onIndex+3);
var condition =line.Substring(onIndex+3,conditionLen);
if(condition.IndexOf(" AND ")>-1)
{
var conditionArray=condition.Split(" AND ");
for (int i = 0; i < conditionArray.Length; i++)
{
Console.WriteLine($"conditionArray[{i}]:{conditionArray[i]}");
var tableColumnArrary=conditionArray[i].Split('=');
for(int c = 0; c < tableColumnArrary.Length; c++)
{
var column=tableColumnArrary[c].Trim();
if(!columns.ContainsKey(column))
{
columns.Add(column,"");
}
Console.WriteLine($"tableColumnArrary[{c}]:{column}");
}
}
}
else
{
Console.WriteLine($"condition:{condition}");
//table column
var tableColumnArrary=condition.Split('=');
for(int i = 0; i < tableColumnArrary.Length; i++)
{
var column=tableColumnArrary[i].Trim();
if(!columns.ContainsKey(column))
{
columns.Add(column,"");
}
Console.WriteLine($"tableColumnArrary[{i}]:{column}");
}
}
}
}
}
}
Console.WriteLine(line);
}
}
}
catch (Exception e)
{
throw;
}
CreateTable(tables,columns);
}
public static void ReadFile()
{
using (StreamReader sr = new StreamReader(@"C:UsersstepwanDesktopSQLProcessSQLQuery12.sql"))
{
String line;
// Read and display lines from the file until the end of
// the file is reached.
var isTable=false;
var i=0;
while ((line = sr.ReadLine()) != null)
{
Console.WriteLine($"line{i}:{line}");
i++;
}
}
}
public static void CreateTable(Dictionary<string,string> tables,Dictionary<string,string>columns)
{
var t=0;
foreach (var item in tables)
{
t++;
Console.WriteLine($"{t} {item.Key} ,{item.Value}");
}
var c=0;
foreach (var item in columns)
{
var tableAs=item.Key.Split('.')[0];
var output=item.Key;
foreach (var table in tables)
{
if(table.Value.ToUpperInvariant()==tableAs.ToUpperInvariant())
{
output=output.Replace(tableAs,table.Key) ;
}
}
c++;
Console.WriteLine($"{c} {output}");
}
}
static void ColumnProcess()
{
}
static void JoinTableProcess()
{
}
static void FromTableProcess()
{
}
}
}