.net mysql sqlhelper_用于MySql的SqlHelper

1 ///

2

3 ///Title ：MySqlHelper4 ///Author ：WinterT5 ///Date ：2015-1-8 08:12:546 ///Description：7 ///ExecuteNonQuery8 ///ExecuteScalar9 ///ExecuteReader10 ///ExecuteTable11 ///

12 public static classMySqlHelper13 {14 ///

15 ///返回配置文件中指定的连接16 ///

17 /// 配置文件中指定的连接

18 private staticMySqlConnection GetConnection()19 {20 string connString = ConfigurationManager.ConnectionStrings[1].ConnectionString;21 return newMySqlConnection(connString);22 }23 #region ExecuteNonQuery

24 ///

25 ///执行sql语句26 ///

27 /// sql语句

28 /// 受影响行数

29 public static int ExecuteNonQuery(stringsql)30 {31 using (MySqlConnection conn =GetConnection())32 {33 using (MySqlCommand cmd =conn.CreateCommand())34 {35 cmd.CommandText =sql;36 returncmd.ExecuteNonQuery();37 }38 }39 }40 ///

41 ///根据给定连接，执行带参数的SQL语句42 ///

43 /// 连接、使用前确保连接以打开。

44 /// 带参数的sql语句

45 /// 参数

46 /// 受影响行数

47 public static intExecuteNonQuery48 (MySqlConnection conn, string sql, paramsMySqlParameter[] paras)49 {50 using (MySqlCommand cmd =conn.CreateCommand())51 {52 cmd.CommandText =sql;53 cmd.Parameters.AddRange(paras);54 returncmd.ExecuteNonQuery();55 }56 }57 ///

58 ///执行带参数的SQL语句59 ///

60 /// 带参数的sql语句

61 /// 参数

62 /// 受影响行数

63 public static intExecuteNonQuery64 (string sql, paramsMySqlParameter[] paras)65 {66 using (MySqlConnection conn =GetConnection())67 {68 returnExecuteNonQuery(conn, sql, paras);69 }70 }71 #endregion

72 #region ExecuteScalar

73 ///

74 ///执行sql语句，返回第一行第一列75 ///

76 /// sql语句

77 /// 第一行第一列

78 public static Object ExecuteScalar(stringsql)79 {80 using (MySqlConnection conn =GetConnection())81 {82 using (MySqlCommand cmd =conn.CreateCommand())83 {84 cmd.CommandText =sql;85 returncmd.ExecuteScalar();86 }87 }88 }89 ///

90 ///根据Connection对象，执行带参数的sql语句，返回第一行第一列91 ///

92 /// 连接

93 /// sql语句

94 /// 参数

95 /// 返回第一行第一列

96 public static objectExecuteScalar97 (MySqlConnection conn, stringsql, MySqlParameter[] paras)98 {99 using (MySqlCommand cmd =conn.CreateCommand())100 {101 cmd.CommandText =sql;102 cmd.Parameters.AddRange(paras);103 returncmd.ExecuteScalar();104 }105 }106 ///

107 ///执行带参数的sql语句，返回第一行第一列108 ///

109 /// sql语句

110 /// 参数

111 /// 返回第一行第一列

112 public static objectExecuteScalar113 (stringsql, MySqlParameter[] paras)114 {115 using (MySqlConnection conn =GetConnection())116 {117 returnExecuteScalar(conn, sql, paras);118 }119 }120 #endregion

121 #region ExecuteReader

122 ///

123 ///执行sql语句，返回一个MySqlDataReader124 ///

125 /// sql语句

126 /// 一个MySqlDataReader对象

127 public static MySqlDataReader ExecuteReader(stringsql)128 {129 MySqlConnection conn =GetConnection();130 using (MySqlCommand cmd =conn.CreateCommand())131 {132 cmd.CommandText =sql;133 conn.Open();134 returncmd.ExecuteReader(CommandBehavior.CloseConnection);135 }136 }137 ///

138 ///根据指定的连接，执行带参数的sql语句，返回一个Reader对象139 ///

140 /// 连接

141 /// sql语句

142 /// 参数

143 /// 一个MySqlDataReader对象

144 public staticMySqlDataReader ExecuteReader145 (MySqlConnection conn, string sql, paramsMySqlParameter[] paras)146 {147 using (MySqlCommand cmd =conn.CreateCommand())148 {149 cmd.CommandText =sql;150 cmd.Parameters.AddRange(paras);151 conn.Open();152 returncmd.ExecuteReader(CommandBehavior.CloseConnection);153 }154 }155 ///

156 ///执行带参数的sql语句，返回一个Reader对象157 ///

158 /// sql语句

159 /// 参数

160 /// 一个MySqlDataReader对象

161 public staticMySqlDataReader ExecuteReader162 (string sql, paramsMySqlParameter[] paras)163 {164 MySqlConnection conn =GetConnection();165 using (MySqlCommand cmd =conn.CreateCommand())166 {167 returnExecuteReader(conn, sql, paras);168 }169 }170 #endregion

171 #region ExecuteTable

172 ///

173 ///执行sql语句，返回一个DataTable174 ///

175 /// sql语句

176 /// DataTable

177 public static DataTable ExecuteTable(stringsql)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 = newDataTable();187 table.Load(reader);188 returntable;189 }190 }191 }192 }193 ///

194 ///根据连接，执行带参数的sql语句，返回一个DataTable195 ///

196 /// 连接，切记连接已打开

197 /// sql语句

198 /// 参数

199 /// DataTable

200 public staticDataTable ExecuteTable201 (MySqlConnection conn, string sql, paramsMySqlParameter[] 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 = newDataTable();210 table.Load(reader);211 returntable;212 }213 }214 }215 ///

216 ///执行带参数的sql语句217 ///

218 /// sql语句

219 /// 参数

220 /// DataTable

221 public staticDataTable ExecuteTable222 (string sql, paramsMySqlParameter[] paras)223 {224 using (MySqlConnection conn =GetConnection())225 {226 returnExecuteTable(conn, sql, paras);227 }228 }229

230 #endregion

231 }