用以MySql的SqlHelper_mysql

  1     /// <summary>   2    3     /// Title  ：MySqlHelper   4     /// Author ：WinterT   5     /// Date   ：2015-1-8 08:12:54   6     /// Description：   7     ///       ExecuteNonQuery   8     ///       ExecuteScalar   9     ///       ExecuteReader  10     ///       ExecuteTable  11     /// </summary>  12     public static class MySqlHelper  13     {  14         /// <summary>  15         /// 返回配置文件中指定的连接  16         /// </summary>  17         /// <returns>配置文件中指定的连接</returns>  18         private static MySqlConnection GetConnection()  19         {  20             string connString = ConfigurationManager.ConnectionStrings[1].ConnectionString;  21             return new MySqlConnection(connString);  22         }  23         #region ExecuteNonQuery  24         /// <summary>  25         /// 执行sql语句  26         /// </summary>  27         /// <param name="sql">sql语句</param>  28         /// <returns>受影响行数</returns>  29         public static int ExecuteNonQuery(string sql)  30         {  31             using (MySqlConnection conn = GetConnection())  32             {  33                 using (MySqlCommand cmd = conn.CreateCommand())  34                 {  35                     cmd.CommandText = sql;  36                     return cmd.ExecuteNonQuery();  37                 }  38             }  39         }  40         /// <summary>  41         /// 根据给定连接，执行带参数的SQL语句  42         /// </summary>  43         /// <param name="conn">连接、使用前确保连接以打开。</param>  44         /// <param name="sql">带参数的sql语句</param>  45         /// <param name="paras">参数</param>  46         /// <returns>受影响行数</returns>  47         public static int ExecuteNonQuery  48             (MySqlConnection conn, string sql, params MySqlParameter[] paras)  49         {  50             using (MySqlCommand cmd = conn.CreateCommand())  51             {  52                 cmd.CommandText = sql;  53                 cmd.Parameters.AddRange(paras);  54                 return cmd.ExecuteNonQuery();  55             }  56         }  57         /// <summary>  58         /// 执行带参数的SQL语句  59         /// </summary>  60         /// <param name="sql">带参数的sql语句</param>  61         /// <param name="paras">参数</param>  62         /// <returns>受影响行数</returns>  63         public static int ExecuteNonQuery  64             (string sql, params MySqlParameter[] paras)  65         {  66             using (MySqlConnection conn = GetConnection())  67             {  68                 return ExecuteNonQuery(conn, sql, paras);  69             }  70         }  71         #endregion  72         #region ExecuteScalar  73         /// <summary>  74         /// 执行sql语句，返回第一行第一列  75         /// </summary>  76         /// <param name="sql">sql语句</param>  77         /// <returns>第一行第一列</returns>  78         public static Object ExecuteScalar(string sql)  79         {  80             using (MySqlConnection conn = GetConnection())  81             {  82                 using (MySqlCommand cmd = conn.CreateCommand())  83                 {  84                     cmd.CommandText = sql;  85                     return cmd.ExecuteScalar();  86                 }  87             }  88         }  89         /// <summary>  90         /// 根据Connection对象，执行带参数的sql语句，返回第一行第一列  91         /// </summary>  92         /// <param name="conn">连接</param>  93         /// <param name="sql">sql语句</param>  94         /// <param name="paras">参数</param>  95         /// <returns>返回第一行第一列</returns>  96         public static object ExecuteScalar  97             (MySqlConnection conn, string sql, MySqlParameter[] paras)  98         {  99             using (MySqlCommand cmd = conn.CreateCommand()) 100             { 101                 cmd.CommandText = sql; 102                 cmd.Parameters.AddRange(paras); 103                 return cmd.ExecuteScalar(); 104             } 105         } 106         /// <summary> 107         /// 执行带参数的sql语句，返回第一行第一列 108         /// </summary> 109         /// <param name="sql">sql语句</param> 110         /// <param name="paras">参数</param> 111         /// <returns>返回第一行第一列</returns> 112         public static object ExecuteScalar 113             (string sql, MySqlParameter[] paras) 114         { 115             using (MySqlConnection conn = GetConnection()) 116             { 117                 return ExecuteScalar(conn, sql, paras); 118             } 119         } 120         #endregion 121         #region ExecuteReader 122         /// <summary> 123         /// 执行sql语句，返回一个MySqlDataReader 124         /// </summary> 125         /// <param name="sql">sql语句</param> 126         /// <returns>一个MySqlDataReader对象</returns> 127         public static MySqlDataReader ExecuteReader(string sql) 128         { 129             MySqlConnection conn = GetConnection(); 130             using (MySqlCommand cmd = conn.CreateCommand()) 131             { 132                 cmd.CommandText = sql; 133                 conn.Open(); 134                 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 135             } 136         } 137         /// <summary> 138         /// 根据指定的连接，执行带参数的sql语句，返回一个Reader对象 139         /// </summary> 140         /// <param name="conn">连接</param> 141         /// <param name="sql">sql语句</param> 142         /// <param name="paras">参数</param> 143         /// <returns>一个MySqlDataReader对象</returns> 144         public static MySqlDataReader ExecuteReader 145             (MySqlConnection conn, string sql, params MySqlParameter[] paras) 146         { 147             using (MySqlCommand cmd = conn.CreateCommand()) 148             { 149                 cmd.CommandText = sql; 150                 cmd.Parameters.AddRange(paras); 151                 conn.Open(); 152                 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 153             } 154         } 155         /// <summary> 156         /// 执行带参数的sql语句，返回一个Reader对象 157         /// </summary> 158         /// <param name="sql">sql语句</param> 159         /// <param name="paras">参数</param> 160         /// <returns>一个MySqlDataReader对象</returns> 161         public static MySqlDataReader ExecuteReader 162             (string sql, params MySqlParameter[] paras) 163         { 164             MySqlConnection conn = GetConnection(); 165             using (MySqlCommand cmd = conn.CreateCommand()) 166             { 167                 return ExecuteReader(conn, sql, paras); 168             } 169         } 170         #endregion 171         #region ExecuteTable 172         /// <summary> 173         /// 执行sql语句，返回一个DataTable 174         /// </summary> 175         /// <param name="sql">sql语句</param> 176         /// <returns>DataTable</returns> 177         public static DataTable ExecuteTable(string sql) 178         { 179             using (MySqlConnection conn = GetConnection()) 180             { 181                 using (MySqlCommand cmd = conn.CreateCommand()) 182                 { 183                     cmd.CommandText = sql; 184                     using (MySqlDataReader reader = cmd.ExecuteReader()) 185                     { 186                         DataTable table = new DataTable(); 187                         table.Load(reader); 188                         return table; 189                     } 190                 } 191             } 192         } 193         /// <summary> 194         /// 根据连接，执行带参数的sql语句，返回一个DataTable 195         /// </summary> 196         /// <param name="conn">连接，切记连接已打开</param> 197         /// <param name="sql">sql语句</param> 198         /// <param name="paras">参数</param> 199         /// <returns>DataTable</returns> 200         public static DataTable ExecuteTable 201             (MySqlConnection conn, string sql, params MySqlParameter[] paras) 202         { 203             using (MySqlCommand cmd = conn.CreateCommand()) 204             { 205                 cmd.CommandText = sql; 206                 cmd.Parameters.AddRange(paras); 207                 using (MySqlDataReader reader = cmd.ExecuteReader()) 208                 { 209                     DataTable table = new DataTable(); 210                     table.Load(reader); 211                     return table; 212                 } 213             } 214         } 215         /// <summary> 216         /// 执行带参数的sql语句 217         /// </summary> 218         /// <param name="sql">sql语句</param> 219         /// <param name="paras">参数</param> 220         /// <returns>DataTable</returns> 221         public static DataTable ExecuteTable 222             (string sql, params MySqlParameter[] paras) 223         { 224             using (MySqlConnection conn = GetConnection()) 225             { 226                 return ExecuteTable(conn, sql, paras); 227             } 228         } 229         230         #endregion 231     }