时间：2021-07-01 10:21:17 帮助过：5人阅读
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySQLDriverCS;
namespace mysql1
{
public partial class Form1 : Form
{
publicForm1()
{
InitializeComponent();
}
privatevoid Form1_Load(objectsender, EventArgs e)
{
MySQLConnectionconn = null;
conn = newMySQLConnection(newMySQLConnectionString("localhost", "test","root", "123456").AsString);
conn.Open();
//MySQLCommandcommn = new MySQLCommand("set names gb2312", conn);
//commn.ExecuteNonQuery();
stringsql = "select * from gw_test ";
MySQLDataAdaptermda = new MySQLDataAdapter(sql,conn);
DataSetds = new DataSet();
mda.Fill(ds, "table1");
this.dataGrid1.DataSource= ds.Tables["table1"];
conn.Close();
}
}
}
下面简绍的本人写好的操作mysql的类.里面对数据库的连接, 数据的增删改查做了封装.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySQLDriverCS;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;
using System.Collections;
/**
* 下面的DbServer类对 C#操作mysql数据库做了一个简化
封装了 操作数据库最常用的增删改查操作
微信订阅号 next_space 关注会有更多的资源
*/
namespace shiyan4
{
class DbServer
{
private string dbname;
private string dbhost;
private string dbuser;
private string dbpwd;
private string dbtype="mysql";
MySQLConnection conn = null;
MySQLCommand command;
private bool isConnect;
public DbServer()
{
}
//创建数据库驱动类 dbhost 主机地址 dbname 数据库名 dbuser 用户名 dbpwd密码
public DbServer(string dbhost, string dbname, string dbuser, string dbpwd)
{
this.dbhost = dbhost; this.dbname = dbname;
this.dbpwd = dbpwd; this.dbuser = dbuser;
this.isConnect = false;
}
~DbServer()
{
conn.Close();
}
//连接数据库
public bool connect()
{
conn = new MySQLConnection(new MySQLConnectionString(dbhost, dbname,dbuser, dbpwd).AsString);
try
{ conn.Open();}
catch (Exception ex)
{
MessageBox.Show("数据库连接失败");//MessageBox.Show(ex.Message);
return false;
}
return true;
}
//从数据库中读取记录 sql 要执行的语句
public DataTable getDataTable(string tableName, string con, string fields = "")
{
if (fields == "")
fields = "*";
string sql = string.Format("select {0} from {1} where {2};", fields, tableName, con);
MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);
// DataSet ds = new DataSet(); mda.Fill(ds, "table1");
DataTable dt = new DataTable();
mda.Fill(dt);
return dt;
}
//删除数据 table 表名 condition 条件
public bool delDate(string table,string condition)
{
string str = string.Format("delete from {0} where {1}", table, condition);
int res = exceSql(str);
//MessageBox.Show(res + "");
if (res == -1)
return false;
return true;
}
//添加数据 table表名 r 要添加的数据
public bool addData(string tableName,Row r)
{
ArrayList list=r.getList();
IEnumerator enumerator = list.GetEnumerator();
StringBuilder fields = new StringBuilder("(");
StringBuilder data = new StringBuilder("(");
while (enumerator.MoveNext())
{
RowItem it=(RowItem) enumerator.Current;
string filedname = it.getFieldName();
string value = it.getValue();
//fields += "'" + filedname + "'" + ",";
fields.AppendFormat("`{0}`,", filedname);
data.AppendFormat("'{0}',", value);
}
fields.Replace(',', ')', fields.Length - 1,1);
data.Replace(',', ')', data.Length - 1, 1);
//MessageBox.Show(fields.ToString()+" "+data.ToString());
string sqlstr = string.Format("INSERT INTO {0} {1} VALUES{2}", tableName,fields.ToString(), data.ToString());
int res = exceSql(sqlstr);
if (res == -1)
return false;
return true;
}
//根据条件查找数据 table 表名 con 条件 fields 待查询的字段
public ArrayList findData(string tableName,string con,string fields="")
{
if (fields == "")
fields = "*";
string sql = string.Format("select {0} from {1} where {2};",fields, tableName, con);
// MessageBox.Show(sql);
MySQLCommand cmd = new MySQLCommand(sql, conn);
command = new MySQLCommand("", conn);
command.CommandText = sql;
MySQLDataReader reader = command.ExecuteReaderEx();
string str = "0";
int length = 0;
int fieldNum = reader.FieldCount;
ArrayList rows = new ArrayList();
while (reader.Read())
{
ArrayList row = new ArrayList();
for (int i = 0; i < fieldNum;i++ )
{
row.Add(reader.GetString(i));
}
rows.Add(row);
length++;
}
reader.Close();
cmd.Dispose();
return rows;
}
//更新数据 table 表名 r 新的数据 con 条件
public bool updateData(string tableName,Row r,string con)
{
ArrayList list = r.getList();
IEnumerator enumerator = list.GetEnumerator();
StringBuilder fields = new StringBuilder();
while (enumerator.MoveNext())
{
RowItem it = (RowItem)enumerator.Current;
string filedname = it.getFieldName();
string value = it.getValue();
fields.AppendFormat("{0}='{1}',", filedname,value);
}
fields.Replace(',', ' ', fields.Length - 1, 1);
string sql = string.Format("update {0} set {1} where {2};", tableName,fields.ToString(),con);
// MessageBox.Show(sql);
int res = exceSql(sql);
if (res == -1)
return false;
return true;
}
//直接执行sql命令 返回受影响的行数
public int exceSql(string sql)
{
command = new MySQLCommand("", conn);
command.CommandText = sql;
int res;
try
{
res = command.ExecuteNonQuery();
//返回结果为受影响行数
// MessageBox.Show(res + "");
}
catch (System.Exception ex)
{
MessageBox.Show("执行命令失败:" + ex.Message);
return -1;
}
finally
{
command.Dispose();
}
return res;
}
//创建表
}
//单个字段
class RowItem
{
string fieldName;
string value;
public RowItem(string fieldName, string value)
{
this.fieldName = fieldName;
this.value = value;
}
public string getFieldName()
{
return fieldName;
}
public string getValue()
{
return value;
}
}
//一行数据
class Row
{
ArrayList list;
public Row()
{
list = new ArrayList();
}
//添加一个 键值对
public void addRowItem(string fieldName,string value)
{
RowItem it = new RowItem(fieldName, value);
list.Add(it);
}
public ArrayList getList()
{
return list;
}
}
}
/**
db = new DbServer("localhost", "test", "root", "123456");
db.connect();
string sql = "select * from gw_test ";
DataTable dt = db.getDataTable(sql);
this.dataGrid1.DataSource = dt;
Row r = new Row();
r.addRowItem(new RowItem("gw1", "tes1t"));
r.addRowItem(new RowItem("gw2", "1111"));
//if (db.addData("gw_test", r))
{
// MessageBox.Show("添加成功");
}
Row newdata=new Row();
newdata.addRowItem(new RowItem("gw2","55555555"));
if (db.updateData("gw_test", newdata, "gw2='222'"))
{
MessageBox.Show("更新成功");
};
if(db.delDate("gw_test","gw1= 'tes1t'"))
{
MessageBox.Show("删除成功");
}
*/using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySQLDriverCS;
using System.Collections;
//DbSercer示例程序
namespace shiyan4
{
public partial class Form1 : Form
{
DbServer db;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
db = new DbServer("localhost", "test", "root", "123456");
db.connect();
DataTable dt=db.getDataTable("student", "1=1");
this.dataGrid1.DataSource = dt;
}
private void btn_add_Click(object sender, EventArgs e)
{
Row r = new Row();
r.addRowItem("sno", "122055905");
r.addRowItem("name", "1111");
r.addRowItem("cid", "1220551");
r.addRowItem("enteryear", "2014");
if (db.addData("student", r))
{
MessageBox.Show("添加成功");
}
}
private void btn_del_Click(object sender, EventArgs e)
{
if (db.delDate("student", "1=1"))
{
MessageBox.Show("删除成功");
}
}
private void btn_find_Click(object sender, EventArgs e)
{
ArrayList datas=db.findData("student", "1=1");
string result = "";
foreach (ArrayList o in datas)
{
foreach (string oo in o)
{
result += oo+" ";
}
result += "\n";
}
MessageBox.Show(result);
}
//
private void btn_update_Click(object sender, EventArgs e)
{
Row newdata = new Row();
newdata.addRowItem("name", "gw");
if (db.updateData("student", newdata, "sno='122055905'"))
{
MessageBox.Show("更新成功");
};
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void add_Click(object sender, EventArgs e)
{
string id = this.tb_no.Text;
string name = this.tb_name.Text;
string cid = this.tb_cid.Text;
MessageBox.Show(id + name + cid);
;
Row r = new Row();
r.addRowItem("sno", id);
r.addRowItem("name", name);
r.addRowItem("cid", cid);
r.addRowItem("enteryear", "2014");
if (db.addData("student", r))
{
MessageBox.Show("添加成功");
}
DataTable dt = db.getDataTable("student", "1=1");
this.dataGrid1.DataSource = dt;
}
}
}
/**
db = new DbServer("localhost", "test", "root", "123456");
db.connect();
string sql = "select * from gw_test ";
DataTable dt = db.getDataTable(sql);
this.dataGrid1.DataSource = dt;
Row r = new Row();
r.addRowItem(new RowItem("gw1", "tes1t"));
r.addRowItem(new RowItem("gw2", "1111"));
//if (db.addData("gw_test", r))
{
// MessageBox.Show("添加成功");
}
Row newdata=new Row();
newdata.addRowItem(new RowItem("gw2","55555555"));
if (db.updateData("gw_test", newdata, "gw2='222'"))
{
MessageBox.Show("更新成功");
};
if(db.delDate("gw_test","gw1= 'tes1t'"))
{
MessageBox.Show("删除成功");
}
*/
C#连接mysql数据库
标签：c# mysql
