Beispiel #1
0
        public void BulInsert_ShouldSuccessed()
        {
            var conn = new SharpHsqlConnection("Initial Catalog=mytest;User Id=sa;Pwd=;");

            try {
                conn.Open();
                var commandText = "DROP TABLE IF EXIST \"clients\";CREATE TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char, \"photo\" varbinary, \"created\" date );";
                var cmd         = new SharpHsqlCommand(commandText, conn);
                cmd.ExecuteNonQuery();

                var tran = conn.BeginTransaction();
                {
                    var data        = new Byte[] { 255, 255, 255, 255, 255, 255, 255, 255, 255, 255 };
                    var base64Photo = Convert.ToBase64String(data, 0, data.Length);

                    var insertCommand = new SharpHsqlCommand("", conn);
                    for (var i = 0; i < 1000; i++)
                    {
                        insertCommand.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, 'NOMBRE" + i.ToString() + "', '" + base64Photo + "', NOW() );";
                        insertCommand.ExecuteNonQuery();
                    }
                }
                tran.Commit();

                Assert.Pass();
            }
            catch (SharpHsqlException ex) {
                Assert.Fail(ex.Message);
            }
            finally {
                conn.Close();
            }
        }
Beispiel #2
0
        public void CreateTable_ShouldSuccessed()
        {
            var conn = new SharpHsqlConnection("Initial Catalog=mytest;User Id=sa;Pwd=;");

            try {
                conn.Open();

                var cmd = new SharpHsqlCommand("", conn);

                cmd.CommandText = "DROP TABLE IF EXIST \"data\";CREATE TABLE \"data\" (\"id\" int NOT NULL PRIMARY KEY, \"MyObject\" OBJECT);";
                var res = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, res);

                cmd.CommandText = "DROP TABLE IF EXIST \"clients\";CREATE TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char, \"photo\" varbinary, \"created\" date );";
                res             = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, res);
            }
            catch (SharpHsqlException ex) {
                Assert.Fail(ex.Message);
            }
            finally {
                conn.Close();
            }
        }
        static void Main(string[] args)
        {
            SharpHsqlConnection conn = new SharpHsqlConnection("Initial Catalog=mytest;User Id=sa;Pwd=;");

            byte[] data        = new byte[] { 255, 255, 255, 255, 255, 255, 255, 255, 255, 255 };
            string base64photo = Convert.ToBase64String(data, 0, data.Length);

            try
            {
                conn.Open();

                SharpHsqlCommand cmd = new SharpHsqlCommand("", conn);

                int res;

                Console.Write("Create table (y/n)?");
                string create = Console.ReadLine();
                if (create.ToLower() == "y")
                {
                    cmd.CommandText = "DROP TABLE IF EXIST \"data\";CREATE TABLE \"data\" (\"id\" int NOT NULL PRIMARY KEY, \"MyObject\" OBJECT);";
                    res             = cmd.ExecuteNonQuery();

                    cmd.CommandText = "DROP TABLE IF EXIST \"clients\";CREATE TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char, \"photo\" varbinary, \"created\" date );";
                    res             = cmd.ExecuteNonQuery();

                    var tran = conn.BeginTransaction();

                    cmd = new SharpHsqlCommand("", conn);

                    for (int i = 0; i < 10; i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, 'NOMBRE" + i.ToString() + "', '" + base64photo + "', NOW() );";
                        res             = cmd.ExecuteNonQuery();
                        cmd.CommandText = "CALL IDENTITY();";
                        int id = (int)cmd.ExecuteScalar();
                        Console.WriteLine("Inserted id={0}", id);
                    }

                    cmd.CommandText = "DROP TABLE IF EXIST \"books\";CREATE TABLE \"books\" (\"id\" INT NOT NULL PRIMARY KEY, \"name\" char, \"author\" char, \"qty\" int, \"value\" numeric);";
                    res             = cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO \"books\" VALUES (1, 'Book000', 'Any', 1, 23.5);";
                    res             = cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO \"books\" VALUES (2, 'Book001', 'Andy', 2, 43.9);";
                    res             = cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO \"books\" VALUES (3, 'Book002', 'Andy', 3, 37.25);";
                    res             = cmd.ExecuteNonQuery();
                    tran.Commit();
                }

                Console.WriteLine();



                Console.Write("Do Bulk INSERTS (y/n)?");
                string bulk = Console.ReadLine();
                if (bulk.ToLower() == "y")
                {
                    var tran = conn.BeginTransaction();

                    cmd = new SharpHsqlCommand("", conn);

                    for (int i = 0; i < 1000; i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, 'NOMBRE" + i.ToString() + "', '" + base64photo + "', NOW() );";
                        res             = cmd.ExecuteNonQuery();
                    }

                    tran.Commit();

                    Console.WriteLine("Inserted 1000 new clients.");
                    Console.WriteLine();
                }

                cmd = new SharpHsqlCommand("", conn);

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\",  \"clients\".\"photo\", \"clients\".\"created\" FROM \"clients\" ORDER BY \"clients\".\"id\" ";
                IDataReader reader = cmd.ExecuteReader();

                byte[] photo = null;

                while (reader.Read())
                {
                    long len = reader.GetBytes(3, 0, null, 0, 0);
                    photo = new byte[len];
                    reader.GetBytes(3, 0, photo, 0, (int)len);
                    Console.WriteLine("id={0}, doubleValue={1}, nombre={2}, photo={3}, created={4}", reader.GetInt32(0), reader.GetDouble(1), reader.GetString(2), photo.Length, reader.GetDateTime(4).ToString("yyyy.MM.dd hh:mm:ss.fffffff"));
                }

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT * FROM \"books\"";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("id={0}book={1},\tauthor={2},\tqty={3},\tvalue={4}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(4));
                }

                Console.WriteLine();

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT * FROM \"books\" ORDER BY \"value\"";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("id={0}book={1},\tauthor={2},\tqty={3},\tvalue={4}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(4));
                }

                Console.WriteLine();

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT COUNT(*) as CNT, SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy'";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("count={0},\tvalue={1}", reader.GetInt32(0), reader.GetDecimal(1));
                }

                Console.WriteLine();

                reader.Close();

                cmd.CommandText = "SELECT \"name\", \"author\", SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy' GROUP BY \"name\", \"author\";";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("name={0},\tauthor={1},\tvalue={2}", reader.GetString(0), reader.GetString(1), reader.GetDecimal(2));
                }

                Console.WriteLine();

                reader.Close();

                cmd.CommandText = "SELECT \"name\", SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy' GROUP BY \"name\";";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("name={0},\tvalue={1}", reader.GetString(0), reader.GetDecimal(1));
                }

                Console.WriteLine();

                reader.Close();
                cmd.CommandText = "DELETE FROM \"clients\" WHERE \"clients\".\"id\" = 6;";
                res             = cmd.ExecuteNonQuery();

                Console.WriteLine();

                cmd.CommandText = "SELECT MAX(\"clients\".\"id\") FROM \"clients\";";
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    res = (int)result;
                    Console.WriteLine("MAX=" + res);
                }

                cmd.CommandText = "SELECT SUM(\"clients\".\"id\") FROM \"clients\";";
                result          = cmd.ExecuteScalar();
                if (result != null)
                {
                    res = (int)result;
                    Console.WriteLine("SUM=" + res);
                }

                cmd.CommandText = "SELECT COUNT(\"clients\".\"id\") FROM \"clients\";";
                result          = cmd.ExecuteScalar();
                if (result != null)
                {
                    res = (int)result;
                    Console.WriteLine("COUNT=" + res);
                }

                cmd.CommandText = "SELECT AVG(\"clients\".\"id\") FROM \"clients\";";
                result          = cmd.ExecuteScalar();
                if (result != null)
                {
                    res = (int)result;
                    Console.WriteLine("AVG=" + res);
                }

                cmd.CommandText = "CALL ABS(-33.5632);";
                result          = cmd.ExecuteScalar();
                if (result != null)
                {
                    Double abs = (Double)result;
                    Console.WriteLine("ABS=" + abs);
                }

                cmd.CommandText = "CREATE ALIAS CALCRATE FOR \"ExternalFunction,ExternalFunction.Simple.calcrate\";";
                res             = cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE ALIAS EXTTAN FOR \"ExternalFunction,ExternalFunction.Simple.tan\";";
                res             = cmd.ExecuteNonQuery();

                cmd.CommandText = "CALL CALCRATE(100, 21);";
                Decimal rate = (Decimal)cmd.ExecuteScalar();
                Console.WriteLine("CALCRATE=" + rate);

                cmd.CommandText = "CALL EXTTAN(23.456);";
                Double tan = (Double)cmd.ExecuteScalar();
                Console.WriteLine("EXTTAN=" + tan);

                cmd.CommandText = "CALL SQRT(3);";
                Double sqrt = (Double)cmd.ExecuteScalar();
                Console.WriteLine("SQRT=" + sqrt);

                cmd.CommandText = "CALL SUBSTRING('0123456', 3, 2);";
                string subs = (String)cmd.ExecuteScalar();
                Console.WriteLine("SUBSTRING=" + subs);

                cmd.CommandText = "CALL ASCII('A');";
                int ascii = (int)cmd.ExecuteScalar();
                Console.WriteLine("ASCII=" + ascii);

                cmd.CommandText = "CALL USER();";
                string user = (string)cmd.ExecuteScalar();
                Console.WriteLine("USER="******"SELECT \"clients\".\"photo\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";
                byte[] b = (byte[])cmd.ExecuteScalar();

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";

                SharpHsqlDataAdapter adapter = new SharpHsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                res     = adapter.Fill(ds);
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("DataSet.Fill: " + ds.Tables[0].Rows.Count);

                cmd.CommandText = "DECLARE @MyVar CHAR;SET @MyVar = 'Andy';";
                cmd.ExecuteNonQuery();

                Console.WriteLine();
                cmd.CommandText = "SELECT @MyVar;";
                string var = (string)cmd.ExecuteScalar();
                Console.WriteLine("@MyVar=" + var);

                Console.WriteLine();

                cmd.CommandText = "SELECT \"name\", \"author\", SUM(\"value\") FROM \"books\" WHERE \"author\" = @MyVar GROUP BY \"name\", \"author\";";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("name={0},\tauthor={1},\tvalue={2}", reader.GetString(0), reader.GetString(1), reader.GetDecimal(2));
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, @MyVar, '" + base64photo + "', NOW() );";
                res             = cmd.ExecuteNonQuery();
                cmd.CommandText = "DECLARE @MyId INT;SET @MyId = IDENTITY();";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @MyId;";
                int myid = (int)cmd.ExecuteScalar();
                Console.WriteLine("Inserted id={0}", myid);

                Console.WriteLine();

                cmd.CommandText = "SET @MyId = SELECT MAX(\"clients\".\"id\") + 1 FROM \"clients\";";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @MyId;";
                myid            = (int)cmd.ExecuteScalar();
                Console.WriteLine("Next id={0}", myid);

                Console.WriteLine();
                reader.Close();

                DateTime dt = DateTime.Now;

                cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (@DoubleValue, @nombre, @photo, @date );SET @Id = IDENTITY();";
                cmd.Parameters.Add(new SharpHsqlParameter("@Id", DbType.Int32, 0, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Current, null));
                cmd.Parameters.Add(new SharpHsqlParameter("@DoubleValue", DbType.Double, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, 1.1));
                cmd.Parameters.Add(new SharpHsqlParameter("@nombre", DbType.String, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, "Andrés"));
                cmd.Parameters.Add(new SharpHsqlParameter("@photo", DbType.Binary, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, photo));
                cmd.Parameters.Add(new SharpHsqlParameter("@date", DbType.DateTime, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, dt));
                res = cmd.ExecuteNonQuery();
                SharpHsqlParameter p = (SharpHsqlParameter)cmd.Parameters["@Id"];
                myid = (int)p.Value;
                Console.WriteLine("Inserted id={0}", myid);
                Console.WriteLine();

                cmd.Parameters.Clear();
                cmd.CommandText = "SELECT \"clients\".\"created\" FROM \"clients\" WHERE \"clients\".\"id\" = " + myid + ";";
                reader          = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(String.Format("Dates are equal: {0}.", dt.Equals(reader.GetDateTime(0))));
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW DATABASES;";
                reader          = cmd.ExecuteReader();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.Write(reader.GetName(i));
                    Console.Write("\t");
                }
                Console.Write(Environment.NewLine);

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write(reader.GetValue(i).ToString());
                        Console.Write("\t");
                        Console.Write(Environment.NewLine);
                    }
                }

                Console.WriteLine();
                reader.Close();

                // Dataset Fill for SHOW DATABASES
                adapter = new SharpHsqlDataAdapter(cmd);
                ds      = new DataSet();
                res     = adapter.Fill(ds);
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("DATABASES: " + ds.Tables[0].Rows.Count);

                Console.WriteLine();

                cmd.CommandText = "SHOW TABLES;";
                reader          = cmd.ExecuteReader();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.Write(reader.GetName(i));
                    Console.Write("\t");
                }
                Console.Write(Environment.NewLine);

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write(reader.GetValue(i).ToString());
                        Console.Write("\t");
                        Console.Write(Environment.NewLine);
                    }
                }

                Console.WriteLine();
                reader.Close();

                // Dataset Fill for SHOW TABLES
                adapter = new SharpHsqlDataAdapter(cmd);
                ds      = new DataSet();
                res     = adapter.Fill(ds);
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("TABLES: " + ds.Tables[0].Rows.Count);

                Hashtable myData = new Hashtable();
                myData.Add("1", "ONE");
                myData.Add("2", "TWO");
                myData.Add("3", "TREE");
                myData.Add("4", "FOUR");
                myData.Add("5", "FIVE");

                cmd.Parameters.Clear();
                cmd.CommandText = "DELETE FROM \"data\" WHERE \"id\" = 1;";
                res             = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = "INSERT INTO \"data\" (\"id\", \"MyObject\") VALUES( @id, @MyObject);";
                cmd.Parameters.Add(new SharpHsqlParameter("@id", DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, 1));
                cmd.Parameters.Add(new SharpHsqlParameter("@MyObject", DbType.Object, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, myData));
                res = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();


                cmd.CommandText = "SELECT \"data\".\"id\", \"data\".\"MyObject\" FROM \"data\";";
                reader          = cmd.ExecuteReader();
                Console.Write(Environment.NewLine);

                int       myId     = 0;
                Hashtable readData = null;
                while (reader.Read())
                {
                    myId     = reader.GetInt32(0);
                    readData = (Hashtable)reader.GetValue(1);
                }

                foreach (DictionaryEntry entry in readData)
                {
                    Console.WriteLine(String.Format("Key: {0}, Value: {1}", entry.Key.ToString(), entry.Value.ToString()));
                }


                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW ALIAS;";
                reader          = cmd.ExecuteReader();

                Console.Write(Environment.NewLine);

                while (reader.Read())
                {
                    Console.WriteLine("ALIAS {0} FOR {1}", reader.GetString(0), reader.GetString(1));
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW PARAMETERS CALCRATE;";
                reader          = cmd.ExecuteReader();

                Console.Write(Environment.NewLine);

                while (reader.Read())
                {
                    Console.WriteLine("ALIAS: {0}, PARAM: {1},\t TYPE {2},\t POSITION: {3}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW COLUMNS \"clients\";";
                reader          = cmd.ExecuteReader();

                Console.Write(Environment.NewLine);

                while (reader.Read())
                {
                    Console.WriteLine("TABLE: {0}, COLUMN: {1},\n\t NATIVE TYPE: {2},\t DB TYPE: {3},\n\t POSITION: {4},\t NULLABLE: {5},\t IDENTITY: {6}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetValue(3), reader.GetInt32(4), reader.GetBoolean(5), reader.GetBoolean(6));
                }

                Console.WriteLine();
                reader.Close();
            }
            catch (SharpHsqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
                conn = null;
            }

            Console.WriteLine();
            Console.WriteLine("Press [ENTER] to exit.");
            Console.ReadLine();
        }
        /// <summary>
        /// Create and prepare a SharpHsqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        /// </summary>
        /// <remarks>
        /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
        /// 
        /// If the caller provided the connection, we want to leave it to them to manage.
        /// </remarks>
        /// <param name="connection">A valid SharpHsqlConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SharpHsqlTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of SharpHsqlParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SharpHsqlHelper</param>
        /// <returns>SharpHsqlReader containing the results of the command</returns>
        private static SharpHsqlReader ExecuteReader(SharpHsqlConnection connection, SharpHsqlTransaction transaction, CommandType commandType, string commandText, SharpHsqlParameter[] commandParameters, SharpHsqlConnectionOwnership connectionOwnership)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            SharpHsqlCommand cmd = new SharpHsqlCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

                // Create a reader
                SharpHsqlReader dataReader;

                // Call ExecuteReader with the appropriate CommandBehavior
                if (connectionOwnership == SharpHsqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // Detach the SharpHsqlParameters from the command object, so they can be used again.
                // HACK: There is a problem here, the output parameter values are fletched
                // when the reader is closed, so if the parameters are detached from the command
                // then the SharpHsqlReader can´t set its values.
                // When this happen, the parameters can´t be used again in other command.
                bool canClear = true;
                foreach(SharpHsqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if( mustCloseConnection )
                    connection.Close();
                throw;
            }
        }
        /// <summary>
        /// Execute a SharpHsqlCommand (that returns a resultset) against the specified SharpHsqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// string  r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SharpHsqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SharpHsqlConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
        /// <param name="commandParameters">An array of SharpHsqlParamters used to execute the command</param>
        /// <returns>An string containing the resultset generated by the command</returns>
        public static string ExecuteXml(SharpHsqlConnection connection, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            SharpHsqlCommand cmd = new SharpHsqlCommand();
            try
            {
                PrepareCommand(cmd, connection, (SharpHsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

                // Create the DataAdapter & DataSet
                SharpHsqlDataAdapter obj_Adapter =new SharpHsqlDataAdapter (cmd);
                DataSet ds=new DataSet();
                ds.Locale  =CultureInfo.InvariantCulture;
                obj_Adapter.Fill(ds);

                // Detach the SharpHsqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();
                string retval= ds.GetXml();
                 ds.Clear();
                 obj_Adapter.Dispose ();
                return retval;

            }
            catch
            {
                if( mustCloseConnection )
                    connection.Close();
                throw;
            }
        }
        /// <summary>
        /// Execute a SharpHsqlCommand (that returns a 1x1 resultset) against the specified SharpHsqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new SharpHsqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SharpHsqlConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of SharpHsqlParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(SharpHsqlConnection connection, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            // Create a command and prepare it for execution
            SharpHsqlCommand cmd = new SharpHsqlCommand();

            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SharpHsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

            // Execute the command & return the results
            object retval = cmd.ExecuteScalar();

            // Detach the SharpHsqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if( mustCloseConnection )
                connection.Close();

            return retval;
        }
        /// <summary>
        /// Execute a SharpHsqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SharpHsqlReader dr = ExecuteReader(connString, CommandType.Text, "Select Orderid from TableTransaction where ProdId=?", new SharpHsqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SharpHsqlConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of SharpHsqlParamters used to execute the command</param>
        /// <returns>A SharpHsqlReader containing the resultset generated by the command</returns>
        public static SharpHsqlReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
        {
            if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
            SharpHsqlConnection connection = null;
            try
            {
                connection = new SharpHsqlConnection(connectionString);

                // Call the private overload that takes an internally owned connection in place of the connection string
                return ExecuteReader(connection, null, commandType, commandText, commandParameters,SharpHsqlConnectionOwnership.Internal);
            }
            catch
            {
                // If we fail to return the SharpHsqlDatReader, we need to close the connection ourselves
                if( connection != null ) connection.Close();
                throw;
            }
        }
        /// <summary>
        /// Execute a SharpHsqlCommand (that returns a resultset) against the specified SharpHsqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SharpHsqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SharpHsqlConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of SharpHsqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(SharpHsqlConnection connection, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            // Create a command and prepare it for execution
            SharpHsqlCommand cmd = new SharpHsqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SharpHsqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

            // Create the DataAdapter & DataSet
            SharpHsqlDataAdapter da = new SharpHsqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            ds.Locale  =CultureInfo.InvariantCulture;

            // Fill the DataSet using default values for DataTable names, etc
            da.Fill(ds);

            // Detach the SharpHsqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if( mustCloseConnection )
                connection.Close();

            // Return the dataset
            return ds;
        }
        /// <summary>
        /// Private helper method that execute a SharpHsqlCommand (that returns a resultset) against the specified SharpHsqlTransaction and SharpHsqlConnection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new SharpHsqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SharpHsqlConnection</param>
        /// <param name="transaction">A valid SharpHsqlTransaction</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of SharpHsqlParamters used to execute the command</param>
        private static void FillDataset(SharpHsqlConnection connection, SharpHsqlTransaction transaction, CommandType commandType, 
			string commandText, DataSet dataSet, string[] tableNames,
			params SharpHsqlParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );
            if( dataSet == null ) throw new ArgumentNullException( "dataSet" );

            // Create a command and prepare it for execution
            SharpHsqlCommand command = new SharpHsqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

            // Create the DataAdapter & DataSet
            SharpHsqlDataAdapter dataAdapter = new SharpHsqlDataAdapter(command);

            try
            {
                // Add the table mappings specified by the user
                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int index=0; index < tableNames.Length; index++)
                    {
                        if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" );
                        dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                        tableName += (index + 1).ToString();
                    }
                }

                // Fill the DataSet using default values for DataTable names, etc
                dataAdapter.Fill(dataSet);

                // Detach the SharpHsqlParameters from the command object, so they can be used again
                command.Parameters.Clear();

                if( mustCloseConnection )
                    connection.Close();
            }
            finally
            {
                dataAdapter.Dispose();
            }
        }
Beispiel #10
0
        static void Main(string[] args)
        {
            SharpHsqlConnection conn = new SharpHsqlConnection("Initial Catalog=mytest;User Id=sa;Pwd=;");

            byte[] data = new byte[]{255,255,255,255,255,255,255,255,255,255};
            string base64photo = Convert.ToBase64String(data, 0, data.Length);

            try
            {
                conn.Open();

                SharpHsqlCommand cmd = new SharpHsqlCommand("", conn);

                int res;

                Console.Write("Create table (y/n)?");
                string create = Console.ReadLine();
                if( create.ToLower() == "y" )
                {
                    cmd.CommandText = "DROP TABLE IF EXIST \"data\";CREATE TABLE \"data\" (\"id\" int NOT NULL PRIMARY KEY, \"MyObject\" OBJECT);";
                    res = cmd.ExecuteNonQuery();

                    cmd.CommandText = "DROP TABLE IF EXIST \"clients\";CREATE TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char, \"photo\" varbinary, \"created\" date );";
                    res = cmd.ExecuteNonQuery();

                    SharpHsqlTransaction tran = conn.BeginTransaction();

                    cmd = new SharpHsqlCommand("", conn);

                    for(int i=0;i<10;i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, 'NOMBRE" + i.ToString() + "', '" + base64photo + "', NOW() );";
                        res = cmd.ExecuteNonQuery();
                        cmd.CommandText = "CALL IDENTITY();";
                        int id = (int)cmd.ExecuteScalar();
                        Console.WriteLine("Inserted id={0}", id );
                    }

                    cmd.CommandText = "DROP TABLE IF EXIST \"books\";CREATE TABLE \"books\" (\"id\" INT NOT NULL PRIMARY KEY, \"name\" char, \"author\" char, \"qty\" int, \"value\" numeric);";
                    res = cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO \"books\" VALUES (1, 'Book000', 'Any', 1, 23.5);";
                    res = cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO \"books\" VALUES (2, 'Book001', 'Andy', 2, 43.9);";
                    res = cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO \"books\" VALUES (3, 'Book002', 'Andy', 3, 37.25);";
                    res = cmd.ExecuteNonQuery();
                    tran.Commit();
                }

                Console.WriteLine();

                Console.Write("Do Bulk INSERTS (y/n)?");
                string bulk = Console.ReadLine();
                if( bulk.ToLower() == "y" )
                {
                    SharpHsqlTransaction tran = conn.BeginTransaction();

                    cmd = new SharpHsqlCommand("", conn);

                    for(int i=0;i<1000;i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, 'NOMBRE" + i.ToString() + "', '" + base64photo + "', NOW() );";
                        res = cmd.ExecuteNonQuery();
                    }

                    tran.Commit();

                    Console.WriteLine("Inserted 1000 new clients.");
                    Console.WriteLine();
                }

                cmd = new SharpHsqlCommand("", conn);

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\",  \"clients\".\"photo\", \"clients\".\"created\" FROM \"clients\" ORDER BY \"clients\".\"id\" ";
                IDataReader reader = cmd.ExecuteReader();

                byte[] photo = null;

                while( reader.Read() )
                {
                    long len = reader.GetBytes(3, 0, null, 0, 0);
                    photo = new byte[len];
                    reader.GetBytes(3, 0, photo, 0, (int)len);
                    Console.WriteLine("id={0}, doubleValue={1}, nombre={2}, photo={3}, created={4}", reader.GetInt32(0), reader.GetDouble(1), reader.GetString(2), photo.Length, reader.GetDateTime(4).ToString("yyyy.MM.dd hh:mm:ss.fffffff") );
                }

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT * FROM \"books\"";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("id={0}book={1},\tauthor={2},\tqty={3},\tvalue={4}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(4) );
                }

                Console.WriteLine();

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT * FROM \"books\" ORDER BY \"value\"";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("id={0}book={1},\tauthor={2},\tqty={3},\tvalue={4}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(4) );
                }

                Console.WriteLine();

                reader.Close();

                Console.WriteLine();

                cmd.CommandText = "SELECT COUNT(*) as CNT, SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy'";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("count={0},\tvalue={1}", reader.GetInt32(0), reader.GetDecimal(1) );
                }

                Console.WriteLine();

                reader.Close();

                cmd.CommandText = "SELECT \"name\", \"author\", SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy' GROUP BY \"name\", \"author\";";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("name={0},\tauthor={1},\tvalue={2}", reader.GetString(0), reader.GetString(1), reader.GetDecimal(2) );
                }

                Console.WriteLine();

                reader.Close();

                cmd.CommandText = "SELECT \"name\", SUM(\"value\") FROM \"books\" WHERE \"author\" = 'Andy' GROUP BY \"name\";";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("name={0},\tvalue={1}", reader.GetString(0), reader.GetDecimal(1) );
                }

                Console.WriteLine();

                reader.Close();
                cmd.CommandText = "DELETE FROM \"clients\" WHERE \"clients\".\"id\" = 6;";
                res = cmd.ExecuteNonQuery();

                Console.WriteLine();

                cmd.CommandText = "SELECT MAX(\"clients\".\"id\") FROM \"clients\";";
                object result = cmd.ExecuteScalar();
                if( result != null )
                {
                    res = (int)result;
                    Console.WriteLine("MAX=" + res);
                }

                cmd.CommandText = "SELECT SUM(\"clients\".\"id\") FROM \"clients\";";
                result = cmd.ExecuteScalar();
                if( result != null )
                {
                    res = (int)result;
                    Console.WriteLine("SUM=" + res);
                }

                cmd.CommandText = "SELECT COUNT(\"clients\".\"id\") FROM \"clients\";";
                result = cmd.ExecuteScalar();
                if( result != null )
                {
                    res = (int)result;
                    Console.WriteLine("COUNT=" + res);
                }

                cmd.CommandText = "SELECT AVG(\"clients\".\"id\") FROM \"clients\";";
                result = cmd.ExecuteScalar();
                if( result != null )
                {
                    res = (int)result;
                    Console.WriteLine("AVG=" + res);
                }

                cmd.CommandText = "CALL ABS(-33.5632);";
                result = cmd.ExecuteScalar();
                if( result != null )
                {
                    Double abs = (Double)result;
                    Console.WriteLine("ABS=" + abs);
                }

                cmd.CommandText = "CREATE ALIAS CALCRATE FOR \"ExternalFunction,ExternalFunction.Simple.calcrate\";";
                res = cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE ALIAS EXTTAN FOR \"ExternalFunction,ExternalFunction.Simple.tan\";";
                res = cmd.ExecuteNonQuery();

                cmd.CommandText = "CALL CALCRATE(100, 21);";
                Decimal rate = (Decimal)cmd.ExecuteScalar();
                Console.WriteLine("CALCRATE=" + rate);

                cmd.CommandText = "CALL EXTTAN(23.456);";
                Double tan = (Double)cmd.ExecuteScalar();
                Console.WriteLine("EXTTAN=" + tan);

                cmd.CommandText = "CALL SQRT(3);";
                Double sqrt = (Double)cmd.ExecuteScalar();
                Console.WriteLine("SQRT=" + sqrt);

                cmd.CommandText = "CALL SUBSTRING('0123456', 3, 2);";
                string subs = (String)cmd.ExecuteScalar();
                Console.WriteLine("SUBSTRING=" + subs);

                cmd.CommandText = "CALL ASCII('A');";
                int ascii = (int)cmd.ExecuteScalar();
                Console.WriteLine("ASCII=" + ascii);

                cmd.CommandText = "CALL USER();";
                string user = (string)cmd.ExecuteScalar();
                Console.WriteLine("USER="******"SELECT \"clients\".\"photo\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";
                byte[] b = (byte[])cmd.ExecuteScalar();

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";

                SharpHsqlDataAdapter adapter = new SharpHsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                res = adapter.Fill( ds );
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("DataSet.Fill: " + ds.Tables[0].Rows.Count);

                cmd.CommandText = "DECLARE @MyVar CHAR;SET @MyVar = 'Andy';";
                cmd.ExecuteNonQuery();

                Console.WriteLine();
                cmd.CommandText = "SELECT @MyVar;";
                string var = (string)cmd.ExecuteScalar();
                Console.WriteLine("@MyVar=" + var);

                Console.WriteLine();

                cmd.CommandText = "SELECT \"name\", \"author\", SUM(\"value\") FROM \"books\" WHERE \"author\" = @MyVar GROUP BY \"name\", \"author\";";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine("name={0},\tauthor={1},\tvalue={2}", reader.GetString(0), reader.GetString(1), reader.GetDecimal(2) );
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (1.1, @MyVar, '" + base64photo + "', NOW() );";
                res = cmd.ExecuteNonQuery();
                cmd.CommandText = "DECLARE @MyId INT;SET @MyId = IDENTITY();";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @MyId;";
                int myid = (int)cmd.ExecuteScalar();
                Console.WriteLine("Inserted id={0}", myid );

                Console.WriteLine();

                cmd.CommandText = "SET @MyId = SELECT MAX(\"clients\".\"id\") + 1 FROM \"clients\";";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @MyId;";
                myid = (int)cmd.ExecuteScalar();
                Console.WriteLine("Next id={0}", myid );

                Console.WriteLine();
                reader.Close();

                DateTime dt = DateTime.Now;

                cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\", \"photo\", \"created\") VALUES (@DoubleValue, @nombre, @photo, @date );SET @Id = IDENTITY();";
                cmd.Parameters.Add( new SharpHsqlParameter("@Id", DbType.Int32, 0, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Current, null) );
                cmd.Parameters.Add( new SharpHsqlParameter("@DoubleValue", DbType.Double, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, 1.1) );
                cmd.Parameters.Add( new SharpHsqlParameter("@nombre", DbType.String, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, "Andrés") );
                cmd.Parameters.Add( new SharpHsqlParameter("@photo", DbType.Binary, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, photo) );
                cmd.Parameters.Add( new SharpHsqlParameter("@date", DbType.DateTime, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, dt) );
                res = cmd.ExecuteNonQuery();
                SharpHsqlParameter p = (SharpHsqlParameter)cmd.Parameters["@Id"];
                myid = (int)p.Value;
                Console.WriteLine("Inserted id={0}", myid );
                Console.WriteLine();

                cmd.Parameters.Clear();
                cmd.CommandText = "SELECT \"clients\".\"created\" FROM \"clients\" WHERE \"clients\".\"id\" = " + myid + ";";
                reader = cmd.ExecuteReader();

                while( reader.Read() )
                {
                    Console.WriteLine( String.Format("Dates are equal: {0}.", dt.Equals( reader.GetDateTime(0) ) ) );
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW DATABASES;";
                reader = cmd.ExecuteReader();

                for( int i=0;i<reader.FieldCount;i++)
                {
                    Console.Write( reader.GetName(i)  );
                    Console.Write( "\t"  );
                }
                Console.Write( Environment.NewLine  );

                while( reader.Read() )
                {
                    for( int i=0;i<reader.FieldCount;i++)
                    {
                        Console.Write( reader.GetValue(i).ToString()  );
                        Console.Write( "\t"  );
                        Console.Write( Environment.NewLine  );
                    }
                }

                Console.WriteLine();
                reader.Close();

                // Dataset Fill for SHOW DATABASES
                adapter = new SharpHsqlDataAdapter(cmd);
                ds = new DataSet();
                res = adapter.Fill( ds );
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("DATABASES: " + ds.Tables[0].Rows.Count);

                Console.WriteLine();

                cmd.CommandText = "SHOW TABLES;";
                reader = cmd.ExecuteReader();

                for( int i=0;i<reader.FieldCount;i++)
                {
                    Console.Write( reader.GetName(i)  );
                    Console.Write( "\t"  );
                }
                Console.Write( Environment.NewLine  );

                while( reader.Read() )
                {
                    for( int i=0;i<reader.FieldCount;i++)
                    {
                        Console.Write( reader.GetValue(i).ToString()  );
                        Console.Write( "\t"  );
                        Console.Write( Environment.NewLine  );
                    }
                }

                Console.WriteLine();
                reader.Close();

                // Dataset Fill for SHOW TABLES
                adapter = new SharpHsqlDataAdapter(cmd);
                ds = new DataSet();
                res = adapter.Fill( ds );
                adapter = null;

                Console.WriteLine();
                Console.WriteLine("TABLES: " + ds.Tables[0].Rows.Count);

                Hashtable myData = new Hashtable();
                myData.Add( "1", "ONE" );
                myData.Add( "2", "TWO" );
                myData.Add( "3", "TREE" );
                myData.Add( "4", "FOUR" );
                myData.Add( "5", "FIVE" );

                cmd.Parameters.Clear();
                cmd.CommandText = "DELETE FROM \"data\" WHERE \"id\" = 1;";
                res = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = "INSERT INTO \"data\" (\"id\", \"MyObject\") VALUES( @id, @MyObject);";
                cmd.Parameters.Add( new SharpHsqlParameter("@id", DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, 1) );
                cmd.Parameters.Add( new SharpHsqlParameter("@MyObject", DbType.Object, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, myData) );
                res = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                cmd.CommandText = "SELECT \"data\".\"id\", \"data\".\"MyObject\" FROM \"data\";";
                reader = cmd.ExecuteReader();
                Console.Write( Environment.NewLine  );

                int myId = 0;
                Hashtable readData = null;
                while( reader.Read() )
                {
                    myId = reader.GetInt32(0);
                    readData = (Hashtable)reader.GetValue(1);
                }

                foreach( DictionaryEntry entry in readData )
                {
                    Console.WriteLine( String.Format("Key: {0}, Value: {1}", entry.Key.ToString(), entry.Value.ToString() ) );
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW ALIAS;";
                reader = cmd.ExecuteReader();

                Console.Write( Environment.NewLine  );

                while( reader.Read() )
                {
                    Console.WriteLine("ALIAS {0} FOR {1}", reader.GetString(0), reader.GetString(1)  );
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW PARAMETERS CALCRATE;";
                reader = cmd.ExecuteReader();

                Console.Write( Environment.NewLine  );

                while( reader.Read() )
                {
                    Console.WriteLine("ALIAS: {0}, PARAM: {1},\t TYPE {2},\t POSITION: {3}", reader.GetString(0), reader.GetString(1), reader.GetString(2),  reader.GetInt32(3)  );
                }

                Console.WriteLine();
                reader.Close();

                cmd.CommandText = "SHOW COLUMNS \"clients\";";
                reader = cmd.ExecuteReader();

                Console.Write( Environment.NewLine  );

                while( reader.Read() )
                {
                    Console.WriteLine("TABLE: {0}, COLUMN: {1},\n\t NATIVE TYPE: {2},\t DB TYPE: {3},\n\t POSITION: {4},\t NULLABLE: {5},\t IDENTITY: {6}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetValue(3),  reader.GetInt32(4), reader.GetBoolean(5), reader.GetBoolean(6) );
                }

                Console.WriteLine();
                reader.Close();
            }
            catch( SharpHsqlException  ex )
            {
                Console.WriteLine(ex.Message);
            }
            catch( Exception e )
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
                conn = null;
            }

            Console.WriteLine();
            Console.WriteLine("Press [ENTER] to exit.");
            Console.ReadLine();
        }
Beispiel #11
0
        private void DoTest()
        {
            try
            {
                txtResult.Text = "Test started...";

                //System.Diagnostics.Debugger.Launch();

                SharpHsqlConnection conn = new SharpHsqlConnection("Initial Catalog=\\program files\\pocketSample\\mytest;User Id=sa;Pwd=;");
                conn.Open();

                SharpHsqlTransaction tran = conn.BeginTransaction();

                SharpHsqlCommand cmd = new SharpHsqlCommand("", conn);

                int res;

                if( MessageBox.Show( "Drop & create 'clients' table?", "Drop Table", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2 ) == DialogResult.Yes )
                {

                    txtResult.Text += "\r\nDropping clients table...";

                    cmd.CommandText = "DROP TABLE IF EXIST \"clients\";CREATE CACHED TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char);";
                    res = cmd.ExecuteNonQuery();

                    for(int i=0;i<10;i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\") VALUES (1.1, 'NOMBRE" + i.ToString() + "');";
                        res = cmd.ExecuteNonQuery();
                        cmd.CommandText = "CALL IDENTITY();";
                        int id = (int)cmd.ExecuteScalar();
                        txtResult.Text += String.Format("\r\nInserted id={0}", id );
                    }
                }

                if( MessageBox.Show( "Bulk INSERT 'clients' table?", "Insert Table", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2 ) == DialogResult.Yes )
                {

                    txtResult.Text += "\r\nBulk Insert clients table...";

                    for(int i=0;i<1000;i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\") VALUES (1.1, 'NOMBRE" + i.ToString() + "');";
                        res = cmd.ExecuteNonQuery();
                    }

                    txtResult.Text += "\r\nInserted 1000 rows.";
                }

                txtResult.Text += "\r\nSelecting rows...";

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\"";
                IDataReader reader = cmd.ExecuteReader();

                string row = "";
                int count = 0;

                while( reader.Read() )
                {
                    count++;
                    row = String.Format("id={0}, doubleValue={1}, nombre={2}", reader.GetInt32(0), reader.GetDouble(1), reader.GetString(2) );
                }

                txtResult.Text += String.Format("\r\nSelected {0} rows.", count);
                txtResult.Text += String.Format("\r\nLast row: \r\n{0}", row);

                reader.Close();

                tran.Commit();
                //tran.Rollback();

                cmd.CommandText = "DELETE FROM \"clients\" WHERE \"clients\".\"id\" = 5;";
                res = cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT MAX(\"clients\".\"id\") FROM \"clients\";";
                res = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nMAX=" + res;

                cmd.CommandText = "SELECT SUM(\"clients\".\"id\") FROM \"clients\";";
                res = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSUM=" + res;

                cmd.CommandText = "SELECT COUNT(\"clients\".\"id\") FROM \"clients\";";
                res = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nCOUNT=" + res;

                cmd.CommandText = "SELECT AVG(\"clients\".\"id\") FROM \"clients\";";
                res = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nAVG=" + res;

                cmd.CommandText = "CALL ABS(-33.5632);";
                Double abs = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nABS=" + abs;

                cmd.CommandText = "CREATE ALIAS CALCRATE FOR \"ExternalFunction,ExternalFunction.Simple.calcrate\";";
                res = cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE ALIAS EXTTAN FOR \"ExternalFunction,ExternalFunction.Simple.tan\";";
                res = cmd.ExecuteNonQuery();

                cmd.CommandText = "CALL CALCRATE(100, 21);";
                Decimal rate = (Decimal)cmd.ExecuteScalar();
                txtResult.Text += "\r\nCALCRATE=" + rate;

                cmd.CommandText = "CALL EXTTAN(23.456);";
                Double tan = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nEXTTAN=" + tan;

                cmd.CommandText = "CALL SQRT(3);";
                Double sqrt = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSQRT=" + sqrt;

                cmd.CommandText = "CALL SUBSTRING('0123456', 3, 2);";
                string subs = (String)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSUBSTRING=" + subs;

                cmd.CommandText = "CALL ASCII('A');";
                int ascii = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nASCII=" + ascii;

                cmd.CommandText = "CALL USER();";
                string user = (string)cmd.ExecuteScalar();
                txtResult.Text += "\r\nUSER="******"SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";

                SharpHsqlDataAdapter adapter = new SharpHsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                res = adapter.Fill( ds );
                adapter = null;

                txtResult.Text += "\r\nDataSet.Fill: " + ds.Tables[0].Rows.Count;

                conn.Close();
                conn = null;
            }
            catch( SharpHsqlException  ex )
            {
                txtResult.Text += "\r\nERROR: " + ex.Message;
            }
            catch( Exception ex )
            {
                txtResult.Text += "\r\nERROR: " + ex.Message;
            }
        }
Beispiel #12
0
        protected SharpHsqlConnection GenerateTestDatabase(DataSet dbPrototype)
        {
            var tempDirectory = Path.Combine(Directory.GetCurrentDirectory(), ".tests", Path.GetRandomFileName());

            Directory.CreateDirectory(tempDirectory);
            var connection = new SharpHsqlConnection(String.Format("Initial Catalog={0}/{1};User Id=sa;Pwd=;", tempDirectory, dbPrototype.DataSetName));

            try {
                connection.Open();
                foreach (var table in dbPrototype.Tables.Cast <DataTable>())
                {
                    var createTableQueryBuilder = new StringBuilder();
                    createTableQueryBuilder.AppendFormat("DROP TABLE IF EXIST {0};", table.TableName);
                    createTableQueryBuilder.AppendFormat("CREATE TABLE {0} (", table.TableName);

                    var counter = 0;
                    foreach (var column in table.Columns.Cast <DataColumn>())
                    {
                        var columnDescription = column.ColumnName + " " + GetDbType(column.DataType);

                        if (!column.AllowDBNull)
                        {
                            columnDescription += " NOT NULL";
                        }

                        if (table.PrimaryKey.Contains(column))
                        {
                            columnDescription += " PRIMARY KEY";
                        }

                        createTableQueryBuilder.Append(columnDescription);
                        if (counter < table.Columns.Count - 1)
                        {
                            createTableQueryBuilder.Append(",");
                        }

                        counter += 1;
                    }

                    createTableQueryBuilder.Append(");");

                    var cmd = new SharpHsqlCommand(createTableQueryBuilder.ToString(), connection);
                    cmd.ExecuteNonQuery();

                    var tran = connection.BeginTransaction();
                    {
                        foreach (var row in table.Rows.Cast <DataRow>())
                        {
                            var rowInsertQuery = "INSERT INTO " + table.TableName + "(";
                            var columnIndex    = 0;
                            foreach (var column in table.Columns.Cast <DataColumn>())
                            {
                                rowInsertQuery += column.ColumnName;
                                if (columnIndex < table.Columns.Count - 1)
                                {
                                    rowInsertQuery += ",";
                                }

                                columnIndex += 1;
                            }

                            rowInsertQuery += ") VALUES (";
                            columnIndex     = 0;
                            foreach (var column in table.Columns.Cast <DataColumn>())
                            {
                                rowInsertQuery += FormatValue(column.DataType, row[column]);
                                if (columnIndex < table.Columns.Count - 1)
                                {
                                    rowInsertQuery += ",";
                                }

                                columnIndex += 1;
                            }

                            rowInsertQuery += ");";

                            var insertCommand = new SharpHsqlCommand(rowInsertQuery, connection);
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                }

                return(connection);
            }
            finally {
                connection.Close();
            }
        }
Beispiel #13
0
        private void DoTest()
        {
            try
            {
                txtResult.Text = "Test started...";

                //System.Diagnostics.Debugger.Launch();

                SharpHsqlConnection conn = new SharpHsqlConnection("Initial Catalog=\\program files\\pocketSample\\mytest;User Id=sa;Pwd=;");
                conn.Open();

                SharpHsqlTransaction tran = conn.BeginTransaction();

                SharpHsqlCommand cmd = new SharpHsqlCommand("", conn);

                int res;

                if (MessageBox.Show("Drop & create 'clients' table?", "Drop Table", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                {
                    txtResult.Text += "\r\nDropping clients table...";

                    cmd.CommandText = "DROP TABLE IF EXIST \"clients\";CREATE CACHED TABLE \"clients\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char);";
                    res             = cmd.ExecuteNonQuery();

                    for (int i = 0; i < 10; i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\") VALUES (1.1, 'NOMBRE" + i.ToString() + "');";
                        res             = cmd.ExecuteNonQuery();
                        cmd.CommandText = "CALL IDENTITY();";
                        int id = (int)cmd.ExecuteScalar();
                        txtResult.Text += String.Format("\r\nInserted id={0}", id);
                    }
                }

                if (MessageBox.Show("Bulk INSERT 'clients' table?", "Insert Table", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                {
                    txtResult.Text += "\r\nBulk Insert clients table...";

                    for (int i = 0; i < 1000; i++)
                    {
                        cmd.CommandText = "INSERT INTO \"clients\" (\"DoubleValue\", \"nombre\") VALUES (1.1, 'NOMBRE" + i.ToString() + "');";
                        res             = cmd.ExecuteNonQuery();
                    }

                    txtResult.Text += "\r\nInserted 1000 rows.";
                }

                txtResult.Text += "\r\nSelecting rows...";

                cmd.CommandText = "SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\"";
                IDataReader reader = cmd.ExecuteReader();

                string row   = "";
                int    count = 0;

                while (reader.Read())
                {
                    count++;
                    row = String.Format("id={0}, doubleValue={1}, nombre={2}", reader.GetInt32(0), reader.GetDouble(1), reader.GetString(2));
                }

                txtResult.Text += String.Format("\r\nSelected {0} rows.", count);
                txtResult.Text += String.Format("\r\nLast row: \r\n{0}", row);

                reader.Close();

                tran.Commit();
                //tran.Rollback();

                cmd.CommandText = "DELETE FROM \"clients\" WHERE \"clients\".\"id\" = 5;";
                res             = cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT MAX(\"clients\".\"id\") FROM \"clients\";";
                res             = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nMAX=" + res;

                cmd.CommandText = "SELECT SUM(\"clients\".\"id\") FROM \"clients\";";
                res             = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSUM=" + res;

                cmd.CommandText = "SELECT COUNT(\"clients\".\"id\") FROM \"clients\";";
                res             = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nCOUNT=" + res;

                cmd.CommandText = "SELECT AVG(\"clients\".\"id\") FROM \"clients\";";
                res             = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nAVG=" + res;

                cmd.CommandText = "CALL ABS(-33.5632);";
                Double abs = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nABS=" + abs;

                cmd.CommandText = "CREATE ALIAS CALCRATE FOR \"ExternalFunction,ExternalFunction.Simple.calcrate\";";
                res             = cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE ALIAS EXTTAN FOR \"ExternalFunction,ExternalFunction.Simple.tan\";";
                res             = cmd.ExecuteNonQuery();

                cmd.CommandText = "CALL CALCRATE(100, 21);";
                Decimal rate = (Decimal)cmd.ExecuteScalar();
                txtResult.Text += "\r\nCALCRATE=" + rate;

                cmd.CommandText = "CALL EXTTAN(23.456);";
                Double tan = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nEXTTAN=" + tan;

                cmd.CommandText = "CALL SQRT(3);";
                Double sqrt = (Double)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSQRT=" + sqrt;

                cmd.CommandText = "CALL SUBSTRING('0123456', 3, 2);";
                string subs = (String)cmd.ExecuteScalar();
                txtResult.Text += "\r\nSUBSTRING=" + subs;

                cmd.CommandText = "CALL ASCII('A');";
                int ascii = (int)cmd.ExecuteScalar();
                txtResult.Text += "\r\nASCII=" + ascii;

                cmd.CommandText = "CALL USER();";
                string user = (string)cmd.ExecuteScalar();
                txtResult.Text += "\r\nUSER="******"SELECT \"clients\".\"id\", \"clients\".\"DoubleValue\", \"clients\".\"nombre\" FROM \"clients\" WHERE \"clients\".\"id\" = 5;";

                SharpHsqlDataAdapter adapter = new SharpHsqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                res     = adapter.Fill(ds);
                adapter = null;

                txtResult.Text += "\r\nDataSet.Fill: " + ds.Tables[0].Rows.Count;

                conn.Close();
                conn = null;
            }
            catch (SharpHsqlException ex)
            {
                txtResult.Text += "\r\nERROR: " + ex.Message;
            }
            catch (Exception ex)
            {
                txtResult.Text += "\r\nERROR: " + ex.Message;
            }
        }