public H2CommandBuilder(H2DataAdapter adapter)
{
DataAdapter = adapter;
// Letting ADO.NET do its job does not appear to work (yet) :
//if (false)
//{
// adapter.InsertCommand = (H2Command)GetInsertCommand();
// adapter.UpdateCommand = (H2Command)GetUpdateCommand();
// return;
//}
var connection = adapter.SelectCommand.Connection;
var select = adapter.SelectCommand.CommandText.ToLower();
var mat = selectRegex.Match(select);
if (!mat.Success)
{
throw new Exception("Select command not recognized : '" + select + "'");
}
var tableName = mat.Groups[2].Value;
{
var mmat = columnRegex.Match(tableName);
if (mmat.Success)
{
tableName = mmat.Groups[1].Value;
}
}
var columnTypeCodes = connection.GetColumnTypeCodes(tableName);
IList <String> cols = mat.Groups[1].Value.Split(',');
if (cols.Count == 1 && cols[0].Trim().Equals("*"))
{
cols = columnTypeCodes.Keys.ToList();
}
cols = cols.Select(c => c.Trim()).ToList();
var updateCommand = new H2Command(connection);
var insertCommand = new H2Command(connection);
var updateSets = new List <String>();
var updateWheres = new List <String>();
var colasrx = new Regex("\"?(.*)\"? as \"?(.*)\"?");
int nextParam = 0;
var aliases = new Dictionary <String, String>();
foreach (var col in cols)
{
var colasmat = colasrx.Match(col);
String alias;
String columnName;
if (colasmat.Success)
{
alias = colasmat.Groups[2].Value.ToUpper().Trim();
columnName = colasmat.Groups[1].Value.ToUpper().Trim();
}
else
{
alias = columnName = col.ToUpper().Trim();
}
aliases[columnName] = alias;
var paramName = (nextParam++).ToString();
updateSets.Add("\"" + columnName + "\" = ?");//:" + paramName);
var typeCode = columnTypeCodes[columnName];
var dbType = H2Helper.GetDbType(typeCode);
updateCommand.Parameters.Add(new H2Parameter(paramName, dbType)
{
SourceColumn = alias,
DbType = dbType,
Direction = ParameterDirection.Input,
SourceVersion = DataRowVersion.Current
});
}
var pks = connection.GetPrimaryKeysColumns(tableName);
foreach (var pk in pks.Select(c => c.ToUpper()))
{
var columnName = pk;
var paramName = (nextParam++).ToString();
updateWheres.Add("\"" + columnName + "\" = ?");//:" + paramName);
if (!aliases.TryGetValue(columnName, out string alias))
{
alias = columnName;
}
var typeCode = columnTypeCodes[columnName];
var dbType = H2Helper.GetDbType(typeCode);
updateCommand.Parameters.Add(new H2Parameter(paramName, dbType)
{
SourceColumn = alias,
DbType = dbType,
Direction = ParameterDirection.Input,
SourceVersion = DataRowVersion.Original
});
}
var insertValues = new List <String>();
nextParam = 0;
foreach (var columnName in cols.Select(c => c.ToUpper()))
{
var paramName = (nextParam++).ToString();
insertValues.Add("?");//":" + paramName);
if (!aliases.TryGetValue(columnName, out string alias))
{
alias = columnName;
}
var typeCode = columnTypeCodes[columnName];
var dbType = H2Helper.GetDbType(typeCode);
insertCommand.Parameters.Add(new H2Parameter(paramName, dbType)
{
SourceColumn = alias,
DbType = dbType,
Direction = ParameterDirection.Input,
SourceVersion = DataRowVersion.Original
});
}
updateCommand.CommandText = "update " + tableName + " set " + updateSets.Commas() + " where " + updateWheres.Commas();
adapter.UpdateCommand = updateCommand;
insertCommand.CommandText = "insert into " + tableName + "(" + cols.Commas() + ") values (" + insertValues.Commas() + ")";
adapter.InsertCommand = insertCommand;
}
public override DataTable GetSchemaTable()
{
/*
* JDBC reference :
* http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSetMetaData.html
*
* ADO.NET reference :
* http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx
*/
var table = new DataTable();
var ColumnName = table.Columns.Add("ColumnName", typeof(String));
var ColumnOrdinal = table.Columns.Add("ColumnOrdinal", typeof(Int32));
var ColumnSize = table.Columns.Add("ColumnSize", typeof(Int32));
var NumericPrecision = table.Columns.Add("NumericPrecision", typeof(Int32));
var NumericScale = table.Columns.Add("NumericScale", typeof(Int32));
var IsUnique = table.Columns.Add("IsUnique", typeof(bool));
var IsKey = table.Columns.Add("IsKey", typeof(bool));
var BaseServerName = table.Columns.Add("BaseServerName", typeof(String));
var BaseCatalogName = table.Columns.Add("BaseCatalogName", typeof(String));
var BaseColumnName = table.Columns.Add("BaseColumnName", typeof(String));
var BaseSchemaName = table.Columns.Add("BaseSchemaName", typeof(String));
var BaseTableName = table.Columns.Add("BaseTableName", typeof(String));
var DataType = table.Columns.Add("DataType", typeof(Type));
var AllowDBNull = table.Columns.Add("AllowDBNull", typeof(bool));
var ProviderType = table.Columns.Add("ProviderType");
var IsAliased = table.Columns.Add("IsAliased", typeof(bool));
var IsExpression = table.Columns.Add("IsExpression", typeof(bool));
var IsIdentity = table.Columns.Add("IsIdentity", typeof(bool));
var IsAutoIncrement = table.Columns.Add("IsAutoIncrement", typeof(bool));
var IsRowVersion = table.Columns.Add("IsRowVersion", typeof(bool));
var IsHidden = table.Columns.Add("IsHidden", typeof(bool));
var IsLong = table.Columns.Add("IsLong", typeof(bool));
var IsReadOnly = table.Columns.Add("IsReadOnly", typeof(bool));
var ProviderSpecificDataType = table.Columns.Add("ProviderSpecificDataType");
var DataTypeName = table.Columns.Add("DataTypeName", typeof(String));
var DbType = table.Columns.Add("DbType", typeof(DbType)); // not standard !!!
//var XmlSchemaCollectionDatabase = table.Columns.Add("XmlSchemaCollectionDatabase");
//var XmlSchemaCollectionOwningSchema = table.Columns.Add("XmlSchemaCollectionOwningSchema");
//var XmlSchemaCollectionName = table.Columns.Add("XmlSchemaCollectionName");
//var dbMeta = connection.connection.getMetaData();
var tablesPksAndUniques = new Dictionary <String, KeyValuePair <HashSet <String>, HashSet <String> > >();
var meta = Meta;
var nCols = meta.getColumnCount();
table.MinimumCapacity = nCols;
for (int iCol = 1; iCol <= nCols; iCol++)
{
// Beware : iCol starts at 1 (JDBC convention)
var row = table.NewRow();
var name = meta.getColumnName(iCol);
var label = meta.getColumnLabel(iCol);
var tableName = meta.getTableName(iCol);
KeyValuePair <HashSet <String>, HashSet <String> > pksAndUniques;
if (!tablesPksAndUniques.TryGetValue(tableName, out pksAndUniques))
{
pksAndUniques = new KeyValuePair <HashSet <string>, HashSet <string> >(
connection.GetPrimaryKeysColumns(tableName),
connection.GetUniqueColumns(tableName)
);
}
row[ColumnName] = label != null ? label : name;
row[ColumnOrdinal] = iCol - 1;
row[BaseColumnName] = name;
row[BaseSchemaName] = meta.getSchemaName(iCol);
row[BaseTableName] = tableName;
row[ColumnSize] = meta.getColumnDisplaySize(iCol);
row[IsReadOnly] = meta.isReadOnly(iCol);
row[IsKey] = pksAndUniques.Key.Contains(name);
row[IsUnique] = pksAndUniques.Value.Contains(name);
row[DataTypeName] = meta.getColumnTypeName(iCol); // TODO check this !
row[NumericPrecision] = meta.getPrecision(iCol);
row[NumericScale] = meta.getScale(iCol);
var jdbcType = meta.getColumnType(iCol);
var type = H2Helper.GetType(jdbcType);
var dbType = H2Helper.GetDbType(jdbcType);
row[DataType] = type;
row[DbType] = dbType;
row[AllowDBNull] = meta.isNullable(iCol);
table.Rows.Add(row);
}
return(table);
//throw new NotImplementedException();
}