Dispose() public method

public Dispose ( ) : void
return void
Example #1
0
 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;
         }
     }
 }
Example #2
0
 /// <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);
             }
         }
     }
 }
Example #3
0
        /// <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;
        }
Example #4
0
        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);
            }
        }
Example #6
0
        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();
                }
            }
        }
Example #7
0
        /// <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;
        }
Example #8
0
 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.");
            }
        }
Example #11
0
 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;
 }
Example #12
0
 /// <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();
             }
         }
     }
 }
Example #13
0
 /// <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;
 }
Example #14
0
 public void SqlCommand(string sqlStatement)
 {
     var command = new SQLiteCommand { Connection = _connection };
     command.CommandText = sqlStatement;
     command.ExecuteNonQuery();
     command.Dispose();
 }
Example #15
0
 protected void ExecuteSqlCommand(string command)
 {
     var createCommand = new SQLiteCommand(command, _sqliteCon);
     {
         createCommand.ExecuteNonQuery();
     }
     createCommand.Dispose();
 }
Example #16
0
 public bool deleteMGroup(string mGroupName)
 {
     string cmdString = "DELETE FROM mgroup WHERE mGroupName =" + mGroupName + ";";
     SQLiteCommand sqlDeleteMGroup = new SQLiteCommand(cmdString, conn);
     sqlDeleteMGroup.ExecuteNonQuery();
     sqlDeleteMGroup.Dispose();
     return true;
 }
Example #17
0
 public bool deleteSession(int sessionId)
 {
     string cmdString = "DELETE FROM session WHERE sessionId = " + sessionId + ";";
     SQLiteCommand sqlDeleteSession = new SQLiteCommand(cmdString, conn);
     sqlDeleteSession.ExecuteNonQuery();
     sqlDeleteSession.Dispose();
     return true;
 }
Example #18
0
 public void executeWithoutResult(string query)
 {
     var command = new SQLiteCommand(consql);
     command.CommandText = query;
     command.ExecuteNonQuery();
     //free
     command.Dispose();
 }
Example #19
0
 public bool changeNotice(string groupId, string newNotice)
 {
     string cmdString = "UPDATE group SET notice =" +
         newNotice + "WHERE groupId = " + groupId + ";";
     SQLiteCommand sqlChangeNotice = new SQLiteCommand(cmdString, conn);
     sqlChangeNotice.ExecuteNonQuery();
     sqlChangeNotice.Dispose();
     return true;
 }
Example #20
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            if (!Event.Text.Equals("") && !StartTime.Text.Equals("") && !EndTime.Text.Equals("") && (!Winners.Text.Equals("") || (bool)Fail.IsChecked) && !GMList.Text.Equals("") && !PlayerCount.Text.Equals(""))
            {
                var db = DBMethods.OPENDB();
                string query = "SELECT * FROM events WHERE Name = '" + Event.Text + "' AND Status = 'APPROVED' COLLATE NOCASE LIMIT 1;";
                SQLiteCommand command = new SQLiteCommand(query);
                command.Connection = db;

                SQLiteDataReader results = command.ExecuteReader();
                if (results.HasRows)
                {
                    results.Dispose();
                    string[] peopleList = GMList.Text.Replace(", ", ",").Split(',');
                    foreach (string GM in peopleList)
                    {
                        using (SQLiteCommand cmd2 = new SQLiteCommand(db))
                        {
                            query = "SELECT * FROM gm WHERE Name = '" + GM + "' COLLATE NOCASE LIMIT 1;";
                            cmd2.CommandText = query;
                            using (results = cmd2.ExecuteReader())
                            {
                                if (!results.HasRows)
                                {
                                    Constants.Toast("GM " + GM + " not found. Event not logged.");
                                    return;
                                }
                            }
                        }
                    }

                    command.Dispose();
                    db.Close();

                    logEvent();
                }
                else
                {
                    Constants.Toast("Event not in approved list");
                }
            }
            else
            {
                if (Event.Text.Equals(""))
                    Constants.Toast("Please insert an event.");
                else if(StartTime.Text.Equals(""))
                    Constants.Toast("Please insert a start time.");
                else if (EndTime.Text.Equals(""))
                    Constants.Toast("Please insert an end time.");
                else if (Winners.Text.Equals(""))
                    Constants.Toast("Please insert event winner(s).");
                else if (GMList.Text.Equals(""))
                    Constants.Toast("Please list the involved GMs (including yourself).");
                else if (PlayerCount.Text.Equals(""))
                    Constants.Toast("Please insert player count.");
            }
        }
Example #21
0
 public bool changeRemark(string friendId, string newRemark)
 {
     string cmdString = "UPDATE friend SET remark ="+newRemark+
         "WHERE friendId =" + friendId + ";";
     SQLiteCommand sqlChangeRemark = new SQLiteCommand(cmdString, conn);
     sqlChangeRemark.ExecuteNonQuery();
     sqlChangeRemark.Dispose();
     return true;
 }
Example #22
0
 public bool changeMGroup(string friendId, string newMGroupName)
 {
     string cmdString = "UPDATE friend SET mGroupName ="+newMGroupName+
         "WHERE friendId =" + friendId + ";";
     SQLiteCommand sqlChangeMGroup = new SQLiteCommand(cmdString, conn);
     sqlChangeMGroup.ExecuteNonQuery();
     sqlChangeMGroup.Dispose();
     return true;
 }
Example #23
0
        public SQLiteDataReader executeWithResult(string query)
        {
            var command = new SQLiteCommand(consql);
            command.CommandText = query;
            SQLiteDataReader reader = command.ExecuteReader();

            //free 
            command.Dispose();
            return reader;
        }
Example #24
0
        public void checkTables()
        {
            FileInfo file = new FileInfo("createscript.sql");
            string script = file.OpenText().ReadToEnd();

            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = script;
            executeCommandThreadSafe(command);
            command.Dispose();
        }
Example #25
0
        public bool AddToRecentDatabases(string DBPath)
        {
            try
            {
                //First I check if the file already exists in the database
                String sql = String.Format("SELECT * FROM RecentDatabases WHERE path = '{0}';", DBPath);

                SQLiteCommand command = new SQLiteCommand(sql, _Connection);
                SQLiteDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    //This means the opened DB is already within those available, I just need to update the latest_access
                    //First I clean the connection to avoid problems with the next query
                    reader.Close();
                    command.Dispose();

                    //Now I update the table
                    sql = String.Format("UPDATE RecentDatabases SET latest_access = '{0}' WHERE path = '{1}';",
                                        DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), DBPath);

                    command = new SQLiteCommand(sql, _Connection);
                    command.ExecuteNonQuery();

                }
                else
                {
                    //Not found, I need to add it

                    //First I clean the connection to avoid problems with the next query
                    reader.Close();
                    command.Dispose();

                    //Now I check if I have or exceed limit of 5 entries
                    if (GetRecentDatabasesCount() >= 5)
                    {
                        //If limit reached I remove the oldest
                        RemoveOldestRecentDatabase();
                    }

                    //Now I can add the new entry
                    sql = String.Format("INSERT INTO RecentDatabases (path) VALUES ('{0}');", DBPath);

                    command = new SQLiteCommand(sql, _Connection);
                    command.ExecuteNonQuery();

                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
Example #26
0
        public GroupDB(string userId)
        {
            this.userId = userId;

            string dbPath = Environment.CurrentDirectory + "/db/" + userId + "/group.db";
            conn = new SQLiteConnection(dbPath);
            string cmdString = "CREATE TABLE IF NOT EXISTS group(groupId integer, groupName varchar(40), num integer, time date, notice text, type varchar(20));";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(cmdString, conn);
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.Dispose();
        }
Example #27
0
 public bool addMGroup(MGroup mg)
 {
     string cmdString = "INSERT TO mgroup VALUES(" +
         mg.MGroupId + "," +
         mg.MGroupName + "," +
         mg.Num + "," + ");";
     SQLiteCommand cmdAddMGroup = new SQLiteCommand(cmdString, conn);
     cmdAddMGroup.ExecuteNonQuery();
     cmdAddMGroup.Dispose();
     return true;
 }
Example #28
0
 public bool changeName(string groupId, string newName)
 {
     string cmdString = "UPDATE group SET groupName =" +
         newName + "WHERE groupId = " + groupId ;
     conn.Open();
     SQLiteCommand sqlChangeName = new SQLiteCommand(cmdString, conn);
     sqlChangeName.ExecuteNonQuery();
     sqlChangeName.Dispose();
     conn.Close();
     return true;
 }
Example #29
0
 /// <summary>
 /// 单例模式的实际构造方法
 /// </summary>
 /// <param name="userId">用户ID</param>
 private MsgDB(string userId)
 {
     this.userId = userId;
     string dbPath = Environment.CurrentDirectory + "/db/" + userId + "/msg.db";
     conn = new SQLiteConnection(dbPath);
     string cmdString = @"CREATE TABLE IF NOT EXISTS msg(msgId integer,sessionId integer, senderId varchar(20), receiverId varchar(20), senderName varchar(40), type integer,
     content text, isComing integer, date time, isRead integer, bak1 text, bak2 text, bak3 text, bak4 text, bak5 text, bak6 text);";
     SQLiteCommand cmdCreateTable = new SQLiteCommand(cmdString, conn);
     cmdCreateTable.ExecuteNonQuery();
     cmdCreateTable.Dispose();
 }
 public void doActionOnCompleteTodoList(Action<TodoListData> action)
 {
     SQLiteCommand command = new SQLiteCommand(connection);
     List<TodoListData> todoList = new List<TodoListData>();
     command.CommandText = "SELECT * FROM TodoList ORDER BY idTodo ASC;";
     SQLiteDataReader reader = executeReaderThreadSafe(command);
     while (reader.Read()) {
         action(new TodoListData(reader.GetInt32(0), (TodoType)reader.GetInt32(1), reader["parameter"].ToString(), reader["title"].ToString(), reader["description"].ToString()));
     }
     command.Dispose();
 }