C# MySqlDataAdapter.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;
 }

 public DataTable getRolePrivileges(string pRoleName)
 {
     try
     {
         DataTable _dt = new DataTable();
         MySqlDataAdapter _adapter = new MySqlDataAdapter("call spGetRolePrivileges('" + pRoleName + "')", GlobalVariables.goMySqlConnection);
         try
         {
             _adapter.Fill(_dt);
             return _dt;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             _adapter.Dispose();
         }
     }
     catch (Exception ex)
     {
         throw new Exception("Error Message!\r\n" + ex.Message + "\r\nError in : " + this.ToString() + "." + ex.TargetSite + " - " + ex.StackTrace.Substring(ex.StackTrace.IndexOf("line")) + ".");
     }
 }

 public DataTable getTags(string pImageId)
 {
     try
     {
         DataTable _dt = new DataTable();
         MySqlDataAdapter _adapter = new MySqlDataAdapter("call spGetImageTags('" + pImageId + "')", GlobalVariables.goMySqlConnection);
         try
         {
             _adapter.Fill(_dt);
             return _dt;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             _adapter.Dispose();
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 protected DataSet executeSqlCommandDataSet(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);
             }
         }
         MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(sqlCommand);
         DataSet ds = new DataSet();
         sqlAdapter.Fill(ds);
         sqlAdapter.Dispose();
         return ds;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         sqlCon.Close();
         sqlCon.Dispose();
     }
 }

 public DataTable get()
 {
     DataTable _dt = new DataTable();
     try
     {
         MySqlDataAdapter _adapter = new MySqlDataAdapter("call spGetDocTypes()", GlobalVariables.goMySqlConnection);
         try
         {
             _adapter.Fill(_dt);
             return _dt;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             _adapter.Dispose();
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 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();
     }
 }

 public static DataTable GetDataTable(string query)
 {
     command = new MySqlCommand(query, connection);
     MySqlDataAdapter adapter = new MySqlDataAdapter();
     adapter.SelectCommand = command;
     DataTable dataTable = new DataTable();
     adapter.Fill(dataTable);
     adapter.Dispose();
     command.Dispose();
     return dataTable;
 }

        public string ConnectionTest()
        {
            s = ConfigurationManager.AppSettings["MySql"].ToString();
            MySqlConnection mysqlcon = new MySqlConnection(s);
            MySqlCommand mysqlcom = new MySqlCommand("", mysqlcon);
            MySqlDataAdapter mysqldataadp = new MySqlDataAdapter(mysqlcom);
            DataSet ds=new DataSet();
            string message = "";
            try
            {
                //mysqlcon.Open();
                if (mysqlcon.State != ConnectionState.Open)
                {
                    mysqlcon.Open();
                }
                mysqldataadp.SelectCommand.CommandText="select * from ow_articletype";
                message = "打开数据库成功！";
                mysqldataadp.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    message = ds.Tables[0].Rows[0][1].ToString();
                }
                else
                {
                    message = "可惜查不到数据";
                }
            }
            catch (Exception ex)
            {
                if(mysqlcon.State==ConnectionState.Open)
                {
                    mysqlcon.Close();
                    mysqlcon.Dispose();
                }
                message = "数据库访问失败！ 信息："+ex.Message;
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                }
                mysqlcon.Dispose();
                mysqlcom.Dispose();
                mysqldataadp.Dispose();
            }
            return message;
        }

        public DataTable ExecuteQuery(string sql, ITransaction transaction, Action<IParameterAdder> paramaterLoader)
        {
            Log.Info("Sql:" + sql);
            InvokeOnSqlExecute(new OnSqlExecuteDeligateArgs(sql));
            if (sql == null) throw new ArgumentNullException("sql");

            var mySqlTransactionWrapper = transaction as MySqlTransactionWrapper;
            var mySqlCommand = new MySqlCommand(sql, _sqlConnection, mySqlTransactionWrapper != null ? mySqlTransactionWrapper.Transaction : null);
            paramaterLoader(new MysqlParaAdder(mySqlCommand.Parameters));
            var sqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
            var dtResult = new DataTable();
            sqlDataAdapter.Fill(dtResult);
            sqlDataAdapter.Dispose();
            return dtResult;
        }

        /// <summary>
        ///  cambia la imagen del usuario con los parametros nombre de la nueva imagen 
        ///  el id del usuario a cambiar y la direccion de la imagen ya guardada para eliminar anterior
        /// </summary>
        /// <param name="imagen"></param>
        /// <param name="id_user"></param>
        /// <param name="server_path"></param>
        /// <returns> regresa el nombre de la imagen en caso todo salio bien , regresa null en caso de que hubo un problema</returns>
        public string CambiarImagen(string imagen , string id_user , string server_path)
        {
            try
            {
                string imagen_anterior = null;
                Conexion conn = new Conexion();
                conn.IniciarConexion();
                if (conn.GetConexion == null) return null;
                string sql = "select imagen from user where id_user like '" + id_user + "'";
                MySqlDataAdapter adaptador = new MySqlDataAdapter(sql, conn.GetConexion);
                DataSet ds = new DataSet();
                adaptador.Fill(ds);
                DataTable tabla = ds.Tables[0];
                foreach (DataRow rows in tabla.Rows)
                {
                    imagen_anterior = rows.Field<string>("imagen", DataRowVersion.Default);
                }
                adaptador.Dispose();
                ds.Dispose();
                tabla.Dispose();
                sql = "Update user set imagen = '" + imagen + "' where id_user like '" + id_user + "'";
                MySqlCommand cmd = new MySqlCommand(sql, conn.GetConexion);
                MySqlDataReader read = cmd.ExecuteReader();
                if (read.RecordsAffected >= 1)
                {
                    if (imagen_anterior != " NULL"
                        || imagen_anterior != "NULL"
                        || imagen_anterior != ""
                        || imagen_anterior != " ")
                    {

                        string ruta = System.IO.Path.Combine(server_path, imagen_anterior);
                        System.IO.File.Delete(ruta);
                    }
                    conn.CerrarConexion();
                    return imagen;
                }
                else
                {
                    conn.CerrarConexion();
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

        public DataSet SqlCommand(string sqlCommand)
        {
            ds = new DataSet();
            try
            {
                using (MySqlDataAdapter dataAdapter =
                new MySqlDataAdapter(sqlCommand, connectString))
                {
                    dataAdapter.Fill(ds, "list");
                    dataAdapter.Dispose();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }

            return ds;
        }

 public DataTable get()
 {
     try
     {
         string _sql = "call spGetEmployeeTypes()";
         loMySqlDataAdapter = new MySqlDataAdapter(_sql, GlobalVariables.goMySqlConnection);
         loDataTable = new DataTable("EmployeeTypes");
         loMySqlDataAdapter.Fill(loDataTable);
         return loDataTable;
     }
     catch (Exception)
     {
         throw;
     }
     finally
     {
         loMySqlDataAdapter.Dispose();
     }
 }

        public DataTable getDetailsByID(string pUserId)
        {
            try
            {
                string _sql = "call spGetUserDetailsById('" + pUserId + "')";
                loMySqlDataAdapter = new MySqlDataAdapter(_sql, GlobalVariables.goMySqlConnection);
                DataTable _dt = new DataTable();
                loMySqlDataAdapter.Fill(_dt);
                return _dt;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                loMySqlDataAdapter.Dispose();
            }
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        cnString  = ConfigurationSettings.AppSettings["connectionstring"].ToString();

        String version = Request.QueryString["version"];
        String AppId = Request.QueryString["AppId"]; // TODO - not needed for now

        string sqlstring ;
        if (version == null)
        {
            sqlstring = "SELECT * FROM categories where id <> 2 order by ordering DESC";

        }
        else if( AppId.Equals("2") )
        {
            sqlstring = "SELECT * FROM categories where off=0 order by ordering DESC";
        }
        else
        {
            sqlstring = "SELECT * FROM categories order by ordering DESC";
        }

        MySqlConnection cn = new MySqlConnection(cnString);
        MySqlDataAdapter dr = new MySqlDataAdapter(sqlstring, cn);
        try
        {
            DataSet ds = new DataSet();
            dr.Fill(ds);
            string jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
            Response.Write(jsonString);
        }
        finally
        {
            //  Explicitly dispose the SelectCommand instance
            dr.SelectCommand.Dispose();
            dr.Dispose();
            cn.Close();
        }

        Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
    }

        public DataTable get(string pNewspaperId)
        {
            try
            {
                string _sql = "call spGetSections('" + pNewspaperId + "')";

                loMySQLDataAdapter = new MySqlDataAdapter(_sql, GlobalVariables.goMySqlConnection);
                loDataTable = new DataTable("Sections");
                loMySQLDataAdapter.Fill(loDataTable);
                return loDataTable;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                loMySQLDataAdapter.Dispose();
            }
        }