C# 连接操作MYSQL 数据库

C# 连接操作MYSQL 数据库_02

  1 using MySql.Data.MySqlclient;        // 引进MYSQL 库
  2 
  3 // C# 连接 MYSQL 数据库更好的方法
  4 
  5 void func_02()
  6 {
  7     string str_conn_info = "";
  8     str_conn_info += " Host       = " + db_ip + ";";
  9     str_conn_info += " Database   = " + db_ip + ";";
 10     str_conn_info += " User       = " + db_ip + ";";
 11     str_conn_info += " password   = " + db_ip + ";";
 12     str_conn_info += " port       = " + db_ip + ";";
 13     str_conn_info += " Charset = utf8";
 14 
 15     MySqlCommand sql_cmd = new MySqlCommand();
 16     sql_cmd.Connection = new MySqlConnection();
 17 
 18     sql_cmd.Connection.ConnectionString = str_conn_info;
 19 
 20     if (!connect_to_mysql(sql_cmd.Connection))
 21     {
 22         // 连接失败
 23         return;
 24     }
 25 
 26     try
 27     {
 28         // 添加SQL 语句，并运行
 29         sql_cmd.CommandText = "SELECT mail_id, mail_src_id, first_read_time, get_items_time, del_mail_time"
 30             + " FROM log_mail WHERE player_id = 1";
 31 
 32         // 运行的结果集放到MySqlDataReader 中
 33         MySqlDataReader data_reader = sql_cmd.ExecuteReader();
 34 
 35         // 从结果集中取数据
 36         try
 37         {
 38             while (data_reader.Read())
 39             {
 40                 PlayerMailRecordForm.struMailRecord mail_record = new PlayerMailRecordForm.struMailRecord();
 41                 mail_record.mail_id = data_reader.GetUInt32("mail_id");
 42                 mail_record.mail_src_id = data_reader.GetUInt32("mail_src_id");
 43                 mail_record.str_first_read = data_reader.GetString("first_read_time");
 44                 if (data_reader["get_items_time"] != DBNull.Value)    // 如果该字段 IS NOT NULL
 45                     mail_record.str_get_items = data_reader.GetString("get_items_time");
 46                 if (data_reader["del_mail_time"] != DBNull.Value)
 47                     mail_record.str_del_mail = data_reader.GetString("del_mail_time");
 48             }
 49 
 50 
 51             // 关闭结果集，如果不关闭，下次运行SQL 语句时会出问题。
 52             data_reader.Close();
 53         }
 54         catch (Exception ex)
 55         {
 56             MessageBox.Show(ex.Message);
 57 
 58             // 关闭结果集，如果不关闭，下次运行SQL 语句时会出问题。
 59             data_reader.Close();
 60             return;
 61         }
 62     }
 63     catch (System.Exception ex)
 64     {
 65         MessageBox.Show(ex.Message);
 66         disconnect_from_mysql(sql_cmd.Connection);
 67         return;
 68     }
 69 
 70     // 关闭连接
 71     disconnect_from_mysql(sql_cmd.Connection);
 72     
 73     // 这里没有找到对于MySqlCommand 释放的方法，感觉应该是没有，不过也没关系了，这里整个对象都回收了，应该是OK的。
 74 }
 75 
 76 
 77 public bool connect_to_mysql(MySqlConnection conn_clt)
 78 {
 79     switch (conn_clt.State)
 80     {
 81         case ConnectionState.Open:
 82         case ConnectionState.Connecting:
 83         case ConnectionState.Executing:
 84         case ConnectionState.Fetching:
 85         case ConnectionState.Broken:
 86             break;
 87         case ConnectionState.Closed:
 88         default:
 89             try
 90             {
 91                 conn_clt.Open();
 92             }
 93             catch(Exception ex)
 94             {
 95                 MessageBox.Show(ex.Message);
 96                 return false;
 97             }
 98             break;
 99     }
100 
101     return true;
102 }
103 
104 public void disconnect_from_mysql(MySqlConnection conn_clt)
105 {
106     switch (conn_clt.State)
107     {
108         case ConnectionState.Open:
109         case ConnectionState.Connecting:
110         case ConnectionState.Executing:
111         case ConnectionState.Fetching:
112         case ConnectionState.Broken:
113             conn_clt.Close();
114             break;
115         case ConnectionState.Closed:
116         default:
117             break;
118     }
119 }