SQL Server游标生成工具

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 

在实际应用时，经常需要找到这个模板，然后再根据实际的表结果，重写一遍。经常遇到以下二个问题

1  上面的例子脚本不知道放在哪里了，或是有很多例子脚本，不方便很快找出来

2  重写游标的例子，经常重复，又没有技术难度可言。比如读取工作单生产计划，读取用户。

经过思考，于是写个游标生成工具，把上面的模板代码，应用到代码生成器中。

注意上图中的Script Cursor，这是用来生成游标模板的。选择一个数据库，树左边选择表名，勾选字段值，点击执行

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  

源代码不到50行，全文如下

 List<ColumnInfo>  fieldlist = this.GetFieldlist();  StringBuilder builder=new StringBuilder();  string typeName = string.Empty;              foreach (ColumnInfo 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"); 

有以下几点需要注意

1  生成的脚本中，字段名称，表名称，均要加上方括号，以避免名称重突。

2  最后生成的SQL源代码，还需要应用下面的方法，将SQL关键字大写。

将SQL查询语句的关键字大写的方法来自CSDN下载区，全文如下

private static Regex RegexSQLCapitalize = new Regex("\badd\b|\baggregate\b|\baction\b|\balter\b|\bas\b|\basc\b|\basymmetric\b|\bauthorization\b|\bbegin\b|\bbinary\b|\bbit\b|\bby\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|\bexcept\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|\bsymmetric\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) {             //先按行划分            Regex rowReg = new Regex("rn");            string[] strRows = rowReg.Split(source);             StringBuilder strBuilder = new StringBuilder();            int rowsCount = strRows.Length;             for (int i = 0; i < rowsCount; i++)            {                //去掉一行中的一个或多个空白                //strRows[i] = Regex.Replace(strRows[i], @"s+", " ");                 //按空格划分                string[] strWords = strRows[i].Split(new char['�']);                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 mcmcCount = 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("rn");            }            return strBuilder.ToString().Replace("rnrn", "rn"); } 

正则表达式替换字符串中的关键字，这个方法没有任何依赖，可拷贝到您的项目或类库中，为SQL 脚本增加关键字大写功能。

3  SQL 脚本格式化功能  假如能把生成的SQL脚本格式化一下，生成美观的SQL脚本，增加可读性。SQL Pretty Printer可以做到，但是没有找到API可以调用这个功能。

4 多表关联的游标模板没有做到。应该尝试从多个关联表中生成游标。不过表与表之间的关系难以自动生成，比如像下面的母子表游标询语句

Declare Cur Cursor For Select r.Description,r.WorkCenter  FROM JobOrder  j, JobOrderRouting r      WHERE j.JobNo=r.JobNo Open Cur 

游标要从2个关联的表中读取数据，假如2个表之间有外键关联，可以生成2个表的外键关联字段的关系，也就是上面的SQL游标可以自动生成，但是有的2个表之间没有外键关联的，还是要手工指定，相当于是个半成品的游标生成器，于是只好把这个功能点拿掉，只做最简单的一种情况，生成一个表的若干个字段的游标查询，没有设计多表查询的游标。

原文链接：http://www.cnblogs.com/JamesLi2015/archive/2013/05/20/3088024.html