SQL dynamic configuration, dynamic parsing of SQL

The way in which SQL is dynamically configured in a project can make later maintenance and post-release modifications more convenient, regardless of how you use that configuration to resolve to the final, truly executable SQL.
The following code is a relatively simple processing method, SQL parameters to # #括起来.

1. Create a SQL that needs to be parsed in the code, and you need to configure it later

/// <summary> /// temporarily used for testing /// </summary> Static string " SELECT * from tb_user WHERE ID = #ID # and NAME like '% #NAME #% ' ";

2. Establish a method based on the parameter key to parse, generally use this method

/// <summary>///parsing sql and parameter collections into SQL that needs to be executed/// </summary>/// <param name= "str" ></param>/// <param name= "dic" ></param>/// <returns></returns> Public Static stringAnalyticalbydic (stringSTR, dictionary<string,string>DIC) {STR=Str.    ToUpper (); foreach(varIteminchdic) {str= str. Replace (string. Format ("#{0}#", item. Key.toupper ()), item.    Value); }    returnstr;}

3. Establish a method based on sequential parsing, which is not recommended

/// <summary>///parsing sql and parameter collections into SQL that needs to be executed/// </summary>/// <param name= "str" ></param>/// <param name= "list" ></param>/// <returns></returns> Public Static stringAnalyticalbylist (stringSTR, list<string>list) {STR=Str.    ToUpper (); List<string> sqllist =Newlist<string>(); varSqlstr =str; inti =0; intj =0; intsum =0;  while(true) {i= str. Substring (sum, str.) Length-sum). IndexOf ('#') +1; Sum+=i; J= str. Substring (sum, str.) Length-sum). IndexOf ('#'); if(I <0|| J <0)        {             Break; } sqllist.add (str.        Substring (Sum, j)); Sum+ = j +1; }     for(intindex =0; Index < sqllist.count && Index < list. Count; index++) {str= str. Replace (string. Format ("#{0}#", Sqllist[index].    ToUpper ()), List[index]); }    returnstr;}

4. Method has been built, just try the demo to see the effect, directly in the main inside the call is good

Static voidMain (string[] args) {Dictionary<string,string> dic =Newdictionary<string,string>(); Dic. ADD ("ID","123"); Dic. ADD ("Name","Jimmy.pan"); stringsql =_sql;    Console.WriteLine (Analyticalbydic (SQL, DIC)); List<string> list =Newlist<string>(); List. ADD ("456"); List. ADD ("Jimmy"); SQL=_sql;    Console.WriteLine (analyticalbylist (SQL, list)); Console.read ();}

5. Running results such as

6. Overall code

usingSystem;usingSystem.Collections.Generic;namespacesqlanalytical{classProgram {Static voidMain (string[] args) {Dictionary<string,string> dic =Newdictionary<string,string>(); Dic. ADD ("ID","123"); Dic. ADD ("Name","Jimmy.pan"); stringsql = _sql;Console.WriteLine (Analyticalbydic (SQL, DIC)); List<string> list =Newlist<string>(); List. ADD ("456"); List. ADD ("Jimmy"); SQL=_sql;            Console.WriteLine (analyticalbylist (SQL, list));        Console.read (); }        /// <summary>        ///parsing sql and parameter collections into SQL that needs to be executed/// </summary>        /// <param name= "str" ></param>        /// <param name= "dic" ></param>        /// <returns></returns>         Public Static stringAnalyticalbydic (stringSTR, dictionary<string,string>dic) {str=Str.            ToUpper (); foreach(varIteminchdic) {str= str. Replace (string. Format ("#{0}#", item. Key.toupper ()), item.            Value); }            returnstr; }        /// <summary>        ///parsing sql and parameter collections into SQL that needs to be executed/// </summary>        /// <param name= "str" ></param>        /// <param name= "list" ></param>        /// <returns></returns>         Public Static stringAnalyticalbylist (stringSTR, list<string>list) {STR=Str.            ToUpper (); List<string> sqllist =Newlist<string>(); varSqlstr =str; inti =0; intj =0; intsum =0;  while(true) {i= str. Substring (sum, str.) Length-sum). IndexOf ('#') +1; Sum+=i; J= str. Substring (sum, str.) Length-sum). IndexOf ('#'); if(I <0|| J <0)                {                     Break; } sqllist.add (str.                Substring (Sum, j)); Sum+ = j +1; }             for(intindex =0; Index < sqllist.count && Index < list. Count; index++) {str= str. Replace (string. Format ("#{0}#", Sqllist[index].            ToUpper ()), List[index]); }            returnstr; }        /// <summary>        ///temporarily used for testing/// </summary>        Static string_sql ="SELECT * from tb_user WHERE ID = #ID # and NAME like '% #NAME #% '"; }}

SQL dynamic configuration, dynamic parsing of SQL