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 }