asp.net使用mysql数据库
Asp.net连接mysql 不推荐使用ODBC,推荐是用mysql官网提供的组件MySQL.Data.Dll，放在bin下面，添加引用即可
下面是封装的几个常用操作
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using MySql.Data.MySqlClient; 6 using System.Data; 7 using System.Text; 8 9 namespace WebApplication2 10 { 11 public class DBHelper 12 { 13 public static string connectionString = "Host=127.0.0.1;UserName=root;Password=root;" 14 +"Database=mmloo;Port=3306;CharSet=utf8;Allow Zero Datetime=true"; 15 16 public DBHelper(){} 17 18 public static string DataTableToJson(DataTable dt) 19 { 20 StringBuilder jsonBuilder = new StringBuilder(); 21 jsonBuilder.Append("{\""); 22 jsonBuilder.Append(dt.TableName); 23 jsonBuilder.Append("\":["); 24 jsonBuilder.Append("["); 25 for (int i = 0; i < dt.Rows.Count; i++) 26 { 27 jsonBuilder.Append("{"); 28 for (int j = 0; j < dt.Columns.Count; j++) 29 { 30 jsonBuilder.Append("\""); 31 jsonBuilder.Append(dt.Columns[j].ColumnName); 32 jsonBuilder.Append("\":\""); 33 jsonBuilder.Append(dt.Rows[i][j].ToString()); 34 jsonBuilder.Append("\","); 35 } 36 jsonBuilder.Remove(jsonBuilder.Length - 1, 1); 37 jsonBuilder.Append("},"); 38 } 39 jsonBuilder.Remove(jsonBuilder.Length - 1, 1); 40 jsonBuilder.Append("]"); 41 jsonBuilder.Append("}"); 42 return jsonBuilder.ToString(); 43 } 44 /// <summary> 45 /// 执行sql语句，返回影响的记录数 46 /// </summary> 47 /// <param name="SQLString"></param> 48 /// <returns></returns> 49 public static int ExecuteNonQuery(string SQLString) 50 { 51 using (MySqlConnection connection = new MySqlConnection(connectionString)) 52 { 53 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) 54 { 55 try 56 { 57 connection.Open(); 58 int rows = cmd.ExecuteNonQuery(); 59 return rows; 60 } 61 catch (MySql.Data.MySqlClient.MySqlException e) 62 { 63 connection.Close(); 64 throw e; 65 } 66 } 67 } 68 } 69 70 71 /// <summary> 72 /// 执行SQL语句，返回影响的记录数 73 /// </summary> 74 /// <param name="SQLString">SQL语句</param> 75 /// <returns>影响的记录数</returns> 76 public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms) 77 { 78 using (MySqlConnection connection = new MySqlConnection(connectionString)) 79 { 80 using (MySqlCommand cmd = new MySqlCommand()) 81 { 82 try 83 { 84 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 85 int rows = cmd.ExecuteNonQuery(); 86 cmd.Parameters.Clear(); 87 return rows; 88 } 89 catch (MySql.Data.MySqlClient.MySqlException e) 90 { 91 throw e; 92 } 93 } 94 } 95 } 96 97 98 /// <summary> 99 /// 执行查询语句，返回DataTable 100 /// </summary> 101 /// <param name="SQLString">查询语句</param> 102 /// <returns>DataTable</returns> 103 public static DataTable ExecuteDataTable(string SQLString) 104 { 105 using (MySqlConnection connection = new MySqlConnection(connectionString)) 106 { 107 DataSet ds = new DataSet(); 108 try 109 { 110 connection.Open(); 111 MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); 112 command.Fill(ds, "ds"); 113 } 114 catch (MySql.Data.MySqlClient.MySqlException ex) 115 { 116 throw new Exception(ex.Message); 117 } 118 return ds.Tables[0]; 119 } 120 } 121 122 123 /// <summary> 124 /// 执行查询语句，返回DataSet 125 /// </summary> 126 /// <param name="SQLString">查询语句</param> 127 /// <returns>DataTable</returns> 128 public static DataTable ExecuteDataTable(string SQLString, params MySqlParameter[] cmdParms) 129 { 130 using (MySqlConnection connection = new MySqlConnection(connectionString)) 131 { 132 MySqlCommand cmd = new MySqlCommand(); 133 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 134 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) 135 { 136 DataSet ds = new DataSet(); 137 try 138 { 139 da.Fill(ds, "ds"); 140 cmd.Parameters.Clear(); 141 } 142 catch (MySql.Data.MySqlClient.MySqlException ex) 143 { 144 throw new Exception(ex.Message); 145 } 146 return ds.Tables[0]; 147 } 148 } 149 } 150 151 152 /// <summary> 153 /// 创建cmd 154 /// </summary> 155 /// <param name="cmd"></param> 156 /// <param name="conn"></param> 157 /// <param name="trans"></param> 158 /// <param name="cmdText"></param> 159 /// <param name="cmdParms"></param> 160 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) 161 { 162 163 if (conn.State != ConnectionState.Open) 164 conn.Open(); 165 cmd.Connection = conn; 166 cmd.CommandText = cmdText; 167 if (trans != null) 168 cmd.Transaction = trans; 169 cmd.CommandType = CommandType.Text;//cmdType; 170 if (cmdParms != null) 171 { 172 foreach (MySqlParameter parameter in cmdParms) 173 { 174 if ((parameter.Direction == ParameterDirection.InputOutput || 175 parameter.Direction == ParameterDirection.Input) && 176 (parameter.Value == null)) 177 { 178 parameter.Value = DBNull.Value; 179 } 180 cmd.Parameters.Add(parameter); 181 } 182 } 183 } 184 185 } 186 }
