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

红太狼 2022-09-18 13:45 275阅读 0赞

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