C# 连接Mysq数据库及持久化方法操作

网上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;
        }