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 }

 

转载于:https://www.cnblogs.com/zhangbaochong/p/5675740.html