用于MySql的SqlHelper

  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 ExecuteScalar113             (string sql, MySqlParameter[] paras)114         {115             using (MySqlConnection conn = GetConnection())116             {117                 return ExecuteScalar(conn, sql, paras);118             }119         }120         #endregion121         #region ExecuteReader122         /// <summary>123         /// 执行sql语句，返回一个MySqlDataReader124         /// </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 ExecuteReader145             (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 ExecuteReader162             (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         #endregion171         #region ExecuteTable172         /// <summary>173         /// 执行sql语句，返回一个DataTable174         /// </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语句，返回一个DataTable195         /// </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 ExecuteTable201             (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 ExecuteTable222             (string sql, params MySqlParameter[] paras)223         {224             using (MySqlConnection conn = GetConnection())225             {226                 return ExecuteTable(conn, sql, paras);227             }228         }229        230         #endregion231     }