C# SQLiteCommand.Dispose方法代码示例

 public bool IsValid(string username)
 {
     var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
     string parentDirectory = myDirectory.Parent.FullName;
     using (var cn = new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory, ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString)))
     {
         string _sql = @"SELECT id FROM Users " +
                "WHERE Name = '"+username+"';";
         cn.Open();
         var cmd = new SQLiteCommand(_sql, cn);
         var reader = cmd.ExecuteReader();
         if (reader.HasRows)
         {
             reader.Dispose();
             cmd.Dispose();
             return true;
         }
         else
         {
             reader.Dispose();
             cmd.Dispose();
             return false;
         }
     }
 }

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

        /// <summary>
        /// Queries the database, returns an datatable.
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetMultiQuery(string sql)
        {
            DataTable dt = new DataTable();

            try
            {
                SQLiteConnection SConnection = new SQLiteConnection();
                SConnection.ConnectionString =
                    "Data Source=users.siu;"  +
                    "UseUTF16Encoding=True;"  +
                    "Legacy Format=False;";

                SConnection.Open();

                SQLiteCommand Command = new SQLiteCommand(SConnection);
                Command.CommandText = sql;

                SQLiteDataReader Reader = Command.ExecuteReader();
                dt.Load(Reader);
                Reader.Close();
                Reader.Dispose();
                Command.Dispose();

                SConnection.Close();
                SConnection.Dispose();
            }
            catch (Exception e)
            {
                SiusLog.Log(SiusLog.WARNING, "GetQuery", e.Message);
            }
            return dt;
        }

        public String[] GetChatNames()
        {
            List<String> ChatNames = new List<String>();

            try
            {
                SQLiteCommand cmd = new SQLiteCommand("SELECT name, friendlyname, activity_timestamp FROM Chats ORDER BY activity_timestamp DESC", _conn);

                SQLiteDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Object status = reader["activity_timestamp"];
                        String ChatName = (String)reader["friendlyname"];
                        String Name = (String)reader["name"];

                        if (!ChatNames.Contains(Name))
                            ChatNames.Add(ChatName);
                    }
                }

                reader.Dispose();
                cmd.Dispose();

            }
            catch (SQLiteException sex)
            {

            }

            return ChatNames.ToArray();
        }

        public Steal_a_Feel(ref GeniePlugin.Interfaces.IHost host, string sDBLocation)
        {
            InitializeComponent();
            try
            {
                oDS = new DataSet();
                sSQLConn = new SQLiteConnection();

                oDS.Tables.Add("AllData");
                _host = host;

                _host.EchoText(sDBLocation);
                this.tbContainer.Text = this._host.get_Variable("StealingContainer");
                this.cbMark.Checked = this._host.get_Variable("StealingMark") == String.Empty ? false : true;
                this.cbPerceiveHealth.Checked = this._host.get_Variable("StealingPerceiveHealth") == String.Empty ? false : true;
                this.cbPerceive.Checked = this._host.get_Variable("StealingPerceive") == String.Empty ? false : true;
                this.sSQLConn.ConnectionString = "DataSource= " + sDBLocation;
                SQLiteCommand cmd = new SQLiteCommand(sSQLConn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from ItemList";
                SQLiteDataAdapter sDA = new SQLiteDataAdapter(cmd);
                sSQLConn.Open();
                DataSet ds = new DataSet();
                sDA.Fill(ds.Tables["AllData"]);
                sSQLConn.Close();
                sSQLConn.Dispose();
                cmd.Dispose();

                this.comboBox1.DataSource = oDS.Tables[0].Rows[1];
            }
            catch (Exception ex)
            {
                MessageBox.Show(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>
        /// 获得本地保存的用户登录认证信息
        /// </summary>
        /// <returns></returns>
        public static List<Auth> GetLoginAuths()
        {
            List<Auth> auths = null;

            string sql = "select * from log order by updateTime desc";
            string constr ="data source="+Application.StartupPath +"\\login"+".db";

            SQLiteConnection con = new SQLiteConnection(constr);
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(sql);
            cmd.Connection = con;
            SQLiteDataReader dr = cmd.ExecuteReader();
            if (dr != null)
            {
                auths = new List<Auth>();
                while (dr.Read())
                {
                    Auth a =Factory.CreateInstanceObject(dr["auth"].ToString()) as Auth  ;
                    if (a != null)
                        auths.Add(a);
                }
                dr.Close();
            }
            dr.Dispose(); dr = null;
            cmd.Dispose(); cmd = null; 
            con.Close();con.Dispose(); con = null;

            return auths;
        }

 public static DataTable Select(string sql, IList<SQLiteParameter> cmdparams = null)
 {
     SQLiteConnection cnn = Connect;
     if (cnn == null)
         return null;
     DataTable dt = new DataTable();
     SQLiteCommand Comm = null;
     SQLiteDataReader Reader = null;
     try
     {
         Comm = new SQLiteCommand(cnn);
         Comm.CommandText = sql;
         if (cmdparams != null)
             Comm.Parameters.AddRange(cmdparams.ToArray());
         Comm.CommandTimeout = TIMEOUT;
         Reader = Comm.ExecuteReader();
         dt.Load(Reader);
     }
     catch (Exception e)
     {
         _Err = e.Message;
         return null;
     }
     finally
     {
         if (Reader != null)
             Reader.Close();
         if (Comm != null)
             Comm.Dispose();
     }
     return dt;
 }

        public int ExecuteNonQuery(List<string> sqlList)
        {
            int rowsUpdated = 0;
            try
            {
                var con = CONNECTION.OpenCon();

                foreach(var sql in sqlList)
                {
                    var cmd = new SQLiteCommand(sql, con);
                    rowsUpdated += cmd.ExecuteNonQuery();

                    cmd.Dispose();
                }

                CONNECTION.CloseCon(con);
            }
            catch(Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "ExecuteNonQuery Error!",
                    Ex = ex,
                });
                return -1;
            }

            return rowsUpdated;
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            if (!PName.Text.Equals("") && !Reason.Text.Equals("") && !TimeLength.Text.Equals(""))
            {
                var db = DBMethods.OPENDB();
                SQLiteDataReader query = new SQLiteCommand("SELECT * FROM players WHERE Name = '" + PName.Text + "';", db).ExecuteReader();
                if (!query.HasRows)
                {
                    new AddPlayerPrompt().Prompt(PName.Text, Button_Click);
                }
                else
                {
                    query.Dispose();
                    new SQLiteCommand("INSERT INTO loggedpunishment(PlayerName, Punishment, Time, GM, Notes, ID) VALUES(" +
                        "'" + PName.Text + "', '" + (Action.SelectedItem as TextBlock).Text + "', '" + TimeLength.Text + "', '" + DBMethods.GMSELF() + "', " +
                            "'" + Reason.Text + "', " + DBMethods.GenerateUID("loggedpunishment") +");", db).ExecuteNonQuery();

                    DBMethods.UpdatePlayerActivity(PName.Text);
                    Constants.Toast("Punishment logged.");
                    PName.Text = "";
                    Reason.Text = "";
                    Action.SelectedIndex = 0;
                }
                db.Close();
            }
            else
            {
                if (PName.Text.Equals(""))
                    Constants.Toast("Insert player name.");
                else if (Reason.Text.Equals(""))
                    Constants.Toast("Insert reason.");
                else if (TimeLength.Text.Equals(""))
                    Constants.Toast("Insert length of punishment.");
            }
        }

 public DataTable GetDataTable(string strSQL, string TableName)
 {
     DataTable dt = null;
     try
     {
         using (SQLiteConnection scon = new SQLiteConnection(DataAccessUtilities.CreateSQLiteConnectionString()))
         {
             scon.Open();
             SQLiteCommand com = new SQLiteCommand(strSQL, scon);
             using (SQLiteDataReader dr = com.ExecuteReader())
             {
                 dt = new DataTable(TableName);
                 dt.Load(dr);
                 dr.Close();
             }
             com.Dispose();
             scon.Close();
             scon.Dispose();
         }
     } 
     catch (Exception ex)
     {
         LogManager.Instance.LogMessage("Error in GetDataTable", ex);
     }
     return dt;
 }

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public int ExecuteSql(string SQLString)
 {
     using (SQLiteConnection connection = new SQLiteConnection(connectionString))
     {
         using (SQLiteCommand cmd = new SQLiteCommand(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();
             }
         }
     }
 }

 /// <summary>
 /// 向数据库中添加消息
 /// </summary>
 /// <param name="m">消息</param>
 /// <returns>是否成功</returns>
 public bool addMsg(Msg m)
 {
     string cmdString = @"INSERT TO msg VALUES (" +
         m.MsgId + "," +
         m.SessionId + "," +
         m.FromUser + "," +
         m.ToUser + "," +
         m.FromUserName + "," +
         m.Type + "," +
         m.Content + "," +
         m.IsComing + "," +
         m.Date + "," + "," +
         m.IsReaded + "," +
         m.Bak1 + "," +
         m.Bak2 + "," +
         m.Bak3 + "," +
         m.Bak4 + "," +
         m.Bak5 + "," +
         m.Bak6 + "," +
         ");";
     SQLiteCommand sqlAddMsg = new SQLiteCommand(cmdString, conn);
     sqlAddMsg.ExecuteNonQuery();
     sqlAddMsg.Dispose();
     return true;
 }

 public void SqlCommand(string sqlStatement)
 {
     var command = new SQLiteCommand { Connection = _connection };
     command.CommandText = sqlStatement;
     command.ExecuteNonQuery();
     command.Dispose();
 }

 protected void ExecuteSqlCommand(string command)
 {
     var createCommand = new SQLiteCommand(command, _sqliteCon);
     {
         createCommand.ExecuteNonQuery();
     }
     createCommand.Dispose();
 }