How to Get Around the "'" Problem in SQLite and C#

Problems on retrieving data from sqlite3 in C#

Console.Write has multiple overloads. One of them admits object as parameter. Internally, Console.Write would call to the ToString() method of the object returned by reader["fieldname"].
reader["fieldname"] is resturning the underling database type. The c# compiler is complaing about the cast between 'object' to 'string'.

You can change

ShellFile.FromFilePath(reader["Filename"]).Properties.System.Comment.Value = reader["Tweet"];

by

ShellFile.FromFilePath(reader["Filename"].ToString()).Properties.System.Comment.Value = reader["Tweet"].ToString();

Performance issue with SQLite database (.db)

Currencly, the transaction is run per query, which makes no sense.

Enclose your main loop code in the transaction, and remove this GC.Collect().

EDIT:

As I understood, you dont want the global update to be rolled back in case of an error. So I changed the code a bit.

Additionally, I am not sure that the command object can be reused by changing the CommandText and running queries again. That's why I suggest to create it every time.

using (var transaction = con.BeginTransaction()) 
{ 
    for (int q = 0; q < list.Count; q++) 
    { 
        var castarraylist = (ArrayList)(list[q]); 

        for (int y = 0; y < castarraylist.Count; y++) 
        { 
            using (var cmd = new SQLiteCommand(con)) 
            {
                cmd.Transaction = transaction; 
                cmd.CommandText = Convert.ToString(castarraylist[y]);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    // Log the update problem
                    Console.WriteLine("Update problem " + cmd.CommandText + " - Reason: " + ex.Message);
                }
            }
        }
    }

    transaction.Commit();
}

C# SQLite crashing if input is wrong

Try / catch

try {
   SQLiteConnection con = new SQLiteConnection(@"Data Source=C:\App\DRSTZMSTR\Datenbank\Database.db");
   con.Open();
   string query = "SELECT* from '"+yeartxtbox.Text+"' ";
   SQLiteCommand cmd = new SQLiteCommand(query, con);
   DataTable dt = new DataTable();
   SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
   adapter.Fill(dt);

   dataGridView1.DataSource = dt;
   con.Close();
}
catch (Exception e){
   // do whatever you want to tell the user they made a mistake - eg
   MessageBox.Show(e.ToString());
}

Note that this type of SQL string composition is extremely bad thing to do. I can enter "table1; delete * from table1" as the table name. Its called "SQL Injection"

If you want to query the database to get a list of tables you can do this

SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

see SQLite Schema Information Metadata

SQLite: Database Is locked C#

The culprit was the reader, although I closed all the connection with the using parameter, i forgot to close all the readers i used, I should be utilizing using here too, but you get the idea. Database is locked with the readers unclosed

 public static void LoadScannedUser(string ID)
        {
            string sql = "SELECT * FROM Users WHERE ID = '" + ID + "'";

            using (var conn = new SQLiteConnection(ConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {

                    try
                    {
                        cmd.CommandText = sql;
                        conn.Open();
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            scannedUser.ID = reader.GetString(1);
                            scannedUser.Password = reader.GetString(2);
                            scannedUser.PPicture = Cryptorizer.Base64ToImage(reader.GetString(3));
                            scannedUser.FName = reader.GetString(4);
                            scannedUser.MName = reader.GetString(5);
                            scannedUser.LName = reader.GetString(6);
                            scannedUser.Agency = reader.GetString(7);
                            scannedUser.Position = reader.GetString(8);
                        }
                        reader.Close();
                    }

                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                }
            }

        }

c# sqlite query

You have a problem with your query. The SELECT extracts only one column from the table represented by the variable channel. So your GetString(1) in the reader loop fails because there is no field at index 1 (arrays start at index zero). You need to change that GetString index.

Then there is a problem in the return value. You say that you want to return a single string but there is no return statement and you don't have any single string to return

You could write

public string top10()
{
    List<string> toplist = new List<string>();
    String sql = "SELECT user FROM '" + channel + "' ORDER BY currency DESC LIMIT 10";
    using (cmd = new SQLiteCommand(sql, myDB))
    {
        using (SQLiteDataReader r = cmd.ExecuteReader())
        {
            while (r.Read())
            {
                 toplist.Add(r.GetString(0));
            }
        }
    }
    return string.Join(",", toplist);
}

or change the return type of the method to

public List<string> top10()
{
   .....
   return toplist;
}

or to

public string[] top10()
{
   .....
   return toplist.ToArray();
}

I have changed your internal array to a List<string> because if you have less than 10 records your array will have empty strings instead a list will return just the rows found.

---

Related Topics