C# SQLiteDataAdapter.Dispose方法代码示例

 /// <summary>
 /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
 /// </summary>
 /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
 /// <param name="paramList">object[] array of parameter values</param>
 /// <returns></returns>
 public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
 {
     using (SQLiteConnection conn = new SQLiteConnection(connStr))
     {
         using (SQLiteCommand cmd = new SQLiteCommand(commandText, conn))
         {
             try
             {
                 conn.Open();
                 if (paramList != null)
                 {
                     AttachParameters(cmd, commandText, paramList);
                 }
                 DataSet ds = new DataSet();
                 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                 da.Fill(ds);
                 da.Dispose();
                 cmd.Dispose();
                 conn.Close();
                 return ds;
             }
             catch (Exception ex)
             {
                 cmd.Dispose();
                 conn.Close();
                 throw new Exception(ex.Message);
             }
         }
     }
 }

        public static DataTable GetData(string strConn, string strSql, int timeout)
        {
            DataTable dt = new DataTable("td");

            using (SQLiteConnection conn = new SQLiteConnection(strConn))
            {
                conn.Open();

                SQLiteCommand cmd = null;
                SQLiteDataAdapter da = null;

                try
                {
                    cmd = new SQLiteCommand(strSql, conn) { CommandTimeout = timeout };
                    da = new SQLiteDataAdapter { SelectCommand = cmd };

                    da.Fill(dt);

                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception("error getting data " + ex.Message);
                }
                finally
                {
                    if (da != null) { da.Dispose(); }
                    if (cmd != null) { cmd.Dispose(); }

                    conn.Close();
                }
            }
        }

    /// <summary>
    /// Generic Method to return data from sqlite database type
    /// </summary>
    /// <param name="connStr"></param>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataSet GetSqliteData(string connStr, string sql)
    {
        DataSet ds = new DataSet();

        using (SQLiteConnection conn = new SQLiteConnection(connStr))
        {
            conn.Open();

            SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn);

            try
            {
                da.Fill(ds);
            }
            catch (Exception)
            {
                ds = new DataSet();
            }
            finally
            {
                da.Dispose();

                conn.Close();
                conn.Dispose();
            }
        }

        return ds;
    }

 public DataSet ExecuteDataset(SQLiteCommand cmd)
 {
     SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
     DataSet ds = new DataSet();
     da.Fill(ds);
     da.Dispose();
     return ds;
 }

        public bool ReadDataTable(ref DataTable dt, string sqlGetColumnsNamesAndTypes, List<SQL_Parameter> lSQL_Parameter, ref string csError)
        {
            ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2) START", sqlGetColumnsNamesAndTypes);

            //SqlConnection Conn = new SqlConnection("Data Source=razvoj1;Initial Catalog=NOS_BIH;Persist Security Info=True;User ID=sa;Password=sa;");
            if (DynSettings.bPreviewSQLBeforeExecution)
            {
                string new_sql = "";
                bool bChanged = false;
                PreviewSQLCommand(sqlGetColumnsNamesAndTypes,null,ref new_sql,ref bChanged, "ReadDataTable");
                if (bChanged)
                {
                    sqlGetColumnsNamesAndTypes = new_sql;
                }
            }
            try
            {
                switch (m_DBType)
                {
                    case eDBType.MYSQL:
                        {
                            MySqlDataAdapter adapter = new MySqlDataAdapter();

                            if (Connect_Batch(ref csError))
                            {
                                MySqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new MySqlCommand(sqlGetColumnsNamesAndTypes, m_con_MYSQL);
                                if (lSQL_Parameter != null)
                                {
                                    foreach (SQL_Parameter sqlPar in lSQL_Parameter)
                                    {
                                        if (sqlPar.size > 0)
                                        {
                                            SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(sqlPar.Name, sqlPar.MySQLdbType, sqlPar.size).Value = sqlPar.Value;
                                        }
                                        else
                                        {
                                            SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(new MySqlParameter(sqlPar.Name, sqlPar.Value)).Value = sqlPar.Value;
                                        }
                                    }
                                }
                                adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes;
                                adapter.Fill(dt);
                                adapter.Dispose();
                                SqlCommandcommandGetColumnsNamesAndTypes.Dispose();
                                Disconnect_Batch();
                            }
                            else
                            {
                                MessageBox.Show(csError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                            }
                        }
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2)  END", null);
                        return true;

                    case eDBType.MSSQL:
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter();

                            if (Connect_Batch(ref csError))
                            {
                                SqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new SqlCommand(sqlGetColumnsNamesAndTypes, m_con_MSSQL);
                                if (lSQL_Parameter != null)
                                {
                                    foreach (SQL_Parameter sqlPar in lSQL_Parameter)
                                    {
                                        if (sqlPar.size > 0)
                                        {
                                            SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(sqlPar.Name, sqlPar.dbType, sqlPar.size).Value = sqlPar.Value;
                                        }
                                        else
                                        {
                                            SqlCommandcommandGetColumnsNamesAndTypes.Parameters.Add(new SqlParameter(sqlPar.Name, sqlPar.Value)).Value = sqlPar.Value;
                                        }
                                    }
                                }

                                adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes;
                                adapter.Fill(dt);
                                adapter.Dispose();
                                SqlCommandcommandGetColumnsNamesAndTypes.Dispose();
                                Disconnect_Batch();
                            }
                            else
                            {
                                MessageBox.Show(csError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                            }
                        }
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataTable(2)  END", null);
                        return true;

                    case eDBType.SQLITE:
                        {
                            SQLiteDataAdapter adapter = new SQLiteDataAdapter();

                            if (Connect_Batch(ref csError))
                            {
                                SQLiteCommand SqlCommandcommandGetColumnsNamesAndTypes = new SQLiteCommand(sqlGetColumnsNamesAndTypes, m_con_SQLite);
                                if (lSQL_Parameter != null)
                                {
                                    foreach (SQL_Parameter sqlPar in lSQL_Parameter)
//.........这里部分代码省略.........

        public bool ReadDataSet(ref DataSet ds, string sqlGetColumnsNamesAndTypes, ref string csError)
        {
            //SqlConnection Conn = new SqlConnection("Data Source=razvoj1;Initial Catalog=NOS_BIH;Persist Security Info=True;User ID=sa;Password=sa;");
            ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet START", sqlGetColumnsNamesAndTypes);
            if (DynSettings.bPreviewSQLBeforeExecution)
            {
                string new_sql = "";
                bool bChanged = false;
                PreviewSQLCommand(sqlGetColumnsNamesAndTypes, null, ref new_sql, ref bChanged, "ReadDataSet");
                if (bChanged)
                {
                    sqlGetColumnsNamesAndTypes = new_sql;
                }
            }
            try
            {
                switch (m_DBType)
                {
                    case eDBType.MYSQL:
                        {
                            MySqlDataAdapter adapter = new MySqlDataAdapter();
                            string sError = "";
                            if (Connect_Batch(ref sError))
                            {
                                MySqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new MySqlCommand(sqlGetColumnsNamesAndTypes, m_con_MYSQL);
                                adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes;
                                adapter.Fill(ds);
                                adapter.Dispose();
                                SqlCommandcommandGetColumnsNamesAndTypes.Dispose();
                                Disconnect_Batch();
                            }
                            else
                            {
                                MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                            }
                        }
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null);
                        return true;
                    case eDBType.MSSQL:
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter();
                            string sError = "";
                            if (Connect_Batch(ref sError))
                            {
                                SqlCommand SqlCommandcommandGetColumnsNamesAndTypes = new SqlCommand(sqlGetColumnsNamesAndTypes, m_con_MSSQL);
                                adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes;
                                adapter.Fill(ds);
                                adapter.Dispose();
                                SqlCommandcommandGetColumnsNamesAndTypes.Dispose();
                                Disconnect_Batch();
                            }
                            else
                            {
                                MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                            }
                        }
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null);
                        return true;

                    case eDBType.SQLITE:
                        {
                            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
                            string sError = "";
                            if (Connect_Batch(ref sError))
                            {
                                SQLiteCommand SqlCommandcommandGetColumnsNamesAndTypes = new SQLiteCommand(sqlGetColumnsNamesAndTypes, m_con_SQLite);
                                adapter.SelectCommand = SqlCommandcommandGetColumnsNamesAndTypes;
                                adapter.Fill(ds);
                                adapter.Dispose();
                                SqlCommandcommandGetColumnsNamesAndTypes.Dispose();
                                Disconnect_Batch();
                            }
                            else
                            {
                                MessageBox.Show(sError, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                            }
                        }
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null);
                        return true;

                    default:
                        MessageBox.Show("ERROR eSQLType in function:public bool ReadDataSet(..)");
                        ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null);
                        return false;
                }
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show("SQL ERROR:" + ex.Message);
                Disconnect();

                ShowDBErrorMessage(ex.Message, null, "ReadDataSet");
                WriteLogTable(ex);
                ProgramDiagnostic.Diagnostic.Meassure("ReadDataSet END", null);
                return false;
            }
        }

 public override DataTable GetDataTable(string sql)
 {
     SQLiteConnection con = new SQLiteConnection(ConnStr);
     SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, con);
     try
     {
         con.Open();
         DataTable dt = new DataTable();
         sda.Fill(dt);
         return dt;
     }
     catch
     {
         return null;
     }
     finally
     {
         sda.Dispose();
         con.Close();
         con.Dispose();
     }
 }

        public DataTable GetWellAllDescriptorValues(string TableName)
        {
            //  cExtendedList ToReturn = new cExtendedList();

            SQLiteCommand mycommand = new SQLiteCommand(_SQLiteConnection);
            // mycommand.CommandText = "SELECT *, \"" + DescType.GetName() + "\" FROM \"" + TableName + "\"";
            mycommand.CommandText = "SELECT * FROM \"" + TableName + "\"";
            SQLiteDataReader value = mycommand.ExecuteReader();
            // value.Read();

            //object[] myObjectArray = new object[value.FieldCount];

            //while (value.Read())
            //{
            //    value.GetValues(myObjectArray);
            //    int a = 1;
            //    //ToReturn.Add((double)myObjectArray[0]);
            //}
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(mycommand.CommandText, _SQLiteConnection);

            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);

            //Get the collection of rows from the DataSet
            //  DataRowCollection dataRowCol = ds.Tables[0].Rows;

            DataTable TableToReturn = ds.Tables[0];
            //Add the tables available in the DB to the combo box
            //foreach (DataRow dr in dataRowCol)
            //{
            //    tablecombobox.Items.Add(dr["name"]);
            //}

            /*int Pos = value.GetOrdinal(DescType.GetName());

            while (value.Read())
            {
                ToReturn.Add(value.GetFloat(Pos));
            }
            */
            dataAdapter.Dispose();
            dataAdapter = null;

            mycommand.Dispose();
            mycommand = null;
            value.Close();
            value.Dispose();
            value = null;

            return TableToReturn;
        }

        /// <summary>
        /// Executes the given query on the database.
        /// </summary>
        /// <param name="sql">The query</param>
        /// <returns>Result from the database.</returns>
        public DataTable Query(string query, bool logerror = true)
        {
            try
            {
                IsConnect();
                var adapter = new SQLiteDataAdapter();
                var command = Connection.CreateCommand();
                command.CommandText = query;
                adapter.SelectCommand = command;

                var table = new DataTable();
                adapter.Fill(table);

                command.Dispose();
                adapter.Dispose();

                return table;
            }
            catch(SQLiteException s)
            {
                Crash(s, logerror);
                return null;
            }
        }

        private void obtenerPersonas()
        {
            conexion.Open();
            SQLiteCommand command = conexion.CreateCommand();
            command.CommandText = "select * from persona";
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            DataTable dt = new DataTable();
            ad.Fill(dt);

            cbPersonas.DataSource = dt;
            cbPersonas.DisplayMember = "NOMBRE";
            cbPersonas.ValueMember = "ID";

            ad.Dispose();
            command.Dispose();
            conexion.Close();
        }

        /// <summary>
        /// Queries the DB for a single row of data
        /// Example. lookup item with id of 1
        /// Returns a DataRow object
        /// </summary>
        /// <returns>DataRow</returns>
        private static DataRow QueryFetchRow(string sql)
        {
            SQLiteCommand query = Db.CreateCommand();
            query.CommandText = sql;

            SQLiteDataAdapter da = new SQLiteDataAdapter(query);
            DataTable dt = new DataTable();
            try
            {
                dt.BeginLoadData();
                da.Fill(dt);
                dt.EndLoadData();
            }
            catch (Exception ex) { Logging.Write("Exception in QueryFetchRow: " + ex); }
            finally { da.Dispose(); }
            return dt.Rows.Cast<DataRow>().FirstOrDefault();

            /*
             * example usage:
            DataRow data = new DataRow();
            row = QueryFetchRow("SELECT * FROM items WHERE id = 1");
            Logging.Write("row: " + row["item_id"].ToString() + " " + row["item_name"].ToString() + "\n");
            */
        }

        public bool UpdateTable(DataTable table, string tableName)
        {
            try
            {
                TableHelper.SetDefaultColumnValues(table);

                var con = CONNECTION.OpenCon();

                var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
                var cmd = new SQLiteCommandBuilder(adapter);
                adapter.Update(table);

                cmd.Dispose();
                adapter.Dispose();
                CONNECTION.CloseCon(con);

                return true;
            }
            catch (DBConcurrencyException cex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable DBConcurrencyError!",
                    Ex = cex,
                });
                return false;
            }
            catch(Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable Error!",
                    Ex = ex,
                });
                return false;
            }
        }

 /// <summary>
 /// Executes the dataset from a populated Command object.
 /// </summary>
 /// <param name="cmd">Fully populated SQLiteCommand</param>
 /// <returns>DataSet</returns>
 public static DataSet ExecuteDataset(SQLiteCommand cmd)
 {
     if (cmd.Connection.State == ConnectionState.Closed)
          cmd.Connection.Open();
      DataSet ds = new DataSet();
      SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
      da.Fill(ds);
      da.Dispose();
      cmd.Connection.Close();
      cmd.Dispose();
      return ds;
 }

        private void Button_Search(object sender, RoutedEventArgs e)
        {
            SQLiteCommand search = db.CreateCommand();

            String column;
            switch (search_combobox.Text)
            {
                case "ISBN":
                    column = "ISBN"; break;
                case "Title":
                    column = "Title"; break;
                case "Author":
                    column = "Author"; break;
                case "Publisher":
                    column = "Publisher"; break;
                default:
                    column = "OTHER"; break;
            }

            if (!column.Equals("OTHER"))
            {
                search.CommandText = String.Format("SELECT * FROM Book WHERE {0} LIKE @Value", column);
                search.Prepare();
                search.Parameters.AddWithValue("@Value", String.Format("%{0}%", tb_search.Text));
            }
            else
            {
                search.CommandText = String.Format("SELECT c.Course, b.Title, b.Author, b.Publisher, b.ISBN FROM CourseBook AS c INNER JOIN Book AS b ON c.ISBN == b.ISBN WHERE c.Course LIKE @Value");
                search.Prepare();
                search.Parameters.AddWithValue("@Value", String.Format("%{0}%", tb_search.Text));
            }

            SQLiteDataAdapter da = new SQLiteDataAdapter(search);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGrid.DataContext = ds.Tables[0].DefaultView;

            search.Dispose();
            ds.Dispose();
            da.Dispose();
        }

        /// <summary>
        /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
        /// </summary>
        /// <param name="connectionString">SQLite Connection string</param>
        /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
        {
            SQLiteConnection cn = new SQLiteConnection(connectionString);
             SQLiteCommand cmd = cn.CreateCommand();

             cmd.CommandText = commandText;
             if (paramList != null)
             {
                 AttachParameters(cmd,commandText, paramList);
             }
             DataSet ds = new DataSet();
             if (cn.State == ConnectionState.Closed)
                 cn.Open();
             SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
             da.Fill(ds);
             da.Dispose();
             cmd.Dispose();
             cn.Close();
             return ds;
        }