/// <summary>
/// Executes the select query.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="query">The query.</param>
/// <returns>A data table containing the result of the query</returns>
public DataTable ExecuteSelectQuery(string connectionString, string query)
{
using (System.Data.H2.H2Connection connection = new System.Data.H2.H2Connection(connectionString, "sa", string.Empty))
{
this.logger.LogVerbose(string.Format("Execute query {0}", query));
connection.Open();
var com = new H2Command(query, connection);
var result = com.ExecuteReader();
var tableResult = new DataTable();
for (int i = 0; i < result.FieldCount; i++)
{
tableResult.Columns.Add(result.GetName(i), result.GetFieldType(i));
}
while (result.Read())
{
var row = tableResult.NewRow();
for (int i = 0; i < result.FieldCount; i++)
{
row[i] = result.GetValue(i);
}
tableResult.Rows.Add(row);
}
return(tableResult);
}
}
/// <summary>
/// Executes the select query.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="query">The query.</param>
/// <returns>A data table containing the result of the query</returns>
public DataTable ExecuteSelectQuery(string connectionString, string query)
{
using (System.Data.H2.H2Connection connection = new System.Data.H2.H2Connection(connectionString, "sa", string.Empty))
{
this.logger.LogVerbose(string.Format("Execute query {0}", query));
connection.Open();
var com = new H2Command(query, connection);
var result = com.ExecuteReader();
var tableResult = new DataTable();
for (int i = 0; i < result.FieldCount; i++)
{
tableResult.Columns.Add(result.GetName(i), result.GetFieldType(i));
}
while (result.Read())
{
var row = tableResult.NewRow();
for (int i = 0; i < result.FieldCount; i++)
{
row[i] = result.GetValue(i);
}
tableResult.Rows.Add(row);
}
return tableResult;
}
}
public H2Command(string commandText, H2Connection connection, H2Transaction transaction)
{
this.commandText = commandText;
this.connection = connection;
this.collection = new H2ParameterCollection();
this.updatedRowSource = UpdateRowSource.None;
}
public H2Command(string commandText, H2Connection connection, H2Transaction transaction)
{
_commandText = commandText;
Connection = connection;
Parameters = new H2ParameterCollection();
UpdatedRowSource = UpdateRowSource.None;
}
public static HashSet <String> GetPrimaryKeysColumns(this H2Connection connection, String tableName)
{
// Reference : http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String, java.lang.String, java.lang.String)
/*try {
* var dbMeta = connection.connection.getMetaData();
* var res = dbMeta != null ? dbMeta.getPrimaryKeys(null, null, tableName) : null;
* if (res != null) {
* var ret = new HashSet<String>();
* while (res.next()) {
* var columnName = res.getString(4);
* ret.Add(columnName);
* }
* return ret;
* }
* } catch (Exception ex) {
* Console.WriteLine(ex);
* }*/
var ret = new HashSet <String>();
foreach (var list in connection.ReadStrings("select column_list from INFORMATION_SCHEMA.CONSTRAINTS where constraint_type = 'PRIMARY KEY' and upper(table_name) = '" + tableName.ToUpper() + "' "))
{
foreach (var col in list.Split(','))
{
ret.Add(col.Trim());
}
}
return(ret);
}
public static DataTable ReadTable(this H2Connection connection, String tableName)
{
if (tableName == null)
{
return(null);
}
return(connection.ReadQuery("select * from \"" + tableName + "\""));
}
public static List <String> ReadStrings(this H2Connection connection, String query)
{
var ret = new List <String>();
var reader = new H2Command(query, connection).ExecuteReader();
while (reader.Read())
{
ret.Add(reader.GetString(0));
}
return(ret);
}
public static DataTable ReadQuery(this H2Connection connection, String query)
{
if (query == null)
{
return(null);
}
var table = new DataTable()
{
CaseSensitive = false
};
new H2DataAdapter(new H2Command(query, connection)).Fill(table);
return(table);
}
public static Dictionary <String, T> ReadMap <T>(this H2Connection connection, String query)
{
var ret = new Dictionary <String, T>();
var reader = new H2Command(query, connection).ExecuteReader();
while (reader.Read())
{
var key = reader.GetString(0);
var value = reader.GetValue(1);
if (value == DBNull.Value)
{
ret[key] = default(T);
}
else
{
ret[key] = (T)value;
}
}
return(ret);
}
public static HashSet <String> GetUniqueColumns(this H2Connection connection, String tableName)
{
// Reference : http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean, boolean)
/*try {
* var dbMeta = connection.connection.getMetaData();
* var res = dbMeta != null ? dbMeta.getIndexInfo(null, null, tableName, true, false) : null;
* if (res != null) {
* var ret = new HashSet<String>();
* while (res.next()) {
* var columnName = res.getString(4);
* ret.Add(columnName);
* }
* return ret;
* }
* } catch (Exception ex) {
* Console.WriteLine(ex);
* }*/
return(new HashSet <String>(connection.ReadStrings("select column_list from INFORMATION_SCHEMA.CONSTRAINTS where constraint_type = 'UNIQUE' and upper(table_name) = '" + tableName.ToUpper() + "'")));
}
public static Dictionary <String, int> GetColumnTypeCodes(this H2Connection connection, String tableName)
{
// Reference : http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String, java.lang.String, java.lang.String)
/*try {
* var dbMeta = connection.connection.getMetaData();
* var res = dbMeta != null ? dbMeta.getColumns(null, null, tableName, null) : null;
* if (res != null) {
* var ret = new Dictionary<String, int>();
* while (res.next()) {
* var columnName = res.getString(4);
* var colType = res.getInt(5);
* ret[columnName] = colType;
* }
* return ret;
* }
* } catch (Exception ex) {
* Console.WriteLine(ex);
* }*/
return(connection.ReadMap <int>("select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where upper(table_name) = '" + tableName.ToUpper() + "'"));
}
public H2Command(string commandText, H2Connection connection, H2Transaction transaction)
{
this.commandText = commandText;
this.connection = connection;
this.collection = new H2ParameterCollection();
this.updatedRowSource = UpdateRowSource.None;
}
public H2Command(string commandText, H2Connection connection)
: this(commandText, connection, null)
{
}
public H2Command(H2Connection connection)
: this(null, connection, null)
{
}
internal H2Transaction(H2Connection connection) => Connection = connection;
private void wizardPage3_ShowFromNext(object sender, EventArgs e)
{
StoreSettings();
/*Try Connect*/
IDbConnection conn = null;
try
{
if (comboBox1.SelectedIndex == 0 && !System.IO.File.Exists(txtOlaDb.Text.Replace(".h2.", ".lock.")))
{
MessageBox.Show("OLA Does not seem to be started on server?\r\nPlease make sure OLA is running with connected clients when connecting, else all traffic will be redirected through this computer!");
}
conn = GetDBConnection(lstDB.SelectedItem as string);
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmbOLAComp.Items.Clear();
cmd.CommandText = "SELECT VersionNumber FROM Version WHERE moduleId = 1";
try
{
object res = cmd.ExecuteScalar();
}
catch (Exception ee)
{
if (ee.Message.ToUpper().Contains("ENOUGH RIGHTS"))
{
conn.Close();
conn = new H2Connection("jdbc:h2://" + txtOlaDb.Text.Replace(".h2.db", "") + ";AUTO_SERVER=TRUE", "root", "");
try
{
conn.Open();
cmd = conn.CreateCommand();
TryApplyReadRights(cmd);
}
finally
{
conn.Close();
}
conn = GetDBConnection(lstDB.SelectedItem as string);
conn.Open();
cmd = conn.CreateCommand();
}
}
cmd.CommandText = "select eventid, name from Events";
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OlaComp cmp = new OlaComp();
cmp.Id = Convert.ToInt32(reader["eventid"].ToString());
cmp.Name = Convert.ToString(reader["name"]);
cmbOLAComp.Items.Add(cmp);
}
reader.Close();
cmd.Dispose();
if (cmbOLAComp.Items.Count > 0)
cmbOLAComp.SelectedIndex = 0;
}
catch (Exception ee)
{
MessageBox.Show(this, ee.Message);
}
finally
{
if (conn != null)
conn.Close();
}
}
internal H2Transaction(H2Connection connection)
{
this.connection = connection;
}
public H2Command(string commandText, H2Connection connection)
: this(commandText, connection, null)
{
}
internal H2DataReader(H2Connection connection, ResultSet set)
{
_set = set;
_connection = connection;
}
public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
{
this.selectCommand = selectConnection.CreateCommand();
this.selectCommand.CommandText = selectCommandText;
}
public static String ReadString(this H2Connection connection, String query)
{
var result = new H2Command(query, connection).ExecuteScalar() as String;
return(result);
}
internal H2DataReader(H2Connection connection, ResultSet set)
{
this.set = set;
this.connection = connection;
}
internal H2Transaction(H2Connection connection)
{
this.connection = connection;
}
internal H2DataReader(H2Connection connection, ResultSet set)
{
this.set = set;
this.connection = connection;
}
public static String ReadString(this H2Connection connection, String query) => new H2Command(query, connection).ExecuteScalar() as String;
public H2Command(H2Connection connection)
: this(null, connection, null)
{
}
private void wizardPageOLASelEvent_ShowFromNext(object sender, EventArgs e)
{
EventSoftwareOLA ola = GetOlaEventSoftware();
IDbConnection conn = ola.GetDBConnection();
try
{
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmbOlaEvent.Items.Clear();
cmd.CommandText = "SELECT VersionNumber FROM Version WHERE moduleId = 1";
try
{
object res = cmd.ExecuteScalar();
}
catch (Exception ee)
{
if (ee.Message.ToUpper().Contains("ENOUGH RIGHTS"))
{
conn.Close();
conn = new H2Connection("jdbc:h2://" + txtOlaDBLoc.Text.Replace(".h2.db", "") + ";AUTO_SERVER=TRUE", "root", "");
try
{
conn.Open();
cmd = conn.CreateCommand();
TryApplyReadRights(cmd);
}
finally
{
conn.Close();
}
conn = ola.GetDBConnection();
conn.Open();
cmd = conn.CreateCommand();
}
}
cmd.CommandText = "select eventid, name from Events";
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OlaComp cmp = new OlaComp();
cmp.Id = Convert.ToInt32(reader["eventid"].ToString());
cmp.Name = Convert.ToString(reader["name"]);
cmbOlaEvent.Items.Add(cmp);
}
reader.Close();
cmd.Dispose();
if (cmbOlaEvent.Items.Count > 0)
cmbOlaEvent.SelectedIndex = 0;
}
finally
{
conn.Close();
}
}
public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
{
this.selectCommand = selectConnection.CreateCommand();
this.selectCommand.CommandText = selectCommandText;
}