public long SaveQuery(AppDBQueryLink query)
{
MySqlCommand qry;
if (query.id > 0)
{
// update
qry = new MySqlCommand("update query set name=?name, sqltext=?sqltext, connection_id=?connection_id where id=?id", db);
qry.Parameters.AddWithValue("?id", query.id);
}
else
{
// insert
qry = new MySqlCommand("insert into query ( connection_id, name, sqltext) values (?connection_id,?name,?sqltext); select last_insert_id();", db);
}
qry.Parameters.AddWithValue("?connection_id", query.connection_id);
qry.Parameters.AddWithValue("?name", query.name);
qry.Parameters.AddWithValue("?sqltext", query.sqltext);
qry.ExecuteNonQuery();
if (query.id <= 0)
{
query.id = qry.LastInsertedId;
}
return(query.id);
}
public long SaveQuery(AppDBQueryLink query)
{
SQLiteCommand qry;
if (query.id > 0)
{
// update
qry = new SQLiteCommand("update query set name=@name, sqltext=@sqltext, connection_id=@connection_id where id=@id", db);
qry.Parameters.AddWithValue("@id", query.id);
}
else
{
// insert
qry = new SQLiteCommand("insert into query ( connection_id, name, sqltext) values (@connection_id,@name,@sqltext);", db);
}
qry.Parameters.AddWithValue("@connection_id", query.connection_id);
qry.Parameters.AddWithValue("@name", query.name);
qry.Parameters.AddWithValue("@sqltext", query.sqltext);
qry.ExecuteNonQuery();
if (query.id <= 0)
{
qry.CommandText = "select last_insert_rowid()";
query.id = (long)qry.ExecuteScalar();
}
return(query.id);
}
public void Initialize(AppDBQueryLink linkQueryRow, IQueryableConnection connection)
{
currentQuery = linkQueryRow;
completionHelper = QueryComposerResources.ComposerHelper(connection);
Reset();
}
public void Initialize(AppDBQueryLink linkQueryRow, IQueryableConnection connection)
{
currentQuery = linkQueryRow;
completionHelper = QueryComposerResources.ComposerHelper(connection);
Reset();
}
public void DelQuery(AppDBQueryLink query)
{
MySqlCommand qry;
if (query.id > 0)
{
qry = new MySqlCommand("delete from query where id=?id", db);
qry.Parameters.AddWithValue("?id", query.id);
qry.ExecuteNonQuery();
query.id = 0;
}
else
{
// ???
}
}
public void DelQuery(AppDBQueryLink query)
{
SQLiteCommand qry;
if (query.id > 0)
{
qry = new SQLiteCommand("delete from query where id=@id", db);
qry.Parameters.AddWithValue("@id", query.id);
qry.ExecuteNonQuery();
query.id = 0;
}
else
{
// @@@
}
}
public void DelQuery(AppDBQueryLink query)
{
MySqlCommand qry;
if (query.id > 0)
{
qry = new MySqlCommand("delete from query where id=?id", db);
qry.Parameters.AddWithValue("?id", query.id);
qry.ExecuteNonQuery();
query.id = 0;
}
else
{
// ???
}
}
public void DelQuery(AppDBQueryLink query)
{
SQLiteCommand qry;
if (query.id > 0)
{
qry = new SQLiteCommand("delete from query where id=@id", db);
qry.Parameters.AddWithValue("@id", query.id);
qry.ExecuteNonQuery();
query.id = 0;
}
else
{
// @@@
}
}
private void btnAddQuery_Click(object sender, RoutedEventArgs e)
{
var link = new AppDBQueryLink(new AppDBDummyQuery(0, connectionId, "New Query", string.Empty));
var frm = new frmQueryEdit();
frm.Initialize(link, DBConnection);
bool?b = frm.ShowDialog();
if (b == true)
{
btnRefreshQuery.IsEnabled = false;
var editable = (IAppDBEditableQueries)appDB;
editable.SaveQuery(link);
Reload();
}
}
private void btnDelQuery_Click(object sender, RoutedEventArgs e)
{
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
// ask to be sure user hit the right button
var r = MessageBox.Show("Are you sure you want to delete this query?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (r == MessageBoxResult.Yes)
{
btnRefreshQuery.IsEnabled = false;
// AppDB needs to be editable in order to have saveQuery and delQuery functions,
// but this button will be disabled if it's not editable, so we can just blindly do a typecast it here
var editable = (IAppDBEditableQueries)appDB;
editable.DelQuery(link);
Reload();
}
}
}
private void btnEditQuery_Click(object sender, RoutedEventArgs e)
{
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
var frm = new frmQueryEdit();
frm.Initialize(link, DBConnection);
bool?b = frm.ShowDialog();
if (b == true)
{
btnRefreshQuery.IsEnabled = false;
var editable = (IAppDBEditableQueries)appDB;
editable.SaveQuery(link);
// we don't have to do Reload() here, the object (row) should be edited directly, but we still need to reset CurrentQuery (used by GoQuery button)
cmbQueries_SelectionChanged(cmbQueries, null);
}
}
}
private void cmbQueries_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
currentQuery = null;
edSQL.Text = link.sqltext;
// Disable query action buttons
enableQueryActionControls(true);
}
else
{
currentQuery = null;
edSQL.Text = string.Empty;
// Disable query action buttons
enableQueryActionControls(false);
}
}
private void btnEditQuery_Click(object sender, RoutedEventArgs e)
{
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
var frm = new frmQueryEdit();
frm.Initialize(link, DBConnection);
bool? b = frm.ShowDialog();
if (b == true)
{
btnRefreshQuery.IsEnabled = false;
var editable = (IAppDBEditableQueries)appDB;
editable.SaveQuery(link);
// we don't have to do Reload() here, the object (row) should be edited directly, but we still need to reset CurrentQuery (used by GoQuery button)
cmbQueries_SelectionChanged(cmbQueries, null);
}
}
}
public long SaveQuery(AppDBQueryLink query)
{
SQLiteCommand qry;
if (query.id > 0)
{
// update
qry = new SQLiteCommand("update query set name=@name, sqltext=@sqltext, connection_id=@connection_id where id=@id", db);
qry.Parameters.AddWithValue("@id", query.id);
}
else
{
// insert
qry = new SQLiteCommand("insert into query ( connection_id, name, sqltext) values (@connection_id,@name,@sqltext);", db);
}
qry.Parameters.AddWithValue("@connection_id", query.connection_id);
qry.Parameters.AddWithValue("@name", query.name);
qry.Parameters.AddWithValue("@sqltext", query.sqltext);
qry.ExecuteNonQuery();
if (query.id <= 0)
{
qry.CommandText = "select last_insert_rowid()";
query.id = (long)qry.ExecuteScalar();
}
return query.id;
}
private void btnGoQuery_Click(object sender, RoutedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
// parse query parameters
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
if (currentQuery == null)
{
currentQuery = new StoredQuery(link.sqltext);
}
// Aks for query parameters and save them
if (!AskForParameters(currentQuery))
{
// Return if parameter input was canceled
return;
}
currentQuery.RewriteParameters(DBConnection.GetParamPrefixChar());
// Processs query parameters
edSQL.Text = ProcessParameters(currentQuery);
barQuery.Items.Clear();
var hyjackquery = false;
CExplainableQuery expl = QueryExplanationFactory.NewExplain(DBConnection, currentQuery);
if (expl != null)
{
if (expl.HasErrors())
{
MessageBox.Show(expl.GetErrorMsg(), "Query error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else if (!expl.IsAllIndexed())
{
if (MessageBox.Show("This query does not fully make use of indexes, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.IsUsingBadStuff())
{
if (MessageBox.Show("This query could take a long time to run, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.GetMaxResults() >= 65535)
{
if (MessageBox.Show("This query could possibly return a lot of rows, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
}
if (hyjackquery)
{
// execute the explain instead (only works for mysql queries)
var qryExplain = expl._get();
DBConnection.Query(qryExplain);
}
else
{
// execute query and get result set
DBConnection.Query(currentQuery);
}
DataTable dt;
try
{
// todo: datatable contains all results, no cursor/rowtravel/stream
dt = DBConnection.ResultsAsDataTable();
// Enable Refresh query button
btnRefreshQuery.IsEnabled = true;
// Enable Feed to action
btnFeedToAction.IsEnabled = true;
}
catch (Exception x)
{
// todo: handle query errors in a better way
MessageBox.Show(x.Message);
return;
}
// display results in datagrid
gridQueryResults.AutoGenerateColumns = true;
if (dt != null)
{
gridQueryResults.ItemsSource = dt.DefaultView;
string s = "Results: " + dt.Rows.Count;
barQuery.Items.Add(s);
}
else
{
gridQueryResults.ItemsSource = null;
string s = "No results";
barQuery.Items.Add(s);
}
}
}
private void cmbQueries_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
currentQuery = null;
edSQL.Text = link.sqltext;
// Disable query action buttons
enableQueryActionControls(true);
}
else
{
currentQuery = null;
edSQL.Text = string.Empty;
// Disable query action buttons
enableQueryActionControls(false);
}
}
private void btnGoQuery_Click(object sender, RoutedEventArgs e)
{
btnRefreshQuery.IsEnabled = false;
// parse query parameters
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
if (currentQuery == null)
{
currentQuery = new StoredQuery(link.sqltext);
}
// Aks for query parameters and save them
if (!AskForParameters(currentQuery))
{
// Return if parameter input was canceled
return;
}
currentQuery.RewriteParameters(DBConnection.GetParamPrefixChar());
// Processs query parameters
edSQL.Text = ProcessParameters(currentQuery);
barQuery.Items.Clear();
var hyjackquery = false;
CExplainableQuery expl = QueryExplanationFactory.NewExplain(DBConnection, currentQuery);
if (expl != null)
{
if (expl.HasErrors())
{
MessageBox.Show(expl.GetErrorMsg(), "Query error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else if (!expl.IsAllIndexed())
{
if (MessageBox.Show("This query does not fully make use of indexes, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.IsUsingBadStuff())
{
if (MessageBox.Show("This query could take a long time to run, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
else if (expl.GetMaxResults() >= 65535)
{
if (MessageBox.Show("This query could possibly return a lot of rows, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
hyjackquery = true;
}
}
}
if (hyjackquery)
{
// execute the explain instead (only works for mysql queries)
var qryExplain = expl._get();
DBConnection.Query(qryExplain);
}
else
{
// execute query and get result set
DBConnection.Query(currentQuery);
}
DataTable dt;
try
{
// todo: datatable contains all results, no cursor/rowtravel/stream
dt = DBConnection.ResultsAsDataTable();
// Enable Refresh query button
btnRefreshQuery.IsEnabled = true;
// Enable Feed to action
btnFeedToAction.IsEnabled = true;
}
catch (Exception x)
{
// todo: handle query errors in a better way
MessageBox.Show(x.Message);
return;
}
// display results in datagrid
gridQueryResults.AutoGenerateColumns = true;
if (dt != null)
{
gridQueryResults.ItemsSource = dt.DefaultView;
string s = "Results: " + dt.Rows.Count;
barQuery.Items.Add(s);
}
else
{
gridQueryResults.ItemsSource = null;
string s = "No results";
barQuery.Items.Add(s);
}
}
}
public long SaveQuery(AppDBQueryLink query)
{
MySqlCommand qry;
if (query.id > 0)
{
// update
qry = new MySqlCommand("update query set name=?name, sqltext=?sqltext, connection_id=?connection_id where id=?id", db);
qry.Parameters.AddWithValue("?id", query.id);
}
else
{
// insert
qry = new MySqlCommand("insert into query ( connection_id, name, sqltext) values (?connection_id,?name,?sqltext); select last_insert_id();", db);
}
qry.Parameters.AddWithValue("?connection_id", query.connection_id);
qry.Parameters.AddWithValue("?name", query.name);
qry.Parameters.AddWithValue("?sqltext", query.sqltext);
qry.ExecuteNonQuery();
if (query.id <= 0)
{
query.id = qry.LastInsertedId;
}
return query.id;
}
private void btnDelQuery_Click(object sender, RoutedEventArgs e)
{
var row = cmbQueries.SelectedItem;
if (row != null)
{
var link = new AppDBQueryLink(row);
// ask to be sure user hit the right button
var r = MessageBox.Show("Are you sure you want to delete this query?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (r == MessageBoxResult.Yes)
{
btnRefreshQuery.IsEnabled = false;
// AppDB needs to be editable in order to have saveQuery and delQuery functions,
// but this button will be disabled if it's not editable, so we can just blindly do a typecast it here
var editable = (IAppDBEditableQueries)appDB;
editable.DelQuery(link);
Reload();
}
}
}
private void btnAddQuery_Click(object sender, RoutedEventArgs e)
{
var link = new AppDBQueryLink(new AppDBDummyQuery(0, connectionId, "New Query", string.Empty));
var frm = new frmQueryEdit();
frm.Initialize(link, DBConnection);
bool? b = frm.ShowDialog();
if (b == true)
{
btnRefreshQuery.IsEnabled = false;
var editable = (IAppDBEditableQueries)appDB;
editable.SaveQuery(link);
Reload();
}
}