Technical Principles and source code sharing of SQL Server cursor Generation Tool

ERP reports are often made, involving reading data during the stored procedure, reading data associated with multiple tables, and using a cursor. Typical Example of reading a user table cursor

  declare  @ age  int   declare  @ name  varchar  (20)  declare  cur  cursor   for   select  Age, name  from  t_user  open  cur  fetch 
     next   from  cur  into 
     @ age, @ name  while  @ fetch_status = 0  begin   Update  t_user  set  [name] = @ name, age = @ age  fetch   next   from  cur  into  @ age, @ name  end   close  cur  deallocate 
      cur 

In actual applications, you often need to find this template, and then rewrite it based on the actual table results. The following two problems are frequently encountered:

1. The above example script does not know where it is stored, or there are many examples of scripts, which are not convenient to be found soon.

2. The example of rewriting the cursor is often repeated and there is no technical difficulty. For example, read the work order production plan and read the user.

After thinking, I wrote a cursor generation tool to generate the template above.CodeTo the code generator.

note that script cursor is used to generate a cursor template. Select a database, select the table name on the left of the tree, select the field value, and click execute

  declare  @ userid nvarchar (10)  declare  @ username nvarchar (50)  declare  cur  cursor   for   select  [userid], [username]  from  [ User ]  open  cur  fetch   next   from  cur  into  @ userid, @ username  while  @ fetch_status = 0  begin   fetch   next   from  cur  into  @ userid, @ username  end   close  cur  deallocate 
    cur 

 

Source codeLess than 50 lines. The full text is as follows:

List <columninfo> fieldlist = This . Getfieldlist (); stringbuilder builder = New Stringbuilder ();String Typename = String . Empty; Foreach (Columninfo In Fieldlist ){ Switch (Columninfo. typename ){ Case   "Datetime" : Case   "Int" : Case   "Image" : Case   "Bit" : Typename = columninfo. typename; Break ; Case   "Nvarchar" : Case   "Nchar" : Case   "Varchar" : Case   "Char" : Typename = String . Format ( "{0} ({1 })" , Columninfo. typename, columninfo. Length ); Break ;} Builder. appendline ( String . Format ( "Declare @ {0} {1 }" , Columninfo. columnname, typename);} var columns = String . Join ( "," , (From Column In Fieldlist select "[" + Column. columnname + "]" ). Toarray ()); String Fetchnex = String . Join ( "," , (From Column In Fieldlist select"@" + Column. columnname). toarray ()); String Update = String . Join ( "," , (From Column In Fieldlist select "@" + Column. columnname + "= [" + Column. columnname + "]" ). Toarray (); builder. appendline ( String . Format ( "Declare cur cursor for select {0} from [{1}]" , Columns, This . Tablename); builder. appendline ( "Open cur" ); Builder. appendline ( String . Format ( "Fetch next from cur into {0 }" , Fetchnex); builder. appendline ( "While @ fetch_status = 0" ); Builder. appendline ( "Begin" ); // Builder. appendline (string. Format ("Update [{0}] set {1}", this. tablename, update )); Builder. appendline ( String . Format ( "Fetch next from cur into {0 }" , Fetchnex); builder. appendline ( "End" ); Builder. appendline ( "Close cur" ); Builder. appendline ( "Deallocate cur" );

Note the following:

1. Square brackets must be added to the field name and table name in the generated script to avoid name conflict.

2. For the final generated SQL source code, you also need to apply the following method to uppercase SQL keywords.

The keyword of the SQL query statement is capitalized in the csdn Download Area. The full text is as follows:

  Private   Static RegEx regexsqlcapitalize = New RegEx ("\ BADD \ B |\\ baggregate \ B |\\ baction \ B |\\ Balter \ B |\\ bas \ B |\\ basc \ \ B | \ basypolicric \ B |\\ bauthorization \ B |\\ bbegin \ B |\\ bbinary \ B |\\ bbit \ B | \\ by \ B | \ bcascade \ B | \ bcase \ B | \ bcatalog \ B | \ bcharacter \ B | \ bchar \ B | \ bcheck \ B | \ bcheckpoint \ B |\\ bclose \ B |\\ bclustered \ B |\\ bconstraint \ B |\\ bcollate \\ B |\\ bcolumn \ B |\\ bcommit \ B |\\ bcontains \ B |\\ bcontinue \ B |\\ bcreate \ B |\\ bcross \ B | \ bcursor \ B | \ bdatabase \ B | \ bdeallocate \ B | \ bdesc \ B | \ bdecimal \ B | \ \ bdeclare \ B |\\ bdefault \ B |\\ bdelete \ B |\\ bdesc \ B |\\ bdistinct \ B |\\ bdouble \ B | \ bdrop \ B | \ belse \ B | \ bend \ B | \ bescape \ B | \ b1_t \ B | \ bexec \ \ B | \ bexecute \ B |\\ bexternal \ B |\\ bfetch \ B |\\ bfloat \ B |\\ bforeign \ B | \\ bFor \ B | \ bfrom \ B | \ bfunction \ B | \ bget \ B | \ Bgroup \ B | \ bgoto \ B | \ bgrant \ B | \ bhaving \ B | \ bidentity \ B | \ binto \ B | \ bindex \ B | \ binsert \\ B | \ binstead \ B | \ bint \ B | \ bkey \ B | \ bname \ B | \ BOF \ B | \ bon \ B | \ bopen \ B | \ boption \ B | \ border \ B | \ boutput \ B | \ bprimary \ B | \ \ breturn \ B |\\ brollback \ B |\\ bschema \ B |\\ bselect \ B |\\ bsize \ B |\\ b1_ric \ B | \ bset \ B | \ bserver \ B | (\ btable \ B) | \ BThen \ B | \ BTOP \ B | \ btime \ B | \ btimestamp \ B | \ BTO \ B | \ btrigger \ \ B | \ bprocedure \ B |\\ btype \ B |\\ bunion \ B |\\ bunique \ B |\\ bupdate \ B | \\ buse \ B | \ bvalues \ B |\\ bvalue \ B |\\ bvarchar \ B |\\ bview \ B |\\ bwhen \ B | \ bwhile \ B | \ bwhere \ B | \ BWith \ B | \ bnvarchar \ B | \ bnchar \ B | \ bdatetime \\ B | \ bfloat \ B | \ bdate \ B | \ bdatediff \ B | \ bdateadd \ B | \ bdatename \ B | \ bdatepart \ B | getdate | \ breferences \ B |\\ Babs \ B |\\ bavg \ B |\\ bcast \ B |\\ bconvert \ B | \ bcount \ B |\\ bday \ B |\\ bisnull \ B |\\ blen \ B |\\ Bmax \ B |\\ bmin \ \ B | \ bmonth \ B |\\ byear \ B |\\ breplace \ B |\\ bsubstring \ B |\\ bsum \ B | \\ bupper \ B | \ Buser \ B | \ ball \ B | \ bany \ B | \ band \ B | \ Bbetween \ B | \ bexists \ B | \ bin \ B | \ binner \ B | \ bis \ B | \ bjoin \ B | \ bleft \\ B | \ blike \ B | \ bnot \ B | \ bnull \ B | \ bor \ B | \ bright \ B | \ btry \ B | \ bcatch \ B", Regexoptions. ignorecase ); Public   Static   String Capitalizesqlclause ( String Source ){ // Divides data by row first RegEx rowreg = New RegEx ( "\ R \ n" ); String [] Strrows = rowreg. Split (source); stringbuilder strbuilder = New Stringbuilder (); Int Rowscount = strrows. length; For ( Int I = 0; I <rowscount; I ++ ){ // Remove one or more spaces in a row                  // Strrows [I] = RegEx. Replace (strrows [I], @ "\ s + ","");                  // Divide by Space                  String [] Strwords = strrows [I]. Split ( New   Char [ '\ 0' ]); Int Wordscount = strwords. length; For ( Int J = 0; j <wordscount; j ++) {strbuilder. append ( "" ); If (Regexsqlcapitalize. ismatch (strwords [J]) {matchcollection MC = regexsqlcapitalize. Matches (strwords [J]); Int Mccount = mc. count; For ( Int K = 0; k <mccount; k ++) {strwords [J] = strwords [J]. replace (MC [K]. value, MC [K]. value. toupper ();} strbuilder. append (strwords [J]);} Else {Strbuilder. append (strwords [J]);} strbuilder. append ( "" );} Strbuilder. append ("\ R \ n" );} Return Strbuilder. tostring (). Replace ( "\ R \ n" , "\ R \ n" );}

The regular expression replaces the keywords in the string. This method does not have any dependencies and can be copied to your project or class library to add the keyword uppercase function for the SQL script.

 

3. If the SQL script formatting function can be used to format the generated SQL script, a beautiful SQL script will be generated to increase readability. SQL pretty printer can do this, but the API is not found to call this function.

4. The multi-Table associated cursor template is not implemented. You should try to generate a cursor from multiple associated tables. However, it is difficult to automatically generate the relationship between the table and the table, for example, the following sub-Table cursor query statement

 
DeclareCurCursor For SelectR. Description, R. workcenterFromJoborder J, joborderrouting RWhereJ. jobno = R. jobnoOpenCur
 

The cursor needs to read data from two associated tables. If there is a foreign key association between two tables, you can generate the relationship between the foreign key association fields of two tables, that is to say, the preceding SQL cursor can be automatically generated, but some two tables do not have a foreign key Association, you still need to manually specify it, which is equivalent to a semi-finished cursor generator, therefore, we had to remove this function and only generate the cursor query for several fields in a table in the simplest case. We did not design the cursor for multi-table queries.