解析SQL中的包含的列和表

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:\Users\stepwan\Desktop\SQLProcess\SQLQuery12.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:\Users\stepwan\Desktop\SQLProcess\SQLQuery12.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()
        {
            
        }


    }
}

 

转载于:https://www.cnblogs.com/hellohongfu/p/7516087.html