DBHelper类参考模板

1、1. using System;  2. using System.Collections.Generic;  3. using System.Linq;  4. using System.Text;  5. using System.Data.SqlClient;    6. using System.Collections;  7. using System.Data; &

2、#160;8. using System.Configuration;  9. using System.Web;  10.   11.   12. public sealed class SqlHelper  13.   14.   15.     public static string connectionString =

3、 ConfigurationManager.ConnectionStrings"db_JXCconn".ConnectionString;  16.   17.     public SqlHelper()  18.       19.       20.  21.     #region

4、 公用方法  22.     public static int GetMaxID(string FieldName, string TableName)  23.       24.         string strsql = "select max(&

5、quot; + FieldName + ")+1 from " + TableName;  25.         object obj = SqlHelper.GetSingle(strsql);  26.         if (obj =

6、60;null)  27.           28.             return 1;  29.           30.        

7、0;else  31.           32.             return int.Parse(obj.ToString();  33.           34.     &

8、#160; 35.   36.     public static bool Exists(string strSql)  37.       38.         object obj = SqlHelper.GetSingle(strSql);  39.  

9、60;      int cmdresult;  40.         if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value)  41.           1 / 16

10、42.             cmdresult = 0;  43.           44.         else  45.         

11、0; 46.             cmdresult = int.Parse(obj.ToString();  47.           48.         if (cmdresult = 0) &#

12、160;49.           50.             return false;  51.           52.         else 

13、 53.           54.             return true;  55.           56.       57.   58. &

14、#160;   public static bool Exists(string strSql, params SqlParameter cmdParms)  59.       60.         object obj = SqlHelper.GetSingle(strSql, cmdParm

15、s);  61.         int cmdresult;  62.         if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value)  63.       &#

16、160;   64.             cmdresult = 0;  65.           66.         else  67.      

17、     68.             cmdresult = int.Parse(obj.ToString();  69.           70.         if (cmdresult&

18、#160;= 0)  71.           72.             return false;  73.           74.       

19、  else  75.           76.             return true;  77.           78.       

20、;79.     #endregion  80.  81.     #region  执行简单SQL语句  82.     / <summary>  83.     / 执行SQL语句，返回影响的记录数  84.     / </summar

21、y>  85.     / <param name="SQLString">SQL语句</param>  86.     / <returns>影响的记录数</returns>  87.     public static int ExecuteSql(string SQLS

22、tring)  88.       89.         using (SqlConnection connection = new SqlConnection(connectionString)  90.           91.   

23、0;         using (SqlCommand cmd = new SqlCommand(SQLString, connection)  92.               93.         &

24、#160;       try  94.                   95.                     co

25、nnection.Open();  96.                     int rows = cmd.ExecuteNonQuery();  97.               

26、      return rows;  98.                   99.                 catch (System.Data

27、.SqlClient.SqlException E)  100.                   101.                     connection.Close();

28、  102.                     throw new Exception(E.Message);  103.                  

29、60;104.               105.           106.       107.   108.     / <summary>  109.    

30、; / 执行SQL语句，返回影响的记录数 适用于select语句  110.     / </summary>  111.     / <param name="SQLString">SQL语句</param>  112.     / <returns>影响的记录数</ret

31、urns>  113.     public static int ExecuteSql2(string SQLString)  114.       115.         using (SqlConnection connection = new SqlConnection(c

32、onnectionString)  116.           117.             using (SqlCommand cmd = new SqlCommand(SQLString, connection)  118.    

33、0;          119.                 try  120.                   121. 

34、0;                   connection.Open();  122.                     int rows = Con

35、vert.ToInt32(cmd.ExecuteScalar();  123.                     return rows;  124.                 

36、  125.                 catch (System.Data.SqlClient.SqlException E)  126.                   127.

37、60;                   connection.Close();  128.                     throw new Excepti

38、on(E.Message);  129.                   130.               131.           132. &#

39、160;     133.   134.     / <summary>  135.     / 执行多条SQL语句，实现数据库事务。  136.     / </summary>  137.     / <param name=

40、"SQLStringList">多条SQL语句</param>       138.     public static void ExecuteSqlTran(ArrayList SQLStringList)  139.       140.        &

41、#160;using (SqlConnection conn = new SqlConnection(connectionString)  141.           142.             conn.Open();  143.     

42、        SqlCommand cmd = new SqlCommand();  144.             cmd.Connection = conn;  145.           

43、  SqlTransaction tx = conn.BeginTransaction();  146.             cmd.Transaction = tx;  147.             try  148.

44、              149.                 for (int n = 0; n < SQLStringList.Count; n+)  150.   

45、;                151.                     string strsql = SQLStringListn.ToString();  152. 

46、0;                   if (strsql.Trim().Length > 1)  153.                     

47、  154.                         cmd.CommandText = strsql;  155.               

48、60;         cmd.ExecuteNonQuery();  156.                       157.            &

49、#160;      158.                 tx.Commit();  159.               160.       

50、0;     catch (System.Data.SqlClient.SqlException E)  161.               162.                 tx.Rollback(

51、);  163.                 throw new Exception(E.Message);  164.               165.       &#

52、160;   166.       167.   168.     / <summary>  169.     / 执行带一个存储过程参数的的SQL语句。  170.     / </summary>  171.    

53、60;/ <param name="SQLString">SQL语句</param>  172.     / <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>  173.     / <returns>影响的记录数</returns>

54、  174.     public static int ExecuteSql(string SQLString, string content)  175.       176.         using (SqlConnection connection = new

55、0;SqlConnection(connectionString)  177.           178.             SqlCommand cmd = new SqlCommand(SQLString, connection);  179.   

56、0;         System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("content", SqlDbType.VarChar);  180.   181.          

57、60;  myParameter.Value = content;  182.             cmd.Parameters.Add(myParameter);  183.             try  184.    

58、;           185.                 connection.Open();  186.                 i

59、nt rows = cmd.ExecuteNonQuery();  187.                 return rows;  188.               189.    

60、;         catch (System.Data.SqlClient.SqlException E)  190.               191.               &

61、#160; throw new Exception(E.Message);  192.               193.             finally  194.        

62、0;      195.                 cmd.Dispose();  196.                 connection.Close();  197

63、.               198.           199.       200.   201.     / <summary>  202.     

64、;/ 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)  203.     / </summary>  204.     / <param name="strSQL">SQL语句</param>  205.     / <param name="fs">图像字节

65、,数据库的字段类型为image的情况</param>  206.     / <returns>影响的记录数</returns>  207.     public static int ExecuteSqlInsertImg(string strSQL, byte fs)  208.      

66、0;209.         using (SqlConnection connection = new SqlConnection(connectionString)  210.           211.            

67、60;SqlCommand cmd = new SqlCommand(strSQL, connection);  212.             System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("fs", Sq

68、lDbType.Binary);  213.             myParameter.Value = fs;  214.             cmd.Parameters.Add(myParameter);  215.     

69、        try  216.               217.                 connection.Open();  218.   

70、              int rows = cmd.ExecuteNonQuery();  219.                 return rows;  220.    

71、60;          221.             catch (System.Data.SqlClient.SqlException E)  222.               223.

72、                throw new Exception(E.Message);  224.               225.          

73、60;  finally  226.               227.                 cmd.Dispose();  228.        

74、60;        connection.Close();  229.               230.           231.       232.   233. &

75、#160;   / <summary>  234.     / 执行一条计算查询结果语句，返回查询结果（object）。  235.     / </summary>  236.     / <param name="SQLString">计算查询结果语句</param>

76、  237.     / <returns>查询结果（object）</returns>  238.     public static object GetSingle(string SQLString)  239.       240.        

77、 using (SqlConnection connection = new SqlConnection(connectionString)  241.           242.             using (SqlCommand cmd = ne

78、w SqlCommand(SQLString, connection)  243.               244.                 try  245.      

79、60;            246.                     connection.Open();  247.            

80、;         object obj = cmd.ExecuteScalar();  248.                     if (Object.Equals(obj, null) | (Object.E

81、quals(obj, System.DBNull.Value)  249.                       250.                   &#

82、160;     return null;  251.                       252.                 

83、;    else  253.                       254.                    

84、60;    return obj;  255.                       256.                  &

85、#160;257.                 catch (System.Data.SqlClient.SqlException e)  258.                   259.  

86、0;                  connection.Close();  260.                     throw new Exception(e.Me

87、ssage);  261.                   262.               263.           264.  

88、60;    265.   266.     / <summary>  267.     / 执行查询语句，返回SqlDataReader  268.     / </summary>  269.     / <param name=&qu

89、ot;strSQL">查询语句</param>  270.     / <returns>SqlDataReader</returns>  271.     public static SqlDataReader ExecuteReader(string strSQL)  272.       2

90、73.         SqlConnection connection = new SqlConnection(connectionString);  274.         SqlCommand cmd = new SqlCommand(strSQL, connection);  275.  

91、       try  276.           277.             connection.Open();  278.            

92、 SqlDataReader myReader = cmd.ExecuteReader();  279.             return myReader;  280.           281.        

93、 catch (System.Data.SqlClient.SqlException e)  282.           283.             throw new Exception(e.Message);  284.     

94、0;     285.   286.       287.   288.     / <summary>  289.     / 执行查询语句，返回DataSet  290.     / </summary>  29

95、1.     / <param name="SQLString">查询语句</param>  292.     / <returns>DataSet</returns>  293.     public static DataSet Query(string SQLString)  

96、;294.       295.         using (SqlConnection connection = new SqlConnection(connectionString)  296.           297.      

97、;       DataSet ds = new DataSet();  298.             try  299.               300.   

98、              connection.Open();  301.                 SqlDataAdapter command = new SqlDataAdapter(SQLString, conne

99、ction);  302.   303.                 command.Fill(ds, "ds");  304.               305.    &

100、#160;        catch (System.Data.SqlClient.SqlException ex)  306.               307.               &#

101、160; throw new Exception(ex.Message);  308.               309.             return ds;  310.       &#

102、160;   311.       312.   313.     / <summary>  314.     / 执行查询语句，返回datatable  315.     / </summary>  316.    

103、0;/ <param name="SQLString">查询语句</param>  317.     / <returns>DataSet</returns>  318.     public static DataTable QueryTable(string SQLString)  319.   

104、    320.         using (SqlConnection connection = new SqlConnection(connectionString)  321.           322.         

105、;    DataSet ds = new DataSet();  323.             try  324.               325.      

106、           connection.Open();  326.                 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);  

107、;327.                 command.Fill(ds, "ds");  328.               329.          

108、   catch (System.Data.SqlClient.SqlException ex)  330.               331.                 throw new Excep

109、tion(ex.Message);  332.               333.             return ds.Tables0;  334.           335.

110、      336.     #endregion  337.  338.     #region 执行带参数的SQL语句  339.     / <summary>  340.     / 执行SQL语句，返回影响的记录数  341.  

111、;   / </summary>  342.     / <param name="SQLString">SQL语句</param>  343.     / <returns>影响的记录数</returns>  344.     public static&#

112、160;int ExecuteSql(string SQLString, params SqlParameter cmdParms)  345.       346.         using (SqlConnection connection = new SqlConnection(connectionString) 

113、60;347.           348.             using (SqlCommand cmd = new SqlCommand()  349.             &

114、#160; 350.                 try  351.                   352.          &

115、#160;          PrepareCommand(cmd, connection, null, SQLString, cmdParms);  353.                     int rows 

116、= cmd.ExecuteNonQuery();  354.                     cmd.Parameters.Clear();  355.                

117、0;    return rows;  356.                   357.                 catch (System.Data.SqlClie

118、nt.SqlException E)  358.                   359.                     throw new Excepti

119、on(E.Message);  360.                   361.               362.           363. &#

120、160;     364.   365.     / <summary>  366.     / 执行多条SQL语句，实现数据库事务。  367.     / </summary>  368.     / <param name=

121、"SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter）</param>  369.     public static void ExecuteSqlTran(Hashtable SQLStringList)  370.       371.       &

122、#160; using (SqlConnection conn = new SqlConnection(connectionString)  372.           373.             conn.Open();  374.    

123、         using (SqlTransaction trans = conn.BeginTransaction()  375.               376.            &

124、#160;    SqlCommand cmd = new SqlCommand();  377.                 try  378.               

125、    379.                     /循环  380.                     foreach

126、0;(DictionaryEntry myDE in SQLStringList)  381.                       382.                

127、         string cmdText = myDE.Key.ToString();  383.                         SqlParameter cmdParms =

128、 (SqlParameter)myDE.Value;  384.                         PrepareCommand(cmd, conn, trans, cmdText, cmdParms);  385.    &

129、#160;                    int val = cmd.ExecuteNonQuery();  386.                   

130、60;     cmd.Parameters.Clear();  387.   388.                         trans.Commit();  389.      

131、0;                390.                   391.               

132、  catch  392.                   393.                     trans.Rollback();  394.

133、                     throw;  395.                   396.        &

134、#160;      397.           398.       399.   400.     / <summary>  401.     / 执行一条计算查询结果语句，返回查询结果（object）。 

135、60;402.     / </summary>  403.     / <param name="SQLString">计算查询结果语句</param>  404.     / <returns>查询结果（object）</returns>  405.    

136、0;public static object GetSingle(string SQLString, params SqlParameter cmdParms)  406.       407.         using (SqlConnection connection = new SqlConnection(co

137、nnectionString)  408.           409.             using (SqlCommand cmd = new SqlCommand()  410.         &

138、#160;     411.                 try  412.                   413.      &

139、#160;              PrepareCommand(cmd, connection, null, SQLString, cmdParms);  414.                    &

140、#160;object obj = cmd.ExecuteScalar();  415.                     cmd.Parameters.Clear();  416.            

141、0;        if (Object.Equals(obj, null) | (Object.Equals(obj, System.DBNull.Value)  417.                       418.

142、                        return null;  419.                     &#

143、160; 420.                     else  421.                       422.  &

144、#160;                      return obj;  423.                      

145、0;424.                   425.                 catch (System.Data.SqlClient.SqlException e)  426.   &

146、#160;               427.                     throw new Exception(e.Message);  428.    

147、0;              429.               430.           431.       432.   43

148、3.     / <summary>  434.     / 执行查询语句，返回SqlDataReader  435.     / </summary>  436.     / <param name="strSQL">查询语句</param> 

149、60;437.     / <returns>SqlDataReader</returns>  438.     public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter cmdParms)  439.       

150、;440.         SqlConnection connection = new SqlConnection(connectionString);  441.         SqlCommand cmd = new SqlCommand();  442.     

151、60;   try  443.           444.             PrepareCommand(cmd, connection, null, SQLString, cmdParms);  445.     &#

152、160;       SqlDataReader myReader = cmd.ExecuteReader();  446.             cmd.Parameters.Clear();  447.            

153、; return myReader;  448.           449.         catch (System.Data.SqlClient.SqlException e)  450.           451.  

154、           throw new Exception(e.Message);  452.           453.   454.       455.   456.     / <s

155、ummary>  457.     / 执行查询语句，返回DataSet  458.     / </summary>  459.     / <param name="SQLString">查询语句</param>  460.     / <

156、;returns>DataSet</returns>  461.     public static DataSet Query(string SQLString, params SqlParameter cmdParms)  462.       463.         using

157、60;(SqlConnection connection = new SqlConnection(connectionString)  464.           465.             SqlCommand cmd = new SqlCommand(); 

158、 466.             PrepareCommand(cmd, connection, null, SQLString, cmdParms);  467.             using (SqlDataAdapter da = ne

159、w SqlDataAdapter(cmd)  468.               469.                 DataSet ds = new DataSet();  470.  &#

160、160;              try  471.                   472.              &

161、#160;      da.Fill(ds, "ds");  473.                     cmd.Parameters.Clear();  474.         &#