c# 连接mysql数据库的3种方法及示例

《c# 连接mysql数据库的3种方法及示例》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

C#连接MYSQL数据库的方法及示例连接MYSQL数据库的方法及示例方法一：usingMySql.DatausingMySql.Data.MySqlClient;其他操作跟SQL是差不多,无非就是前缀变成MySql了.补充:下面是连接字符串,供参考.MySqlConnectioncon=newMySql.Data.MySqlClient.MySqlConnection("Database='testdb';DataSource='localhost';UserId='db';Password='apple';charset='utf8'");con.Open();MySqlCommandcmd=newMySqlCommand();cmd.Connection=con;使用MYSQL推出的MySQLConnector/NetisanADO.NETdriverforMySQL该组件为MYSQL为ADO.NET访问MYSQL数据库设计的.NET访问组件。安装完成该组件后，引用命名空间MySql.Data.MySqlClient;使用命令行编译时：csc/r:MySql.Data.dlltest.cs方法二：通过ODBC访问MYSQL数据库访问前要先下载两个组件：odbc.net和MYSQL的ODBC驱动(MySQLConnector/ODBC(MyODBC)driver)目前为3.51版安装完成后，即可通过ODBC访问MYSQL数据库方法三：使用CoreLab推出的MYSQL访问组件，面向.NET安装完成后，引用命名空间：CoreLab.MySql;使用命令编译时：csc/r:CoreLab.MySql.dlltest.cs以下为访问MYSQL数据库实例编译指令：csc/r:CoreLab.MySql.dll/r:MySql.Data.dlltest.csusingSystem;usingSystem.Net;usingSystem.Text;usingCoreLab.MySql;usingSystem.Data.Odbc;usingMySql.Data.MySqlClient;classConnectMySql{ publicvoidConnect_CoreLab(){stringconstr="UserId=root;Host=localhost;Database=qing;password=qing";MySqlConnectionmycn=newMySqlConnection(constr);mycn.Open();MySqlCommandmycm=newMySqlCommand("select*fromshop",mycn);MySqlDataReadermsdr=mycm.ExecuteReader();while(msdr.Read()){if(msdr.HasRows){Console.WriteLine(msdr.GetString(0));}}msdr.Close();mycn.Close();}publicvoidConnect_Odbc(){//stringMyConString="DSN=MySQL;UID=root;PWD=qing";stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=test;"+"UID=root;"+"PASSWORD=qing;"+"OPTION=3";OdbcConnectionMyConn=newOdbcConnection(MyConString);MyConn.Open();OdbcCommandmycm=newOdbcCommand("select*fromhello",MyConn);OdbcDataReadermsdr=mycm.ExecuteReader();while(msdr.Read()){if(msdr.HasRows){Console.WriteLine(msdr.GetString(0));}}msdr.Close();MyConn.Close();}publicvoidConnect_Net(){ stringmyConnectionString="Database=test;DataSource=localhost;UserId=root;Password=qing";MySqlConnectionmycn=newMySqlConnection(myConnectionString);mycn.Open();MySqlCommandmycm=newMySqlCommand("select*fromhello",mycn);MySqlDataReadermsdr=mycm.ExecuteReader();while(msdr.Read()){if(msdr.HasRows){Console.WriteLine(msdr.GetString(0));}}msdr.Close();mycn.Close();}publicstaticvoidMain(){ConnectMySqlms=newConnectMySql();ms.Connect_CoreLab();ms.Connect_Odbc();Connect_Net();}}1、用MySQLDriverCS连接MySQL数据库先下载和安装MySQLDriverCS，地址：http://sourceforge.net/projects/mysqldrivercs/在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exeusingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel; usingSystem.Data;usingSystem.Data.Odbc;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingMySQLDriverCS;namespacemysql{publicpartialclassForm1:Form{publicForm1(){InitializeComponent();}privatevoidForm1_Load(objectsender,EventArgse){MySQLConnectionconn=null; conn=newMySQLConnection(newMySQLConnectionString("localhost","inv","root","831025").AsString);conn.Open();MySQLCommandcommn=newMySQLCommand("setnamesgb2312",conn);commn.ExecuteNonQuery();stringsql="select*fromexchange";MySQLDataAdaptermda=newMySQLDataAdapter(sql,conn);DataSetds=newDataSet();mda.Fill(ds,"table1");this.dataGrid1.DataSource=ds.Tables["table1"];conn.Close();}}} 2、通过ODBC访问mysql数据库：参考：http://www.microsoft.com/china/community/Column/63.mspx1.安装MicrosoftODBC.net：我安装的是mysql-connector-odbc-3.51.22-win32.msi2.安装MDAC2.7或者更高版本：我安装的是mdac_typ.exe2.7简体中文版3.安装MySQL的ODBC驱动程序：我安装的是odbc_net.msi4.管理工具->数据源ODBC–>配置DSN…5.解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)6.代码中增加引用usingMicrosoft.Data.Odbc;usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Drawing;usingSystem.Linq;//vs2005好像没有这个命名空间，在c#2008下测试自动生成的usingSystem.Text;usingSystem.Windows.Forms;usingMicrosoft.Data.Odbc;namespacemysql{ publicpartialclassForm1:Form{publicForm1(){InitializeComponent();}privatevoidForm1_Load(objectsender,EventArgse){stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=inv;"+"UID=root;"+"PASSWORD=831025;"+"OPTION=3";OdbcConnectionMyConnection=newOdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(" success,connectedsuccessfully! ");stringquery="insertintotestvalues(''hello'',''lucas'',''liu'')";OdbcCommandcmd=newOdbcCommand(query,MyConnection); //处理异常：插入重复记录有异常try{cmd.ExecuteNonQuery();}catch(Exceptionex){Console.WriteLine("recordduplicate.");}finally{cmd.Dispose();}//***********************用read方法读数据到textbox**********************stringtmp1=null;stringtmp2=null;stringtmp3=null;query="select*fromtest";OdbcCommandcmd2=newOdbcCommand(query,MyConnection);OdbcDataReaderreader=cmd2.ExecuteReader();while(reader.Read()){tmp1=reader[0].ToString();tmp2=reader[1].ToString(); tmp3=reader[2].ToString();}this.textBox1.Text=tmp1+""+tmp2+""+tmp3;*///************************用datagridview控件显示数据表**************************stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=inv;"+"UID=root;"+"PASSWORD=831025;"+"OPTION=3";OdbcConnectionMyConnection=newOdbcConnection(MyConString);OdbcDataAdapteroda=newOdbcDataAdapter("select*fromcustomer",MyConnection);DataSetds=newDataSet();oda.Fill(ds,"employee");this.dataGridView1.DataSource=ds.Tables["employee"];*/MyConnection.Close();}}} 文章出处：http://www.diybl.com/course/4_webprogram/asp.net/asp_netshl/2008429/112011.html1、用MySQLDriverCS连接MySQL数据库先下载和安装MySQLDriverCS，地址：http://sourceforge.net/projects/mysqldrivercs/在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exeusingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Data.Odbc;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingMySQLDriverCS;namespacemysql{ publicpartialclassForm1:Form{publicForm1(){InitializeComponent();}privatevoidForm1_Load(objectsender,EventArgse){MySQLConnectionconn=null;conn=newMySQLConnection(newMySQLConnectionString("localhost","inv","root","831025").AsString);conn.Open();MySQLCommandcommn=newMySQLCommand("setnamesgb2312",conn);commn.ExecuteNonQuery();stringsql="select*fromexchange";MySQLDataAdaptermda=newMySQLDataAdapter(sql,conn);DataSetds=newDataSet();mda.Fill(ds,"table1"); this.dataGrid1.DataSource=ds.Tables["table1"];conn.Close();}}}2、通过ODBC访问mysql数据库：参考：http://www.microsoft.com/china/community/Column/63.mspx1.安装MicrosoftODBC.net：我安装的是mysql-connector-odbc-3.51.22-win32.msi2.安装MDAC2.7或者更高版本：我安装的是mdac_typ.exe2.7简体中文版3.安装MySQL的ODBC驱动程序：我安装的是odbc_net.msi4.管理工具->数据源ODBC–>配置DSN…5.解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)6.代码中增加引用usingMicrosoft.Data.Odbc;usingSystem; usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Drawing;usingSystem.Linq;//vs2005好像没有这个命名空间，在c#2008下测试自动生成的usingSystem.Text;usingSystem.Windows.Forms;usingMicrosoft.Data.Odbc;namespacemysql{publicpartialclassForm1:Form{publicForm1(){InitializeComponent();}privatevoidForm1_Load(objectsender,EventArgse){stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+ "DATABASE=inv;"+"UID=root;"+"PASSWORD=831025;"+"OPTION=3";OdbcConnectionMyConnection=newOdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(" success,connectedsuccessfully! ");stringquery="insertintotestvalues(''hello'',''lucas'',''liu'')";OdbcCommandcmd=newOdbcCommand(query,MyConnection);//处理异常：插入重复记录有异常try{cmd.ExecuteNonQuery();}catch(Exceptionex){Console.WriteLine("recordduplicate.");}finally{cmd.Dispose();}//***********************用read方法读数据到textbox********************** stringtmp1=null;stringtmp2=null;stringtmp3=null;query="select*fromtest";OdbcCommandcmd2=newOdbcCommand(query,MyConnection);OdbcDataReaderreader=cmd2.ExecuteReader();while(reader.Read()){tmp1=reader[0].ToString();tmp2=reader[1].ToString();tmp3=reader[2].ToString();}this.textBox1.Text=tmp1+""+tmp2+""+tmp3;*///************************用datagridview控件显示数据表**************************stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=inv;"+"UID=root;"+"PASSWORD=831025;"+"OPTION=3"; OdbcConnectionMyConnection=newOdbcConnection(MyConString);OdbcDataAdapteroda=newOdbcDataAdapter("select*fromcustomer",MyConnection);DataSetds=newDataSet();oda.Fill(ds,"employee");this.dataGridView1.DataSource=ds.Tables["employee"];*/MyConnection.Close();}}}文章出处：http://www.diybl.com/course/4_webprogram/asp.net/asp_netshl/2008429/112011.htmlC#连接mysql数据库 1.连接:1.安装MicrosoftODBC.net。2.安装MySQL的ODBC驱动程序。2.解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)3.代码中增加引用usingMicrosoft.Data.Odbc;4.编写代码stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=samp_db;"+"UID=root;"+"PASSWORD=;"+"OPTION=3";//ConnecttoMySQLusingConnector/ODBCOdbcConnectionMyConnection=newOdbcConnection(MyConString); MyConnection.Open();Console.WriteLine(" !!!success,connectedsuccessfully!!! ");MyConnection.Close();2.全部例程:/***@sample:mycon.cs*@purpose:DemosampleforODBC.NETusingConnector/ODBC*@author:Venu,venu@mysql.com**(C)CopyrightMySQLAB,1995-2003***//*buildcommand**csc/t:exe*/out:mycon.exemycon.cs*/r:Microsoft.Data.Odbc.dll*/usingConsole=System.Console;usingMicrosoft.Data.Odbc;namespacemyodbc3{classmycon{staticvoidMain(string[]args){try{//ConnectionstringforConnector/ODBC2.50/*stringMyConString="DRIVER={MySQL};"+"SERVER=localhost;"+"DATABASE=test;"+"UID=venu;"+"PASSWORD=venu;"+"OPTION=3";*///ConnectionstringforConnector/ODBC3.51stringMyConString="DRIVER={MySQLODBC3.51Driver};"+"SERVER=localhost;"+"DATABASE=test;" +"UID=venu;"+"PASSWORD=venu;"+"OPTION=3";//ConnecttoMySQLusingConnector/ODBCOdbcConnectionMyConnection=newOdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(" !!!success,connectedsuccessfully!!! ");//DisplayconnectioninformationConsole.WriteLine("ConnectionInformation:");Console.WriteLine("tConnectionString:"+MyConnection.ConnectionString);Console.WriteLine("tConnectionTimeout:"+MyConnection.ConnectionTimeout);Console.WriteLine("tDatabase:"+MyConnection.Database);Console.WriteLine("tDataSource:"+MyConnection.DataSource);Console.WriteLine("tDriver:"+MyConnection.Driver);Console.WriteLine("tServerVersion:"+MyConnection.ServerVersion);//CreateasampletableOdbcCommandMyCommand=newOdbcCommand("DROPTABLEIFEXISTSmy_odbc_net",MyConnection);MyCommand.ExecuteNonQuery();MyCommand.CommandText="CREATETABLEmy_odbc_net(idint,namevarchar(20),idbbigint)";MyCommand.ExecuteNonQuery();//InsertMyCommand.CommandText="INSERTINTOmy_odbc_netVALUES(10,'venu',300)";Console.WriteLine("INSERT,Totalrowsaffected:"+MyCommand.ExecuteNonQuery());;//InsertMyCommand.CommandText="INSERTINTOmy_odbc_netVALUES(20,'mysql',400)";Console.WriteLine("INSERT,Totalrowsaffected:"+MyCommand.ExecuteNonQuery());//InsertMyCommand.CommandText="INSERTINTOmy_odbc_netVALUES(20,'mysql',500)";Console.WriteLine("INSERT,Totalrowsaffected:"+MyCommand.ExecuteNonQuery());//UpdateMyCommand.CommandText="UPDATEmy_odbc_netSETid=999WHEREid=20";Console.WriteLine("Update,Totalrowsaffected:"+MyCommand.ExecuteNonQuery());//COUNT(*) MyCommand.CommandText="SELECTCOUNT(*)asTRowsFROMmy_odbc_net";Console.WriteLine("TotalRows:"+MyCommand.ExecuteScalar());//FetchMyCommand.CommandText="SELECT*FROMmy_odbc_net";OdbcDataReaderMyDataReader;MyDataReader=MyCommand.ExecuteReader();while(MyDataReader.Read()){if(string.Compare(MyConnection.Driver,"myodbc3.dll")==0){Console.WriteLine("Data:"+MyDataReader.GetInt32(0)+""+MyDataReader.GetString(1)+""+MyDataReader.GetInt64(2));//SupportedonlybyConnector/ODBC3.51}else{Console.WriteLine("Data:"+MyDataReader.GetInt32(0)+""+MyDataReader.GetString(1)+""+MyDataReader.GetInt32(2));//BIGINTsnotsupportedbyConnector/ODBC}}//CloseallresourcesMyDataReader.Close();MyConnection.Close();}catch(OdbcExceptionMyOdbcException)//CatchanyODBCexception..{for(inti=0;i0){PageCount+=1;}currentPage=1;recNo=0;LoadPage();这是前一段需要用到,精选了一些资料,希望对大家有帮助.