/// <summary>
/// Creates temporary tables on the connection so schema information can be queried
/// </summary>
/// <remarks>
/// There's a lot of work involved in getting schema information out of SharpHsql, but LINQ expects to
/// be able to query on schema tables. Therefore we need to "fake" it by generating temporary tables
/// filled with the schema of the current connection. We get away with making this information static
/// because schema information seems to always be queried on a new connection object, so the schema is
/// always fresh.
/// </remarks>
/// <param name="cnn">The connection upon which to build the schema tables</param>
void ISharpHsqlSchemaExtensions.BuildTempSchema(SharpHsqlConnection cnn)
{
string[] arr = new string[] { "TABLES", "COLUMNS", "VIEWS", "VIEWCOLUMNS", "INDEXES", "INDEXCOLUMNS", "FOREIGNKEYS", "CATALOGS" };
using (DataTable table = cnn.GetSchema("Tables", new string[] { "temp", null, String.Format("SCHEMA{0}", arr[0]) }))
{
if (table.Rows.Count > 0)
{
return;
}
}
for (int n = 0; n < arr.Length; n++)
{
using (DataTable table = cnn.GetSchema(arr[n]))
{
DataTableToTable(cnn, table, String.Format("SCHEMA{0}", arr[n]));
}
}
using (SharpHsqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTS;
cmd.ExecuteNonQuery();
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTCOLUMNS;
cmd.ExecuteNonQuery();
}
}
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();
}
}
private void PrepareVariables(SharpHsqlConnection connection)
{
var cmd = new SharpHsqlCommand("", connection);
cmd.CommandText = "DECLARE @MyVar CHAR;SET @MyVar = 'Andy';";
cmd.ExecuteNonQuery();
}
/// <summary>
/// Creates temporary tables on the connection so schema information can be queried
/// </summary>
/// <remarks>
/// There's a lot of work involved in getting schema information out of SharpHsql, but LINQ expects to
/// be able to query on schema tables. Therefore we need to "fake" it by generating temporary tables
/// filled with the schema of the current connection. We get away with making this information static
/// because schema information seems to always be queried on a new connection object, so the schema is
/// always fresh.
/// </remarks>
/// <param name="cnn">The connection upon which to build the schema tables</param>
void ISharpHsqlSchemaExtensions.BuildTempSchema(SharpHsqlConnection cnn)
{
string[] arr = new string[] { "TABLES", "COLUMNS", "VIEWS", "VIEWCOLUMNS", "INDEXES", "INDEXCOLUMNS", "FOREIGNKEYS", "CATALOGS" };
using (DataTable table = cnn.GetSchema("Tables", new string[] { "temp", null, String.Format("SCHEMA{0}", arr[0]) }))
{
if (table.Rows.Count > 0) return;
}
for (int n = 0; n < arr.Length; n++)
{
using (DataTable table = cnn.GetSchema(arr[n]))
{
DataTableToTable(cnn, table, String.Format("SCHEMA{0}", arr[n]));
}
}
using (SharpHsqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTS;
cmd.ExecuteNonQuery();
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTCOLUMNS;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Simplify the creation of a SharpHsql command object by allowing
/// a CommandType and Command Text to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// SharpHsqlCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
/// </remarks>
/// <param name="connection">A valid SharpHsqlConnection object</param>
/// <param name="commandType">CommandType (TableDirect, Text)</param>
/// <param name="commandText">CommandText</param>
/// <returns>A valid SharpHsqlCommand object</returns>
public static SharpHsqlCommand CreateCommand(SharpHsqlConnection connection, CommandType commandType, string commandText )
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");
// If we receive parameter values, we need to figure out where they go
if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );
// Create a SharpHsqlCommand
SharpHsqlCommand cmd = new SharpHsqlCommand(commandText, connection );
cmd.CommandType = CommandType.Text ;
return cmd;
}
//------------------------------------- Private -------------------------------------------------------
string createMainDb()
{
try {
using (var _conn = new SharpHsqlConnection("Initial Catalog=.;User Id=sa;Pwd=;")) {
_conn.Open();
//-- drop the table if it already exist, and then create new:
var _cmd = new SharpHsqlCommand("", _conn);
_cmd.CommandText = "DROP TABLE IF EXIST \"CardInventory\";";
_cmd.CommandText += "CREATE CACHED TABLE \"CardInventory\" (\"pan\" BIGINT NOT NULL PRIMARY KEY);";
_cmd.ExecuteNonQuery();
}
}
catch (Exception _ex) {
return(_ex.Message);
}
return("Main Db created");
}
protected override string GetDbProviderManifestToken(DbConnection connection)
{
if (String.IsNullOrEmpty(connection.ConnectionString))
{
throw new ArgumentNullException("ConnectionString");
}
bool parseViaFramework = false;
if (connection is SharpHsqlConnection)
{
parseViaFramework = ((SharpHsqlConnection)connection).ParseViaFramework;
}
SortedList <string, string> opts = parseViaFramework ?
SharpHsqlConnection.ParseConnectionStringViaFramework(connection.ConnectionString, false) :
SharpHsqlConnection.ParseConnectionString(connection.ConnectionString);
return(SharpHsqlConnection.FindKey(opts, "DateTimeFormat", "ISO8601"));
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SharpHsqlConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SharpHsqlCommandBuilder builder = new SharpHsqlCommandBuilder();
using (SharpHsqlCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SharpHsqlConvert.TypeToDbType(dc.DataType);
string typeName = SharpHsqlConvert.DbTypeToTypeName(dbtypeName);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SharpHsqlDataAdapter adp = new SharpHsqlDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
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();
}
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a resultset and takes no parameters) against the provided SharpHsqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, CommandType.Text, "Select * from TableTransaction", ds, new string[] {"orders"});
/// </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="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>
public static void FillDataset(SharpHsqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.Text, "Select count(Order) 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>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
// Create & open a SharpHsqlConnection, and dispose of it after we are done
using (SharpHsqlConnection connection = new SharpHsqlConnection(connectionString))
{
// Call the overload that takes a connection in place of the connection string
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a resultset) against the specified SharpHsqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SharpHsqlReader dr = ExecuteReader(conn, CommandType.Text, "Select Orderid 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 SharpHsqlReader containing the resultset generated by the command</returns>
public static SharpHsqlReader ExecuteReader(SharpHsqlConnection connection, CommandType commandType, string commandText, params SharpHsqlParameter[] commandParameters)
{
// Pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection, (SharpHsqlTransaction)null, commandType, commandText, commandParameters, SharpHsqlConnectionOwnership.External);
}
/// <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>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
/// </summary>
/// <param name="command">The SharpHsqlCommand to be prepared</param>
/// <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="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
private static void PrepareCommand(SharpHsqlCommand command, SharpHsqlConnection connection, SharpHsqlTransaction transaction, CommandType commandType, string commandText, SharpHsqlParameter[] commandParameters, out bool mustCloseConnection )
{
if( command == null ) throw new ArgumentNullException( "command" );
if(commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
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;
}
}
/// <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;
}
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();
}
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a resultset and takes no parameters) against the provided SharpHsqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction");
/// </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>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(SharpHsqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SharpHsqlParameters
return ExecuteXml(connection, commandType, commandText, (SharpHsqlParameter[])null);
}
public void LoadContext(object sender, DoWorkEventArgs pArg)
{
var bwLoadDb = sender as BackgroundWorker;
Debug.Assert(bwLoadDb != null);
pArg.Result = true;
pArg.Cancel = false;
int _counter = 0;
const int _dupsCounter = 0;
if (Directory.Exists(serviceFolder))
{
foreach (string _lotFolder in Directory.GetDirectories(serviceFolder))
{
string[] _files = Directory.GetFiles(_lotFolder, "*.txt");
foreach (string _fileName in _files)
{
bwLoadDb.ReportStatus(string.Format("Loaded File: {0}", _fileName.Substring(_fileName.LastIndexOf(@"\") + 1)));
if (bwLoadDb.CancellationPending)
{
pArg.Cancel = true;
return;
}
try {
using (var _conn = new SharpHsqlConnection("Initial Catalog=.;User Id=sa;Pwd=;")) {
_conn.Open();
using (SharpHsqlTransaction _tran = _conn.BeginTransaction()) {
var _cmd = new SharpHsqlCommand("", _conn);
using (StreamReader _sr = File.OpenText(Path.Combine(_lotFolder, _fileName))) {
try {
string _input;
while ((_input = _sr.ReadLine()) != null)
{
if (bwLoadDb.CancellationPending)
{
pArg.Cancel = true;
throw new Exception("Canceled");
}
if (++_counter % 1000 == 0)
{
bwLoadDb.ReportProgress(_counter);
}
if (!char.IsDigit(_input[0]))
{
bwLoadDb.ReportStatus(string.Format("Not number ?: {0} file: {1}", _input[0], _fileName));
throw new Exception("Serial NOT a number");
}
string[] _fields = _input.Split(InventoryController.FieldDelimiter);
if (_fields[1].Length != pinLength)
{
bwLoadDb.ReportStatus(string.Format("Invalid PIN Length: {0} file: {1}", _input[0], _fileName));
throw new Exception("Invalid PIN Length");
}
long _pan;
long.TryParse(_fields[1], out _pan);
if (_pan == 0)
{
bwLoadDb.ReportStatus(string.Format("PIN Not a number? Pin: {0}, file: {1}", _fields[1], _fileName));
throw new Exception("PIN NOT a number");
}
try {
_cmd.CommandText = "INSERT INTO \"CardInventory\" (\"pan\") VALUES (" + _pan + ");";
_cmd.ExecuteNonQuery();
}
catch (Exception _ex) {
bwLoadDb.ReportStatus(String.Format("INSERT Exception: {0}", _ex.Message));
throw;
}
}
}
catch {
_tran.Rollback();
throw;
}
_tran.Commit();
}
}
}
}
catch (Exception _ex) {
pArg.Result = false;
bwLoadDb.ReportStatus("Exception Processing file: " + _fileName + " [" + _ex.Message + "]");
break;
}
}
}
}
bwLoadDb.ReportStatus("Total Scaned: " + _counter + " Dups: " + _dupsCounter + " Loaded: " + (_counter - _dupsCounter));
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(connString, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, 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>
/// <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>
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SharpHsqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
// Create & open a SharpHsqlConnection, and dispose of it after we are done
using (SharpHsqlConnection connection = new SharpHsqlConnection(connectionString))
{
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SharpHsqlConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SharpHsqlCommandBuilder builder = new SharpHsqlCommandBuilder();
using (SharpHsqlCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SharpHsqlConvert.TypeToDbType(dc.DataType);
string typeName = SharpHsqlConvert.DbTypeToTypeName(dbtypeName);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SharpHsqlDataAdapter adp = new SharpHsqlDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
/// <summary>
/// Execute a SharpHsqlCommand (that returns a resultset) against the specified SharpHsqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, 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="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>
public static void FillDataset(SharpHsqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SharpHsqlParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
/// <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();
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();
}
protected bool generateBatch(CancelEventArgs pEvtArg, BackgroundWorker pBWGenCards, int pBatchId, int pBatchSize, int pPinLength, out string[] pCardArray)
{
var _result = false;
pCardArray = new string[pBatchSize];
var _cardArrayIndex = 0;
var _numberOfGeneratedCards = 0;
try {
using (var _conn = new SharpHsqlConnection("Initial Catalog=.;User Id=sa;Pwd=;")) {
_conn.Open();
SharpHsqlTransaction _tran = _conn.BeginTransaction();
var _cmd = new SharpHsqlCommand("", _conn);
int _dupsCounter = 0;
try {
while (_numberOfGeneratedCards < pBatchSize)
{
if (pBWGenCards.CancellationPending)
{
pEvtArg.Cancel = true;
_tran.Rollback();
return(false);
}
long _pin = createRandomNumber(pPinLength);
string _cardNumber = _pin.ToString("d" + pPinLength);
if (!isValidCheckDigit(_cardNumber))
{
continue;
}
try {
_cmd.CommandText = "INSERT INTO \"CardInventory\" (\"pan\") VALUES (" + _pin + ");";
_cmd.ExecuteNonQuery();
}
catch (Exception _ex) {
if (_ex.Message.IndexOf("23000") == 0)
{
if (++_dupsCounter % 100 == 0)
{
pBWGenCards.ReportStatus("Dups: " + (-_dupsCounter));
}
continue;
}
pBWGenCards.ReportStatus("INSERT Exception: " + _ex.Message);
throw;
}
_numberOfGeneratedCards++;
pCardArray[_cardArrayIndex++] = _cardNumber;
if (++counter % 1000 == 0)
{
pBWGenCards.ReportProgress(counter);
}
}
_tran.Commit();
pBWGenCards.ReportStatus("Completed BatchId: " + pBatchId.ToString("D6") + " Number Of cards: " + pBatchSize);
_result = true;
}
catch (Exception _ex) {
_tran.Rollback();
pBWGenCards.ReportStatus("Generate(loop) Card Numbers Exception: " + _ex.Message);
}
}
}
catch (Exception _ex) {
pBWGenCards.ReportStatus("Generate Card Numbers Exception: " + _ex.Message);
}
return(_result);
}
