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