网上C#连接Mysql的例子很多,上传就是方便以后自己用。
1.数据库连接
// 数据库连接
protected MySqlConnection conn()
{
String database = "mysql";
String Source = "10.225.30.159";
String user = "glj";
String password = "glj";
String ConnectString = "Database='" + database + "';Data Source='" + Source + "';User Id='" + user + "';Password='" + password + "';charset=utf8";
MySqlConnection dbconn = new MySqlConnection(ConnectString);
try
{
if (dbconn.State == ConnectionState.Broken)
{
dbconn.Close();//与数据源连接中断,可先关闭,然后重新开启
dbconn.Open();
logWriter.writeLog("\\logFiles\\sucWriteFilesInfo.log", "数据库连接成功");
Console.WriteLine("数据库连接成功");//若连接成功则打印提示
}
else if (dbconn.State == ConnectionState.Closed)
{
dbconn.Open();
logWriter.writeLog("\\logFiles\\sucWriteFilesInfo.log", "数据库连接成功");
Console.WriteLine("数据库连接成功");//若连接成功则打印提示
}
}
catch (Exception ex)
{
string m = ex.Message;
Console.WriteLine("数据库连接失败");//若连接成功则打印提示
logWriter.writeLog("\\logFiles\\sucWriteFilesInfo.log", "数据库连接失败" + ex.Message);
}
return dbconn;
}
2.数据库插入
//插入数据
public void insertData(Entity entity, bool IsTureTb)
{
MySqlConnection dbconn = conn();
MySqlCommand selectCommand = dbconn.CreateCommand();
StringBuilder buffer = new StringBuilder();
if (IsTureTb)
buffer.Append("insert into 中国学术期刊网络出版总库 values('");
else
buffer.Append("insert into 中国学术期刊网络出版错误总库 values('");
buffer.Append(entity.Pian_ming.Replace("'", "''")).Append("','");
buffer.Append(entity.Yw_pian_ming.Replace("'", "''")).Append("','");
buffer.Append(entity.Zuo_zhe.Replace("'", "''")).Append("','");
buffer.Append(entity.Yw_zuo_zhe.Replace("'", "''")).Append("','");
buffer.Append(entity.Ji_gou.Replace("'", "''")).Append("','");
buffer.Append(entity.Zw_zhai_yao.Replace("'", "''")).Append("','");
buffer.Append(entity.Yw_zhai_yao.Replace("'", "''")).Append("','");
buffer.Append(entity.Zw_guan_jian_ci.Replace("'", "''")).Append("','");
buffer.Append(entity.Yw_guan_jian_ci.Replace("'", "''")).Append("','");
buffer.Append(entity.Zw_kan_ming.Replace("'", "''")).Append("','");
buffer.Append(entity.Yw_kan_ming.Replace("'", "''")).Append("','");
buffer.Append(entity.Nian).Append("','");
buffer.Append(entity.Qi).Append("','");
buffer.Append(entity.Wen_jian_ming).Append("','");
buffer.Append(entity.Guang_pan_hao).Append("',");
buffer.Append("null,'");
if (!IsTureTb)
{
buffer.Append(entity.Fj_lu_jing.Replace("\\", "\\\\")).Append("'");
buffer.Append(",");
buffer.Append(entity.Xz_ci_shu).Append(",");
buffer.Append(entity.Sf_you_xiao);
}
else
{
buffer.Append(entity.Fj_lu_jing).Append("'");
}
buffer.Append(")");
MySqlTransaction tx = dbconn.BeginTransaction();
selectCommand.Transaction = tx;
int insertRowCount = 0;
try
{
selectCommand.CommandText = buffer.ToString();
insertRowCount = selectCommand.ExecuteNonQuery();
Console.WriteLine("尝试插入数据, {0}条存储成功。", insertRowCount);
LogWriter.writeLog("sucWriteFilesInfo.log", "尝试插入数据," + insertRowCount + "条存储成功。");
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
Console.WriteLine("尝试插入数据, 结果造成失败。", insertRowCount);
LogWriter.writeLog("sucWriteFilesInfo.log", e.Message);
LogWriter.writeLog("dataErrorInfo.log", e.Message + "\n\n" + buffer.ToString() + "\n\n" + returnDataError(entity));
}
finally
{
CloseCon(dbconn);
}
}
3.数据库修改
//更新错误表数据
public int updateData(Entity entity)
{
MySqlConnection dbconn = conn();
MySqlCommand updateCommand = dbconn.CreateCommand();
StringBuilder buffer = new StringBuilder();
buffer.Append("update 中国学术期刊网络出版错误总库 set 下载次数=");
buffer.Append(entity.Xz_ci_shu).Append(",是否有效=").Append(entity.Sf_you_xiao)
.Append(" where 篇名='").Append(entity.Pian_ming.Replace("'", "''")).Append("'");
MySqlTransaction tx = dbconn.BeginTransaction();
updateCommand.Transaction = tx;
int insertRowCount = 0;
try
{
updateCommand.CommandText = buffer.ToString();
insertRowCount = updateCommand.ExecuteNonQuery();
LogWriter.writeLog("sucWriteFilesInfo.log", "尝试更新数据," + insertRowCount + "条更新成功。");
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
LogWriter.writeLog("sucWriteFilesInfo.log", e.Message);
LogWriter.writeLog("dataErrorInfo.log", e.Message + "\n\n" + buffer.ToString() + "\n\n" + returnDataError(entity));
}
finally
{
CloseCon(dbconn);
}
return insertRowCount;
}
4.删除数据
//删除数据
public int deleteData(Entity entity, bool isTrueTb)
{
MySqlConnection dbconn = conn();
MySqlCommand deleteCommand = dbconn.CreateCommand();
StringBuilder buffer = new StringBuilder();
if (!isTrueTb)
buffer.Append("delete from 中国学术期刊网络出版错误总库 where 篇名='");
else
buffer.Append("delete from 中国学术期刊网络出版总库 where 篇名='");
buffer.Append(entity.Pian_ming.Replace("'", "''")).Append("' and 文件名='").Append(entity.Wen_jian_ming).Append("'");
MySqlTransaction tx = dbconn.BeginTransaction();
deleteCommand.Transaction = tx;
int insertRowCount = 0;
try
{
deleteCommand.CommandText = buffer.ToString();
insertRowCount = deleteCommand.ExecuteNonQuery();
//Console.WriteLine("尝试删除数据, {0}条删除成功。" + entity.Pian_ming, insertRowCount);
LogWriter.writeLog("sucWriteFilesInfo.log", "尝试删除数据," + insertRowCount + "条删除成功");
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
//Console.WriteLine("尝试删除更新数据, 结果造成失败。", insertRowCount);
LogWriter.writeLog("sucWriteFilesInfo.log", e.Message);
LogWriter.writeLog("dataErrorInfo.log", e.Message + "\n\n" + buffer.ToString() + "\n\n" + returnDataError(entity));
}
finally
{
CloseCon(dbconn);
}
return insertRowCount;
}
5.查询数据
//查询错误表数据
public List<Entity> selectData()
{
List<Entity> list = new List<Entity>();
MySqlConnection dbconn = conn();
MySqlCommand testCommand = dbconn.CreateCommand(); // 创建一个 Command.
StringBuilder sql = new StringBuilder("select 篇名 from 中国学术期刊网络出版错误总库 ").Append("where 是否有效 =0 ")
.Append("order by 插入时间 ");
// 定义需要执行的SQL语句.
testCommand.CommandText = sql.ToString();
// 执行SQL命令,结果存储到Reader中
MySqlDataReader testReader = testCommand.ExecuteReader();
try
{
// 处理检索出来的每一条数据.
while (testReader.Read())
{
Entity entity = new Entity();
if (null!=testReader["篇名"])
list.Add(entity);
}
}catch(Exception e){
Console.WriteLine("查询数据失败");
LogWriter.writeLog("sucWriteFilesInfo.log", e.Message);
}finally{
CloseCon(dbconn);
testReader.Close();
}
return list;
}