利用正则表达式类解析SQL语句，达到Worklist兼容各个RIS数据库的目的

在做RIS的项目中遇到一个问题， 因为Worklist要兼容各个RIS数据库， 因此设计了目前这个架构。 

1.xml文件来配置RIS数据库的数据源， 

2.xml文件来存储关于查询/更新数据库的SQL语句， 其中参数/值输入可用用{x}来代替。 如{0}代表第一个参数

3.xml来配置Worklist SCU的query的字段和数据库字段的映射， 可以用Tag ID或者Tag Name与数据库中的Column名来匹配。

4.工作流程： Worklist SCU发出Query， WorklistSCP收到后利用映射文件， 解析成要查询的column名字，根据xml中查询数据库的xml的模板，生成真正的Sql查询语句。调用数据库API执行Sql语句。 

 public class RISQueryHelper
    {
        public string BuildSelectQuery(string select)
        {
            if (String.IsNullOrEmpty(select))
            {
                throw new ArgumentNullException("input select query is null.");
            }
 
            string query = select.Replace("@", "").Replace("'", "'");
 
            return query;
        }
 
        public string BuildUpdateQuery(string update, params object[] columnValues)
        {
            if (String.IsNullOrEmpty(update))
            {
                throw new ArgumentNullException("input sql statement is null or empty.");
            }
 
            BuildTableRelationship(update);
 
            string sql = update.Replace("@", "").Replace("'", "'");
 
            //Sql template mark the parameter by{x},x like: 1,2,3,4,5,...n
            //For example: UPDATE @RequestedProcedure SET @Status='{0}' WHERE @Order_Refid IN (SELECT @Refid FROM @Orders WHERE @AccessionNo='{1}';
            //So we can use String.Format to replace {x} by our real value.
            {
                sql = String.Format(sql, columnValues);
            }
 
            return sql;
        }
 
        public void BuildTableRelationship(string update)
        {
            //{Get table & column name, we will use these data to construct relationship between tables and column for further.
            string[] tables = null;
            string[] cols = null;
 
            if (!BuildUpdateQueryArgs(update, out tables, out cols))
            {
                throw new Exception("Build sql statement failed. Input sql is : " + update);
            }
            //end, need to parse when statement has many tables.}
        }
 
        public bool BuildUpdateQueryArgs(string update, out string[] tableNames, out string[] columnNames)
        {
            if (String.IsNullOrEmpty(update))
            {
                throw new ArgumentNullException("input select query is null.");
            }
 
            List<string> tables = new List<string>();
            List<string> columns = new List<string>();
            string sql = update;
 
            BuildTableNames(ref sql, ref tables);
            BuildColumnNames(ref sql, ref columns);
 
            tableNames = tables.ToArray();
            columnNames = columns.ToArray();
 
            return true;
        }
 
        private void BuildTableNames(ref string sql, ref List<string> tables)
        {
            var statement = Regex.Matches(
                sql,
                @"\bfrom\b.+?\bwhere\b|\bupdate\b.+?\bset\b",
                RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);
 
            foreach (Match t in statement)
            {
                //delete table operation from sql statement.
                sql = sql.Replace(t.Value, "");
 
                //save table name
                tables.Add(Regex.Replace(t.Value, @"\bupdate\b|@|\bset\b|\s|\bfrom\b|\bwhere\b", "", RegexOptions.IgnoreCase));
            }
        }
 
        private void BuildColumnNames(ref string sql, ref List<string> columns)
        {
            var statement = Regex.Matches(
                sql,
                @"@\w+\b\s*=",
                RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);
 
            foreach (Match t in statement)
            {
                //delete column operation from sql statement.
                sql = sql.Replace(t.Value, "");
 
                //save column name
                columns.Add(Regex.Replace(t.Value, @"@|\s|=", "", RegexOptions.IgnoreCase));
            }
        }
    }