/// <summary>
/// Executes the select query.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="query">The query.</param>
/// <returns>A data table containing the result of the query</returns>
public DataTable ExecuteSelectQuery(string connectionString, string query)
{
using (System.Data.H2.H2Connection connection = new System.Data.H2.H2Connection(connectionString, "sa", string.Empty))
{
this.logger.LogVerbose(string.Format("Execute query {0}", query));
connection.Open();
var com = new H2Command(query, connection);
var result = com.ExecuteReader();
var tableResult = new DataTable();
for (int i = 0; i < result.FieldCount; i++)
{
tableResult.Columns.Add(result.GetName(i), result.GetFieldType(i));
}
while (result.Read())
{
var row = tableResult.NewRow();
for (int i = 0; i < result.FieldCount; i++)
{
row[i] = result.GetValue(i);
}
tableResult.Rows.Add(row);
}
return tableResult;
}
}
public static List <String> ReadStrings(this H2Connection connection, String query)
{
var ret = new List <String>();
var reader = new H2Command(query, connection).ExecuteReader();
while (reader.Read())
{
ret.Add(reader.GetString(0));
}
return(ret);
}
public static Dictionary <String, T> ReadMap <T>(this H2Connection connection, String query)
{
var ret = new Dictionary <String, T>();
var reader = new H2Command(query, connection).ExecuteReader();
while (reader.Read())
{
var key = reader.GetString(0);
var value = reader.GetValue(1);
if (value == DBNull.Value)
{
ret[key] = default(T);
}
else
{
ret[key] = (T)value;
}
}
return(ret);
}
public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
{
this.selectCommand = selectConnection.CreateCommand();
this.selectCommand.CommandText = selectCommandText;
}
public H2DataAdapter(H2Command selectCommand)
{
this.selectCommand = selectCommand;
}
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 static String ReadString(this H2Connection connection, String query)
{
var result = new H2Command(query, connection).ExecuteScalar() as String;
return(result);
}
public H2DataAdapter(H2Command selectCommand) => SelectCommand = selectCommand;
public H2DataAdapter(string selectCommandText, H2Connection selectConnection)
{
this.selectCommand = selectConnection.CreateCommand();
this.selectCommand.CommandText = selectCommandText;
}
public H2DataAdapter(H2Command selectCommand)
{
this.selectCommand = selectCommand;
}