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