Пример #1
1
        /// <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;
 }
Пример #4
0
 public H2Command(string commandText, H2Connection connection, H2Transaction transaction)
 {
     _commandText     = commandText;
     Connection       = connection;
     Parameters       = new H2ParameterCollection();
     UpdatedRowSource = UpdateRowSource.None;
 }
Пример #5
0
        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);
        }
Пример #6
0
 public static DataTable ReadTable(this H2Connection connection, String tableName)
 {
     if (tableName == null)
     {
         return(null);
     }
     return(connection.ReadQuery("select * from \"" + tableName + "\""));
 }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        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() + "'")));
        }
Пример #11
0
        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() + "'"));
        }
Пример #12
0
 public H2Command(string commandText, H2Connection connection, H2Transaction transaction)
 {
     this.commandText = commandText;
     this.connection = connection;
     this.collection = new H2ParameterCollection();
     this.updatedRowSource = UpdateRowSource.None;
 }
Пример #13
0
 public H2Command(string commandText, H2Connection connection)
     : this(commandText, connection, null)
 {
 }
Пример #14
0
 public H2Command(H2Connection connection)
     : this(null, connection, null)
 {
 }
Пример #15
0
 internal H2Transaction(H2Connection connection) => Connection = connection;
Пример #16
0
        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();
            }
        }
Пример #17
0
 internal H2Transaction(H2Connection connection)
 {
     this.connection = connection;
 }
Пример #18
0
 public H2Command(string commandText, H2Connection connection)
     : this(commandText, connection, null)
 {
 }
Пример #19
0
 internal H2DataReader(H2Connection connection, ResultSet set)
 {
     _set        = set;
     _connection = connection;
 }
Пример #20
0
 public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
 {
     this.selectCommand = selectConnection.CreateCommand();
     this.selectCommand.CommandText = selectCommandText;
 }
Пример #21
0
        public static String ReadString(this H2Connection connection, String query)
        {
            var result = new H2Command(query, connection).ExecuteScalar() as String;

            return(result);
        }
Пример #22
0
 internal H2DataReader(H2Connection connection, ResultSet set)
 {
     this.set = set;
     this.connection = connection;
 }
Пример #23
0
 internal H2Transaction(H2Connection connection)
 {
     this.connection = connection;
 }
Пример #24
0
 internal H2DataReader(H2Connection connection, ResultSet set)
 {
     this.set        = set;
     this.connection = connection;
 }
Пример #25
0
 public static String ReadString(this H2Connection connection, String query)
 => new H2Command(query, connection).ExecuteScalar() as String;
Пример #26
0
 public H2Command(H2Connection connection)
     : this(null, connection, null)
 {
 }
Пример #27
0
        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();
            }
        }
Пример #28
0
 public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
 {
     this.selectCommand             = selectConnection.CreateCommand();
     this.selectCommand.CommandText = selectCommandText;
 }