C#怎样链接mysql数据库
C#一般链接sqlserver数据库,当然也会链接oracle。C#和MYSQL搭配貌似不多见哦
下面说说方法。
1、下载链接库文件,MySql.Data.dll
2、工程引用文件,并在类文件中应用using
using MySql.Data; using MySql.Data.MySqlClient;
3、下来其他就和sqlserver差不多了。
4、配置文件中的链接字符串和链接变量
<appSettings> <add key="conn" value="Database='tdm';Data Source='localhost';User Id='root';Password='root';charset='utf8';pooling=true"/> </appSettings>
public static string Conn = System.Configuration.ConfigurationManager.AppSettings["conn"];
5、定义两个常用的数据库类方法,操作数据库和查询
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) {
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(Conn))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static DataSet GetDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(Conn);
try {
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
//清除参数
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception e ) { throw e; } }
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) {
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null) {
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
} }
6、使用方法,显示数据
private void button2_Click(object sender, EventArgs e) {
string sql="select * from deviceinfo";
try {
dataGridView1.DataSource = MySqlHelper.GetDataSet( CommandType.Text,sql,null).Tables[0]; } catch (Exception) {
this.Text = "数据库连接失败"; }
}
7、使用方法,对数据进行增删改
try {
string sql = "select * from deviceinfo";
DataTable dt = MySqlHelper.GetDataSet(CommandType.Text, sql, null).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = "update deviceinfo set devicestatus=" + fsMakeStatuts().ToString() + " where deviceid='" + dt.Rows[i]["deviceid"].ToString() + "'"; MySqlHelper.ExecuteNonQuery(CommandType.Text, sql, null);
}
} catch (Exception) {
this.Text = "err"; }
原文链接:C#怎样链接mysql数据库