1 using System;   2 using System.Collections.Generic;   3 using System.Text;   4 using System.Data;   5    6 using System.Configuration;   7 using System.Data.Common;   8 using System.Data.SqlClient;   9   10 using Microsoft.Practices.EnterpriseLibrary.Data;  11 using Microsoft.Practices.EnterpriseLibrary.Data.Sql;  12 using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;  13 using Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation;  14 using Microsoft.Practices.EnterpriseLibrary.Data.Properties;  15 using Microsoft.Practices.EnterpriseLibrary.Common;  16 using System.Security.Cryptography;  17 using System.IO;  18   19   20 namespace BO  21 {  22     public class SqlHelperExtension  23     {  24   25         public const int PREORDAIN_TYPE = 9;  26         public static string BaseStoreID = "";  27         public static string BaseTimeBar = "";  28         public static string BaseCloseStoreTime = "05:00:00";  29   30         /// <summary>  31         /// 服务器脱网标志  32         /// </summary>  33         public static bool OffLine = true;  34         /// <summary>  35         /// 脱网数据标志  36         /// </summary>  37         public static bool OffDataFlag = false;  38         /// <summary>  39         /// 数据库服务器Ip地址  40         /// </summary>  41         public static string DataSourceIp1 = GetSocketIP();  42   43         /// <summary>  44         /// 数据库服务器端口  45         /// </summary>  46         public static string DataSourcePort = GetDataSourcePort();  47         /// <summary>  48         /// Socket发送端口  49         /// </summary>  50         public static string SocketSendPort = GetSocketPort();  51         private const String CONFIG_CONNECTION_STRING = "DBConnectionString";  52         private static SqlDatabase DATABASE;  53         private static DbTransaction DBTRANSACTION;  54         private static String DB_CONN_STRING = GetConnectionString();  55         private static String CONNECT_FLAG = GetConnectFlag();  56   57         //密钥  58         private const string KEY = "GERRARD8";  59         //执行SQL的超时时间  60         private static int DBCOMMAND_TIMEOUT = GetTimeOut();  61   62         //基本操作  63         #region "BasicProcess"  64         private static String GetConnectionString()  65         {  66             String connectionString;  67             try  68             {  69                 //临时不加密  70                 //connectionString = Decrypt(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, KEY);  71                 connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;  72                 //connectionString = ConfigurationManager.ConnectionStrings[CONFIG_CONNECTION_STRING].ConnectionString;  73             }  74             catch (Exception e)  75             {  76                 throw new Exception("连接数据库失败", e);  77             }  78             return connectionString;  79         }  80   81         private static String GetConnectFlag()  82         {  83             String strConnectFlag = ConfigurationManager.AppSettings["CONNECT_FLG"];  84             if (strConnectFlag == null)  85             {  86                 throw new Exception("读取连接类型失败");  87             }  88             return strConnectFlag;  89         }  90   91         private static int GetTimeOut()  92         {  93             string timeout = ConfigurationManager.AppSettings["DBCOMMAND_TIMEOUT"].ToString();  94             if (timeout == "")  95             {  96                 throw new Exception("读取Timeout时间失败");  97             }  98             return Convert.ToInt32(timeout);  99         } 100         private static String GetSocketIP() 101         { 102             DataSourceIp1 = ConfigurationManager.AppSettings["DataSourceIP1"]; 103             if (DataSourceIp1 == null) 104             { 105                 throw new Exception("读取服务器IP失败"); 106             } 107             return DataSourceIp1; 108         } 109         private static String GetDataSourcePort() 110         { 111             string dataSourcePort = ConfigurationManager.AppSettings["DataSourcePort"]; 112             if (dataSourcePort == null) 113             { 114                 throw new Exception("读取服务器数据库端口失败"); 115             } 116             return dataSourcePort; 117         } 118         private static String GetSocketPort() 119         { 120             string socketPort = ConfigurationManager.AppSettings["SocketSendPort"]; 121             if (socketPort == null) 122             { 123                 throw new Exception("读取数据发送端口失败"); 124             } 125             return socketPort; 126         } 127  128         public static SqlDatabase GetDatabase() 129         { 130             try 131             { 132                 //0:一直连接 133                 if (CONNECT_FLAG == "0") 134                 { 135                     //没有创建则重新创建 136                     if (DATABASE == null) 137                     { 138                         //DATABASE = DatabaseFactory.CreateDatabase(CONFIG_CONNECTION_STRING) as SqlDatabase; 139                         DATABASE = new SqlDatabase(DB_CONN_STRING); 140                     } 141                     return DATABASE; 142                 } 143                 else //1:每次创建新的DATABASE 144                 { 145                     //SqlDatabase newDatabase = DatabaseFactory.CreateDatabase(CONFIG_CONNECTION_STRING) as SqlDatabase; 146                     SqlDatabase newDatabase = new SqlDatabase(DB_CONN_STRING); 147                     return newDatabase; 148                 } 149             } 150             catch (SqlException e) 151             { 152                 throw GetApplicationException(e); 153             } 154             catch (Exception e) 155             { 156                 throw e; 157             } 158         } 159  160         private static void CloseDatabase(SqlDatabase sqlDatabase) 161         { 162             if (CONNECT_FLAG == "1") 163             { 164                 if (sqlDatabase != null) 165                 { 166                     sqlDatabase = null; 167                 } 168             } 169         } 170  171         private static Exception GetApplicationException(SqlException e) 172         { 173             switch (e.Number) 174             { 175                 case 1231: 176                     return new ApplicationException("服务器连接超时", e); 177                 case 2627: 178                     return new ApplicationException("主键重复", e); 179                 case 207: 180                     return new ApplicationException("列不存在", e); 181                 case 208: 182                     return new ApplicationException("表不存在", e); 183                 case 53: 184                     return new ApplicationException("服务器连接失败", e); 185  186             } 187             return e; 188         } 189  190         /// <summary> 191         /// 设置Dbcommand的超时时间，单位秒 192         /// </summary> 193         /// <param name="dbCommand"></param> 194         /// <returns></returns> 195         private static DbCommand SetDbCommand(DbCommand dbCommand) 196         { 197             dbCommand.CommandTimeout = DBCOMMAND_TIMEOUT; 198             return dbCommand; 199         } 200         #endregion 201  202         //共享事务处理 203         #region "ShareTransaction" 204         /// <summary> 205         /// 创建连接并打开，同时开启事务，并且将事务句柄存储在静态变量中 206         /// </summary> 207         public static DbTransaction OpenTransaction() 208         { 209             try 210             { 211                 DbConnection dbConnection = GetDatabase().CreateConnection(); 212                 if (dbConnection.State != ConnectionState.Open) 213                 { 214                     dbConnection.Open(); 215                 } 216                 DBTRANSACTION = dbConnection.BeginTransaction(); 217                 return DBTRANSACTION; 218             } 219             catch (Exception e) 220             { 221                 throw new Exception("打开事务失败", e); 222             } 223         } 224  225  226         /// <summary> 227         /// 回滚事务 228         /// </summary> 229         public static void RollbackTransaction() 230         { 231             if (DBTRANSACTION == null) 232             { 233                 throw new Exception("回滚事务失败"); 234             } 235             else 236             { 237                 DBTRANSACTION.Rollback(); 238                 CloseDatabase(DATABASE); 239                 DBTRANSACTION.Dispose(); 240                 DBTRANSACTION = null; 241             } 242         } 243  244         /// <summary> 245         /// 提交事务 246         /// </summary> 247         public static void CommitTransaction() 248         { 249             if (DBTRANSACTION == null) 250             { 251                 throw new Exception("提交事务失败"); 252             } 253             else 254             { 255                 DBTRANSACTION.Commit(); 256                 CloseDatabase(DATABASE); 257                 DBTRANSACTION.Dispose(); 258                 DBTRANSACTION = null; 259             } 260         } 261         #endregion 262  263         //单独事务处理 264         #region "SingleTransaction" 265         /// <summary> 266         /// 创建连接并打开，同时开启事务，并返回事务，作为单独事务进行后续操作 267         /// </summary> 268         public static DbTransaction OpenSingleTransaction() 269         { 270             try 271             { 272                 DbConnection dbConnection = GetDatabase().CreateConnection(); 273                 if (dbConnection.State != ConnectionState.Open) 274                 { 275                     dbConnection.Open(); 276                 } 277                 return dbConnection.BeginTransaction(); 278             } 279             catch (Exception e) 280             { 281                 throw new Exception("打开事务失败", e); 282             } 283         } 284  285  286         /// <summary> 287         /// 回滚事务 288         /// </summary> 289         public static void RollbackSingleTransaction(DbTransaction dbTransaction) 290         { 291             try 292             { 293                 dbTransaction.Rollback(); 294                 CloseDatabase(DATABASE); 295                 dbTransaction.Dispose(); 296                 dbTransaction = null; 297             } 298             catch (Exception e) 299             { 300                 throw new Exception("回滚事务失败", e); 301             } 302         } 303  304         /// <summary> 305         /// 提交事务 306         /// </summary> 307         public static void CommitSingleTransaction(DbTransaction dbTransaction) 308         { 309             try 310             { 311                 dbTransaction.Commit(); 312                 CloseDatabase(DATABASE); 313                 dbTransaction.Dispose(); 314                 dbTransaction = null; 315             } 316             catch (Exception e) 317             { 318                 throw new Exception("提交事务失败", e); 319             } 320         } 321         #endregion 322  323         //参数转换 324         #region "AddParaToCommand" 325         /// <summary> 326         /// 将传入的字典参数集转换为SQL参数集 327         /// </summary> 328         /// <param name="dbCommand"></param> 329         /// <param name="dictParameter"></param> 330         private static void AddParaToCommand(DbCommand dbCommand, Dictionary<String, Object> dictParameter) 331         { 332             foreach (KeyValuePair<String, Object> kvp in dictParameter) 333             { 334                 dbCommand.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value)); 335             } 336         } 337  338         /// <summary> 339         /// 将传入的字典参数集转换为SQL参数集，参数类型为传出参数 340         /// </summary> 341         /// <param name="dbCommand"></param> 342         /// <param name="dictOutParameter"></param> 343         private static void AddParaToCommandOutput(DbCommand dbCommand, Dictionary<String, Object> dictOutParameter) 344         { 345             foreach (KeyValuePair<String, Object> kvp in dictOutParameter) 346             { 347                 SqlParameter sqlParameter = new SqlParameter(kvp.Key, kvp.Value); 348                 sqlParameter.Direction =ParameterDirection.InputOutput; 349                 dbCommand.Parameters.Add(sqlParameter); 350             } 351         } 352  353         /// <summary> 354         /// 将执行好的SQL参数集返回给传出字典参数集 355         /// </summary> 356         /// <param name="dictOutParameter"></param> 357         /// <param name="dbCommand"></param> 358         private static void AddCommandToPara(ref Dictionary<String, Object> dictOutParameter, DbCommand dbCommand) 359         { 360             Dictionary<String, Object> newDictOutParameter = new Dictionary<String, Object>(); 361             foreach (KeyValuePair<String, Object> kvp in dictOutParameter) 362             { 363                 newDictOutParameter.Add(kvp.Key, dbCommand.Parameters[kvp.Key].Value); 364             } 365  366             dictOutParameter = newDictOutParameter; 367         } 368         #endregion 369  370         //执行返回Dataset的SQL文 371         #region "ExecuteDataset" 372         public static DataSet ExecuteDataset(String strSQL) 373         { 374             SqlDatabase sqlDatabase = GetDatabase(); 375             try 376             { 377                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 378                 if (DBTRANSACTION == null) 379                 { 380                     return sqlDatabase.ExecuteDataSet(dbCommand); 381                 } 382                 else 383                 { 384                     return sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION); 385                 } 386             } 387             catch (SqlException e) 388             { 389                 throw GetApplicationException(e); 390             } 391             catch (Exception e) 392             { 393                 throw e; 394             } 395             finally 396             { 397                 CloseDatabase(sqlDatabase); 398             } 399         } 400  401         public static DataSet ExecuteDataset(String strSQL, Dictionary<String, Object> dictParameter) 402         { 403             SqlDatabase sqlDatabase = GetDatabase(); 404             try 405             { 406                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 407                 AddParaToCommand(dbCommand, dictParameter); 408                 if (DBTRANSACTION == null) 409                 { 410                     return sqlDatabase.ExecuteDataSet(dbCommand); 411                 } 412                 else 413                 { 414                     return sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION); 415                 } 416             } 417             catch (SqlException e) 418             { 419                 throw GetApplicationException(e); 420             } 421             catch (Exception e) 422             { 423                 throw e; 424             } 425             finally 426             { 427                 CloseDatabase(sqlDatabase); 428             } 429         } 430         #endregion 431  432         //执行无返回Dataset的SQL文 433         #region "ExecuteNoQuery" 434         public static int ExecuteNoQuery(String strSQL) 435         { 436             SqlDatabase sqlDatabase = GetDatabase(); 437             try 438             { 439                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 440                 if (DBTRANSACTION == null) 441                 { 442                     return sqlDatabase.ExecuteNonQuery(dbCommand); 443                 } 444                 else 445                 { 446                     return sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 447                 } 448             } 449             catch (SqlException e) 450             { 451                 throw GetApplicationException(e); 452             } 453             catch (Exception e) 454             { 455                 throw e; 456             } 457             finally 458             { 459                 CloseDatabase(sqlDatabase); 460             } 461         } 462  463         public static int ExecuteNoQuery(String strSQL, Dictionary<String, Object> dictParameter) 464         { 465             SqlDatabase sqlDatabase = GetDatabase(); 466             try 467             { 468                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 469                 AddParaToCommand(dbCommand, dictParameter); 470                 if (DBTRANSACTION == null) 471                 { 472                     return sqlDatabase.ExecuteNonQuery(dbCommand); 473                 } 474                 else 475                 { 476                     return sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 477                 } 478             } 479             catch (SqlException e) 480             { 481                 throw GetApplicationException(e); 482             } 483             catch (Exception e) 484             { 485                 throw e; 486             } 487             finally 488             { 489                 CloseDatabase(sqlDatabase); 490             } 491         } 492         #endregion 493  494         //执行返回Dataset的存储过程 495         #region "ExecuteDatasetProc" 496         public static DataSet ExecuteDatasetProc(String strProcName) 497         { 498             SqlDatabase sqlDatabase = GetDatabase(); 499             try 500             { 501                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 502                 if (DBTRANSACTION == null) 503                 { 504                     return sqlDatabase.ExecuteDataSet(dbCommand); 505                 } 506                 else 507                 { 508                     return sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION); 509                 } 510             } 511             catch (SqlException e) 512             { 513                 throw GetApplicationException(e); 514             } 515             catch (Exception e) 516             { 517                 throw e; 518             } 519             finally 520             { 521                 CloseDatabase(sqlDatabase); 522             } 523         } 524  525         public static DataSet ExecuteDatasetProc(String strProcName, Dictionary<String, Object> dictParameter) 526         { 527             SqlDatabase sqlDatabase = GetDatabase(); 528             try 529             { 530                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 531                 AddParaToCommand(dbCommand, dictParameter); 532                 if (DBTRANSACTION == null) 533                 { 534                     return sqlDatabase.ExecuteDataSet(dbCommand); 535                 } 536                 else 537                 { 538                     return sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION); 539                 } 540             } 541             catch (SqlException e) 542             { 543                 throw GetApplicationException(e); 544             } 545             catch (Exception e) 546             { 547                 throw e; 548             } 549             finally 550             { 551                 CloseDatabase(sqlDatabase); 552             } 553         } 554  555         public static DataSet ExecuteDatasetProc(String strProcName, Dictionary<String, Object> dictParameter, ref Dictionary<String, Object> dictOutParameter) 556         { 557             SqlDatabase sqlDatabase = GetDatabase(); 558             try 559             { 560                 DataSet dataset; 561                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 562                 AddParaToCommand(dbCommand, dictParameter); 563                 AddParaToCommandOutput(dbCommand, dictOutParameter); 564                 if (DBTRANSACTION == null) 565                 { 566                     dataset = sqlDatabase.ExecuteDataSet(dbCommand); 567                 } 568                 else 569                 { 570                     dataset = sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION); 571                 } 572                 AddCommandToPara(ref dictOutParameter, dbCommand); 573                 return dataset; 574             } 575             catch (SqlException e) 576             { 577                 throw GetApplicationException(e); 578             } 579             catch (Exception e) 580             { 581                 throw e; 582             } 583             finally 584             { 585                 CloseDatabase(sqlDatabase); 586             } 587         } 588         #endregion 589  590         //执行无返回Dataset的存储过程 591         #region "ExecuteNoQueryProc" 592         public static int ExecuteNoQueryProc(String strProcName) 593         { 594             SqlDatabase sqlDatabase = GetDatabase(); 595             try 596             { 597                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 598                 if (DBTRANSACTION == null) 599                 { 600                     return sqlDatabase.ExecuteNonQuery(dbCommand); 601                 } 602                 else 603                 { 604                     return sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 605                 } 606             } 607             catch (SqlException e) 608             { 609                 throw GetApplicationException(e); 610             } 611             catch (Exception e) 612             { 613                 throw e; 614             } 615             finally 616             { 617                 CloseDatabase(sqlDatabase); 618             } 619         } 620  621         public static int ExecuteNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter) 622         { 623             SqlDatabase sqlDatabase = GetDatabase(); 624             try 625             { 626                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 627                 AddParaToCommand(dbCommand, dictParameter); 628                 if (DBTRANSACTION == null) 629                 { 630                     return sqlDatabase.ExecuteNonQuery(dbCommand); 631                 } 632                 else 633                 { 634                     return sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 635                 } 636             } 637             catch (SqlException e) 638             { 639                 throw GetApplicationException(e); 640             } 641             catch (Exception e) 642             { 643                 throw e; 644             } 645             finally 646             { 647                 CloseDatabase(sqlDatabase); 648             } 649         } 650         //will be deleted 651         //public static int ExecuteNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter, String strReturnPara) 652         //{ 653         //    SqlDatabase sqlDatabase = GetDatabase(); 654         //    try 655         //    { 656         //        DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 657         //        AddParaToCommand(dbCommand, dictParameter); 658         //        dbCommand.Parameters.Add(new SqlParameter(strReturnPara, SqlDbType.Int)); 659         //        dbCommand.Parameters[strReturnPara].Direction = ParameterDirection.Output; 660         //        if (DBTRANSACTION == null) 661         //        { 662         //            sqlDatabase.ExecuteNonQuery(dbCommand); 663         //        } 664         //        else 665         //        { 666         //            sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 667         //        } 668         //        return int.Parse(dbCommand.Parameters[@strReturnPara].Value.ToString()); 669         //    } 670         //    catch (SqlException e) 671         //    { 672         //        throw GetApplicationException(e); 673         //    } 674         //    catch (Exception e) 675         //    { 676         //        throw e; 677         //    } 678         //    finally 679         //    { 680         //        CloseDatabase(sqlDatabase); 681         //    } 682         //} 683  684         public static int ExecuteNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter, ref Dictionary<String, Object> dictOutParameter) 685         { 686             SqlDatabase sqlDatabase = GetDatabase(); 687             try 688             { 689                 int intAffected; 690                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 691                 AddParaToCommand(dbCommand, dictParameter); 692                 AddParaToCommandOutput(dbCommand, dictOutParameter); 693                 if (DBTRANSACTION == null) 694                 { 695                     intAffected = sqlDatabase.ExecuteNonQuery(dbCommand); 696                 } 697                 else 698                 { 699                     intAffected = sqlDatabase.ExecuteNonQuery(dbCommand, DBTRANSACTION); 700                 } 701                 AddCommandToPara(ref dictOutParameter, dbCommand); 702                 return intAffected; 703             } 704             catch (SqlException e) 705             { 706                 throw GetApplicationException(e); 707             } 708             catch (Exception e) 709             { 710                 throw e; 711             } 712             finally 713             { 714                 CloseDatabase(sqlDatabase); 715             } 716         } 717         #endregion 718  719         //在单独的事务中执行返回Dataset的SQL文 720         #region "ExecuteTransDataset" 721         public static DataSet ExecuteTransDataset(String strSQL, DbTransaction dbTransaction) 722         { 723             SqlDatabase sqlDatabase = GetDatabase(); 724             try 725             { 726                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 727                 return sqlDatabase.ExecuteDataSet(dbCommand, dbTransaction); 728             } 729             catch (SqlException e) 730             { 731                 throw GetApplicationException(e); 732             } 733             catch (Exception e) 734             { 735                 throw e; 736             } 737             finally 738             { 739                 //CloseDatabase(sqlDatabase); 740             } 741         } 742  743         public static DataSet ExecuteTransDataset(String strSQL, Dictionary<String, Object> dictParameter, DbTransaction dbTransaction) 744         { 745             SqlDatabase sqlDatabase = GetDatabase(); 746             try 747             { 748                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 749                 AddParaToCommand(dbCommand, dictParameter); 750                 return sqlDatabase.ExecuteDataSet(dbCommand, dbTransaction); 751             } 752             catch (SqlException e) 753             { 754                 throw GetApplicationException(e); 755             } 756             catch (Exception e) 757             { 758                 throw e; 759             } 760             finally 761             { 762                 //CloseDatabase(sqlDatabase); 763             } 764         } 765         #endregion 766  767         //在单独的事务中执行无返回Dataset的SQL文 768         #region "ExecuteTransNoQuery" 769         public static int ExecuteTransNoQuery(String strSQL, DbTransaction dbTransaction) 770         { 771             SqlDatabase sqlDatabase = GetDatabase(); 772             try 773             { 774                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 775                 return sqlDatabase.ExecuteNonQuery(dbCommand, dbTransaction); 776             } 777             catch (SqlException e) 778             { 779                 throw GetApplicationException(e); 780             } 781             catch (Exception e) 782             { 783                 throw e; 784             } 785             finally 786             { 787                 //CloseDatabase(sqlDatabase); 788             } 789         } 790  791         public static int ExecuteTransNoQuery(String strSQL, Dictionary<String, Object> dictParameter, DbTransaction dbTransaction) 792         { 793             SqlDatabase sqlDatabase = GetDatabase(); 794             try 795             { 796                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetSqlStringCommand(strSQL)); 797                 AddParaToCommand(dbCommand, dictParameter); 798                 return sqlDatabase.ExecuteNonQuery(dbCommand, dbTransaction); 799             } 800             catch (SqlException e) 801             { 802                 throw GetApplicationException(e); 803             } 804             catch (Exception e) 805             { 806                 throw e; 807             } 808             finally 809             { 810                 //CloseDatabase(sqlDatabase); 811             } 812         } 813         #endregion 814  815         //在单独的事务中执行返回Dataset的存储过程 816         #region "ExecuteTransDatasetProc" 817         public static DataSet ExecuteTransDatasetProc(String strProcName, DbTransaction dbTransaction) 818         { 819             SqlDatabase sqlDatabase = GetDatabase(); 820             try 821             { 822                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 823                 return sqlDatabase.ExecuteDataSet(dbCommand, dbTransaction); 824             } 825             catch (SqlException e) 826             { 827                 throw GetApplicationException(e); 828             } 829             catch (Exception e) 830             { 831                 throw e; 832             } 833             finally 834             { 835                 //CloseDatabase(sqlDatabase); 836             } 837         } 838  839         public static DataSet ExecuteTransDatasetProc(String strProcName, Dictionary<String, Object> dictParameter, DbTransaction dbTransaction) 840         { 841             SqlDatabase sqlDatabase = GetDatabase(); 842             try 843             { 844                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 845                 AddParaToCommand(dbCommand, dictParameter); 846                 return sqlDatabase.ExecuteDataSet(dbCommand, dbTransaction); 847             } 848             catch (SqlException e) 849             { 850                 throw GetApplicationException(e); 851             } 852             catch (Exception e) 853             { 854                 throw e; 855             } 856             finally 857             { 858                 //CloseDatabase(sqlDatabase); 859             } 860         } 861  862         public static DataSet ExecuteTransDatasetProc(String strProcName, Dictionary<String, Object> dictParameter, ref Dictionary<String, Object> dictOutParameter, DbTransaction dbTransaction) 863         { 864             SqlDatabase sqlDatabase = GetDatabase(); 865             try 866             { 867                 DataSet dataset; 868                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 869                 AddParaToCommand(dbCommand, dictParameter); 870                 AddParaToCommandOutput(dbCommand, dictOutParameter); 871                 dataset = sqlDatabase.ExecuteDataSet(dbCommand, dbTransaction); 872                 AddCommandToPara(ref dictOutParameter, dbCommand); 873                 return dataset; 874             } 875             catch (SqlException e) 876             { 877                 throw GetApplicationException(e); 878             } 879             catch (Exception e) 880             { 881                 throw e; 882             } 883             finally 884             { 885                 //CloseDatabase(sqlDatabase); 886             } 887         } 888         #endregion 889  890         //在单独的事务中执行无返回Dataset的存储过程 891         #region "ExecuteNoQueryProc" 892         public static int ExecuteNoQueryProc(String strProcName, DbTransaction dbTransaction) 893         { 894             SqlDatabase sqlDatabase = GetDatabase(); 895             try 896             { 897                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 898                 return sqlDatabase.ExecuteNonQuery(dbCommand, dbTransaction); 899             } 900             catch (SqlException e) 901             { 902                 throw GetApplicationException(e); 903             } 904             catch (Exception e) 905             { 906                 throw e; 907             } 908             finally 909             { 910                 //CloseDatabase(sqlDatabase); 911             } 912         } 913  914         public static int ExecuteNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter, DbTransaction dbTransaction) 915         { 916             SqlDatabase sqlDatabase = GetDatabase(); 917             try 918             { 919                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 920                 AddParaToCommand(dbCommand, dictParameter); 921                 return sqlDatabase.ExecuteNonQuery(dbCommand, dbTransaction); 922             } 923             catch (SqlException e) 924             { 925                 throw GetApplicationException(e); 926             } 927             catch (Exception e) 928             { 929                 throw e; 930             } 931             finally 932             { 933                 //CloseDatabase(sqlDatabase); 934             } 935         } 936  937         public static int ExecuteNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter, ref Dictionary<String, Object> dictOutParameter, DbTransaction dbTransaction) 938         { 939             SqlDatabase sqlDatabase = GetDatabase(); 940             try 941             { 942                 int intAffected; 943                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName)); 944                 AddParaToCommand(dbCommand, dictParameter); 945                 AddParaToCommandOutput(dbCommand, dictOutParameter); 946                 intAffected = sqlDatabase.ExecuteNonQuery(dbCommand, dbTransaction); 947                 AddCommandToPara(ref dictOutParameter, dbCommand); 948                 return intAffected; 949             } 950             catch (SqlException e) 951             { 952                 throw GetApplicationException(e); 953             } 954             catch (Exception e) 955             { 956                 throw e; 957             } 958             finally 959             { 960                 //CloseDatabase(sqlDatabase); 961             } 962         } 963         #endregion 964  965         #region 加密解密方法 966         /// <summary> 967         /// 加密字符串 968         /// </summary> 969         /// <param name="pToEncrypt">加密内容</param> 970         /// <param name="sKey">密钥</param> 971         /// <returns></returns> 972         public static string Encrypt(string pToEncrypt, string sKey) 973         { 974             DESCryptoServiceProvider des = new DESCryptoServiceProvider(); 975             byte[] inputByteArray; 976             inputByteArray = Encoding.Default.GetBytes(pToEncrypt); 977             //建立加密对象的密钥和偏移量 978             //原文使用ASCIIEncoding.ASCII方法的GetBytes方法 979             //使得输入密码必须输入英文文本 980             des.Key = ASCIIEncoding.ASCII.GetBytes(sKey); 981             des.IV = ASCIIEncoding.ASCII.GetBytes(sKey); 982             //写二进制数组到加密流 983             //(把内存流中的内容全部写入) 984             System.IO.MemoryStream ms = new System.IO.MemoryStream(); 985             CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write); 986  987  988             //写二进制数组到加密流 989             //(把内存流中的内容全部写入) 990             cs.Write(inputByteArray, 0, inputByteArray.Length); 991             cs.FlushFinalBlock(); 992             // 建立输出字符串      993             StringBuilder ret = new StringBuilder(); 994             foreach (byte b in ms.ToArray()) 995             { 996                 ret.AppendFormat("{0:X2}", b); 997             } 998             return ret.ToString(); 999         }1000 1001         /// <summary>1002         /// 解密字符串1003         /// </summary>1004         /// <param name="pToDecrypt">解密内容</param>1005         /// <param name="sKey">密钥</param>1006         /// <returns></returns>1007         public static string Decrypt(string pToDecrypt, string sKey)1008         {1009             DESCryptoServiceProvider des = new DESCryptoServiceProvider();1010             // 把字符串放入byte数组1011             int len;1012             len = ((pToDecrypt.Length / 2) - 1);1013             byte[] inputByteArray = new byte[len + 1];1014             int i;1015             for (int x = 0; x <= len; x++)1016             {1017                 i = Convert.ToInt32(pToDecrypt.Substring((x * 2), 2), 16);1018                 inputByteArray[x] = (byte)i;1019             }1020             //建立加密对象的密钥和偏移量，此值重要，不能修改1021             des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);1022             des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);1023             System.IO.MemoryStream ms = new System.IO.MemoryStream();1024             CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);1025             cs.Write(inputByteArray, 0, inputByteArray.Length);1026             cs.FlushFinalBlock();1027             string Decrypt = Encoding.Default.GetString(ms.ToArray());1028             return Decrypt;1029         }1030         #endregion1031 1032         #region "ExecuteStrNoQueryProc"1033         public static string ExecuteStrNoQueryProc(String strProcName, Dictionary<String, Object> dictParameter, String strReturnPara, int size)1034         {1035             SqlDatabase sqlDatabase = GetDatabase();1036             try1037             {1038                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName));1039                 //DbCommand dbCommand = sqlDatabase.GetStoredProcCommand(strProcName);1040                 AddParaToCommand(dbCommand, dictParameter);1041                 dbCommand.Parameters.Add(new SqlParameter(strReturnPara, SqlDbType.NVarChar, size));1042                 dbCommand.Parameters[strReturnPara].Direction = ParameterDirection.Output;1043 1044 1045                 sqlDatabase.ExecuteNonQuery(dbCommand);1046 1047                 return dbCommand.Parameters[@strReturnPara].Value.ToString();1048             }1049             catch (SqlException e)1050             {1051                 throw GetApplicationException(e);1052             }1053             catch (Exception e)1054             {1055                 throw e;1056             }1057             finally1058             {1059                 CloseDatabase(sqlDatabase);1060             }1061         }1062         #endregion1063 1064         #region "ExecuteDatasetProcOutStr"1065         public static DataSet ExecuteDatasetProcOutStr(String strProcName, Dictionary<String, Object> dictParameter, string strOutPara, out int intOutValue)1066         {1067             SqlDatabase sqlDatabase = GetDatabase();1068             try1069             {1070                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName));1071                 AddParaToCommand(dbCommand, dictParameter);1072                 dbCommand.Parameters.Add(new SqlParameter(strOutPara, SqlDbType.Int));1073                 dbCommand.Parameters[strOutPara].Direction = ParameterDirection.Output;1074 1075                 DataSet ds = null;1076 1077                 if (DBTRANSACTION == null)1078                 {1079                     ds = sqlDatabase.ExecuteDataSet(dbCommand);1080                 }1081                 else1082                 {1083                     ds = sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION);1084                 }1085 1086                 intOutValue = http://www.mamicode.com/(int)dbCommand.Parameters[strOutPara].Value;1087 1088                 return ds;1089             }1090             catch (SqlException e)1091             {1092                 throw GetApplicationException(e);1093             }1094             catch (Exception e)1095             {1096                 throw e;1097             }1098             finally1099             {1100                 CloseDatabase(sqlDatabase);1101             }1102         }1103         public static DataSet ExecuteDatasetProcOutStr(String strProcName, Dictionary<String, Object> dictParameter, string strOutPara, ref string intOutValue)1104         {1105             SqlDatabase sqlDatabase = GetDatabase();1106             try1107             {1108                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName));1109                 AddParaToCommand(dbCommand, dictParameter);1110                 dbCommand.Parameters.Add(new SqlParameter(strOutPara, SqlDbType.NVarChar));1111                 dbCommand.Parameters[strOutPara].Direction = ParameterDirection.Output;1112 1113                 DataSet ds = null;1114 1115                 if (DBTRANSACTION == null)1116                 {1117                     ds = sqlDatabase.ExecuteDataSet(dbCommand);1118                 }1119                 else1120                 {1121                     ds = sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION);1122                 }1123 1124                 intOutValue =http://www.mamicode.com/ dbCommand.Parameters[strOutPara].Value.ToString();1125 1126                 return ds;1127             }1128             catch (SqlException e)1129             {1130                 throw GetApplicationException(e);1131             }1132             catch (Exception e)1133             {1134                 throw e;1135             }1136             finally1137             {1138                 CloseDatabase(sqlDatabase);1139             }1140         }1141         public static DataSet ExecuteDatasetProcOutStr(String strProcName, Dictionary<String, Object> dictParameter, ref Dictionary<string,object> strOutParaValue)1142         {1143             SqlDatabase sqlDatabase = GetDatabase();1144             try1145             {1146                 DbCommand dbCommand = SetDbCommand(sqlDatabase.GetStoredProcCommand(strProcName));1147                 AddParaToCommand(dbCommand, dictParameter);1148                 foreach (string outPara in strOutParaValue.Keys)1149                 {1150                     dbCommand.Parameters.Add(new SqlParameter(outPara, SqlDbType.NVarChar, int.MaxValue));1151                     dbCommand.Parameters[outPara].Direction = ParameterDirection.Output;1152                 }1153 1154                 DataSet ds = null;1155 1156                 if (DBTRANSACTION == null)1157                 {1158                     ds = sqlDatabase.ExecuteDataSet(dbCommand);1159                 }1160                 else1161                 {1162                     ds = sqlDatabase.ExecuteDataSet(dbCommand, DBTRANSACTION);1163                 }1164                 Dictionary<string, object> dictParaTemp = new Dictionary<string, object>();1165                 foreach (KeyValuePair<string, object> kv in strOutParaValue)1166                 {1167                     dictParaTemp.Add(kv.Key.ToString(), kv.Value);1168                 }1169 1170                 foreach (KeyValuePair<string, object> kv in dictParaTemp)1171                 {1172                     strOutParaValue[kv.Key] = dbCommand.Parameters[kv.Key.ToString()].Value;1173                 }1174 1175                 return ds;1176             }1177             catch (SqlException e)1178             {1179                 throw GetApplicationException(e);1180             }1181             catch (Exception e)1182             {1183                 throw e;1184             }1185             finally1186             {1187                 CloseDatabase(sqlDatabase);1188             }1189         }1190         #endregion1191     }1192 }