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