C# MySqlCommand.Dispose方法代码示例

 public static string getFirstRow(string strSql)
 {
     MySqlConnection connection = Getconn();
     if (connection == null) throw new ArgumentNullException("connection");
     connection.Open();
     MySqlCommand cmd = new MySqlCommand(strSql, connection);
     MySqlDataAdapter da = new MySqlDataAdapter(cmd);
     DataSet ds = new DataSet();
     da.Fill(ds);
     DataTable dt = ds.Tables[0];
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         if (dt.Rows[i][0] != null)
         {
             da.Dispose();
             cmd.Dispose();
             connection.Close();
             return dt.Rows[i][0].ToString();
         }
     }
     da.Dispose();
     cmd.Dispose();
     connection.Close();
     return null;
 }

        /// <summary>
        /// 一个公用interface执行所有非 select语句
        /// </summary>
        /// <param name="non_query_type"></param>
        /// <returns>返回影响数据库几行</returns>
        public int GeneralNonSelectQuery(MySqlCommand cmd)
        {
            int iReturn = 0;
            string connStr = sql.GetSQL(sql.SQL.S_CONNECTION_STR);
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                iReturn = cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return iReturn;
        }

        public bool trylogin(string username, string passwort)
        {
            MySqlConnection con = new MySqlConnection("host=5.135.97.241;user=lesen;password=test234;database=test;");
            MySqlCommand cmd = new MySqlCommand("select * FROM login WHERE user = '" + username + "' AND pass = '" + passwort + "' AND datum >= CURRENT_DATE() - INTERVAL 0 DAY ;");
            cmd.Connection = con;
            con.Open();

            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read() != false)
            {

                if (reader.IsDBNull(0) == true)
                {
                    cmd.Connection.Close();
                    reader.Dispose();
                    cmd.Dispose();
                    return false;

                }
                else
                {
                    cmd.Connection.Close();
                    reader.Dispose();
                    cmd.Dispose();
                    return true;

                }
            }
            else
            {
                return false;
            }
        }

        public ArrayList ObtenerDatos()
        {
            this.IniciarConeccion();
            MySqlCommand Comando = new MySqlCommand(this.Query(),this.Conectado);

            MySqlDataReader LeerSQL = Comando.ExecuteReader();

            ArrayList DatosTabla = new ArrayList();

            while (LeerSQL.Read())
            {
                Datos datos = new Datos();
                datos.nombre = LeerSQL["Nombre"].ToString();
                datos.apellido = LeerSQL["Apellido"].ToString();
                datos.domicilio = LeerSQL["Domicilio"].ToString();
                datos.fecha = LeerSQL["Fecha"].ToString();

                DatosTabla.Add(datos);
            }
            Comando.Dispose();
            Comando = null;

            LeerSQL.Close();
            LeerSQL = null;

            this.CerrarConeccion();

            return DatosTabla;
        }

 public bool delete(string pImageID)
 {
     try
     {
         MySqlCommand _delete = new MySqlCommand("call spDeleteImage('" + pImageID + "')", GlobalVariables.goMySqlConnection);
         try
         {
             int _rowsAffected = _delete.ExecuteNonQuery();
             if (_rowsAffected > 0)
                 return true;
             return false;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             _delete.Dispose();
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public int ExecuteSql(string SQLString)
 {
     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {
         using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
         {
             try
             {
                 connection.Open();
                 int rows = cmd.ExecuteNonQuery();
                 return rows;
             }
             catch (MySql.Data.MySqlClient.MySqlException e)
             {
                 connection.Close();
                 throw new Exception(e.Message);
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
 }

        public static int GetNumberOfRowsInATable()
        {
            int numberOfRows = 0;
            try
            {
                string mySqlConnectionString = MakeMySqlConnectionString();
                var conn = new MySqlConnection {ConnectionString = mySqlConnectionString};

                using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM " + Program.selectedEventName, conn))
                {
                    conn.Open();
                    numberOfRows = int.Parse(cmd.ExecuteScalar().ToString());
                    conn.Close();
                    cmd.Dispose();
                    return numberOfRows;
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error Code: " + ex.ErrorCode);
                Console.WriteLine(ex.Message);
                ConsoleWindow.WriteLine("Error Code: " + ex.ErrorCode);
                ConsoleWindow.WriteLine(ex.Message);
            }
            return numberOfRows;
        }

 public Login1 CheckExternalEmail(String Email)
 {
     MySqlCommand cmd = new MySqlCommand("CheckExternalEmail", connection);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@MEmail", Email);
     MySqlDataReader reader = cmd.ExecuteReader();
     if (reader.HasRows)
     {
         Login1 login1 = new Login1();
         if (reader.Read())
         {
             login1.Id = reader.GetInt32("Id");
             login1.FirstName = reader.GetString("FirstName");
             login1.LastName = reader.GetString("LastName");
             login1.Email = reader.GetString("Email");
             login1.Password = reader.GetString("Password");
             login1.FavouriteGenre = reader.GetString("FavouriteGenre");
         }
         return login1;
     }
     else
     {
         reader.Dispose();
         cmd.Dispose();
         return null;
     }
 }

 protected MySqlDataReader executeSqlCommandDataReader(String sqlCommandStr, List<MySqlParameter> paramList)
 {
     MySqlConnection sqlCon = this.getMySqlConnection();
     try
     {
         sqlCon.Open();
         MySqlCommand sqlCommand = new MySqlCommand(sqlCommandStr, sqlCon);
         if (paramList != null && paramList.Count > 0)
         {
             foreach (MySqlParameter param in paramList)
             {
                 sqlCommand.Parameters.Add(param);
             }
         }
         MySqlDataReader sqlReader = sqlCommand.ExecuteReader();
         sqlCommand.Dispose();
         return sqlReader;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         sqlCon.Close();
         sqlCon.Dispose();
     }
 }

        /// <summary>
        /// guarda cualquier error producido en algun proceso del programa
        /// </summary>
        /// <param name="error">descripcion del error</param>
        /// <param name="sector">sector donde se produjo</param>
        /// <param name="id_user">uaurio a quien se le produjo (opcional)</param>
        public static void Set_Log_Error( string error, string sector, string id_user = null)
        {
            string fecha = null;
            fecha = Seguridad.FormatoFecha(DateTime.Now.Year.ToString()
                , DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString());

            try
            {
                Conexion conn = new Conexion();
                conn.IniciarConexion();
                if (conn == null)
                           return;

                if (id_user == null) id_user = "NULL";
                string mysql = "INSERT INTO log_ (id_user , error , sector , fecha) VALUES ('" + id_user + "','" + error + "','" + sector + "','" + fecha + "')";
                MySqlCommand cmd = new MySqlCommand(mysql, conn.GetConexion);
                MySqlDataReader read = cmd.ExecuteReader();
                if(read.RecordsAffected < 1)
                    TempLog.Add(error + "," + sector + "," + id_user + "," + fecha);
                cmd.Dispose();
                read.Dispose();
                conn.CerrarConexion();
            }
            catch
            {
                TempLog.Add(error + "," + sector + "," + id_user + "," + fecha);
            }
        }

 public ANS_GROUP_INVITE(String charName, WorldClient client)
     : base((UInt32)Opcodes.ANS_GROUP_INVITE)
 {
     MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM `characters` WHERE `name`= @name", WorldServer.Database.Connection.Instance);
     try
     {
         cmd.Prepare();
         cmd.Parameters.AddWithValue("@name", charName);
         Byte rows = Convert.ToByte(cmd.ExecuteScalar());
         if (client.Name == charName) WriteUInt32Reverse((uint)ResponseCodes.RC_GROUP_INVITE_SELF);
         else if (client.Name != charName)
         {
             if (rows < 1) WriteUInt32Reverse((uint)ResponseCodes.RC_GROUP_INVITE_NOT_FOUND);
             else if (rows >= 1)
             {
                 WriteUInt32((uint)ResponseCodes.RC_SUCCESS);
                 WriteParsedString(charName);
             }
         }
     }
     finally
     {
         cmd.Dispose();
     }
 }

        //创建军团 成功返回军团id，失败返回-1
        public static int CreateLegion(LegionInfo info)
        {
            MySqlCommand command;
            info.name = Coding.GB2312ToLatin1(info.name);
            info.notice = Coding.GB2312ToLatin1(info.notice);
            String leader_name = Coding.GB2312ToLatin1(info.leader_name);
            String sql = String.Format(MysqlString.CREATE_LEGION, info.name, info.title, info.leader_id, leader_name, info.money, info.notice);
            String utf_sql = sql;
            command = new MySqlCommand(utf_sql, MysqlConn.GetConn());
            MysqlConn.Conn_Open();
            command.ExecuteNonQuery();
            MysqlConn.Conn_Close();
            command.Dispose();

            //取主键-- 不能用于多线程或者多个程序操作该数据库。。切记
            String _key = "select max(id) from cq_legion";
            command = new MySqlCommand(_key, MysqlConn.GetConn());
            MysqlConn.Conn_Open();
            MySqlDataReader reader = command.ExecuteReader();
            int ret = -1;
            reader.Read();
            if (reader.HasRows)
            {
                ret =Convert.ToInt32(reader[0].ToString());
            }
            MysqlConn.Conn_Close();
            command.Dispose();
            return ret;
        }

 private void GetItemsDataSet(object mangosDB)
 {
     string SQL = "SELECT entry,name FROM item_template;";
     MyReceiverInvoke mi = new MyReceiverInvoke(invokeDataGridView);
     MySqlConnection Conn = new MySqlConnection(sManager.GetConnStr());
     MySqlCommand chgDB = new MySqlCommand("USE " + mangosDB.ToString() + ";", Conn);
     MySqlCommand setname = new MySqlCommand("set names 'gbk';", Conn);
     MySqlDataAdapter adp = new MySqlDataAdapter(SQL, Conn);
     try
     {
         this.Invoke(mi, new object[] { 1, "正在读取物品信息..." });
         Conn.Open();
         setname.ExecuteNonQuery();
         setname.Dispose();
         chgDB.ExecuteNonQuery();
         chgDB.Dispose();
         ItemsSet = new DataSet();
         adp.Fill(ItemsSet);
         adp.Dispose();
         if (ItemsSet.Tables[0].Rows.Count == 0)
         {
             throw new Exception("没有找到任何物品！");
         }
         this.Invoke(mi, new object[] { 2, string.Empty });
     }
     catch (Exception err)
     {
         this.Invoke(mi, new object[] { 0, err.Message });
     }
     finally
     {
         Conn.Close();
     }
 }

        /// <summary>
        /// 创建账户
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public static Account CreateAccount(Account account)
        {

            // 检查邮箱是否有重复
            var existEmailAccounts = SearchAccountsByEmail(account.Email);
            if(existEmailAccounts.Any())
                throw new Exception("邮箱已经被注册过了，请换一个邮箱注册");
            var existNameAccounts = SearchAccountsByName(account.Name);
            if(existNameAccounts.Any())
                throw new Exception("用户名已经被注册过了，请换一个用户名注册");

            string query = "INSERT INTO account(Name,Email,Password,IsActive,EnumDataEntityStatus) values('" +
                           account.Name + "','" + account.Email + "','" + account.Password + "'," + account.IsActive +
                           "," + (int)account.EnumDataEntityStatus + ")";

            MySqlCommand myCommand = new MySqlCommand(query, MyConnection);
            MyConnection.Open();
            int exeCount = myCommand.ExecuteNonQuery();
            try
            {
                if (exeCount > 0)
                {
                    return account;
                }
            }
            finally
            {
                myCommand.Dispose();
                MyConnection.Close();
            }
            return null;
        }

 public static void DisposeMysql(MySqlCommand obj)
 {
     if (obj != null)
     {
         obj.Dispose();
     }
 }