C# SQLite crashing if input is wrong-CodePudding

I've got an problem with an Windows Forms Program: The user should be able to choose which table(year) they want to see.

That's my solution:

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

I'm using a textbox "yeartxtbox" to receive the wanted table. Basically its working, but every time the user inputted table does not exist the program crashes.

Any idea how I could fix this?

My thoughts:

Instead of using a textbox I could use an combobox to display the existing tables, but I have no ide how to realize this. I couldn't find anything online.

CodePudding user response：

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

CodePudding user response：

Your approach is wrong by allowing the user to type in a textbox and that is why the user can type a table name that does not exist. Instead of a textbox, use a combo box that an item can only be selected from a list of items that exist in the database.

In order to populate the combo box, change your query to :- string query = "Select [ID],[Name] from sysobjects where xtype = 'U'";

Get the result into your datatable and bind your Combo box to that datatable , then the user can only see the list of existing tables and select anyone.

Note:- sysobjects is a table in every SQL Server database that contains all the objects. xType column = 'U' means you are looking at only tables. That should resolve your problem.