一个用CLR写存储过程的项目回顾 (一) 开发过程

前段时间接到一个Task,是用CLR来改写现有的生成流水号存储过程,改写的原因主要是因为原的代码逻辑比较复杂,没有人能继续维护,同时也探讨用CLR来编写存储过程的可能性，借以提高开发效率,

接到任务的时候非常高兴,毕竟这个算是业务的一个核心,有一定的难度和挑战;

马上就开始动手准备了;

先是了解这个task的要求功能

里面有3种case,最主要的一个case 是设定有 CommonStartPrefix  
CommonStartPrefix  的结构如下

TableName	ColName	Prefix1	Prefix2	Prefix3	PrefixY	PrefixM	PrefixD	PrefixOrder	IsAlpha	ViewRef
使用GetDocNo之Table及Key ColumnName		前缀值1	前缀值2	前缀值3	yy/yyyy	m/mm	d/dd	1/2/3/y/m/d	0/1 流水号会否使用英文字母	参考Table/View

根据CommonStartPrefix  的设定值来确定流水的生成流水的值格式

再查询系统表或由传入的参数来生成流水的长度值

最后生成流水号再update记录流水的CommonStart表
举个例来说:
CommonStartPrefix  的值为

CommonStart 的值为


那么选择StartDate为2006-1-18,docLen为10,生成的最后docno为120061J002

1 为Prefix1的值,2006为传入的月份,J为18号,002为查询的CommonStart 的值

了解功能后,然后就开始看以前的存储过程,代码写得有点混乱,看起来sql真是有点痛苦

在痛苦中熬了2天后,终于大概明白了这个存储过程的流程,开始找资料准备了

先后在cnblogs,msdn,和webcast里找来资料,开始弄这个东西了

时间分配是
1 安装开发环境 0.5 day
2 测试msdn 例子 0.5 day
3 理解开发spec和阅读getdocnocom  0.5  day     
4 整理开发内容 和 编写测试文档 0.5 day
5  处理接口和运算逻辑 1 day
6 编写字符处理方法 1.5 day   
7 自测 和编写测试文档 0.5 day

安排好了就开始一个个干了

安装环境的时候,我先装的是VS2005,再安装的SQLServer2005,后来发现其实安装VS2005其实就可以开发了,使用他自带的SQLEXPRESS就可以了

测试例子最先找的测试例子是ugoer兄的例子：
http://www.cnblogs.com/ugoer/archive/2005/04/01/129986.html

拿来一试编译通不过,后来才看清楚那是beta版的写法,看来正式版和beta版是有差异的
beta版是这样写的

[SqlProcedure]
    public static void InsertData(SqlString name)
    {
        SqlCommand InsertCurrencyCommand = SqlContext.GetCommand();
        InsertCurrencyCommand.CommandText = "INSERT INTO table1 (Name, addDate) VALUES ('"+name.Value+"', '" + DateTime.Now.ToString() + "')";
        InsertCurrencyCommand.ExecuteNonQuery();
    }

而正式版的写法是:
    [SqlProcedure]
    public static void InsertData(string name)
    {
        using (SqlConnection myConnection = new SqlConnection("context connection=true"))
        {
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand("insert TestCLR values ('1','"+name+"')",myConnection);
            SqlContext.Pipe.ExecuteAndSend(myCommand);//
        }
    }

开始阅读整理逻辑是比较痛苦的,原来的SQL语句为了生成这些字符样式，用了好多奇怪的用法和IF，主要是为了好配置生成流水号的样式

接下来定义的方法,把以前写在sql的功能抽离出来,生成一个个static的方法,这样抽离后代码结构看起来清爽很多,也可以利用c#中自带的方法,例如正则表达验证什么;
 
下一步是把sql的逻辑转换C#的代码逻辑表现来出来,呵呵,以前要用游标写的地方,现在DataReader就可以搞定了,而且VS2005的智能提示用起来很不错的,代码要写得快得多了,不过这部分的时间比预计要延长了些;比较老火的地方是sqlconnection在存储过程中不能多次new,这样在循环里要做查询的时候就没有办法了,如果有这样的情况只有又在代码里嵌套游标

花了2天时间基本把拼sql和处理代码逻辑的事情搞完了,剩下的是测试和优化效能了

测试的时候,还是比较原始,基本按照功能说明的地方走了一下功能测试,检查了下功能是否满足,最后用sqlserver2005自带的显示客户端统计信息来统计了下最后执行的结果;又修修改改了下,效率方面基本和写存储过程差不多,值的注意的是最后部署要改为Release模式,默认是Debug模式

到了这一步,工作应该进行到80%了吧
最后附开发代码:

  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Data.SqlTypes;
  5using System.Text;
  6using Microsoft.SqlServer.Server;
  7
  8 
  9
 10/**//************************************************************
 11  Author:  CQ David          Date: 2006-01-24
 12  Description:     //修改獲取流水號存儲過程
 13  History:         
 14             <author>   <time>         <desc>
 15Create      CQ David    2006-01-24 　     Create,測試微軟msdn例子
 16Modify      CQ David    2006-01-31 　     定義接口和函數
 17
 18***********************************************************/
 19
 20public partial class StoredProcedures
 21{
 22
 23    GetDocNoComSqlProcedure#region GetDocNoComSqlProcedure
 24    /**//// <summary>
 25    /// 存储过程主函数
 26    /// </summary>
 27    /// <param name="TableName">獲取流水号的TableName</param>
 28    /// <param name="colName">传入健值</param>
 29    /// <param name="StartDate">獲取的时间</param>
 30    /// <param name="OtherKey">其他健值</param>
 31    /// <param name="docLen">獲取的长度</param>
 32    /// <param name="docNo">输出流水号</param>
 33    [SqlProcedure]    
 34    public static void CLR_GetDocNoCom(string TableName,string colName ,string StartDate,string OtherKey,int docLen,out string docNo)
 35    {
 36        SqlConnection myConnection = new SqlConnection("context connection=true");
 37        try
 38        {
 39            Definition#region Definition
 40
 41            DateTime StartDateValue;//開始時間
 42
 43            StringBuilder sb = new StringBuilder();//查询所用的StringBuilder
 44
 45            string TempDocNo;//臨時DocNo
 46
 47            #endregion
 48            
 49
 50            myConnection.Open();
 51
 52            StartDateValue = FormatStartDate(StartDate);
 53
 54            SqlDataReader dr_1 = GetCommonStartPrefix(TableName, colName, sb, myConnection);
 55
 56            TempDocNo = FormatTempDocNo(dr_1, StartDateValue, StartDate, TableName, colName, OtherKey, docLen, sb, myConnection);
 57
 58            docNo = TempDocNo;
 59
 60        }
 61        catch(Exception ex)
 62        {
 63            if (ex.Message == "Initial Doc No # not set. Please set the Doc No")
 64            {
 65                SqlContext.Pipe.Send("Initial Doc No # not set. Please set the Doc No");
 66            }
 67            else
 68            {
 69                SqlContext.Pipe.Send("Expected Doc No not gained !");
 70            }
 71            docNo = string.Empty;
 72        }
 73        finally
 74        {
 75            myConnection.Close();
 76        }
 77    }
 78
 79    #endregion
 80
 81    Function#region Function
 82   /**//// <summary>
 83   /// 格式化TempDocNo
 84   /// </summary>
 85   /// <param name="dr">传入CommonStartPrefix信息</param>
 86   /// <param name="StartDateValue">开始时间</param>
 87   /// <param name="StartDate">TempDocNo</param>
 88   /// <param name="TableName">獲取TableDate名</param>
 89   /// <param name="colName">獲取的健名</param>
 90   /// <param name="OtherKey">其他健名</param>
 91   /// <param name="docLen">长度</param>
 92   /// <param name="sb">构造sql的StringBuilder對象</param>
 93   /// <param name="myConnection">連接Connection</param>
 94   /// <returns>返回值</returns>
 95    private static string FormatTempDocNo(SqlDataReader dr, DateTime StartDateValue, string StartDate, string TableName, string colName, string OtherKey, int docLen, StringBuilder sb, SqlConnection myConnection)
 96    {
 97        string strTempDocNo = string.Empty; //存儲臨時TempDocNo的值
 98        string strReGen = string.Empty;
 99        bool isAlpha = false;               //需要格式化流水號
100        string strKeyName = string.Empty;    //定義流水的keyName 
101        string strLength = string.Empty;    //定義流水的長度2 
102        string strColValue = string.Empty;    //定義流水的属性值
103        string sqlwhere = " and startdate = '" + Convert.ToDateTime(StartDate).ToString("yyyy-MM-dd") + "'";//需要查詢的日期.
104        string startValue = string.Empty; //獲DocNo的取开始值     
105
106        if (dr.Read())
107        {
108            if (!dr.IsDBNull(0))
109            {
110                如果CommonStartPrefix有值按PrefixOrder格式化日期#region 如果CommonStartPrefix有值按PrefixOrder格式化日期
111
112                string strPrefixOrder = dr["PrefixOrder"].ToString();//前缀PrefixOrder
113                isAlpha = Convert.ToBoolean(dr["isAlpha"]);
114                if (strPrefixOrder.Length != 0)
115                {
116
117                    for (int i = 0; i < strPrefixOrder.Length; i++)
118                    {
119                        if (strPrefixOrder.Substring(i, 1) == "1")
120                        {
121                            strTempDocNo = strTempDocNo + dr["Prefix1"].ToString();
122                        }
123                        else if (strPrefixOrder.Substring(i, 1) == "2")
124                        {
125                            strTempDocNo = strTempDocNo + dr["Prefix2"].ToString();
126                        }
127                        else if (strPrefixOrder.Substring(i, 1) == "3")
128                        {
129                            strTempDocNo = strTempDocNo + dr["Prefix3"].ToString();
130                        }
131                        else if (strPrefixOrder.Substring(i, 1) == "y")
132                        {
133                            string strPrefix = dr["Prefixy"].ToString().ToLower();
134
135                            if (strPrefix == "yy")
136                            {
137                                strTempDocNo = strTempDocNo + FormatYearByYY(StartDateValue);
138                            }
139                            else if (strPrefix == "yyyy")
140                            {
141                                string TempYear = StartDateValue.Year.ToString().TrimEnd();
142                                strTempDocNo = strTempDocNo + TempYear.Substring(TempYear.Length - 4, TempYear.Length);
143                                //strReGen = "year";
144                            }
145                        }
146                        else if (strPrefixOrder.Substring(i, 1) == "m")
147                        {
148                            if (dr["PrefixM"].ToString() == "m")
149                            {
150                                strTempDocNo = strTempDocNo + FormatMonthByM(StartDateValue);
151                            }
152                            else if (dr["PrefixM"].ToString() == "mm")
153                            {
154                                string TempMonth;
155                                TempMonth = "0" + StartDateValue.Month.ToString("");
156                                strTempDocNo = strTempDocNo + TempMonth.Substring(TempMonth.Length - 2, 2);
157                                //strReGen = "month";
158                            }
159                        }
160                        else if (strPrefixOrder.Substring(i, 1) == "d")
161                        {
162                            if (dr["PrefixD"].ToString().ToLower() == "d")
163                            {
164                                strTempDocNo = strTempDocNo + FormatDayD(StartDateValue);
165                            }
166                            else if (dr["PrefixD"].ToString().ToLower() == "dd")
167                            {
168                                string TempDay;
169                                TempDay = "0" + StartDateValue.Day.ToString("");
170                                strTempDocNo = strTempDocNo + TempDay.Substring(TempDay.Length - 2, 2);
171                                //strReGen = "day";
172                            }
173                        }
174                    }
175
176                }
177                #endregion
178            }
179        }
180        dr.Close();
181
182        如果沒有值按流水號生成值#region 如果沒有值按流水號生成值
183        if (docLen == 0 && colName.ToString().Trim() != "")
184        {
185            docLen = GetDocLen(TableName, colName, sb, myConnection);    //定義流水的長度 
186        }
187        
188
189        SqlDataReader dr_2 = GetKey(TableName, colName, docLen, sb, myConnection); //
190
191        if (dr_2.Read())
192        {
193            strKeyName = dr_2["keyName"].ToString();
194            docLen = Convert.ToInt32(dr_2["Length"]);
195        }
196        dr_2.Close();
197
198        int RecCnt = GetCommonStartCount(sb, TableName, colName, OtherKey, sqlwhere,
199            StartDate, TableName, colName, docLen, myConnection);//獲取当天RecCnt的值
200
201 
202
203        startValue = GetsqlSelectStart(sb, TableName, docLen, colName, OtherKey, StartDate, myConnection);
204
205        //RecCnt = GetSelectTableCount(sb, TableName, colName, startValue, OtherKey, myConnection);
206
207        //因为StringBuilder的原因构造sqlUpdateStart,sqlSelectTable放在后面构造       
208
209        while (RecCnt > 0 || startValue == string.Empty)
210        {
211           startValue = GetsqlSelectStart(sb, TableName, docLen, colName, OtherKey, StartDate, myConnection);
212           startValue = GetIsAlphaChar(isAlpha, startValue, docLen, strTempDocNo);
213           GetsqlUpdateStart(sb, TableName,colName,  OtherKey, sqlwhere, myConnection);
214           RecCnt = GetSelectTableCount(sb, TableName, colName, startValue,OtherKey ,myConnection);
215        }
216
217        strTempDocNo = startValue;
218        #endregion
219        return strTempDocNo;
220    }
221
222    /**//// <summary>
223    /// 獲取CommonStartPrefix相關信息
224    /// </summary>
225    /// <param name="TableName">需要處理的Table</param>
226    /// <param name="colName">處理的字段</param>
227    /// <returns>返回CommonStartPrefix相關信息</returns>
228    private static SqlDataReader GetCommonStartPrefix(string TableName, string ColName, StringBuilder sb, SqlConnection myConnection)
229    {
230        SqlCommand cmd = new SqlCommand();
231        sb.Append("select PrefixOrder,IsAlpha,TableName as ViewRef,Prefix1,Prefix2,Prefix3,PrefixY,PrefixM,PrefixD from CommonStartPrefix where TableName = '");
232        sb.Append(TableName);
233        sb.Append("' and isnull(ColName,'') = '");
234        sb.Append(ColName);
235        sb.Append("'");
236        cmd.CommandText = sb.ToString();
237        cmd.Connection = myConnection;
238        
239        SqlDataReader dr = cmd.ExecuteReader();
240        sb.Remove(0, sb.Length);
241        return dr;    
242    }
243
244    /**//// <summary>
245    /// 格式化字符時間
246    /// </summary>
247    /// <returns>返回格式化后的日期</returns>
248    private static DateTime FormatStartDate(string StartDate)
249    {
250        if (StartDate == string.Empty)
251        {
252            StartDate = DateTime.Now.ToString("yyyy-MM-dd");
253        }
254        else
255        {
256            StartDate = Convert.ToDateTime(StartDate).ToString("yyyy-MM-dd");
257        }
258
259        return Convert.ToDateTime(StartDate);
260    }
261
262    
263
264    /**//// <summary>
265    /// 格式化YY格式的年份
266    /// </summary>
267    /// <param name="TempPrefix">傳入YY格式</param>
268    /// <returns>返回Y的格式</returns>
269    private static string FormatYearByYY(DateTime StartDateValue)
270    {
271        string strYear  ;//返回Year值
272        strYear = string.Empty;
273        strYear = TransferASCII(StartDateValue);
274        return strYear;
275    }
276
277 
278
279    /**//// <summary>
280    /// 格式化M格式的月份
281    /// </summary>
282    /// <param name="TempPrefix">傳入1位的月份</param>
283    /// <returns>返回2位的月份</returns>
284    private static string FormatMonthByM(DateTime StartDateValue)
285    {
286        string strMonth = string.Empty;
287        if (StartDateValue.Month >= 10)
288        {
289            strMonth = Convert.ToString((char)(StartDateValue.Month + 55));
290        }
291        else
292        {
293            strMonth = StartDateValue.Month.ToString() ;
294        }
295        return strMonth;
296    }
297
298    /**//// <summary>
299    /// 格式化D格式的日期
300    /// </summary>
301    /// <param name="TempPrefix">傳入D格式</param>
302    /// <returns>返回Y的格式</returns>
303    private static string FormatDayD(DateTime StartDateValue)
304    {
305        string strDay = string.Empty;
306        int intLeftTemp;//返回TempYear的
307
308        intLeftTemp = StartDateValue.Day + 55;
309
310        if (intLeftTemp - 55 >= 10)
311        {
312            if (intLeftTemp > 72)
313            {
314                if (intLeftTemp >= (int)'I')
315                {
316                    intLeftTemp = intLeftTemp + 1;
317                }
318                if (intLeftTemp >= (int)'O')
319                {
320                    intLeftTemp = intLeftTemp + 1;
321                }
322                if (intLeftTemp >= (int)'U')
323                {
324                    intLeftTemp = intLeftTemp + 1;
325                }
326                if (intLeftTemp >= (int)'V')
327                {
328                    intLeftTemp = intLeftTemp + 1;
329                }
330            }
331            strDay = Convert.ToString((char)intLeftTemp);
332        }
333        else
334        {
335            strDay =  StartDateValue.Day.ToString();
336        }
337        return strDay;
338    }
339
340    /**//// <summary>
341    /// 格式化YY格式的年份
342    /// </summary>
343    /// <param name="TempPrefix">傳入YY,DD格式</param>
344    /// <returns>返回Y,D,M的格式</returns>
345    private static string TransferASCII(DateTime TempChar)
346    {
347        string strTempChar;//返回Year值
348        string strRightTemp; //返回Year第一位
349        int intLeftTemp;//返回TempYear的
350
351        strTempChar = "000" + Convert.ToString(TempChar.Year - 1900).TrimStart();
352        strRightTemp = strTempChar.Substring(strTempChar.Length - 1, 1);
353        strTempChar = strTempChar.Substring(strTempChar.Length - 3,3);
354
355
356        intLeftTemp = int.Parse(strTempChar.Substring(0,2)) + 55;//从10开始变为A
357
358        if (intLeftTemp - 55 >= 10)
359        {
360            if (intLeftTemp  >= 72)//减少判断次数,字母I之前不用判断
361            {
362                if (intLeftTemp  >= (int)'I')
363                {
364                    intLeftTemp = intLeftTemp + 1;
365                }
366                else if (intLeftTemp  >= (int)'O')
367                {
368                    intLeftTemp = intLeftTemp + 1;
369                }
370                else if (intLeftTemp  >=(int)'U')
371                {
372                    intLeftTemp = intLeftTemp + 1;
373                }
374                else if (intLeftTemp  >=(int)'V')
375                {
376                    intLeftTemp = intLeftTemp + 1;
377                }                
378            }
379            strTempChar = Convert.ToString((Char)(intLeftTemp)) + strRightTemp;  
380        }
381        else
382        {
383            strTempChar = strRightTemp;        
384        }
385        return strTempChar;
386    }
387
388    /**//// <summary>
389    /// 獲取流水號長度
390    /// </summary>
391    /// <param name="ViewRef">需要獲取的視圖和表名</param>
392    /// <param name="colName">列名</param>
393    /// <returns>返回長度</returns>
394    private static int GetDocLen(string viewRef, string colName,StringBuilder sb,SqlConnection myConnection)
395    {
396        int intDocLen = 0;
397        SqlCommand cmd = new SqlCommand();
398        sb.Append("SELECT  a.prec FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id WHERE  b.name = '");
399        sb.AppendFormat(viewRef);
400        sb.AppendFormat("'AND a.name = '");
401        sb.Append(colName);
402        sb.Append("'");
403        cmd.CommandText = sb.ToString();
404        cmd.Connection = myConnection;
405
406        SqlDataReader dr = cmd.ExecuteReader();
407        sb.Remove(0, sb.Length);
408
409        if (dr.Read())
410        {
411            intDocLen = Convert.ToInt32(dr["prec"]);
412        }
413        dr.Close();
414        return intDocLen;
415    }
416
417    /**//// <summary>
418    /// 獲取流水號長度
419    /// </summary>
420    /// <param name="ViewRef">需要獲取的視圖和表名</param>
421    /// <param name="colName">列名</param>
422    /// <returns>返回長度</returns>
423    private static SqlDataReader GetKey(string viewRef, string colName, int intLen ,StringBuilder sb, SqlConnection myConnection)
424    {
425        SqlCommand cmd = new SqlCommand();
426
427        sb.Append("DECLARE  @KeyName nvarchar(50),@Length int,@OtherKey nvarchar(4000)");
428        sb.Append("DECLARE @kColName nvarchar(100), @kColPrec  int , @kColDesc nvarchar(4000), @kColValue nvarchar(4000), @sqlOtherKey nvarchar(4000), @fnXmlParserGetValueByName nvarchar(500) ");
429        sb.Append("SET @fnXmlParserGetValueByName = dbo.fnBaseDbName() + '.dbo.fnXmlParserGetValueByName' ");
430        sb.Append("SET @sqlOtherKey ='' ");
431        sb.Append("BEGIN ");
432        sb.Append(" DECLARE FindKeyCursor CURSOR  STATIC  FORWARD_ONLY");
433        sb.Append(" FOR (");
434        sb.Append("SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) decs FROM (" );
435        sb.Append("syscolumns a INNER JOIN sysobjects b ON a.id = b.id ");  
436        sb.Append("INNER JOIN sysindexkeys c ON a.colid = c.colid AND b.id = c.id AND c.indid = 1 ");
437        sb.Append("LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'TABLE','");
438        sb.Append( viewRef);
439        sb.Append("', 'column', DEFAULT) d ON a.name = d.objname COLLATE Chinese_Taiwan_Stroke_CI_AS ");
440        sb.Append(") WHERE b.type = 'U' AND b.name = '");
441        sb.Append(viewRef);
442        sb.Append("' UNION SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000))  FROM ( ");
443        sb.Append("syscolumns a INNER JOIN sysobjects b ON a.id = b.id ");
444        sb.Append("LEFT OUTER JOIN ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'VIEW', '");
445        sb.Append(viewRef);
446        sb.Append("', 'column', DEFAULT) d ON a.name = d.objname COLLATE Chinese_Taiwan_Stroke_CI_AS ");
447        sb.Append(") WHERE b.type = 'V' AND b.name = '");
448        sb.Append(viewRef);
449        sb.Append("') ");
450        sb.Append(" OPEN FindKeyCursor ");
451        sb.Append(" FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc ");
452        sb.Append(" WHILE @@FETCH_STATUS = 0 ");
453        sb.Append(" begin");
454        sb.Append("  PRINT @kColName");
455        sb.Append("  IF CHARINDEX('<docno>',@kColDesc) >  0  OR  @@CURSOR_ROWS  =  1 ");
456        sb.Append("  BEGIN");
457        sb.Append("   SET @KeyName = @kColName");
458        sb.Append("   SET @Length = @kColPrec");
459        sb.Append("  END else");
460        sb.Append("  BEGIN");
461        sb.Append("   EXEC @kColValue = @fnXmlParserGetValueByName  @OtherKey, @kColName");
462        sb.Append("   SET @sqlOtherKey = @sqlOtherKey + ' AND  ' +@kColName +'  =  '''+ IsNull(@kColValue, '') + ''''");
463        sb.Append("  END");
464        sb.Append("  FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc");
465        sb.Append(" END");
466        sb.Append(" CLOSE FindKeyCursor");
467        sb.Append(" DEALLOCATE FindKeyCursor end");
468        sb.Append(" SET @KeyName = IsNull('");
469        sb.Append(colName);  
470        sb.Append("', @KeyName)");
471        sb.Append(" SET @Length = IsNull(");
472        sb.Append(intLen);
473        sb.Append(", @Length) ");
474        sb.Append("select @KeyName  KeyName  , @Length  Length  ");
475
476
477        cmd.CommandText = sb.ToString();
478        cmd.Connection = myConnection;
479
480        SqlDataReader dr = cmd.ExecuteReader();
481        sb.Remove(0, sb.Length);
482
483        return dr;
484    }
485
486    /**//// <summary>
487    /// 獲取流水號長度
488    /// </summary>
489    /// <param name="ViewRef">需要獲取的視圖和表名</param>
490    /// <param name="colName">列名</param>
491    /// <returns>返回長度</returns>
492    private static string GetColValue(string otherKey, string colName, StringBuilder sb, SqlConnection myConnection1)
493    {
494        string strColValue = string.Empty;
495        
496        myConnection1.Open();
497        SqlCommand cmd = new SqlCommand();
498        sb.Append("declare @a nvarchar(4000) exec @a=fnXmlParserGetValueByName ");
499        sb.Append(otherKey);
500        sb.Append(",");
501        sb.Append(colName);
502        sb.Append(" select @a as ColValue");
503        cmd.CommandText = sb.ToString();
504        cmd.Connection = myConnection1;
505
506        SqlDataReader dr = cmd.ExecuteReader();
507        sb.Remove(0, sb.Length);
508
509        if (dr.Read())
510        {
511            strColValue = dr["ColValue"].ToString();
512        }
513        dr.Close();
514
515        return strColValue;
516    }
517
518    /**//// <summary>
519    /// 獲取流水號開始長度
520    /// </summary>
521    /// <param name="sb">构造sql的StringBuilder對象</param>
522    /// <param name="TableName">獲取TableDate名</param>
523    /// <param name="colName">獲取的健名</param>
524    /// <param name="OtherKey">其他健名</param>
525    /// <param name="sqlWhere">年月日合</param>
526    /// <param name="StartDateValue">開始時間</param>
527    /// <param name="ViewRef">獲取流水的表名或視圖名</param>
528    /// <param name="KeyName">字段健值</param>
529    /// <param name="intLength">长度</param>
530    /// <param name="myConnection">連接Connection</param>
531    /// <returns>開始的長度</returns>    
532    private static int GetCommonStartCount(StringBuilder sb, string TableName, string ColName, string OtherKey,
533            string sqlWhere, string StartDate, string ViewRef,
534            string KeyName,int intLength, SqlConnection myConnection)
535    {
536        int intRecCnt = 0;
537        sb.Append(" SELECT Count(*)  RecCnt  ");
538        sb.Append(" FROM CommonStart WHERE Upper(TableName) = Upper('"+ TableName +"') ");
539        sb.Append(" AND Upper(IsNull(ColName, '''')) = Upper(IsNull('"+ ColName +"', '''')) ");
540        sb.Append(" AND Upper(OtherKey) = Upper('" + OtherKey + "') and startdate ='" + StartDate + "'  ");
541
542
543        SqlCommand cmd = new SqlCommand();
544        cmd.CommandText = sb.ToString();
545        cmd.Connection = myConnection;
546
547        SqlDataReader dr = cmd.ExecuteReader();
548        sb.Remove(0, sb.Length);
549
550        if (dr.Read())
551        {
552            intRecCnt = Convert.ToInt32( dr["RecCnt"]);
553        }
554
555        dr.Close();
556        sb.Remove(0, sb.Length);
557        
558        if (intRecCnt == 0)
559        {
560            InsertCommonStart(sb, TableName, ColName, OtherKey, sqlWhere, StartDate);
561            try
562            {
563                cmd.CommandText = sb.ToString();
564                cmd.ExecuteNonQuery();
565                sb.Remove(0, sb.Length);
566            }
567            catch
568            {
569                throw new Exception("Initial Doc No # not set. Please set the Doc No");
570            }     
571        }            
572        return intRecCnt;       
573
574    }
575    /**//// <summary>
576    /// 返回InsertCommonStart 的Sql
577    /// </summary>
578    /// <param name="sb">傳入StringBuilder對象</param>
579    /// <param name="tableName">傳入Table</param>
580    /// <param name="colName">傳入健值</param>
581    /// <param name="otherKey">其他Key</param>
582    /// <param name="sqlWhere">年月日合</param>
583    /// <param name="StartDateValue">開始時間</param>
584    private static void InsertCommonStart(StringBuilder sb, string tableName, string colName, string otherKey,
585            string sqlWhere, string StartDateValue)
586    {
587        sb.Append(" INSERT INTO CommonStart (TableName , ColName, StartDate , StartValue, ");
588        sb.Append(" OtherKey) Values ( ");
589        sb.Append("'" + tableName + "',");
590        sb.Append("'" + colName + "',");
591        sb.Append("'" + StartDateValue + "',");
592        sb.Append(" 1,'" + otherKey  + "' ) ");
593    }
594
595    /**//// <summary>
596    /// 根據Length獲取流水長度
597    /// </summary>
598    /// <param name="sb">傳入StringBuilder對象</param>
599    /// <param name="tableName">傳入Table</param>
600    /// <param name="intlength">流水長度,由系統表取出</param>
601    /// <param name="colName">傳入健值</param>
602    /// <param name="otherKey">其他健值</param>
603    /// <param name="sqlWhere">年月日合</param>
604    private static string GetsqlSelectStart(StringBuilder sb, string tableName, int intlength, string colName, string otherKey,
605        string startDate,SqlConnection myConnection)
606    {
607        string returnValue = string.Empty;
608        sb.Append(" SELECT Right(Replicate(N'0',");
609        sb.Append(intlength);
610        sb.Append(")+Cast(StartValue AS nvarchar),");
611        sb.Append(intlength.ToString());
612        sb.Append(") as StartValue ");
613        sb.Append(" FROM CommonStart WHERE Upper(TableName)=Upper('");
614        sb.Append(tableName);
615        sb.Append("') AND Upper(IsNull(ColName, '''')) ");
616        sb.Append(" = Upper(IsNull('");
617        sb.Append(colName);
618        sb.Append("', ''''))") ;
619        sb.Append(" AND Upper(OtherKey) = Upper('" + otherKey + "') and startdate ='" + startDate + "'  ");
620        
621        SqlCommand cmd = new SqlCommand(sb.ToString(),myConnection);
622        SqlDataReader dr = cmd.ExecuteReader();
623 
624        if(dr.Read())
625        {
626            returnValue = dr["StartValue"].ToString();
627        }
628
629        dr.Close();
630        sb.Remove(0,sb.Length);
631        return returnValue;        
632    }
633
634    /**//// <summary>
635    /// 更新CommonStart
636    /// </summary>
637    /// <param name="sb">傳入StringBuilder對象</param>
638    /// <param name="tableName">傳入Table</param>
639    /// <param name="intlength">流水長度,由系統表取出</param>
640    /// <param name="colName">傳入健值</param>
641    /// <param name="otherKey">其他健值</param>
642    /// <param name="sqlWhere">年月日合</param>
643    /// <returns></returns>
644    private static void GetsqlUpdateStart(StringBuilder sb,string tableName, string colName, string otherKey,
645            string sqlWhere,SqlConnection myConnection)
646    {
647        sb.Append("  UPDATE CommonStart SET StartValue = StartValue + 1 ");
648        sb.Append("  WHERE Upper(TableName) = Upper('");
649        sb.Append(tableName);
650        sb.Append("') AND Upper(IsNull(ColName, '''')) = Upper(IsNull('");
651        sb.Append(colName);
652        sb.Append("', ''''))  AND Upper(OtherKey) = Upper('");
653        sb.Append(otherKey);
654        sb.Append("')   ");
655        sb.Append(sqlWhere);
656
657        SqlCommand cmd = new SqlCommand(sb.ToString(),myConnection);
658        cmd.ExecuteNonQuery();
659        sb.Remove(0,sb.Length);
660        //cmd.Dispose();
661    }
662
663    /**//// <summary>
664    /// 查詢table或視圖中是否已存在該值
665    /// </summary>
666    /// <param name="sb">傳入StringBuilder對象</param>
667    /// <param name="tableName">傳入Table</param>
668    /// <param name="intlength">流水長度,由系統表取出</param>
669    /// <param name="colName">傳入健值</param>
670    /// <param name="otherKey">其他健值</param>
671    /// <param name="sqlWhere">年月日合</param>
672    /// <returns></returns>
673    private static int GetSelectTableCount(StringBuilder sb,string viewRef, string keyName, string startValue,string sqlOtherKey,
674        SqlConnection myConnnection)
675    {
676        int intValue = 0;
677        sb.Append(" SELECT Count(*) as RecCnt FROM  ");
678        sb.Append(viewRef);
679        sb.Append(" WHERE ");
680        sb.Append(keyName);
681        sb.Append(" = '");
682        sb.Append(startValue );
683        sb.Append(sqlOtherKey);
684        sb.Append("'");
685
686        SqlCommand cmd = new SqlCommand(sb.ToString(), myConnnection);
687        SqlDataReader dr = cmd.ExecuteReader();
688
689        if (dr.Read())
690        {
691            intValue = Convert.ToInt32(dr["RecCnt"]);
692        }
693        sb.Remove(0,sb.Length);
694        dr.Close();
695        return intValue;
696    }
697
698    /**//// <summary>
699    /// 轉換為字符處理
700    /// </summary>
701    /// <param name="IsAlpha">是否要轉換字符</param>
702    /// <param name="StartValue">開始值</param>
703    /// <param name="Length">長度值</param>
704    /// <param name="TempDocNo">系統前面產生的流水</param>
705    /// <returns></returns>
706    private static string GetIsAlphaChar(bool IsAlpha, string StartValue, int Length, string TempDocNo)
707    {
708        string Temp = string.Empty;
709        if (IsAlpha)
710        {
711            string WaterNo = StartValue;
712            string WaterNoChr = string.Empty;
713            int WaterNoCnt = 0;
714
715            while (WaterNoCnt < Length - TempDocNo.Length)
716            {
717                //int WaterNoDigit = int.Parse(((int.Parse(WaterNo)/Math.Pow(32,WaterNoCnt))%32).ToString());
718                int WaterNoDigit = int.Parse((int.Parse(WaterNo) % 32).ToString());
719                if (WaterNoDigit < 10)
720                {
721                    WaterNoChr = WaterNoDigit.ToString();
722                }
723                else
724                {
725                    int TempWaterNoDigit = WaterNoDigit + 55;
726                    int TempWaterCharShift = 0;
727                    if (TempWaterNoDigit >= 72)//减少判断次数,字母I之前不用判断
728                    {
729                        if (TempWaterNoDigit + TempWaterCharShift >= (int)'I')
730                            TempWaterCharShift = TempWaterCharShift + 1;
731
732                        if (TempWaterNoDigit + TempWaterCharShift >= (int)'O')
733                            TempWaterCharShift = TempWaterCharShift + 1;
734
735                        if (TempWaterNoDigit + TempWaterCharShift >= (int)'U')
736                            TempWaterCharShift = TempWaterCharShift + 1;
737
738                        if (TempWaterNoDigit + TempWaterCharShift >= (int)'V')
739                            TempWaterCharShift = TempWaterCharShift + 1;
740                    }
741                    WaterNoChr = Convert.ToString((char)(TempWaterNoDigit + TempWaterCharShift));
742                }
743                WaterNoCnt++;
744            }
745
746            WaterNo = WaterNoChr;
747            Temp = TempDocNo + WaterNo;
748        }
749        else
750        {
751            int StartIndex = Length - TempDocNo.Length;
752            Temp = TempDocNo + StartValue.Substring(StartValue.Length - StartIndex, StartIndex);
753        }
754        return Temp;
755    }
756
757    #endregion
758
759
760};
761
762
763
764