ASP.NET - 使用MqSql数据库
1. 首先需要安装mysql，
脚本之家下载地址： http://www.jb51.net/softs/2193.html 或者去mysql.com官网都可以,一路next，安装好后，有个简单配置,提示有个设置登录密码和服务名称，
默认localhost，用户名root，密码自己设置。
2. 安装了mysql数据库后，需要一个管理工具，就像sqlserver的SQL Server Management Studio一样，推荐使用Navicat for MySQL，
下载地址：http://www.jb51.net/database/2223.html，安装后，打开界面如下图：
确定后，右键新建的连接名字，就可以建数据库，建表了。字段类型和sqlserver基本一致。
3. Asp.net连接mysql 不推荐使用ODBC,推荐是用mysql官网提供的组件动)MySQL.Data.Dll，官网地址， http://dev.mysql.com/downloads/connector/net/5.1.html；可能需要简单的注册下用户，看好版本，如果嫌弃麻烦可以直接下载一个相应版本的MySQL.Data.Dll ，放入bin下，增加引用即可。
4. 我自己写的一个简单MysqlHelper.cs类，操作和sqlserver 很相似。有了这个类，你就知道怎么用了，很简单
代码如下：
1 using System; 2 using MySql.Data.MySqlClient; 3 using System.Collections.Generic; 4 using System.Configuration; 5 using System.Data; 6 using System.Text.RegularExpressions; 7 8 namespace LOAF.DAL 9 { 10 public class MysqlHelper 11 { 12 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 13 public static string connectionString = ConfigurationManager.AppSettings["MySQL"]; 14 15 /// <summary> 16 /// 构造函数 17 /// </summary> 18 public MysqlHelper() { } 19 20 #region ExecuteNonQuery 21 //执行SQL语句，返回影响的记录数 22 /// <summary> 23 /// 执行SQL语句，返回影响的记录数 24 /// </summary> 25 /// <param name="SQLString">SQL语句</param> 26 /// <returns>影响的记录数</returns> 27 public static int ExecuteNonQuery(string SQLString) 28 { 29 using (MySqlConnection connection = new MySqlConnection(connectionString)) 30 { 31 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) 32 { 33 try 34 { 35 connection.Open(); 36 int rows = cmd.ExecuteNonQuery(); 37 return rows; 38 } 39 catch (MySql.Data.MySqlClient.MySqlException e) 40 { 41 connection.Close(); 42 throw e; 43 } 44 } 45 } 46 } 47 /// <summary> 48 /// 执行SQL语句，返回影响的记录数 49 /// </summary> 50 /// <param name="SQLString">SQL语句</param> 51 /// <returns>影响的记录数</returns> 52 public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms) 53 { 54 using (MySqlConnection connection = new MySqlConnection(connectionString)) 55 { 56 using (MySqlCommand cmd = new MySqlCommand()) 57 { 58 try 59 { 60 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 61 int rows = cmd.ExecuteNonQuery(); 62 cmd.Parameters.Clear(); 63 return rows; 64 } 65 catch (MySql.Data.MySqlClient.MySqlException e) 66 { 67 throw e; 68 } 69 } 70 } 71 } 72 //执行多条SQL语句，实现数据库事务。 73 /// <summary> 74 /// 执行多条SQL语句，实现数据库事务。 75 /// </summary> 76 /// <param name="SQLStringList">多条SQL语句</param> 77 public static bool ExecuteNoQueryTran(List<String> SQLStringList) 78 { 79 using (MySqlConnection conn = new MySqlConnection(connectionString)) 80 { 81 conn.Open(); 82 MySqlCommand cmd = new MySqlCommand(); 83 cmd.Connection = conn; 84 MySqlTransaction tx = conn.BeginTransaction(); 85 cmd.Transaction = tx; 86 try 87 { 88 for (int n = 0; n < SQLStringList.Count; n++) 89 { 90 string strsql = SQLStringList[n]; 91 if (strsql.Trim().Length > 1) 92 { 93 cmd.CommandText = strsql; 94 PrepareCommand(cmd, conn, tx, strsql, null); 95 cmd.ExecuteNonQuery(); 96 } 97 } 98 cmd.ExecuteNonQuery(); 99 tx.Commit(); 100 return true; 101 } 102 catch 103 { 104 tx.Rollback(); 105 return false; 106 } 107 } 108 } 109 #endregion 110 111 #region ExecuteScalar 112 /// <summary> 113 /// 执行一条计算查询结果语句，返回查询结果（object）。 114 /// </summary> 115 /// <param name="SQLString">计算查询结果语句</param> 116 /// <returns>查询结果（object）</returns> 117 public static object ExecuteScalar(string SQLString) 118 { 119 using (MySqlConnection connection = new MySqlConnection(connectionString)) 120 { 121 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) 122 { 123 try 124 { 125 connection.Open(); 126 object obj = cmd.ExecuteScalar(); 127 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 128 { 129 return null; 130 } 131 else 132 { 133 return obj; 134 } 135 } 136 catch (MySql.Data.MySqlClient.MySqlException e) 137 { 138 connection.Close(); 139 throw e; 140 } 141 } 142 } 143 } 144 /// <summary> 145 /// 执行一条计算查询结果语句，返回查询结果（object）。 146 /// </summary> 147 /// <param name="SQLString">计算查询结果语句</param> 148 /// <returns>查询结果（object）</returns> 149 public static object ExecuteScalar(string SQLString, params MySqlParameter[] cmdParms) 150 { 151 using (MySqlConnection connection = new MySqlConnection(connectionString)) 152 { 153 using (MySqlCommand cmd = new MySqlCommand()) 154 { 155 try 156 { 157 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 158 object obj = cmd.ExecuteScalar(); 159 cmd.Parameters.Clear(); 160 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 161 { 162 return null; 163 } 164 else 165 { 166 return obj; 167 } 168 } 169 catch (MySql.Data.MySqlClient.MySqlException e) 170 { 171 throw e; 172 } 173 } 174 } 175 } 176 #endregion 177 178 #region ExecuteReader 179 /// <summary> 180 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) 181 /// </summary> 182 /// <param name="strSQL">查询语句</param> 183 /// <returns>MySqlDataReader</returns> 184 public static MySqlDataReader ExecuteReader(string strSQL) 185 { 186 MySqlConnection connection = new MySqlConnection(connectionString); 187 MySqlCommand cmd = new MySqlCommand(strSQL, connection); 188 try 189 { 190 connection.Open(); 191 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 192 return myReader; 193 } 194 catch (MySql.Data.MySqlClient.MySqlException e) 195 { 196 throw e; 197 } 198 } 199 /// <summary> 200 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) 201 /// </summary> 202 /// <param name="strSQL">查询语句</param> 203 /// <returns>MySqlDataReader</returns> 204 public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms) 205 { 206 MySqlConnection connection = new MySqlConnection(connectionString); 207 MySqlCommand cmd = new MySqlCommand(); 208 try 209 { 210 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 211 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 212 cmd.Parameters.Clear(); 213 return myReader; 214 } 215 catch (MySql.Data.MySqlClient.MySqlException e) 216 { 217 throw e; 218 } 219 // finally 220 // { 221 // cmd.Dispose(); 222 // connection.Close(); 223 // } 224 } 225 #endregion 226 227 #region ExecuteDataTable 228 /// <summary> 229 /// 执行查询语句，返回DataTable 230 /// </summary> 231 /// <param name="SQLString">查询语句</param> 232 /// <returns>DataTable</returns> 233 public static DataTable ExecuteDataTable(string SQLString) 234 { 235 using (MySqlConnection connection = new MySqlConnection(connectionString)) 236 { 237 DataSet ds = new DataSet(); 238 try 239 { 240 connection.Open(); 241 MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); 242 command.Fill(ds, "ds"); 243 } 244 catch (MySql.Data.MySqlClient.MySqlException ex) 245 { 246 throw new Exception(ex.Message); 247 } 248 return ds.Tables[0]; 249 } 250 } 251 /// <summary> 252 /// 执行查询语句，返回DataSet 253 /// </summary> 254 /// <param name="SQLString">查询语句</param> 255 /// <returns>DataTable</returns> 256 public static DataTable ExecuteDataTable(string SQLString, params MySqlParameter[] cmdParms) 257 { 258 using (MySqlConnection connection = new MySqlConnection(connectionString)) 259 { 260 MySqlCommand cmd = new MySqlCommand(); 261 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 262 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) 263 { 264 DataSet ds = new DataSet(); 265 try 266 { 267 da.Fill(ds, "ds"); 268 cmd.Parameters.Clear(); 269 } 270 catch (MySql.Data.MySqlClient.MySqlException ex) 271 { 272 throw new Exception(ex.Message); 273 } 274 return ds.Tables[0]; 275 } 276 } 277 } 278 //获取起始页码和结束页码 279 public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord) 280 { 281 using (MySqlConnection connection = new MySqlConnection(connectionString)) 282 { 283 DataSet ds = new DataSet(); 284 try 285 { 286 connection.Open(); 287 MySqlDataAdapter command = new MySqlDataAdapter(cmdText, connection); 288 command.Fill(ds, startResord, maxRecord, "ds"); 289 } 290 catch (MySql.Data.MySqlClient.MySqlException ex) 291 { 292 throw new Exception(ex.Message); 293 } 294 return ds.Tables[0]; 295 } 296 } 297 #endregion 298 299 #region 创建command 300 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) 301 { 302 if (conn.State != ConnectionState.Open) 303 conn.Open(); 304 cmd.Connection = conn; 305 cmd.CommandText = cmdText; 306 if (trans != null) 307 cmd.Transaction = trans; 308 cmd.CommandType = CommandType.Text;//cmdType; 309 if (cmdParms != null) 310 { 311 foreach (MySqlParameter parameter in cmdParms) 312 { 313 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 314 (parameter.Value == null)) 315 { 316 parameter.Value = DBNull.Value; 317 } 318 cmd.Parameters.Add(parameter); 319 } 320 } 321 } 322 #endregion 323 324 #region 获取分页数据 在不用存储过程情况下 325 /// <summary> 326 /// 获取分页数据 在不用存储过程情况下 327 /// </summary> 328 /// <param name="recordCount">总记录条数</param> 329 /// <param name="selectList">选择的列逗号隔开,支持top num</param> 330 /// <param name="tableName">表名字</param> 331 /// <param name="whereStr">条件字符 必须前加 and</param> 332 /// <param name="orderExpression">排序 例如 ID</param> 333 /// <param name="pageIdex">当前索引页</param> 334 /// <param name="pageSize">每页记录数</param> 335 /// <returns></returns> 336 public static DataTable getPager(out int recordCount, string selectList, string tableName, string whereStr, string orderExpression, int pageIdex, int pageSize) 337 { 338 int rows = 0; 339 DataTable dt = new DataTable(); 340 MatchCollection matchs = Regex.Matches(selectList, @"top\s+\d{1,}", RegexOptions.IgnoreCase);//含有top 341 string sqlStr = sqlStr = string.Format("select {0} from {1} where 1=1 {2}", selectList, tableName, whereStr); 342 if (!string.IsNullOrEmpty(orderExpression)) { sqlStr += string.Format(" Order by {0}", orderExpression); } 343 if (matchs.Count > 0) //含有top的时候 344 { 345 DataTable dtTemp = ExecuteDataTable(sqlStr); 346 rows = dtTemp.Rows.Count; 347 } 348 else //不含有top的时候 349 { 350 string sqlCount = string.Format("select count(*) from {0} where 1=1 {1} ", tableName, whereStr); 351 //获取行数 352 object obj = ExecuteScalar(sqlCount); 353 if (obj != null) 354 { 355 rows = Convert.ToInt32(obj); 356 } 357 } 358 dt = ExecuteDataTable(sqlStr, (pageIdex - 1) * pageSize, pageSize); 359 recordCount = rows; 360 return dt; 361 } 362 #endregion 363 } 364 }
5. 需要注意的地方有如下几点
- 在sqlserver中参数化sql语句是用“@”符号，在mysql里面需要是用？号，切记，切记.
- 还有就是sqlserver中删除表可以这样写delete news where ID=12，但是在mysql里面 是delete from news where ID=12，收了from报错
- 我在使用过程中遇到了中文乱码，网上大部分解决办法是把表的设置成utf-8字符集。以及 C:\Program Files\MySQL\MySQL Server 5.1路径下my.ini 打开找到两处
- default-character-set 都设置成=utf8，但是我的还是乱码，最后
posted on 2015-10-17 22:53 ultrastrong 阅读(334) 评论(0) 编辑 收藏 举报
