C# 3 regular expressions match SQL table names

There is a small requirement in the project. C# 3 regular expressions match SQL table names. The table name involved in the SQL executed by the data access layer needs to be extracted through C# code, which is used to record some log-related content, because it is an older project. The data access layer is completely written by ado.net, and does not use advanced ORMs such as ef core to assist with persistence.

Regular expressions match SQL table names

If you want to extract SQL from the data access layer (or what the persistence layer does), it is very convenient for ef core. There are similar articles on this site before. EF Core gets the context SQL statement.

The requirement now encountered is a simple extraction of various types of statements, such as select, update, delete, and insert. In simple terms, it is to extract the table name from the SQL involved in the addition, deletion, and modification of the query. If you use string interception, etc. More troublesome, this kind of dirty work, tired work is left to regular expressions to complete. Stop talking nonsense, just upload the code and use it.

Core code

        /// <summary>
        /// Parse out the table names written, updated, and deleted from the SQL statement
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static List<string> GetTables(string sql)
        {
            var insert = @"[\s\t]*INSERT [\s\t\r\n]*(INTO|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";
            var update = @"[\s\t]*UPDATE [\s\t\r\n]*(SET|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";
            var delete = @"[\s\t]*DELETE *(FROM|)[\s\t\r\n]*(WHERE|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";

            List<string> list = new List<string>();
            var m = Regex.Matches(sql, insert, RegexOptions.IgnoreCase);
            foreach (Match item in m)
            {
                list.Add(item.Value.Trim());
            }
            var m2 = Regex.Matches(sql, update, RegexOptions.IgnoreCase);
            foreach (Match item in m2)
            {
                list.Add(item.Value.Trim());
            }
            var m3 = Regex.Matches(sql, delete, RegexOptions.IgnoreCase);
            foreach (Match item in m3)
            {
                list.Add(item.Value.Trim());
            }
            return list.Select(x => { return x.Substring(x.LastIndexOf(' ')).Trim(); }).Distinct().ToList();
        }

Complete code

class Program
    {
        static void Main(string[] args)
        {
            var sql = @"
UPDATE jhrs.com
   SET [Id] = <Id, uniqueidentifier,>
      ,[Name] = <Name, varchar(50),>
      ,[xSql] = <xSql, text,>
      ,[Num] = <Num, int,>
      ,[Tag] = <Tag, varchar(50),>
      ,[Gec] = <Gec, text,>
      ,[Key] = <Key, varchar(50),>
 WHERE <搜索条件,,>
GO

INSERT znlive.net
           ([Id]
           ,[Key]
           ,[UserId]
           ,[Message]
           ,[ErrorUrl]
     VALUES
           ('')
GO

DELETE FROM [dbo].[Sys_Manage]
      WHERE <search condition,,>
GO
";
            var list = GetTables(sql);
            foreach (var item in list)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Parse out the table names written, updated, and deleted from the SQL statement
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static List<string> GetTables(string sql)
        {
            var insert = @"[\s\t]*INSERT [\s\t\r\n]*(INTO|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";
            var update = @"[\s\t]*UPDATE [\s\t\r\n]*(SET|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";
            var delete = @"[\s\t]*DELETE *(FROM|)[\s\t\r\n]*(WHERE|)[\s\t\r\n]*[A-Za-z0-9_.""\[\]]*[\s\t\r\n]*";

            List<string> list = new List<string>();
            var m = Regex.Matches(sql, insert, RegexOptions.IgnoreCase);
            foreach (Match item in m)
            {
                list.Add(item.Value.Trim());
            }
            var m2 = Regex.Matches(sql, update, RegexOptions.IgnoreCase);
            foreach (Match item in m2)
            {
                list.Add(item.Value.Trim());
            }
            var m3 = Regex.Matches(sql, delete, RegexOptions.IgnoreCase);
            foreach (Match item in m3)
            {
                list.Add(item.Value.Trim());
            }
            return list.Select(x => { return x.Substring(x.LastIndexOf(' ')).Trim(); }).Distinct().ToList();
        }
    }

C# 3 regular expressions match SQL table names

Running result

After testing, the above code can meet the requirements, but if you have a simpler way of writing, please leave a message and feedback.