public void SubsequentDeletes()
{
var selectSql = "SELECT * FROM test";
var deleteSql = "DELETE FROM test WHERE int_field = @id";
var connection = new FbConnection(Connection.ConnectionString);
var select = new FbCommand(selectSql, connection);
var delete = new FbCommand(deleteSql, connection);
delete.Parameters.Add("@id", FbDbType.Integer);
delete.Parameters[0].SourceColumn = "INT_FIELD";
var adapter = new FbDataAdapter(select);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.DeleteCommand = delete;
var ds = new DataSet();
adapter.Fill(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
}
public void SubsequentDeletes()
{
string selectSql = "SELECT * FROM test";
string deleteSql = "DELETE FROM test WHERE int_field = @id";
// Set up conenction and select/delete commands
FbConnection connection = new FbConnection(this.Connection.ConnectionString);
FbCommand select = new FbCommand(selectSql, connection);
FbCommand delete = new FbCommand(deleteSql, connection);
delete.Parameters.Add("@id", FbDbType.Integer);
delete.Parameters[0].SourceColumn = "INT_FIELD";
// Set up the FbDataAdapter
FbDataAdapter adapter = new FbDataAdapter(select);
adapter.DeleteCommand = delete;
// Set up dataset
DataSet ds = new DataSet();
adapter.Fill(ds);
// Delete one row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
// Delete another row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
// Delete another row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
}
public async Task SubsequentDeletes()
{
await using (var select = new FbCommand("SELECT * FROM test", Connection))
{
await using (var delete = new FbCommand("DELETE FROM test WHERE int_field = @id", Connection))
{
delete.Parameters.Add("@id", FbDbType.Integer);
delete.Parameters[0].SourceColumn = "INT_FIELD";
using (var adapter = new FbDataAdapter(select))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.DeleteCommand = delete;
using (var ds = new DataSet())
{
adapter.Fill(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
}
}
}
}
}
public async Task DeleteTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
}
private void button3_Click(object sender, EventArgs e)
{
try{
FbConnection conn = DBConnect.GetConnection();
string myQuery = "SELECT * FROM TRADEMARKS";
FbDataAdapter emp = new FbDataAdapter(myQuery, conn);
FbCommandBuilder commandBuilder = new FbCommandBuilder(emp);
emp.InsertCommand = new FbCommand("ADD_TRADEMARKS", conn);
emp.InsertCommand.CommandType = CommandType.StoredProcedure;
emp.InsertCommand.Parameters.Add(new FbParameter("@INP_TRADEMARK", FbDbType.VarChar, 200, "TRADEMARK"));
emp.InsertCommand.Parameters.Add(new FbParameter("@INP_DESCRIPTION", FbDbType.VarChar, 500, "DESCRIPTION"));
DataSet ds = new DataSet();
emp.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow newRow = dt.NewRow();
newRow["TRADEMARK"] = textBox2.Text;
newRow["DESCRIPTION"] = textBox3.Text;
dt.Rows.Add(newRow);
emp.Update(ds);
ds.AcceptChanges();
MessageBox.Show("Рядок було зареєстровано в базі даних");
}
catch (Exception ec) {
MessageBox.Show("Виняток: " + ec.Message);//Exception
}
}
public void UpdateClobTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["CLOB_FIELD"] = "ONE THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void DeleteTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void SetCommand(string sql, FbConnection con)
{
FbDataAdapter adapter = new FbDataAdapter(sql, con);
DataSet ds = new DataSet();
adapter.Fill(ds);
FbCommandBuilder cb = new FbCommandBuilder(adapter);
FbCommand cmd = cb.GetUpdateCommand();
adapter.Update(ds);
}
public void UpdateFbDA(DataSet ds, string tableName)
{
try {
openConnection();
FbDA.Update(ds, tableName);
} catch (Exception ex) {
Console.Out.WriteLine(ex.Message);
} finally {
closeConnection();
}
}
public bool UpdateDB(DataTable table)
{
FbCommandBuilder commandBuilder = new FbCommandBuilder(adapter);
try
{
adapter.Update(table);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(Unicode(ex.Message), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
public void UpdateTimeStampTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
var dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
public override bool InsUpdRowDataTable(DataTable tbl, DataRowView row, String ins_SQL, String upd_SQL,
String[] fields)
{
bool res = false;
if (tbl != null && row != null && ins_SQL != String.Empty && upd_SQL != String.Empty && fields != null)
{
using (FbCommand ins_cmd = new FbCommand(ins_SQL, db), upd_cmd = new FbCommand(upd_SQL, db))
{
using (FbDataAdapter ad = new FbDataAdapter())
{
ad.InsertCommand = ins_cmd;
ad.UpdateCommand = upd_cmd;
for (int i = 0; i < fields.Length; i++)
{
ad.InsertCommand.Parameters.AddWithValue("@" + fields[i], row[fields[i]]);
ad.UpdateCommand.Parameters.AddWithValue("@" + fields[i], row[fields[i]]);
}
ad.Update(tbl);
}
res = true;
}
}
else
{
if (tbl != null)
{
throw new Exception(DM.errInfo[2]);
}
if (row != null)
{
throw new Exception(DM.errInfo[3]);
}
if (ins_SQL == String.Empty)
{
throw new Exception(DM.errInfo[0] + " (добавление данных)");
}
if (upd_SQL == String.Empty)
{
throw new Exception(DM.errInfo[0] + " (обновление данных)");
}
if (fields != null)
{
throw new Exception(DM.errInfo[4]);
}
}
return(res);
}
public async Task UpdateDateTest()
{
var dtValue = DateTime.Now;
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DATE_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT date_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (DateTime)await command.ExecuteScalarAsync();
Assert.AreEqual(dtValue.Day, val.Day, "date_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "date_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "date_field has not correct year");
}
await transaction.CommitAsync();
}
}
public async Task UpdateTimeTest()
{
var dtValue = new TimeSpan(5, 6, 7);
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["TIME_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT time_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (TimeSpan)await command.ExecuteScalarAsync();
Assert.AreEqual(dtValue.Hours, val.Hours, "time_field has not correct hour");
Assert.AreEqual(dtValue.Minutes, val.Minutes, "time_field has not correct minute");
Assert.AreEqual(dtValue.Seconds, val.Seconds, "time_field has not correct second");
}
await transaction.CommitAsync();
}
}
public void UpdateDateTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["DATE_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT date_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "date_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "date_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "date_field has not correct year");
}
public void UpdateTimeTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
TimeSpan dtValue = new TimeSpan(5, 6, 7);
ds.Tables["TEST"].Rows[0]["TIME_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT time_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
TimeSpan val = (TimeSpan)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Hours, val.Hours, "time_field has not correct hour");
Assert.AreEqual(dtValue.Minutes, val.Minutes, "time_field has not correct minute");
Assert.AreEqual(dtValue.Seconds, val.Seconds, "time_field has not correct second");
}
public async Task InsertTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
var newRow = ds.Tables["TEST"].NewRow();
newRow["int_field"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGint_field"] = 100000;
newRow["SMALLint_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new TimeSpan(10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
}
public override bool DelRowDataTable(DataTable tbl, DataRowView row, String del_SQL, String[] fields)
{
bool res = false;
if (tbl != null && row != null && del_SQL != String.Empty && fields != null)
{
using (FbCommand del_cmd = new FbCommand(del_SQL, db))
{
using (FbDataAdapter ad = new FbDataAdapter())
{
ad.DeleteCommand = del_cmd;
for (int i = 0; i < fields.Length; i++)
{
ad.DeleteCommand.Parameters.AddWithValue("@" + fields[i], row[fields[i]]);
}
ad.Update(tbl);
}
}
res = true;
}
else
{
if (tbl != null)
{
throw new Exception(DM.errInfo[2]);
}
if (row != null)
{
throw new Exception(DM.errInfo[3]);
}
if (del_SQL == String.Empty)
{
throw new Exception(DM.errInfo[0] + " (удаление данных)");
}
if (fields != null)
{
throw new Exception(DM.errInfo[4]);
}
}
return(res);
}
public void UpdateDecimalTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has not correct value");
}
public void UpdateVarCharTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
public void UpdateFloatTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["FLOAT_FIELD"] = (float)100.20;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT float_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
float val = (float)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual((float)100.20, val, "double_field has not correct value");
}
public void UpdateSmallIntTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["SMALLint_field"] = System.Int16.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT smallint_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
short val = (short)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int16.MaxValue, val, "smallint_field has not correct value");
}
public void InsertTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DataRow newRow = ds.Tables["TEST"].NewRow();
newRow["int_field"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGint_field"] = 100000;
newRow["SMALLint_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new TimeSpan(10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public async Task UpdateVarCharTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT varchar_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (string)await command.ExecuteScalarAsync();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
await transaction.CommitAsync();
}
}
public async Task UpdateDecimalTest()
{
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("select * from TEST where int_field = @int_field", Connection, transaction))
{
using (var adapter = new FbDataAdapter(command))
{
using (var builder = new FbCommandBuilder(adapter))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
using (var ds = new DataSet())
{
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = int.MaxValue;
adapter.Update(ds, "TEST");
}
}
}
}
await transaction.CommitAsync();
}
await using (var transaction = await Connection.BeginTransactionAsync())
{
await using (var command = new FbCommand("SELECT decimal_field FROM TEST WHERE int_field = @int_field", Connection, transaction))
{
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (decimal)await command.ExecuteScalarAsync();
Assert.AreEqual(int.MaxValue, val, "decimal_field has not correct value");
}
await transaction.CommitAsync();
}
}
public void UpdateNumericTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["NUMERIC_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT numeric_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "numeric_field has not correct value");
}
private void button2_Click(object sender, EventArgs e)
{
FbConnection conn = DBConnect.GetConnection();
string myQuery = "SELECT * FROM TABLE_2";
FbDataAdapter emp = new FbDataAdapter(myQuery, conn);
FbCommandBuilder commandBuilder = new FbCommandBuilder(emp);
emp.InsertCommand = new FbCommand("ADDRECORD2", conn);
emp.InsertCommand.CommandType = CommandType.StoredProcedure;
emp.InsertCommand.Parameters.Add(new FbParameter("@INPTEXT", FbDbType.VarChar, 50, "text"));
emp.InsertCommand.Parameters.Add(new FbParameter("@INPVALUE", FbDbType.Integer, 50, "value"));
DataSet ds = new DataSet();
emp.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow newRow = dt.NewRow();
newRow["text"] = textBox2.Text;
newRow["value"] = textBox3.Text;
dt.Rows.Add(newRow);
emp.Update(ds);
ds.AcceptChanges();
}
public void ApplyUpdates()
{
ida.Update(dsGrid, nomePreencherTabela);
}
private void hsSaveDataset_Click(object sender, EventArgs e)
{
try
{
DataSet dsUpdate = dataSet1.GetChanges(DataRowState.Modified);
DataSet dsInsert = dataSet1.GetChanges(DataRowState.Added);
DataSet dsDeleted = dataSet1.GetChanges(DataRowState.Deleted);
string constr = ConnectionStrings.Instance().MakeConnectionString(_dbReg);
var cn = new FbConnection(constr);
string cm1 = _cmd;
var da = new FbDataAdapter(cm1, cn);
var cb = new FbCommandBuilder(da);
da.AcceptChangesDuringUpdate = true;
if (dsUpdate != null)
{
var fc = cb.GetUpdateCommand();
da.UpdateCommand = fc;
if (dsUpdate.Tables[0].Rows.Count > 0)
{
_localNotifies?.AddToINFO($@"Table {dsUpdate.Tables[0].TableName} updated {dsUpdate.Tables[0].Rows.Count} records affected");
da.Update(dsUpdate);
}
}
if (dsInsert != null)
{
var fi = cb.GetInsertCommand();
da.InsertCommand = fi;
_localNotifies?.AddToINFO($@"Table {dsInsert.Tables[0].TableName} added {dsInsert.Tables[0].Rows.Count} records affected");
da.Update(dsInsert);
}
if (dsDeleted != null)
{
var fd = cb.GetDeleteCommand();
da.DeleteCommand = fd;
if (dsDeleted.Tables[0].Rows.Count > 0)
{
_localNotifies?.AddToINFO($@"Table {dsDeleted.Tables[0].TableName} deleted {dsDeleted.Tables[0].Rows.Count} records affected");
da.Update(dsDeleted);
}
}
}
catch (Exception ex)
{
var sb = new StringBuilder();
sb.AppendLine("Views und Tabels ohne Primary Key können nicht editiert werden.");
sb.AppendLine(ex.Message);
MessageBox.Show(sb.ToString(), "Dataset Update failed");
//NotifiesClass.Instance().AddToERROR(AppStaticFunctionsClass.GetFormattedError($@"{Name}-> Savedataset_Click()", ex));
_localNotifies?.AddToERROR(sb.ToString());
}
bindingSource1.DataMember = null;
var ri = RunSQL(_history);
_cmd = ri.lastSQL;
if (ri.lastCommandType != SQLCommandType.@select)
{
return;
}
//ClearDataGrid();
RefreshPLAN();
}
