public async Task ParameterDescribeTest()
{
await using (var command = new FbCommand("insert into TEST (int_field) values (@value)", Connection))
{
await command.PrepareAsync();
command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;
await command.ExecuteNonQueryAsync();
}
}
private static async Task InsertTestData(string connectionString)
{
await using (var connection = new FbConnection(connectionString))
{
await connection.OpenAsync();
var commandText = @"
insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)
values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)";
await using (var transaction = await connection.BeginTransactionAsync())
{
await using (var command = new FbCommand(commandText, connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer);
command.Parameters.Add("@char_field", FbDbType.Char);
command.Parameters.Add("@varchar_field", FbDbType.VarChar);
command.Parameters.Add("@bigint_field", FbDbType.BigInt);
command.Parameters.Add("@smallint_field", FbDbType.SmallInt);
command.Parameters.Add("@float_field", FbDbType.Double);
command.Parameters.Add("@double_field", FbDbType.Double);
command.Parameters.Add("@numeric_field", FbDbType.Numeric);
command.Parameters.Add("@date_field", FbDbType.Date);
command.Parameters.Add("@time_Field", FbDbType.Time);
command.Parameters.Add("@timestamp_field", FbDbType.TimeStamp);
command.Parameters.Add("@clob_field", FbDbType.Text);
command.Parameters.Add("@blob_field", FbDbType.Binary);
await command.PrepareAsync();
for (var i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = (double)(i + 10) / 5;
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.UTF8.GetBytes("IRow Number " + i.ToString());
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
}
}
}
public override async Task <DataTable> GetSchemaTableAsync(CancellationToken cancellationToken = default)
#endif
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
var tableCount = 0;
var currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction);
try
{
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
await schemaCmd.PrepareAsync(cancellationToken).ConfigureAwait(false);
_schemaTable.BeginLoadData();
for (var i = 0; i < _fields.Count; i++)
{
var isKeyColumn = false;
var isUnique = false;
var isReadOnly = false;
var precision = 0;
var isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
var reader = await schemaCmd.ExecuteReaderAsync(CommandBehavior.Default, cancellationToken).ConfigureAwait(false);
try
{
if (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
isReadOnly = IsReadOnly(reader) || IsExpression(reader);
isKeyColumn = reader.GetInt32(2) == 1;
isUnique = reader.GetInt32(3) == 1;
precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4);
isExpression = IsExpression(reader);
}
}
finally
{
#if NET48 || NETSTANDARD2_0
reader.Dispose();
#else
await reader.DisposeAsync().ConfigureAwait(false);
#endif
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
await schemaCmd.CloseAsync().ConfigureAwait(false);
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
}
finally
{
#if NET48 || NETSTANDARD2_0
schemaCmd.Dispose();
#else
await schemaCmd.DisposeAsync().ConfigureAwait(false);
#endif
}
return(_schemaTable);
}
