C#:连接MySql数据库以及对数据库的操作
using System; using MySql.Data.MySqlClient; using System.Data; using System.IO; using System.Collections.Generic; public class DataMgr: IDisposable { MySqlConnection sqlConn; //单例模式 public static DataMgr instance; public DataMgr() { instance = this; Connect(); } //连接 public bool Connect() { //数据库 string connStr = "Database=mydb;Data Source=192.168.2.38;"; connStr += "User Id=root;Password=mysql;port=3306"; sqlConn = new MySqlConnection(connStr); try { sqlConn.Open(); return true; } catch (Exception e) { Console.Write("[DataMgr]Connect " + e.Message); return false; } } List<string> list = new List<string>();//查询内容列表 List<string> listcolum = new List<string>();//字段名列表 List<string> listkeyword = new List<string>();//字段名列表 //查询数据 /// <summary> ///查询数据方法 /// </summary> ///<param name = "TableName" >表名</ param > ///<param name = "listColumn" >字段列表</ param > ///<param name = "listkeyword" >查询值列表</ param > public List<string> SelectData(string TableName, string[] listColumn, string[] listkeyword) { string cloumns = listColumn[0] + ""; for (int a = 1; a < listColumn.Length; ++a) { cloumns += "," + listColumn[a] + ""; } string columnvelve = listColumn[0] + " = '" + listkeyword[0]+ "'"; for (int a = 1; a < listColumn.Length; ++a) { columnvelve += " and " + listColumn[a] + "='" + listkeyword[a] + "'"; } string query = "select * from " + TableName + " where " + columnvelve; string strSQL = string.Format("SELECT COLUMN_NAME FROM `information_schema`.`COLUMNS` where `TABLE_SCHEMA`='mydb' and `TABLE_NAME`='主表' ;"); // string query = " select * from 主表 where "+columnvelve ; string cmdStr = string.Format(query); #region---------------查询，利用 MySQLDataReader,依次读取每一条数据------------------- MySqlCommand mySQLCommand = new MySqlCommand(cmdStr, sqlConn); MySqlDataReader mySQLReader = (MySqlDataReader)mySQLCommand.ExecuteReader(); int i = 0; while (mySQLReader.Read()) { i++; for (int j = 0; j < mySQLReader.FieldCount; j++) { string strname = mySQLReader[j].ToString(); list.Add(strname); } } mySQLReader.Close(); MySqlCommand mySQLCommandColumn = new MySqlCommand(strSQL, sqlConn); MySqlDataReader mySQLReaderColumn = (MySqlDataReader)mySQLCommandColumn.ExecuteReader(); while (mySQLReaderColumn.Read()) { i++; for (int j = 0; j < mySQLReaderColumn.FieldCount; j++) { string tempcolum = mySQLReaderColumn[j].ToString(); listcolum.Add(tempcolum); } } mySQLReaderColumn.Close(); for (int j=0;j<list.Count;j++) { if (list[j] == "") { list[j] = "null"; } Console.WriteLine(/*listcolum[j] + ": \n" +*/ list[j] + "\t"); } Console.WriteLine("-----------------操作结束--------------------------------"); #endregion return list; } //查询整张表信息 public void SelectTableData(string TableName) { string cmdStr = string.Format("select * from " + TableName +";"); MySqlCommand mySQLCommand = new MySqlCommand(cmdStr, sqlConn); MySqlDataReader mySQLReader = (MySqlDataReader)mySQLCommand.ExecuteReader(); int i = 0; while (mySQLReader.Read()) { i++; for (int j = 0; j < mySQLReader.FieldCount; j++) { string strtable = mySQLReader[j].ToString(); list.Add(strtable); } } for (int j = 0; j < list.Count; j++) { if (list[j] == "") { list[j] = "null"; } Console.WriteLine(/*listcolum[j]+ ": \n" +*/ list[j] + "\t"); } mySQLReader.Close(); } //添加数据 /// <summary> ///插入数据方法 /// </summary> ///<param name = "TableName" >表名</ param > ///<param name = "listColumn" >字段数组列表</ param > ///<param name = "listKeyWord" >添加的值</ param > public void InsertInto(string TableName, string[] listColumn, string[] listKeyWord) { string query = "INSERT INTO " + TableName + "(" + listColumn[0]; for (int i = 1; i < listColumn.Length; ++i) { query += ", " + listColumn[i]; }query += ")" + " VALUES ('" + listKeyWord[0] + "'"; for (int i = 1; i < listKeyWord.Length; ++i) { query += ", '" + listKeyWord[i] + "'"; } query += ")"; string cmdStr = string.Format(query); if (listKeyWord != null) { MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn); try { cmd.ExecuteNonQuery(); cmd.Dispose(); sqlConn.Close(); } catch (Exception e) { Console.WriteLine("[DataMgr]Register " + e.Message); } } } public void Inst(string tablename,string coumn,string values) { string str = "INSERT INTO "+tablename+"("+coumn+")"+" VALUES( '"+values+"' )"; string cmdstr = string.Format(str); MySqlCommand cmd = new MySqlCommand(cmdstr,sqlConn); try { cmd.ExecuteNonQuery(); } catch(Exception e) { Console.WriteLine("[DataMgre]Register" +e.Message); } } //修改数据 /// <summary> ///修改数据方法 /// </summary> ///<param name = "TableName" >表名</ param > ///<param name = "Column" >字段数组列表</ param > ///<param name = "Columnvalues" >添加的值</ param > ///<param name = "KeyWord" >主键字段名</ param > ///<param name = "Keyvalue" >主键字段值</ param > public void UpdateValue(string TableName, string[] listColumn, string[] Columnvalues, string KeyWord,string Keyvalue) { string query = "UPDATE " + TableName + " SET " + listColumn[0] + " = " +"'"+ Columnvalues[0]+"'"; for (int i = 1; i < Columnvalues.Length; ++i) { query += ", " + listColumn[i] + " =" + "'"+Columnvalues[i]+"'"; } query += " WHERE " + KeyWord + " = '" + Keyvalue + " '"; string cmdStr = string.Format(query); MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn); try { cmd.ExecuteNonQuery(); // return true; } catch (Exception e) { Console.WriteLine("[DataMgr]Register " + e.Message); // return false; } } //保存数据 /// <summary> ///保存数据方法 /// </summary> ///<param name = "TableName" >表名</ param > ///<param name = "RootNode" >XML父节点</ param > ///<param name = "ChildNode" >XML子节点</ param > public void setxml(string TableName,string RootNode,string ChildNode) { String strFileName = "E:/MySql_XML/XML/out.xml";//保存XML文件路径 string cmdStr = string.Format("select * from "+ TableName + ";"); MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn); DataSet _DataSet1 = new DataSet(RootNode); MySqlDataAdapter _MySqlDataAdapter1 = new MySqlDataAdapter(cmd); _MySqlDataAdapter1.Fill(_DataSet1, ChildNode); FileStream myFs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write); _DataSet1.WriteXml(myFs); myFs.Close(); } //删除数据 /// <summary> ///查询数据方法 /// </summary> ///<param name = "TableName" >表名</ param > ///<param name = "cols" >字段数组列表</ param > ///<param name = "colsvalues" >添加的值</ param > public void DeleteDate(string TableName, string[] listColumn, string[] listColumnvalues) { string query = "DELETE FROM " + TableName + " WHERE " + listColumn[0] + " = " +"'"+ listColumnvalues[0]+"'"; for (int i = 1; i < listColumnvalues.Length; ++i) { query += " or " + listColumn[i] + " = " +"'"+ listColumnvalues[i]+"'"; } string cmdStr = string.Format(query); MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn); try { cmd.ExecuteNonQuery(); // return true; } catch (Exception e) { Console.WriteLine("[DataMgr]Register " + e.Message); // return false; } }
public void DeleteAllDate(string TableName, string key, string value) { string query = "DELETE FROM " + TableName + " WHERE " + key + " != " + "'" + value + "'"; string cmdStr = string.Format(query); MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn); try { cmd.ExecuteNonQuery(); // return true; } catch (Exception e) { Console.WriteLine("[DataMgr]Register " + e.Message); // return false; } }
void IDisposable.Dispose() { sqlConn.Close(); sqlConn.Dispose(); } }
需要用到两个dll
MySql.Data和System.Data的dll文件
连接的mysql
